Improve wb_set_col_widths(width = "auto")

[anticmason]

Hi,

When using wb_set_col_widths with width='auto' for auto-fit column width, the result may not be satisfied, as it doesn't take the column name into consideration but only values, I have to write cycles to calculate the max string-width for each columns(column name and values) and then pull them out to xlsx。
Could it be possible to improve this logics by taking the column name and values into consideration together to set col-widths automatically, like autofit method from xlwings package in python? By the way, could it be possible to optimize or create more function refer to xlwings package,since it is really good in excel automation...

Thanks a lot~
looking forward to your reply~

[JanMarvin]

Hi @anticmason ,

indeed width = "auto" could use another round of improvements. I have tweaked it a bit prior to the recent release #1239 , but there are things left out like handling of strings or number formatting (see #1238). Unfortunately there I have doubts that we will ever be able to mimic perfectly what spreadsheet software does, since we'd have to handle different font widths, line breaks etc. And even Excel stumbles in creating identical automatic column widths on various monitors and operating systems.

Anyhow, maybe we shouldn't strive for perfection and humbly improve the current code. Could you create an example of what specifically you want to see improved?

Regarding xlwings, I do not know the package and cannot say what it is doing. My impression—from a video on their website—is, that they are using something similar to RDCOMClient? That is a little different to what openxlsx2 is doing, using just R and XML. The latter RDCOMClient only works on Windows if I remember correctly and probably requires MS Office. Therefore it is unlikely to do anything in that regard. But obviously you could create something like a openxlsx2 to xlwings wrapper package, if you prefer their functions.

[anticmason]

Here comes some codes to accomplish the auto column width by calculate the max width for both title name(field name) and its values and then bind them together to calculate max for all, then use it as a parameter in $set_col_widths(cols=...) from " for circle"

【some functions comes from package：collapse，such as : dapply，fmax，qdf】

summary_list=data.frame(name=c('sheet_name1','sheet_name2,'sheet_name3'),sheet=c('summary_sheet1','summary_sheet2','summary_sheet3'))

title1=transpose(qDF(names(get(summary_list$sheet[1]))))
names(title1)=names(summary_sheet1)

title1_width=dapply(title1 %>% mutate(across(seq_len(fncol(title1)),str_width)),max,MARGIN = 2)

sheet1_width=dapply(summary_sheet1 %>% mutate(across(seq_len(fncol(summary_sheet1)),str_width)),fmax,MARGIN = 2)

sheet1_width = dapply(bind_rows(title1_width,sheet1_width),max,MARGIN = 2)

I think set_col_width() should calculate every column(include title name and its values) to decide the correct column width, not just its values....... IF the string width of a column title longer than its values's string width, the column will be indented a lot that the title couldn't be seen normally. In contrast to Excel, when you double click the column width,it will appear to see the title name and its values both。

Looking forward to your reply
Thanks~