代码之家 › 专栏 › 技术社区 › gaut

保存XLS文件格式错误:自动转换为数字

xls xlsx r

gaut · 技术社区 · 6 年前

WriteXLS 函数似乎总是转换成数字。手动打开xls时也会出现此错误。我怎样才能解决这个问题?

nb <- data.frame("92343E102", stringsAsFactors = F)
WriteXLS::WriteXLS(nb, "testdf.xls")
readxl::read_xls("testdf.xls")
> read_xls("testdf.xls")
# A tibble: 1 x 1
  X.92343E102.
         <dbl>
1     9.23e106

预期结果: 92343E102

如果可能的话,我需要在没有安装python的情况下这样做,所以 dataframes2xls 对我来说不是一个选择-无论如何感谢你的尝试

4 回复 | 直到 6 年前

IRTFM 6 年前

 install.packages("devtools", dependencies=TRUE)
# devtools has a _lot_ of dependencies
# it also has a bunch of system tool requirements
 devtools::install_github("ropensci/writexl")
#make a copy of iris
 tmp <- iris
# set [1,1] to your string:
 tmp[1,1] <- "92343E102"  # makes that column 'character'
 tmp2 <- writexl::write_xlsx(tmp)
 readxl::read_xlsx(tmp2)

# A tibble: 150 x 5
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
   <chr>              <dbl>        <dbl>       <dbl> <chr>  
 1 92343E102            3.5          1.4         0.2 setosa 
 2 4.9                  3            1.4         0.2 setosa 
 3 4.7                  3.2          1.3         0.2 setosa 
 4 4.6                  3.1          1.5         0.2 setosa 
 5 5                    3.6          1.4         0.2 setosa 
 6 5.4                  3.9          1.7         0.4 setosa 
 7 4.6                  3.4          1.4         0.3 setosa 
 8 5                    3.4          1.5         0.2 setosa 
 9 4.4                  2.9          1.4         0.2 setosa 
10 4.9                  3.1          1.5         0.1 setosa 
# ... with 140 more rows

duckmayr 6 年前

我建议你用 dataframes2xls::write.xls() 取而代之的是:

# Make a sample dataframe:
nb <- data.frame(A = "92343E102", B = 92343E102, stringsAsFactors = F)
WriteXLS::WriteXLS(nb, "testdf.xls") # Write out using WriteXLS() ...
readxl::read_xls("testdf.xls") # Doesn't work, per your post
#> # A tibble: 1 x 2
#>          A        B
#>      <dbl>    <dbl>
#> 1 9.23e106 9.23e106
# Maybe we can specify what kind of column it is when reading in the data?
readxl::read_xls("testdf.xls", col_types = "text")
# Still doesn't work, it must be the writer
#> # A tibble: 1 x 2
#>   A           B          
#>   <chr>       <chr>      
#> 1 -2147483648 -2147483648
dataframes2xls::write.xls(nb, "testdf2.xls") # So, try a different writer
readxl::read_xls("testdf2.xls")#, col_types = "text") # Works!
#> # A tibble: 1 x 2
#>   A                      B
#>   <chr>              <dbl>
#> 1 "\"92343E102\"" 9.23e106

^{创建日期:2018-11-02

reprex package

(第0.2.1版)}

Ian Wesley 6 年前

如果可以将其保存为csv,请在Excel中打开。一个选项是使用“数据”选项卡上的“从文本获取外部数据”向导。然后选择csv文件,配置适当的选项,在向导的第三步中,在包含文本的列上选择import as text,如下图所示。然后您可以将其另存为.xls并再次打开,而不必使用Excel将数据类型更改为scientific。

gaut 6 年前

好的,最后我想我找到了一种方法来避免字符串的XLS转换,比如“12E123”到scientific double,这不需要使用excel交互 XLConnect

nb <- data.frame(as.character('92343E102'), stringsAsFactors = F)
WriteXLS::WriteXLS(nb, "testdf.xls")
readxl::read_xls("testdf.xls")
wb <- XLConnect::loadWorkbook("testdf.xls")
XLConnect::createSheet(wb, name="newsheet")
XLConnect::writeWorksheet(wb, nb, sheet = "newsheet")
XLConnect::saveWorkbook(wb)
readxl::read_xls("testdf.xls", sheet=1) #converted string to wrong number
readxl::read_xls("testdf.xls", sheet=2) # success! string stays string

> readxl::read_xls("testdf.xls", sheet=1)
# A tibble: 1 x 1
  as.character..92343E102..
                      <dbl>
1                  9.23e106
> readxl::read_xls("testdf.xls", sheet=2)
# A tibble: 1 x 1
  as.character..92343E102..
  <chr>                    
1 92343E102

当然,由于我只有32位的JAVA在工作,我可能不得不切换到R32为这一点,或写32位-R例程,以正确地保存我的XLS文件。。

一次只做一件事。。。希望这对别人有帮助