Follow-up: Write from R into template in excel while preserving formatting

Question

These are two related follow-up questions to Write from R into template in excel while preserving formatting that I am writing here in a new thread, as I cannot comment the answers in the other one.

Actually Joris Meys answer/code (of the 27th June) does not work for me and I tried this already several times.

So again: the aim is to write with R an excel file while using the formatting in a template-file or just preserving it. Two different tools were mentioned in the answer: XLConnect and xlsx.

Joris Meys answer is based on XLConnect. But using the exact same code my result is that the cells that are filled with new content become white and without any preserved formatting. All the other cells (where no new content is inserted) indeed take over the previous style.

I'm using the newest RGui (32-bit) V. 2.15.1 in WinXP and the newest versions of XLConnect and xlsx (and I also just checked that all the other packages in R are up to date).
Ok a second question related to that: The thread mentions xlsx as an alternative. This information seems to me erroneous, too. I checked the website and the developer seems to be working on a solution on that. Currently I only see the possibility in xlsx to write the formatting manually in code, for example like this:
```
cs2 <- CellStyle(wb) +    
  Font(wb, name="Courier New", isBold=TRUE) +   # add a Font
  Borders(col="blue", position=c("TOP", "BOTTOM"), pen="BORDER_THICK") +  # add borders    
  Alignment(h="ALIGN_RIGHT") 
```
(Source: http://code.google.com/p/rexcel/wiki/LowLevelAPI)

It would be really cool if someone could give a way of writing excel-files while preserving formats with R.

Edit: This is the code (only difference "Sheet1" instead of "aSheet" but I also tried other variants.

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,"Sheet1",startRow=1,startCol=1,header=TRUE)

saveWorkbook(wb)

Joris's XLConnect code works perfectly for me as well. Can you show the exact code you're running that doesn't work? — joran, Sep 28 '12 at 14:45
Try creating the workbook in Excel first, add some formatting, and then change the code slightly to `create = FALSE`. — joran, Sep 28 '12 at 14:56
Thanks for your fast answer! Just inserted the code into the question. — Nebukadnezar, Sep 28 '12 at 14:56
Well, I'm at a loss, then, because we seem to be running the exact same code but getting different results. — joran, Sep 28 '12 at 15:04
Ok Thanks anyway, @joran. I will try later at home whether this is a java-issue because this machine runs a version 6 and the newest is 7 (but I cannot do an update on this machine). — Nebukadnezar, Sep 28 '12 at 16:03
You could paste your raw data in an area that has no formatting at all, preferably a hidden tab, and have the pretty (i.e. formatted) cells use references to the raw data. — flodel, Sep 28 '12 at 20:36
What type of template formatting are you using? XLConnect currently only deals with direct cell styles but not with row/column styles. Thus, for example, if in your template you have a row style (selected a whole row and then applied some formatting) and then use XLConnect to write (new, non-existing) cells in that row then these cells would be created with the default cell style and not the row's cell style. — Martin Studer, Oct 01 '12 at 16:16
@MartinStuder : Yes! This did solve the issue! Thanks a lot! It works by formatting the cells directly while I tried "selecting all" or selecting rows/columns as you said. Gosh, I did not think at this possibility! — Nebukadnezar, Oct 02 '12 at 12:22

score 0 · Accepted Answer · answered Aug 29 '13 at 13:12

Question 1:

Example code for formating cells:

library('xlsx')

# create the workbook 
wb <- createWorkbook()
sheet <- createSheet(wb, sheetName="addDataFrame1")
data <- data.frame(mon=month.abb[1:10], day=1:10, year=2000:2009,
                   date=seq(as.Date("1999-01-01"), by="1 year", length.out=10),
                   bool=c(TRUE, FALSE), log=log(1:10),
                   rnorm=10000*rnorm(10),
                   datetime=seq(as.POSIXct("2011-11-06 00:00:00", tz="GMT"), by="1 hour",
                                length.out=10))
cs1 <- CellStyle(wb) + Font(wb, isItalic=TRUE) # rowcolumns
cs2 <- CellStyle(wb) + Font(wb, color="blue")
cs3 <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border() # header
addDataFrame(data, sheet, startRow=3, startColumn=2, colnamesStyle=cs3,
             rownamesStyle=cs1, colStyle=list('2'=cs2, '3'=cs2))

# save the workbook ...
saveWorkbook(wb, "test.xlsx")

See the output file.

Question 2:

cs2 <- CellStyle(wb) +    
  Font(wb, name="Courier New", isBold=TRUE) +   # add a Font
  Borders(col="blue", position=c("TOP", "BOTTOM"), pen="BORDER_THICK") +  # add borders    
  Alignment(h="ALIGN_RIGHT")

You just have to put variables in the parameter you use in the style.

Follow-up: Write from R into template in excel while preserving formatting

1 Answers1