0

I am trying to replace the data in a few sheets of an existing excel workbook (there are several sheets with formulae in it which remain untouched). I am using the package XLConnect for the same. The idea is to read an existing workbook , clear the data in the required sheets and then write new dataframes in to them. After this, the file is saved both as the initial version as well as another version with the date. However the syntax saveWorkbook is taking a long time to execute and in the end is throwing up the following error Error: OutOfMemoryError (Java): Java heap space

I installed rJava and ran the following as well: options(java.parameters = "-Xmx2048m")

Is there any alternate way to do it?

Following is the code:

xlWb <- loadWorkbook("C:\\\\Users\\z003b1d\\Desktop\\Drop_Data_DB2_Template.xlsx")
wbnames <- as.vector(getSheets(xlWb)) ##sheetNames

clears the data in the existing template

clearSheet(xlWb, sheet= c("Dept Var to Prior Day-FC RDC",           "Dept Var to Prior Day-BP UDC",    "Dept Var to Prior Day-BP",         "Dept Var to Prior Day - FC UDC",               "Drop_Data_DB2", "Forecasts"))

rewrites the data in the required sheets

writeWorksheet(xlWb,list(i1= rdc_finalsorted, i2= udc_finalsorted, i3= rdc_bpr_finalsorted, i4= udc_fcs_finalsorted, i5= data, i6= forecast)  , sheet= c("Dept Var to Prior Day-FC RDC",     "Dept Var to Prior Day-BP UDC",            "Dept Var to Prior Day-BP",         "Dept Var to Prior Day - FC UDC",    "Drop_Data_DB2", "Forecasts"), startRow =1, startCol =1,  header= T, rownames = NULL)

saves the workbook

 saveWorkbook(xlWb,"C:\\\\Users\\z003b1d\\Desktop\\Drop_Data_DB2_Template.xlsx")

creates a version with today's date

saveWorkbook(xlWb, paste0("C:\\\\Users\\z003b1d\\Desktop\\Drop_Data_DB2_",Sys.Date(),".xlsx"))
rhitima
  • 13
  • 1
  • 5
  • 2
    is it possible to use the `openxlsx`-package? if so: then try: https://stackoverflow.com/questions/34172353/how-do-i-modify-an-existing-a-sheet-in-an-excel-workbook-using-openxlsx-package#34242877 – Wimpel Dec 03 '18 at 12:52
  • Thanks a lot it, it did work. However if the sheets have data from before thats not getting over written completely, Is there a way to clear the data in the sheet before writing in to it? – rhitima Dec 06 '18 at 10:51
  • delete the entire sheet, en then re-create it under the same name – Wimpel Dec 06 '18 at 11:06
  • few of the sheets are connected to formulaes in a different sheet so if the delete it will the tabs with formulae stay unaffected? – rhitima Dec 06 '18 at 11:48
  • probably not... – Wimpel Dec 06 '18 at 11:49
  • is there any syntax like clearSheet in XLConnect in openxlsx? – rhitima Dec 06 '18 at 12:05

0 Answers0