Column formatting from R to Excel (with package=xlsx), when you do not know column indices ahead of time (is there a better way)?

excel, r, xlsx

Solution

I don't know if you still need it, but I spent some time "fighting" with the `xlsx` package, in order to produce Excel files from R containing a lot of tables, without knowing in advance the number of the columns, so here's my strategy.

The key point is the creation of the colStyle list; instead of

default.format = list(
  '1'=csdefault,
  '2'=csdefault,
  '3'=csdefault,
  '4'=csdefault)

a shortcut is to create the list as a vector of `list` objects, and then assign the names to its elements:

default.format <-  rep(list(csdefault), 4) # style for the first 4 columns
dollar.format <- rep(list(csdollar), (dim(iris)[2]-length(default.format))) # style for remaining columns
format <- c(default.format, dollar.format) # create the colStyle list
names(format) <- seq(1, dim(iris)[2], by = 1) # assign names to list elements

# add dataframe to the workbook
addDataFrame(iris, sheet, startRow=3, startColumn=1, colStyle=format, colnamesStyle = csTableColNames)

Problem

I've been toying around with the xlsx package as a way of exporting output from R, and I'm having problems setting column data formats when you do not know (beforehand) what columns will be formatted which way. As an aside, I found this most fantastic post on tradeblotter a huge help in getting me to this point. I highly recommend it: background post from Tradeblotter blog In my use case, I'm generating reports monthly, and each month the data.frame to be exported will increment by 1 or more columns (that then need to be formatted correctly). I do not want to manually go in and set column names in the code, but would rather have R count the number of columns for me, then feed a vector of indices to the xlsx code. After studying the structure of the colStyle list objects, I was able to create this workaround, but I have to believe that there is a better way, and I'm looking for some help in finding it. Thanks in advance! Chris ``` require(xlsx) iris$cost <- rbinom(nrow(iris), 3, .85)+1000 iris$cost2 <- rbinom(nrow(iris), 3, .85)+1000 test <- createWorkbook() # Define some cell styles within that workbook csSheetTitle <- CellStyle(test) + Font(test, heightInPoints=18, isBold=TRUE) csdollar <- CellStyle(test, dataFormat=DataFormat("$###,##0.00")) # ... for ratio results csdefault <- CellStyle(test, dataFormat=DataFormat("0.00")) # ... for ratio results csTableColNames <- CellStyle(test) + Font(test, isBold=TRUE) + Alignment(wrapText=TRUE, h="ALIGN_CENTER") + Border(color="black", position=c("TOP", "BOTTOM"), pen=c("BORDER_THIN", "BORDER_THICK")) csTableColNames <- CellStyle(test) + Font(test, isBold=TRUE) + Alignment(wrapText=TRUE, h="ALIGN_CENTER") + Border(color="black", position=c("TOP", "BOTTOM"), pen=c("BORDER_THIN", "BORDER_THICK")) sheet <- createSheet(test, sheetName = names(ytddflist)[i]) rows <- createRow(sheet,rowIndex=1) sheetTitle <- createCell(rows, colIndex=1) setCellValue(sheetTitle[[1,1]], "iris data with cost ($)") setCellStyle(sheetTitle[[1,1]], csSheetTitle) default.format = list( '1'=csdefault, '2'=csdefault, '3'=csdefault, '4'=csdefault) dollar.format =list( 'notused'=csdollar) # here is where I create the dollar.format list object that will eventuall be fed to addDataFrame dollar.format.list <- rep(dollar.format, 2) names(dollar.format.list) <- as.character(c(6, 7)) addDataFrame(iris, sheet, startRow=3, startColumn=1, colStyle=c(default.format,dollar.format.list), colnamesStyle = csTableColNames) saveWorkbook(test , "iriswithdollarx2.xlsx") ```

Original source