R - XLSX: Add one value to an existing excel table

Question

I have a very straight forward Problem, that is rather Standard I think. Unfortunately I have some difficulties to find the correct Syntax to solve it. I simply want to write data (here: one simple integer) to an existing excel table.

I figured out, that a good way to do that is using the package 'xlsx'.

require(xlsx)      # Load the xlsx package

Next I would have to load the file, and load all the sheets.

wb <- loadWorkbook("test.excelfile.xls")    # Load the xls file
sheets <- getSheets(wb)                     # load all sheet objects

Then I enter the values. These are the lines that generate errors.

addDataFrame(data.frame(123), sheets[1])    # Error in addDataFrame(data.frame(123), sheets[1]) :   attempt to apply non-function
addDataFrame(123, sheets[1])                # alternative try, same error

At the end I save the workbook.

saveWorkbook(wb, "test.excelfile.xls")   # not checked yet because error occurs in line above

It is important for me that the already existent xls file is not destroyed. I want to keep all the work sheets, VBA macros and form elements. Only some cells should be changed from within R.

Thank you very much for your help,

Matthias

Answer 1

Finally I managed to do what I was looking for. I googled a bit more for 'Excel connection tools' (see Carl Witthoft's commend) and found that a good package to use is XLConnect.

The following commands write a simple number to one cell. They also leave the VBA macro intact.

require(XLConnect)

# Load workbook; create if not existing
wb <- loadWorkbook("MC_Auswertung.xls", create = TRUE)

# write to the workbook
writeWorksheet(wb, 750, sheet="Auswertung",   # write '750' to sheet 'Auswertung' in workbook 'wb' 
           startRow=8, startCol=4,            # specify the cell to write to
           header=FALSE)                      # do not write a header row

# Save workbook
saveWorkbook(wb)

There is just one thing that strikes me. Before the operation the xls-file had 32.953 byte. After the operations above it only had 28.958 byte. So a significant amount of 'data' disappeared. I have no idea what has gone? The cell contents are still there. So are the diagrams, background and border styles, ...

Looking forward to you commands ;-)