R不能管理“一般”在读取libreoffice生成的xls时格式化单元格 [英] R can't manage "General" formatted cells when reading libreoffice generated xls

问题描述

我为我的电子表格切换到libreoffice，但是当使用gdata包的read.xls（）函数导入在R内部使用libreoffice修改的xls文件时，我注意到一个错误。如果某列的格式为常规，则在R中单元格显示为常规而不是正确的值。为了解决这个问题，我需要格式化每一列到合适的格式（数字，文本等），我宁愿避免，因为一般更好的管理格式（例如。

任何更好的解决方案？

已解决：

>

在RockScience的建议之后，我尝试了 openxlsx：read.xlsx（），它完美地工作。比 gdata：read.xls（）！更快，更不容易出错

解决方案

我也注意到 gdata :: read.xls 不会计算公式，而只是导入在Excel中显示的内容。因此，例如，如果一个公式返回1.0002，但在Excel中显示限制为2位， gdata :: read.xls 将只导入1.00

我已经看过几种将xls数据导入到R中的方法：

gnumeric :: read.gnumeric.sheet

c $ c>确实评估了公式，所以我建议你尝试一下（但是它需要访问在Windows上不易获得的gnumeric命令行）。

还有一个新的包 http：//cran.r-project。 org / web / packages / openxlsx / index.html ，这看起来非常有利。

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()!

解决方案

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:

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.

这篇关于R不能管理“一般”在读取libreoffice生成的xls时格式化单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！