处理xlsm文件的R语言方法

Question

处理xlsm文件的R语言方法

4

我正在处理一份由地震研究中心提供的官方.XLSM文件。您可以从theXLSM下载该文件。我想做的是编写R代码，更改xlsm的特定部分，然后重新评估.xlsm并提取Excel文件的一部分。

使用新下载的.XLSM文件，我首先提取信息而不更改文件：

library(XLConnect)
wb <- loadWorkbook("NGAW2_GMPE_Spreadsheets_v5.7_041415_Protected.xlsm", 
                   create = TRUE)
setStyleAction(wb,XLC$"STYLE_ACTION.NONE")
library(readxl)
read_excel("NGAW2_GMPE_Spreadsheets_v5.7_041415_Protected.xlsm", range = "E23:G46",col_names=FALSE)

结果如下：

    # A tibble: 24 x 3
     X__1  X__2  X__3
    <dbl> <dbl> <dbl>
 1 0.0100 0.443 0.812
 2 0.0200 0.456 0.838
 3 0.0300 0.506 0.938
 4 0.0500 0.632 1.19 
 5 0.0750 0.798 1.53 
 6 0.100  0.902 1.74 
 7 0.150  1.01  1.95 
 8 0.200  0.978 1.88 
 9 0.250  0.893 1.71 
10 0.300  0.800 1.55 
# ... with 14 more rows

我更改了.xlsm文件的某些部分，并想通过以下代码提取更新后的内容。

weight=t(as.matrix(c(1,0,0,0,0)));
writeWorksheet(wb, weight,
               "Main",startRow = 14, 
               startCol = 3, header = FALSE)
saveWorkbook(wb)
read_excel("NGAW2_GMPE_Spreadsheets_v5.7_041415_Protected.xlsm", range = "E23:G46",col_names=FALSE)

然而，结果并不如我预期的那样改变：

# A tibble: 24 x 3
     X__1  X__2  X__3
    <dbl> <dbl> <dbl>
 1 0.0100 0.443 0.812
 2 0.0200 0.456 0.838
 3 0.0300 0.506 0.938
 4 0.0500 0.632 1.19 
 5 0.0750 0.798 1.53 
 6 0.100  0.902 1.74 
 7 0.150  1.01  1.95 
 8 0.200  0.978 1.88 
 9 0.250  0.893 1.71 
10 0.300  0.800 1.55 
# ... with 14 more rows

当我打开.xlsm文件时，首先会弹出以下内容（请点击查看）： EXCEL WARNING

在此之后，Excel中的值按预期更改。我保存了Excel文件，然后回到R运行代码：

read_excel("NGAW2_GMPE_Spreadsheets_v5.7_041415_Protected.xlsm", range = "E23:G46",col_names=FALSE)

最后，它被改变了：

    # A tibble: 24 x 3
     X__1  X__2  X__3
    <dbl> <dbl> <dbl>
 1 0.0100 0.411 0.779
 2 0.0200 0.421 0.798
 3 0.0300 0.447 0.845
 4 0.0500 0.508 0.952
 5 0.0750 0.655 1.23 
 6 0.100  0.792 1.50 
 7 0.150  0.986 1.93 
 8 0.200  0.978 1.92 
 9 0.250  0.870 1.72 
10 0.300  0.751 1.50 
# ... with 14 more rows

有人知道怎么避免openExcel->点击Yes->点击Close->保存的问题吗？

非常感谢大家！

也许不相关的更新：

在MATLAB中不会出现这个问题...然而，我感到非常糟糕的是，我要么在MATLAB中重新编写整个代码，要么我必须探索一些R-MATLAB转换的东西...

仍在寻找更好的解决方案...

如果你们有兴趣，这是一个很好的参考: https://mandymejia.wordpress.com/2014/08/18/three-ways-to-use-matlab-from-r/

outputRange='E23:I43';
filename = 'NGAW2_GMPE_Spreadsheets_v5.7_041415_Protected.xlsm';
sheet = 1;
xlsread(filename,sheet,outputRange)

ans =

    0.0100    0.4111    0.7787    0.2170    0.0010
    0.0200    0.4214    0.7981    0.2225    0.0042
    0.0300    0.4466    0.8449    0.2361    0.0100
    0.0500    0.5082    0.9521    0.2713    0.0315
    0.0750    0.6551    1.2259    0.3500    0.0915
    0.1000    0.7922    1.4991    0.4186    0.1967
    0.1500    0.9858    1.9298    0.5035    0.5506
    0.2000    0.9778    1.9221    0.4974    0.9709
    0.2500    0.8704    1.7183    0.4409    1.3504
    0.3000    0.7510    1.4951    0.3773    1.6779
    0.4000    0.5787    1.1618    0.2883    2.2985
    0.5000    0.4801    0.9721    0.2371    2.9796
    0.7500    0.3263    0.6692    0.1591    4.5566
    1.0000    0.2419    0.5025    0.1164    6.0048
    1.5000    0.1528    0.3202    0.0729    8.5354
    2.0000    0.1094    0.2313    0.0518   10.8676
    3.0000    0.0666    0.1396    0.0318   14.8858
    4.0000    0.0471    0.0979    0.0227   18.7140
    5.0000    0.0351    0.0730    0.0169   21.7952
    7.5000    0.0187    0.0389    0.0090   26.1710
   10.0000    0.0112    0.0233    0.0054   27.8543

M=7;
F=1;
VS30=500;
R=30;
xlswrite(filename,M,sheet,'B24');
xlswrite(filename,R,sheet,'B27');
xlswrite(filename,R,sheet,'B30');
xlswrite(filename,R,sheet,'B33');
xlswrite(filename,999,sheet,'B36');
xlswrite(filename,VS30,sheet,'B39');

xlsread(filename,sheet,outputRange)

ans =

    0.0100    0.1230    0.2293    0.0659    0.0003
    0.0200    0.1252    0.2333    0.0672    0.0012
    0.0300    0.1304    0.2416    0.0704    0.0029
    0.0500    0.1504    0.2780    0.0813    0.0093
    0.0750    0.1904    0.3516    0.1031    0.0266
    0.1000    0.2292    0.4255    0.1234    0.0569
    0.1500    0.2925    0.5520    0.1550    0.1634
    0.2000    0.3194    0.6154    0.1658    0.3172
    0.2500    0.3077    0.6038    0.1568    0.4774
    0.3000    0.2663    0.5270    0.1346    0.5949
    0.4000    0.2126    0.4244    0.1065    0.8444
    0.5000    0.1747    0.3517    0.0868    1.0841
    0.7500    0.1163    0.2371    0.0570    1.6235
    1.0000    0.0840    0.1736    0.0407    2.0864
    1.5000    0.0530    0.1105    0.0254    2.9604
    2.0000    0.0371    0.0779    0.0176    3.6807
    3.0000    0.0225    0.0468    0.0108    5.0209
    4.0000    0.0163    0.0336    0.0079    6.4612
    5.0000    0.0127    0.0263    0.0062    7.9098
    7.5000    0.0085    0.0176    0.0041   11.8785
   10.0000    0.0059    0.0123    0.0029   14.7570

- olk

将工作表对象保存到文件中只是保存数据。Excel“引擎”未实例化，因此您不应该期望“保存到文件”与“保存并重新计算Excel的方式”相同。要做到这一点，您需要实际上使用Excel；也许您可以考虑omegahat的RDCOMClient。 - r2evans

是的，在保存文件时，我们不应该期望重新计算。但是，“read_excel()”函数是否应该重新计算所有内容呢？也许图片中显示的Excel错误阻碍了“read_excel()”函数的重新计算？ - olk

不，重新计算是由Excel完成的，而不是通过读取文件完成的。工作表中的单元格对其他单元格（即公式）具有依赖关系，这些依赖关系并非魔法：Excel会查找它们，根据它们执行某些操作，然后更新表现值。只有Excel完成这个过程后，使用非Excel程序才能产生所需的值。工作表单元格中的公式仅与Excel相关，而不涉及文件格式。（半开玩笑地说：您可以提取公式并实现一个类似Excel的解析引擎，以R代替需要Excel。祝你好运 :-) - r2evans

非常感谢您，e2evans。我同意无论我们做什么都需要用到Excel。尽管如此，MATLAB似乎要比readxl软件包做得更好...如果您有兴趣，请查看我下面发布的答案。 - olk

我无法对Matlab读写Excel文件的实现进行评论，但至少可以建议可能存在解决方案。我无法立即找到简单的DCOM方法，但我建议这将是最强大的方法：让Excel处理在xlsx / xlsm文件中重新计算Excel公式。似乎有一些很好的连接使用像RExcel和/或rcom之类的东西，尽管后者似乎已经从CRAN中消失并且多年没有更新了。这是一个有趣的问题，抱歉我目前没有太多可提供的。 - r2evans

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- olk · Answer 1

虽然在MATLAB中不会出现这个问题... 但是，让我感到非常糟糕的是，我要么必须在MATLAB中重新编写整个代码，要么就必须探索一些R-MATLAB过渡方案...

仍在寻找更好的解决方案...

如果你们感兴趣，这是一个很好的参考资料： https://mandymejia.wordpress.com/2014/08/18/three-ways-to-use-matlab-from-r/

outputRange='E23:I43';
filename = 'NGAW2_GMPE_Spreadsheets_v5.7_041415_Protected.xlsm';
sheet = 1;
xlsread(filename,sheet,outputRange)

ans =

    0.0100    0.4111    0.7787    0.2170    0.0010
    0.0200    0.4214    0.7981    0.2225    0.0042
    0.0300    0.4466    0.8449    0.2361    0.0100
    0.0500    0.5082    0.9521    0.2713    0.0315
    0.0750    0.6551    1.2259    0.3500    0.0915
    0.1000    0.7922    1.4991    0.4186    0.1967
    0.1500    0.9858    1.9298    0.5035    0.5506
    0.2000    0.9778    1.9221    0.4974    0.9709
    0.2500    0.8704    1.7183    0.4409    1.3504
    0.3000    0.7510    1.4951    0.3773    1.6779
    0.4000    0.5787    1.1618    0.2883    2.2985
    0.5000    0.4801    0.9721    0.2371    2.9796
    0.7500    0.3263    0.6692    0.1591    4.5566
    1.0000    0.2419    0.5025    0.1164    6.0048
    1.5000    0.1528    0.3202    0.0729    8.5354
    2.0000    0.1094    0.2313    0.0518   10.8676
    3.0000    0.0666    0.1396    0.0318   14.8858
    4.0000    0.0471    0.0979    0.0227   18.7140
    5.0000    0.0351    0.0730    0.0169   21.7952
    7.5000    0.0187    0.0389    0.0090   26.1710
   10.0000    0.0112    0.0233    0.0054   27.8543

M=7;
F=1;
VS30=500;
R=30;
xlswrite(filename,M,sheet,'B24');
xlswrite(filename,R,sheet,'B27');
xlswrite(filename,R,sheet,'B30');
xlswrite(filename,R,sheet,'B33');
xlswrite(filename,999,sheet,'B36');
xlswrite(filename,VS30,sheet,'B39');

xlsread(filename,sheet,outputRange)

ans =

    0.0100    0.1230    0.2293    0.0659    0.0003
    0.0200    0.1252    0.2333    0.0672    0.0012
    0.0300    0.1304    0.2416    0.0704    0.0029
    0.0500    0.1504    0.2780    0.0813    0.0093
    0.0750    0.1904    0.3516    0.1031    0.0266
    0.1000    0.2292    0.4255    0.1234    0.0569
    0.1500    0.2925    0.5520    0.1550    0.1634
    0.2000    0.3194    0.6154    0.1658    0.3172
    0.2500    0.3077    0.6038    0.1568    0.4774
    0.3000    0.2663    0.5270    0.1346    0.5949
    0.4000    0.2126    0.4244    0.1065    0.8444
    0.5000    0.1747    0.3517    0.0868    1.0841
    0.7500    0.1163    0.2371    0.0570    1.6235
    1.0000    0.0840    0.1736    0.0407    2.0864
    1.5000    0.0530    0.1105    0.0254    2.9604
    2.0000    0.0371    0.0779    0.0176    3.6807
    3.0000    0.0225    0.0468    0.0108    5.0209
    4.0000    0.0163    0.0336    0.0079    6.4612
    5.0000    0.0127    0.0263    0.0062    7.9098
    7.5000    0.0085    0.0176    0.0041   11.8785
   10.0000    0.0059    0.0123    0.0029   14.7570