我正在尝试处理地震研究中心提供的官方.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中,值将按预期更改。我保存了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-> clickYes-> clickClose->保存内容?

非常感谢大家!

一个可能不相关的更新:

虽然在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

最佳答案

虽然在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

10-08 00:03