0

I am trying to create and fill an excel sheet. First, I load a huge xlsx file with many sheets (test4.xlsx). Afterwards I create an empty sheet and than fill it out with a relatively large DataFrame (5000 rows and 80 columns). The first three lines of my code works flawless! At the end, I try to save the workbook, but unfortunately I get the following error(see below). 

wb = openxlsx::loadWorkbook("test4.xlsx")
openxlsx::addWorksheet(wb, "New2")
openxlsx::writeDataTable(wb, sheet = "New2", out, startRow = 12)
openxlsx::saveWorkbook(wb, file = "test4.xlsx", overwrite = T)

**Error in fill$fillFg : $ operator is invalid for atomic vectors**

I tried to repilcate the same thing with an empty Workbook, all four lines work perfectly. Do you have an idea, what causes the error to appear? Have you already worked with multisheet excel files trying to add or update a particular sheet. XLSX package doesn't work as well, I get an error message:

**Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
  java.lang.OutOfMemoryError: Java heap space**

The output of traceback:

traceback()

5: .self$createFillNode(style)
4: .self$updateStyles(this.sty)
3: .self$preSaveCleanUp()
2: wb$saveWorkbook()
1: openxlsx::saveWorkbook(wb, file = "test4.xlsx", overwrite = T)
And_R
  • 1,647
  • 3
  • 18
  • 32
  • Could you share an excerpt of `out`? – Hayden Y. Oct 01 '19 at 22:02
  • It's a data frame with dim = c(5000,80). – And_R Oct 01 '19 at 22:11
  • 1
    And_R, it's probably going to be difficult for us to reproduce this without your sample data (as that seems rather important): I tried this using `mtcars` and could not reproduce your error. (It might be informative to know [which use of `fill$fillFg`](https://github.com/awalker89/openxlsx/blob/ead0038c59a227faa5c03f13aa7c6211f63dc9e0/R/StyleClass.R) is causing the error, perhaps you could also include the output from `traceback()` immediately after the error occurs.) – r2evans Oct 01 '19 at 23:30
  • 1
    Regarding your java error with XLSX, try running `options(java.parameters = "-Xmx4000m")` before you run the XLSX functions. That's assuming you have 4GB of RAM. If that doesn't work and you have 8GB, run `options(java.parameters = "-Xmx8000m")` – Riley Finn Oct 02 '19 at 03:39
  • @r2evans I have included the output of traceback, but do not think it would be helpful. – And_R Oct 02 '19 at 08:08
  • 1
    I placed a debug on `wb$createFileNode` when running the code above, and it was not called, suggesting that your minimal working example is missing something key in your real use. Are you explicitly using styles in your real code? If not, is there any cell styling in your `test4.xlsx` when you load it? (Caveat: I *think* that `debug(wb$createFillNode)` should work, but since I'm not fluent in `openxlsx`-styling, I confess it might be naive.) – r2evans Oct 02 '19 at 14:08
  • @r2evans the file text4.xlsx consist of one Source sheet, this is in my case the sheet "New2" and many pivot tables and different fancy excel stuff with different styles based on this "New2" source data.table, I have to update. – And_R Oct 02 '19 at 18:36
  • I understand, but it makes it difficult to reproduce your error since it doesn't work with a "vanilla" spreadsheet. Sorry, not sure I can do much more. – r2evans Oct 02 '19 at 18:39
  • @r2evans Ok, I see. I will try to anonymize the data and will edit the question with a dataset. Thanks for the willingness to help! – And_R Oct 02 '19 at 18:51

0 Answers0