0

I am trying to import an xlsx worksheet for which I need to read the cell color. I already read this post ( Using R to read out excel-colorinfo) but I can't import the Excel file as workbook, which I understood would be a requirement to then read the cell color.

It is an xlsx file with many sheets, some of which contain thousands of rows and many columns.

The problem is that when I try

library(XLConnect) options(java.parameters = "-Xmx1024m") wb <- loadWorkbook("My_file.xlsx")

After a minute I get the following error

Error: OutOfMemoryError (Java): Java heap space

I also tried what is suggested in this post (Importing a big xlsx file into R?), but it did not seem to work either and I can not use a simple read.xlsx function because I need a WorkBook format to read the cell colors.

I also tried to copy paste the sheet I am interested in another Excel-file and then read that. Since it was a smaller file, I hoped it worked. But I got another error:

wb <- loadWorkbook("test_wb.xlsx", create = F) Error: NoSuchMethodError (Java): org.apache.poi.ss.usermodel.FillPatternType.getCode()S

Any suggestion from your side?

M--
  • 25,431
  • 8
  • 61
  • 93
Peter
  • 47
  • 1
  • 9
  • 1
    See if this helps - [Using R to read out excel-colorinfo](https://stackoverflow.com/questions/42982344/using-r-to-read-out-excel-colorinfo?rq=1). – deepseefan Oct 25 '19 at 14:26
  • do you have a recent version of a Java Runtime Environment (JRE) installed? XLConnect depends on that. – Janhoo Oct 25 '19 at 14:30
  • Also, 1 gigabyte of heap space might not be sufficient. Try something along `options(java.parameters = "-Xmx4g")` – Janhoo Oct 25 '19 at 14:35
  • 1
    I do a lot of work with Excel and R. Honestly, you might want to consider fixing the problem in the workbook. Just create a dummy column (or columns) with a user defined function to pull out the color (`rng.Interior.Color` or something). You can then read that with one of the R packages not dependent on Java (`openxlsx` or `readxl`, for example). Color as data is really not ideal in Excel. –  Oct 25 '19 at 15:12
  • While I second the suggestion to fix it in the workbook, it is probably better to work with Excel files through the COM interface then through XLConnect. Check out the package RDOCMClient. – Ian Wesley Oct 25 '19 at 17:46
  • @Janhoo how do I check which version of JRE I have? Thanks! – Peter Oct 28 '19 at 07:51
  • @Janhoo I tried to use more heap space, but it still returns the same error. – Peter Oct 28 '19 at 08:02
  • @Peter , Version check is explained here: https://www.java.com/de/download/help/version_manual.xml. just install the lastest version. – Janhoo Oct 28 '19 at 09:16
  • Solved by adding `options(java.parameters = "-Xmx8000m")` BEFORE loading my library(xlsx). Thanks to everybody! – Peter Nov 14 '19 at 09:12

0 Answers0