Export data into specific cells in Excel sheet

Question

I need to export a data frame in R to an Excel sheet, which can be done via:

require("xlsx")
write.xlsx(x, file, sheetName="Sheet1", col.names=TRUE, 
    row.names=TRUE, append=FALSE, showNA=TRUE)

My exported data will then be pasted beginning in cell "A1" of the Excel sheet. I can also set append=TRUE so the data will be added at the end of the sheet.

Is there a possibility to put it into a specific cell? I need to export the data frame to a range beginning at cell C10. Is this possible?

Update: The sheet contains data in rows 1-9 as well as columns A-B. Therefore it is not possible to just add empty cells to the data frame and paste it into the excel sheet, because those empty cells would erase the data.

Solution 1

You can do it with the package XLConnect.

library(XLConnect)
wb <- loadWorkbook("File_result.xlsx"), create = TRUE)
createSheet(wb, name = "Sheet1")

# here, you can set the startRow and startCol. Col A = 1, B = 2,...
writeWorksheet(wb,x,"Sheet1",startRow = 10, startCol = 3, header = TRUE)

# automatically adjust column width
setColumnWidth(wb, sheet = "Sheet1", column = 3:4, width = -1)
saveWorkbook(wb)

Solution 2

You can fill the empty cells with empty data:

data <- matrix(1:100, ncol=5)
col.offset <- 2
row.offset <- 9
emptycols <- matrix("", ncol=col.offset, nrow=nrow(data))
data <- cbind(emptycols, data)
emptyrows <- matrix("", nrow=row.offset, ncol=ncol(data))
data <- rbind(emptyrows, data)
write.table(data, "test.csv", row.names=FALSE, col.names=FALSE)

Or, if you want to keep the original data:

data <- matrix(1:100, ncol=5)
col.offset <- 2
row.offset <- 9
orig.data <- as.matrix(read.csv("test.csv", header=FALSE, stringsAsFactors=FALSE))
orig.cols <- orig.data[1:nrow(data), 1:col.offset]
data <- cbind(orig.cols, data)
orig.rows <- orig.data[1:row.offset, 1:ncol(data)]
data <- rbind(orig.rows, data)

Comments

[Molx]

Yes, this code would erase original data. In that case instead of creating empty cols and rows you'd have to read the original data first, and then it would be possible to bind those to the generated new data.

[Dom]

I thought of this way already. It is a solution, but not a very nice one. Is there an easier way?

[Molx]

There you go, I take it that you can adapt to read.xlsx. Note that if the original data has at least one string, all data will be too because it is being saved as a matrix and not as a data.frame.

[PM0087]

It should be mentioned (at 2020) that if XLSX package (with its dependencies) is installed, both XLSX and XLConnect should be uninstalled (with XLSX dependencies) and XLConnect should be installed again, due to some conflict in the dependencies and packages.