1

I am preparing a report that I will need to re-run on a more or less weekly basis. It needs to go to the client in Excel and I've been using the XLConnect package for R with great success but I've got one problem I can't seem to over come on my own.

Given the following code:

simple <- data.frame(a = c(1,2,3,,4,5), b = c(1,2,3,4,5))

library(XLConnect)
prcntg <- createCellStyle(wb)
setDataFormat(prcntg, format = "0.0")

wb <- loadWorkbook("foo.xlsx", create = FALSE)

sheet <- "bar"
createSheet(wb, sheet)

writeWorksheet(wb, simple, sheet = sheet)
rows <- 1:5
cols <- 1:2
setCellStyle(wb, sheet = sheet, row = rows, col = cols, cellstyle = prcntg)

I want the values to be printed as:

a   | b
1.0 | 1.0
2.0 | 2.0
3.0 | 3.0
etc.

But, they are coming into the worksheet as:

a | b
1 | 1
2 | 2
3 | 3
etc.

How do I get the former and not the latter. Based on the documentation and the post I saw here: https://miraisolutions.wordpress.com/2011/08/31/xlconnect-a-platform-independent-interface-to-excel/

I feel like I am doing everything right, but obviously I'm not.

Choens
  • 1,312
  • 2
  • 14
  • 23
  • I've looked at the format command which works a peach, but XLConnect exports the resulting values as strings, which is what they are and not numbers and I want the client to be able to easily use the report for adhoc analysis/reporting. Excel's math commands don't tend to work very well characters. – Choens Apr 24 '12 at 21:50

1 Answers1

1

The following appears to work for me:

wb <- loadWorkbook("~/Desktop/foo.xlsx", create = TRUE)
prcntg <- createCellStyle(wb)
setDataFormat(prcntg, format = "0.0")

sheet <- "bar"
createSheet(wb, sheet)

writeWorksheet(wb, simple, sheet = sheet)
rows <- 2:6
cols <- 1:2
setCellStyle(wb, sheet = sheet, row = rep(2:6,times = 2), col = rep(1:2,times = 6), cellstyle = prcntg)
saveWorkbook(wb)

This runs with (with a warning). Note the specification of the row and col arguments in setCellStyle. I am, however, reluctant to assume that this will work for you as well, as XLConnect has been acting a little wonky for me lately (I'm on OS X and had to build it from source on 2.15.0, as it failed the CRAN checks so there's no binary).

joran
  • 169,992
  • 32
  • 429
  • 468
  • I'm going to try out your suggestion today. I had not realized that I needed to repeat the row and column information in that manner. Would it be fair to say that ideally it needs to take the form of row = rep( 2:6, times=length(cols) ) and similar for col. – Choens Apr 25 '12 at 13:39
  • In general, I believe so, yes. The difference is functions where the arguments are things like `startRow`, `endRow` etc. where it's a little more explicit that you're specifying ranges rather than each individual cell. Honestly, I was surprised the `setCellStyle` was a little different, and it could still be that my version of XLConnect is behaving a little off. – joran Apr 25 '12 at 14:12
  • That works. It seems, although I am still trying to confirm, that the offset from the writeWorksheet() command is not relevant to the SetCellStyle(), so you have to use absolute dimensions here. – Choens Apr 25 '12 at 19:12
  • But, like you I am getting some warnings that I haven't quite figured out. While not perfect, XLConnect is the best thing I know of for writing – Choens Apr 25 '12 at 19:35
  • @joran could you help me with this issue based on this question http://stackoverflow.com/questions/28479568/setcellstyle-apply-cell-style-percent-to-matrix-using-xlconnect – rmuc8 Feb 12 '15 at 16:39