从R写入到excel中的模板，同时保留格式 [英] Write from R into template in excel while preserving formatting

问题描述

我有一个R中的数据框，它要写入excel（或csv），并输出一个漂亮的格式（例如边框，表的标题，而不是从单元格A1开始）。

此刻，我使用函数write.table将我的数据框写入一个csv文件，然后我将数据复制并粘贴到Excel中的文档中，其中我的表格格式化为模板。

当这只是一个数据框时，这不是一个主要问题，但我现在想要为多个数据框执行此操作，并将它们放在excel中的多个选项卡中。 / p>

有没有办法可以自动将我的数据框复制到现有excel电子表格中的所有格式正确设置的特定单元格？

解决方案

正如Joran所说，你有知道可用的。

I have a dataframe's in R which want to write to excel (or csv) and to output in a nice format (e.g. with a border, title for the table, not starting from the cell A1).

At the moment I use the function write.table to write my dataframes into a csv file, and then I copy and paste my data into a document in excel where I have my tables ready formatted as template.

This is not a major issue when it is only one dataframe, but I now want to do it for multiple dataframes and have these across multiple tabs in excel.

Is there a way in which I can automatically copy my dataframes to specific cells in an existing excel spreadsheet with all the formatting correctly set?

解决方案

As Joran said, you have the XLConnect package. Read the documentation or the vignette of that package carefully to know exactly what is possible.

Using XLConnect you normally overwrite the cell styles unless you set the style action to be "none" using

setStyleAction(wb,XLC$"STYLE_ACTION.NONE")

To set you on the right road, a trivial example :

require(XLConnect)
wb <- loadWorkbook("test.xlsx", create=TRUE)
setStyleAction(wb,XLC$"STYLE_ACTION.NONE")

Data <- data.frame(
  a = 1:10,
  b = letters[1:10]
)

writeWorksheet(wb,Data,"aSheet",startRow=1,startCol=1,header=TRUE)

saveWorkbook(wb)

Before

After

---

EDIT : As noted by Dirk Eddelbuettel, you can do the same using the xlsx package. I personally use XLConnect as it can handle both xls and xlsx, and seemed a lot more stable than any of the old packages I used for manipulating EXCEL files. I haven't used the xlsx package yet. You can take a look at the CRAN page on Data Import/Export to know what is available.

这篇关于从R写入到excel中的模板，同时保留格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！