2

I have a pre-formatted Microsoft Excel workbook which I am using as a template. I then want to paste my data.table into the workbook from R, leaving the column titles and formatting intact.

This should be feasible with the package XLConnect:

# Load library    
library(XLConnect)

# Load pre-formatted MS Excel workbook
wb <- loadWorkbook("Myworkbook.xlsx")

# Write data.table to existing worksheet excluding column names:    
writeWorksheet(wb, mydt, sheet = "Datasheet1", startRow = 3, startCol = 1, header = FALSE)

# Save the data to the workbook    
saveWorkbook(wb)

However, when I run this I get the following java error:

Error: NoSuchMethodError (Java): org.apache.poi.ss.usermodel.Cell.setCellType(Lorg/apache/poi/ss/usermodel/CellType;)V

I'm using R version 3.4.0 with RStudio version 1.0.143 with XLConnect_0.2-13 and rJava_0.9-8, Java version is Java 8 Update 66 (64-bit) on a Windows 7 OS with Microsoft Office 2010.

Any ideas on why this might not be working would be much appreciated - I can create workbooks and worksheets from scratch so it seems just appending data to existing worksheets is affected.

Amy M
  • 967
  • 1
  • 9
  • 19
  • No idea, but if you're sick of Java errors, you could try [openxlsx](https://github.com/awalker89/openxlsx), which doesn't use it. – alistaire Jun 28 '17 at 16:07
  • Thanks - will try it, am all for ditching java-dependent solutions as they always seem to cause issues – Amy M Jun 28 '17 at 16:20
  • 1
    openxlsx worked beautifully - will leave this question open to see if anyone knows about the java error but switching to openxlsx for my workflow – Amy M Jun 28 '17 at 16:50

1 Answers1

6

I've also had the same issue and found that in version 0.2-13 of XLConnect there was a move to Apache POI 3.16.

Similarly Apache Commons items were added to it's dependant package XLConnectJars - which is where I think the issue may lie, as there may have been some feature deprecation here.

This issue has been raised, but not addressed on the XLConnect GitHub page.

EDIT: This issue has now been explained on the XLConnect GitHub page. In short the latest version of XLConnect cannot run with the xlsx package as they are using different versions of Apache POI. You can find the post here. Until updates are made to xlsx, the solution below is still valid for a quick fix.

As I already had working code written in XLConnect and due to time constraints re-writing scripts in openxlsx, I've found downgrading to XLConnect/XLConnectJars version 0.2-12 fixes this issue.

The quickest way I found to do this was by uninstalling XLConnect and XLConnectJars, then running the following:

#Assuming devtools is already installed
library(devtools)

install_version("XLConnectJars", version = "0.2-12", repos = "http://cran.us.r-project.org") 
install_version("XLConnect", version = "0.2-12", repos = "http://cran.us.r-project.org")`
Eldqvist
  • 76
  • 5