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?
173k 33 33 gold badges 434 434 silver badges 480 480 bronze badges asked Jun 27, 2012 at 14:48 user1165199 user1165199 6,629 14 14 gold badges 45 45 silver badges 60 60 bronze badgesCSV files are text-only. You cannot include any formatting. You can use R (D)COM to write to and format Excel files from R.
Commented Jun 27, 2012 at 14:54@joran The question contains an important part : keeping the formatting of the original template. Go try that.
Commented Jun 27, 2012 at 15:26 @JorisMeys Yeah, ?setStyleAction . Commented Jun 27, 2012 at 15:34@joran I know (see my answer). But if I have to downvote every question that can be answered by scrolling through quite some pages of manual, there wouldn't be many questions left. This is a perfectly valid question. It took a while of using XLConnect before I encountered that feature. It's not in the vignette, and very few help pages link to that specific information.
Commented Jun 27, 2012 at 15:43As 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
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.
answered Jun 27, 2012 at 15:24 Joris Meys Joris Meys 108k 31 31 gold badges 226 226 silver badges 265 265 bronze badges FWIW the "xslx" package does that too. Commented Jun 27, 2012 at 15:58 Thanks joris, much appreciated! Commented Jun 27, 2012 at 16:04@DirkEddelbuettel; How do we do this in using xlsx package? I did not find any function that can save the formatting of an entire sheet from being preserved?
Commented Jul 20, 2012 at 18:49I've done quite a bit of writing from R to .xlsx files and prefer the openxlsx package much more than xlsx . My reasoning is that I always had troubles getting my OS (64-bit) to work easily with rjava (a dependency of xlsx ). openxlx doesn't require rJava which made my life--at the time, at least--much easier.
Commented May 25, 2016 at 16:20openxlsx works great for 99% of the time but some 3rd party programs I use reject the openxlsx xlsx files and claim they cannot read the excel document. If I open the excel file, then save and retry it always works. Anyone know a workaround for this?
Commented Oct 4, 2018 at 18:02This is very easy to do now with openxlsx2 :
library(openxlsx2) template wb_add_data(x = iris) |> wb_save(file = "Book2.xlsx") template If you do not specify file then by default it will overwrite the workbook.
Book1
I tried to include many formats (font face, fill, text color, borders, column width, row height, etc):
Book2
Here is a function that is based on openxlsx where you specify the workbook ( from_wb ) and sheet name/location ( from_sheet ) that has the styles and the workbook ( to_wb ) and sheet name/location ( to_sheet ) to which you want to transfer the style:
Note: I used the purrr and glue packages as well, but this could be re-written in base R.
copyStyle # get all sheet names from workbooks from_sheets if (is.numeric(to_sheet)) < to_sheet # if sheet name given check that it exists if (is.character(from_sheet) && !from_sheet %in% from_sheets) < stop(glue::glue("was not found in from_wb")) > if (is.character(to_sheet) && !to_sheet %in% to_sheets) < stop(glue::glue("was not found in to_wb")) > # get from_wb sheet styles from_styles
Usage
library(openxlsx) wb Input
getStyles.xlsx is a local Excel workbook with multiple styles on several sheets:
Output
