0

I switched to libreoffice for my spreadsheets, but I noticed an error when importing an xls file modified with libreoffice inside R using the gdata package's read.xls() function. If a column is formatted as General, then once in R the cells display General instead of the right value.

To fix it I need to format every column to the proper format (number, text, etc), which i'd prefer to avoid since general better manage formatting (eg. automatically display the right number of significant digits).

Any better solution?

SOLVED:

After suggestion by RockScience I tried openxlsx:read.xlsx() and it works perfectly. Is even faster and less error prone than gdata:read.xls()!

Bakaburg
  • 3,165
  • 4
  • 32
  • 64
  • 1
    I'd submit this as a bug report to libreoffice (not that this solves your problem). In the meantime, there's the workaround to dump your spreadsheet to `csv` or other text format. – Carl Witthoft Jan 26 '15 at 12:34

1 Answers1

2

I have also noticed that gdata::read.xls doesn't evaluate the formula, but instead just imports what is displayed in excel. So for instance if a formula returns 1.0002 but in excel the display is limited to 2 digits, gdata::read.xls will only import 1.00

I have looked at several ways of importing xls data into R:

enter image description here

As you see gnumeric::read.gnumeric.sheet does evaluate the formula, so I suggest you try this (but it needs access to the gnumeric command line which is not available easily on Windows)

since recently there is also a new package http://cran.r-project.org/web/packages/openxlsx/index.html which seems very promissing.

Community
  • 1
  • 1
RockScience
  • 17,932
  • 26
  • 89
  • 125
  • I don't know whether should mark your answer as correct according to SO guidelines since you provided the solution as a side comment, but openxls::read.xlsx works perfectly and is even faster than gdata::read.xls! thank you! – Bakaburg Jan 27 '15 at 10:48