R - XLSX: Add one value to an existing excel table
I have a very direct problem, it is more of a standard I think. Unfortunately, I am having some difficulty to find the correct syntax to solve it. I just want to write data (here: one simple integer) to an existing excel table.
I realized that a good way to do this is to use the "xlsx" package.
require(xlsx) # Load the xlsx package
Next, I will need to upload a file and upload all sheets.
wb <- loadWorkbook("test.excelfile.xls") # Load the xls file
sheets <- getSheets(wb) # load all sheet objects
Then 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
I save the book at the end.
saveWorkbook(wb, "test.excelfile.xls") # not checked yet because error occurs in line above
It is important for me that the already existing xls file is not destroyed. I want to keep all worksheets, VBA macros and form elements. Only some cells need to be changed from R.
Many thanks for your help,
Matthias
source to share
Finally, I managed to do what I was looking for. I did a little googling for Excel tools (see Carl Witthoft commend) and found a good package to use is XLConnect.
The following commands write a prime number into 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 only one thing that amazes me. Before the xls operation, the file was 32.953 bytes. After operations on it, there were only 28.958 bytes. Thus, a significant amount of "data" has disappeared. I have no idea what's gone? The cell content is still present. There are also charts, backgrounds and border styles, ...
We are looking forward to your commands; -)
source to share