Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Export data into specific cells in Excel sheet

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.

like image 767
Dom Avatar asked Mar 25 '15 16:03

Dom


2 Answers

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)
like image 191
rmuc8 Avatar answered Oct 13 '22 23:10

rmuc8


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)
like image 33
Molx Avatar answered Oct 13 '22 23:10

Molx