Problems with number format after creating .xlsx-file with openxlsx

[IrinaJanzen]

I am creating an .xlsx file with data similar to the example data below. When I try to do calculations in the new excel file, the number format is not correctly applied.

I need to click into a cell, then press enter, so that Excel recognizes that the numbers are actually numbers and then calculations such as sums work.

As I have larger datasets and a lot of files I need to create, changing the format manually is not an option. Does someone know how to solve this directly in R?

# Packages
pacman::p_load(tibble,
               openxlsx)

# Data
example_data <- tibble(x1 = c(NA, "category1", "category2"),
                       x2 = c("value1", 5, 6),
                       x3 = c("value2", 2, 1))

# Format
number_style <- createStyle(numFmt = "NUMBER",
                            halign = "center")

# Workbook
wb <- createWorkbook()

addWorksheet(wb, "example")

writeData(wb,
          sheet = "example",
          example_data,
          colNames = FALSE)

addStyle(wb,
         sheet = "example",
         number_style,
         rows = c(2:3),
         cols = c(2:3),
         gridExpand = TRUE)

saveWorkbook(wb, "example.xlsx", overwrite = TRUE)

[FJCC]

I suspect the problem is that you are storing the columns x2 and x3 as text because those columns contain "value1" and "value2". Try

example_data <- tibble(Category = c("category1", "category2"),
                       value1 = c( 5, 6),
                       value2= c( 2, 1))
...
writeData(wb,
          sheet = "example",
          example_data,
          colNames = TRUE)

If that gives you the desired result, can you tolerate having a header on the category column?

[IrinaJanzen]

You are right, it was because of that, thanks! As I read the data in from some other source and I need to keep the format of the first line as is my code looks a bit different, but with the same result. If someone else should need it:

# Packages
pacman::p_load(tibble,
               dplyr,
               openxlsx)

# Data
example_data <- tibble(x1 = c(NA, "category1", "category2"),
                       x2 = c("value1", 5, 6),
                       x3 = c("value2", 2, 1))

example_dat_num <- mutate(example_data,
                          across(num_range("x", 2:ncol(example_data)), as.numeric))

# Format
number_style <- createStyle(numFmt = "NUMBER",
                            halign = "center")

# Workbook
wb <- createWorkbook()

addWorksheet(wb, "example")

writeData(wb,
          sheet = "example",
          example_dat_num,
          colNames = FALSE)

writeData(wb,
          sheet = "example",
          example_dat[1, ],
          colNames = FALSE)

addStyle(wb,
         sheet = "example",
         number_style,
         rows = c(2:3),
         cols = c(2:3),
         gridExpand = TRUE)

saveWorkbook(wb, "example.xlsx", overwrite = TRUE)

[dromano]

Here is what I think is a simpler approach:

example_data <- 
  data.frame(
    col1 = 1:2, 
    col2 = 3:4, 
    row.names = c('row1', 'row2')
    )

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "example")

writeData(wb,
          sheet = "example",
          example_data,
          rowNames = T,
          colNames = T)

number_style <- createStyle(halign = "center")
addStyle(wb,
         sheet = "example",
         number_style,
         rows = c(2:3),
         cols = c(2:3),
         gridExpand = TRUE
         )

saveWorkbook(wb, "example.xlsx")

^{Created on 2024-07-19 with reprex v2.0.2}