R语言+VBA批量处理压缩包里面的Excel文件（带源代码）

尝试了多种方法，当前发现这种方式相对比较好。

第一步用360解压所有Excel文件到一个文件夹中，选择重新命名

第二步，批量转化Excel文件为CSV文件。不要问为什么要转化为CSV文件，因为数据量巨大的时候，Excel文件用R处理起来非常困难，用Python处理也很困难。。。也许是我渣，以后找到好方法再来分享。项目紧，先得到想要的结果，说远了，拉回来。。。在文件夹中新建一个Excel文件，在sheet1点右键，然后选择查看代码到如下界面

输入上面图中的代码，图中两行分别为目标源文件夹的路径，准备存放CSV的文件路径，注意，最后一定要在文件后面带 "\"，这个反斜杠，这串代码也是在网上找的，最开始出处没标明，原作者可以认领，非常感谢原作者大神。

代码如下：

Sub SaveToCSVs()

    Dim fDir As String

    Dim wB As Workbook

    Dim wS As Worksheet

    Dim fPath As String

    Dim sPath As String

    fPath = "D:\Desktop\Rwork\cylinder stress files\2021-12-10\BC5465CB4772\"

    sPath = "D:\Desktop\Rwork\cylinder stress files\2021-12-10\CSV保存文件夹\"

    fDir = Dir(fPath)

    Do While (fDir <> "")

        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then

            On Error Resume Next

            Set wB = Workbooks.Open(fPath & fDir)

            'MsgBox (wB.Name)

            For Each wS In wB.Sheets

                wS.SaveAs sPath & wB.Name & ".csv", xlCSV

            Next wS

            wB.Close False

            Set wB = Nothing

        End If

        fDir = Dir

        On Error GoTo 0

    Loop

End Sub

然后加载运行，就会得到所有的Excel编程CSV文件，如下

得到的CSV文件们。。。

接下来就方便R语言读取数据了

#加载xlsx包

library(xlsx)

#读取每个CSV的数据（本来准备用for循环的，结果太菜，没弄成，那就先这样吧）
mydata0 <- read.csv("BC5465CB4772_1_0.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata1 <- read.csv("BC5465CB4772_1_1.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata2 <- read.csv("BC5465CB4772_1_2.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata3 <- read.csv("BC5465CB4772_1_3.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata4 <- read.csv("BC5465CB4772_1_4.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata5 <- read.csv("BC5465CB4772_1_5.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata6 <- read.csv("BC5465CB4772_1_6.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata7 <- read.csv("BC5465CB4772_1_7.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata8 <- read.csv("BC5465CB4772_1_8.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata9 <- read.csv("BC5465CB4772_1_9.xlsx.csv", header = TRUE, encoding = "UTF-8")
 
mydata10 <- read.csv("BC5465CB4772_1_10.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata11 <- read.csv("BC5465CB4772_1_11.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata12 <- read.csv("BC5465CB4772_1_12.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata13 <- read.csv("BC5465CB4772_1_13.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata14 <- read.csv("BC5465CB4772_1_14.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata15 <- read.csv("BC5465CB4772_1_15.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata16 <- read.csv("BC5465CB4772_1_16.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata17 <- read.csv("BC5465CB4772_1_17.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata18 <- read.csv("BC5465CB4772_1_18.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata19 <- read.csv("BC5465CB4772_1_19.xlsx.csv", header = TRUE, encoding = "UTF-8")

mydata20 <- read.csv("BC5465CB4772_1_20.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata21 <- read.csv("BC5465CB4772_1_21.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata22 <- read.csv("BC5465CB4772_1_22.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata23 <- read.csv("BC5465CB4772_1_23.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata24 <- read.csv("BC5465CB4772_1_24.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata25 <- read.csv("BC5465CB4772_1_25.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata26 <- read.csv("BC5465CB4772_1_26.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata27 <- read.csv("BC5465CB4772_1_27.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata28 <- read.csv("BC5465CB4772_1_28.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata29 <- read.csv("BC5465CB4772_1_29.xlsx.csv", header = TRUE, encoding = "UTF-8") 

mydata30 <- read.csv("BC5465CB4772_1_30.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata31 <- read.csv("BC5465CB4772_1_31.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata32 <- read.csv("BC5465CB4772_1_32.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata33 <- read.csv("BC5465CB4772_1_33.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata34 <- read.csv("BC5465CB4772_1_34.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata35 <- read.csv("BC5465CB4772_1_35.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata36 <- read.csv("BC5465CB4772_1_36.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata37 <- read.csv("BC5465CB4772_1_37.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata38 <- read.csv("BC5465CB4772_1_38.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata39 <- read.csv("BC5465CB4772_1_39.xlsx.csv", header = TRUE, encoding = "UTF-8") 

mydata40 <- read.csv("BC5465CB4772_1_40.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata41 <- read.csv("BC5465CB4772_1_41.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata42 <- read.csv("BC5465CB4772_1_42.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata43 <- read.csv("BC5465CB4772_1_43.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata44 <- read.csv("BC5465CB4772_1_44.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata45 <- read.csv("BC5465CB4772_1_45.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata46 <- read.csv("BC5465CB4772_1_46.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata47 <- read.csv("BC5465CB4772_1_47.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata48 <- read.csv("BC5465CB4772_1_48.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata49 <- read.csv("BC5465CB4772_1_49.xlsx.csv", header = TRUE, encoding = "UTF-8") 

mydata50 <- read.csv("BC5465CB4772_1_50.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata51 <- read.csv("BC5465CB4772_1_51.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata52 <- read.csv("BC5465CB4772_1_52.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata53 <- read.csv("BC5465CB4772_1_53.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata54 <- read.csv("BC5465CB4772_1_54.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata55 <- read.csv("BC5465CB4772_1_55.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata56 <- read.csv("BC5465CB4772_1_56.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata57 <- read.csv("BC5465CB4772_1_57.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata58 <- read.csv("BC5465CB4772_1_58.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata59 <- read.csv("BC5465CB4772_1_59.xlsx.csv", header = TRUE, encoding = "UTF-8") 

mydata60 <- read.csv("BC5465CB4772_1_60.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata61 <- read.csv("BC5465CB4772_1_61.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata62 <- read.csv("BC5465CB4772_1_62.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata63 <- read.csv("BC5465CB4772_1_63.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata64 <- read.csv("BC5465CB4772_1_64.xlsx.csv", header = TRUE, encoding = "UTF-8") mydata65 <- read.csv("BC5465CB4772_1_65.xlsx.csv", header = TRUE, encoding = "UTF-8")

#合并你想要合并的数据
mydata <- rbind(mydata0, mydata1, mydata2, mydata3, mydata4, mydata5, mydata6, mydata7, mydata8, mydata9, mydata10, mydata11, mydata12, mydata13, mydata14, mydata15, mydata16, mydata17, mydata18, mydata19, mydata20, mydata21, mydata22, mydata23, mydata24, mydata25, mydata26, mydata27, mydata28, mydata29, mydata30, mydata31, mydata32, mydata33, mydata34, mydata35, mydata36, mydata37, mydata38, mydata39, mydata40, mydata41, mydata42, mydata43, mydata44, mydata45, mydata46, mydata47, mydata48, mydata49, mydata50, mydata51, mydata52, mydata53, mydata54, mydata55, mydata56, mydata57, mydata58, mydata59, mydata60, mydata61, mydata62, mydata63, mydata64, mydata65)

#把数据的首行命名，最好为英文名，方便后期处理和调用
names(mydata) <- c("CarNo", "Time", "PedAngle", "PedArm1An", "Arm1Arm2An", "Arm2Arm3An", "Arm3Arm4An", "Arm4Arm5An", "Arm5Arm6An", "Arm6Arm7An", "Arm1Stress", "Arm1Tensile", "Arm2Stress", "Arm2Tensile", "Arm3Stress", "Arm3Tensile", "Arm4Stress", "Arm4Tensile", "Arm5Stress", "Arm5Tensile", "Arm6Stress", "Arm6Tensile", "Arm7Stress", "Arm7Tensile", "Time_3C", "Number")

#把上述整合的数据输出为一个CSV文件，方便后期继续进行调用和处理
write.csv(mydata,"mydata.csv")