0

I like using Excel as a poor man's database for storing data dictionaries and such, because Excel makes it super easy to edit the data in there without the pains of installing a RDBMS.

Now I hit an unexpected problem. I can't find a simple way to rewrite just one of the worksheets, at least not without reading and writing the whole file.

write.xlsx(df,file ="./codebook.xlsx",sheetName="mysheet",overwrite=F)

Complains file exists. With overwrite=T, my sheets are lost.

Jernej Kaše
  • 1
  • 1
  • 1
    Possible duplicate of [using write.xlsx to replace an existing sheet with R package xlsx](http://stackoverflow.com/questions/28053185/using-write-xlsx-to-replace-an-existing-sheet-with-r-package-xlsx) – milos.ai Oct 14 '15 at 08:04
  • You have several options available to you . . . library(xlsx) #load the package # we'll assume your spreadsheet will be 20 rows & 8 columns m <- (matrix('',nrow = 20,ncol = 8)) # '' removes NA from your final spreadsheet # place values in specific cells m[10,2] <- c("B10") # as B10 is in row 10 and column 2 m[20,8] <- c("H20") # export as xlsx write.xlsx(x = m, file = "C:/Users/rshuell001/Desktop/excel_files/Book1.xlsx",sheetName = "Sheet1", row.names = FALSE, col.names=FALSE) ******** ******** ******** ******** ******** ******** ******** ******** – ASH Oct 14 '15 at 16:26
  • Also . . . library(xlsx) # load file contents file <- "C:/Users/rshuell001/Desktop/excel_files/Book1.xlsx" wb <- loadWorkbook(file) sheets <- getSheets(wb) sheet <- sheets[[1]] # or another # data to put into B10:H20 range data <- matrix(runif(7*11), nrow=11, ncol=7) # modify contents addDataFrame(data, sheet, col.names = FALSE, row.names = FALSE, startRow = 10, startColumn = 2) # save to disk saveWorkbook(wb, file) – ASH Oct 14 '15 at 16:26
  • One more . . . library(XLConnect) wb <- loadWorkbook("C:/Users/rshuell001/Desktop/excel_files/Book10.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) – ASH Oct 14 '15 at 16:26

0 Answers0