R-自动调整Excel列宽 [英] R - Autofit Excel column width

问题描述

如何使用openxlsx自动调整列宽?

How do I autofit the column width using openxlsx?

我的一列中有一个日期变量(例如21-08-2017)，如果使用Excel中的ctrl+c复制，并正常粘贴到其他地方，则显示为#######(如果增加列宽以显示内容) Excel，通常会粘贴).我想将该重复性任务集成到我的代码中.这是我现在正在使用的:

One of my columns has a date variable (eg. 21-08-2017) and if copied using ctrl+c from Excel, and pasted normally elsewhere, it shows like #######(if column width is increased to show the content in Excel, it pastes normally). I want to integrate that repeatitive task into my code. Here is what I am using right now:

WB <- loadWorkbook(File)
addWorksheet(WB, Sheet)
writeDataTable(WB, Sheet, DF, withFilter=F, bandedRows=F, firstColumn=T)
saveWorkbook(WB, File,  overwrite =TRUE)

我在此处附加了整个相关代码，我也在基于表值进行条件格式设置. 请在此处建议一种集成自动调整列宽的方法.

I have attached the whole relevant code here, I am also doing conditional formatting based on the table values. Please suggest an way to integrate autofit column width in here.

默认情况下，R的XLSX输出具有默认的8.43列宽，我想将其设置为根据单元格内容自动调整，或者为每列手动设置.

By default, XLSX outputs from R has the default 8.43 columnwidth, I want to either set it to autofit as per cell contents, or set it manually for each column.

致Mod:这是我正在尝试使用openxlsx在R中解决的问题.无论如何，谢谢您的关注.

To Mod: This is a problem I am trying to solve in R, using openxlsx. Anyway, thanks for the attention.

推荐答案

好的，我在文档中进行了又一次广泛的搜索后才知道.似乎几乎没有人从在线解决方案的匮乏中实际使用此功能...

Ok, I got it after another extensive search in the documentation. It seems very few people actually use this from the dearth of solutions online...

setColWidths(WB, Sheet, cols = 1:ncol(DF), widths = "auto")

但是，这仍然不能给出期望的结果，date列仍然很短并且显示########；而列标题也不合适(因为它们的格式为粗体).

However, this still does not give the desired result, the date column is still a bit short and shows ########; while the column headers are not fitting as well (as they are formatted bold).

最后，选择添加c(7.5, 10, "auto", ...)代替"auto"，它不是完全动态的，但现在可以解决此问题.希望看到更好的答案.

Finally, chose to add c(7.5, 10, "auto", ...) replacing just "auto", it is not totally dynamic, but solves the issue for now. Hope to see better answers.

这篇关于R-自动调整Excel列宽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！