Date formatting in other calendar than English

[asltjoey]

Hi Jan,

Your package ist supergeil and extremely helpful. Thank you so much for saving me days, if not weeks, in successfully taking Excel workbook templates and filling data onto them last week after struggling with the former openxlsx.

I have got a further question as to whether it is possible to do the date formatting in other calendar than English, taking Thai - Buddhist for example (see photo). What I have done is to manually format it first in Excel on certain cells/ranges, and then use 'wb_get_cell_style' and 'wb_set_cell_style' functions the same way as 'copy & paste formatting'. Here is my simplified example; the cells with manual Buddhist date formatting reside in the A2:K2 range.

wbx = wb_load(file=file.path(fpath,"my_template.xlsx")) sty = wb_get_cell_style(wb = wbx, sheet = "Rawdata", dims = "A2:K2") wb_add_data(wb = wbx, sheet = "Rawdata", x = my_df, startRow = 2, startCol = 1, colNames = F) wb_set_cell_style(wb = wbx, sheet = "Rawdata", dims = "A3:K100", style = sty)

Million thanks!
Joe Prakasit

[JanMarvin]

Hi Joe,

thanks for your kind words, had to laugh at the supergeil part (do you speak any German?). To answer your question, its possible to so in openxlsx2 (and also in openxlsx)!

library(openxlsx2)

wb <- wb_workbook() %>% 
  wb_add_worksheet("Rawdata") %>% 
  wb_add_data(x = Sys.Date(), colNames = FALSE) %>% 
  wb_add_numfmt( numfmt = "[$-1070000]d/mm/yyyy;@") %>% 
  wb_open()

You can also set your date format to thai with the `openxlsx2.

options("openxlsx2.dateFormat" = "[$-1070000]d/mm/yyyy;@")

I got the correct date format string this way: create a formatted file in Excel, load it with wb_load() and inspecting the correct numfmt found in wb$styles_mgr$styles$numFmts.

Unfortunately, our date detection does not yet work for this date format. It was always a question of when it would break, not that it would break. I'll open an issue for the date detection, but I'm not sure when we'll be able to fix this. You can override it manually.

# read the above workbook
> df <- wb_to_df(wb, colNames = F); df
      A
1 44859

# convert this to a date
> convert_date(df$A)
[1] "2022-10-25"