将矩阵列表很好地导出到Excel中的同一工作表 [英] Export a list of matrices nicely to the same worksheet in Excel

问题描述

我有4个矩阵的列表.其中的前两个:

I have a list of 4 matrices. The first two of them:

$`1857-1903`
                      Bank.o.kassa Obligationer       Lån    Aktier Placeringar.andra.ftg Fodringar Reala.tillgångar Övriga.tillgångar
Bank.o.kassa             1.0000000    0.8014382 0.8079718 0.4421687                    NA 0.8395201        0.9144023         0.7995480
Obligationer             0.8014382    1.0000000 0.8422192 0.4590398                    NA 0.7871074        0.8815054         0.7003110
Lån                      0.8079718    0.8422192 1.0000000 0.5476716                    NA 0.8932723        0.9295665         0.7846206
Aktier                   0.4421687    0.4590398 0.5476716 1.0000000                    NA 0.7203681        0.4221815         0.6281095
Placeringar.andra.ftg           NA           NA        NA        NA                     1        NA               NA                NA
Fodringar                0.8395201    0.7871074 0.8932723 0.7203681                    NA 1.0000000        0.8869905         0.9395209
Reala.tillgångar         0.9144023    0.8815054 0.9295665 0.4221815                    NA 0.8869905        1.0000000         0.8158413
Övriga.tillgångar        0.7995480    0.7003110 0.7846206 0.6281095                    NA 0.9395209        0.8158413         1.0000000
Kapitalinkomster         0.9163688    0.8960790 0.9318508 0.4985449                    NA 0.9277740        0.9821501         0.8755644
                      Kapitalinkomster
Bank.o.kassa                 0.9163688
Obligationer                 0.8960790
Lån                          0.9318508
Aktier                       0.4985449
Placeringar.andra.ftg               NA
Fodringar                    0.9277740
Reala.tillgångar             0.9821501
Övriga.tillgångar            0.8755644
Kapitalinkomster             1.0000000

$`1904-1948`
                      Bank.o.kassa Obligationer         Lån       Aktier Placeringar.andra.ftg   Fodringar Reala.tillgångar Övriga.tillgångar
Bank.o.kassa             1.0000000   -0.5636371  0.12902979 -0.658883574          -0.202688601  0.76474140       0.81544129         0.4642105
Obligationer            -0.5636371    1.0000000  0.23820065  0.369712399           0.649156560 -0.69097442      -0.67090178        -0.3108845
Lån                      0.1290298    0.2382006  1.00000000 -0.301682319           0.663984986 -0.07873989       0.00901089         0.2894697
Aktier                  -0.6588836    0.3697124 -0.30168232  1.000000000          -0.005049947 -0.69634496      -0.77966741        -0.7496402
Placeringar.andra.ftg   -0.2026886    0.6491566  0.66398499 -0.005049947           1.000000000 -0.44833419      -0.31431360         0.1089602
Fodringar                0.7647414   -0.6909744 -0.07873989 -0.696344963          -0.448334185  1.00000000       0.84590718         0.5765548
Reala.tillgångar         0.8154413   -0.6709018  0.00901089 -0.779667406          -0.314313596  0.84590718       1.00000000         0.5367746
Övriga.tillgångar        0.4642105   -0.3108845  0.28946968 -0.749640198           0.108960190  0.57655477       0.53677459         1.0000000
Kapitalinkomster        -0.1114329    0.3693150  0.38160001 -0.125296598           0.720230427 -0.17280772      -0.13946215         0.3720201
                      Kapitalinkomster
Bank.o.kassa                -0.1114329
Obligationer                 0.3693150
Lån                          0.3816000
Aktier                      -0.1252966
Placeringar.andra.ftg        0.7202304
Fodringar                   -0.1728077
Reala.tillgångar            -0.1394622
Övriga.tillgångar            0.3720201
Kapitalinkomster             1.0000000

我想将它们导出到Excel中的相同工作表中.问题是，如果我举个例子 write.csv2(my.list,file="my.list.csv2") 矩阵没有分开，它们之间没有间距.另一方面，我知道如何使用XLConnect将列表导出到包含几张纸的工作簿中(本例中为4张).但是我希望矩阵位于相同的工作表中，并以一定的间距隔开，并具有列表名称(即$ 1857-1903).如果存在行名，那也将是一件好事...有可能吗?找不到任何答案. 最好的问候！

I would like to export them to the same worksheet in Excel. The problem is that if I, example, do write.csv2(my.list,file="my.list.csv2") the matrices are not separated, there is no spacing between them. On the other hand I know how to use XLConnect to export my list to a workbook with several sheets (4 in this case). But I want my matrices to be in the same worksheet, separated by some spacing, with listnames (i.e. $1857-1903) present. And it also would be nice if rownames would be present... Is it possible? Could not find any answer. Best Regards!

我接受了@一月的回答.谢谢！但是，如果我使用write.csv2而不是write.table(为了获得所需的输出)，则会收到警告消息:

I accepted @January answer. Thanks! But if I use write.csv2 instead of write.table (in order to get the desired output) I get a warning message:

In write.csv2(export, file = "funkcorr.csv", quote = F, sep = ",") : attempt to set 'sep' ignored. 然后，Excel中的数字为.代替 ，.因此，我需要手动进行转换.到，在Excel中.知道为什么sep被忽略吗?

In write.csv2(export, file = "funkcorr.csv", quote = F, sep = ",") : attempt to set 'sep' ignored. Then, the numbers in Excel are with . instead of ,. So, I need to manually convert . to , in Excel. Any idea why sep is ignored?

推荐答案

以下是基于XLConnect的解决方案:

The following is an XLConnect-based solution:

# Preparation of dummy data
data = rep(list(WorldPhones), 5)
names(data) = LETTERS[1:5]

require(XLConnect)
wb = loadWorkbook("matrix.xlsx", create = TRUE)
# Create a new sheet
createSheet(wb, name = "mysheet")
# cumulative length (rows) of matrices
# +2 = 1 for list names, 1 for header row
cumlen = cumsum(c(1, head(sapply(data, nrow), n = -1) + 2))
# Write data rows (implicitly vectorized!)
writeWorksheet(wb, data = data, sheet = "mysheet", startRow = cumlen + 1, header = TRUE)
# Write list names
writeWorksheet(wb, data = as.list(names(data)), sheet = "mysheet", startRow = cumlen, header = FALSE)
saveWorkbook(wb)

这篇关于将矩阵列表很好地导出到Excel中的同一工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！