多个工作表汇总

一、R语言操作

library(xlsx)                 '载入xlsx包

data1<-data.frame()        '将空数据框赋给变量data1

data2<-list()                 '将空的列表框赋给变量data2

file<-"c:/users/administrator/desktop/input.xlsx"      '将文件路径赋给变量file

sht<-length(getSheets(loadWorkbook(file)))            '获取工作表的个数  

for(i in 1:sht){                                                           '开始循环

    data2[[i]]<-read.xlsx(file,sheetIndex=i,encoding="UTF-8")         '将工作表数据写入列表

    data1<-rbind(data2[[i]],data1)                                                 '将列表数据合并写入数据框

}

print(data1)                                             ‘显示数据框 

二、Vba+Sql操作

Sub 多工作表汇总()

    Dim Cnn As Object, FullPath$, Sht_Count&, i&, Sql$, Rs As Object

    Set Cnn = CreateObject("Adodb.Connection")

    FullPath = ThisWorkbook.FullName

    Cnn.Open "Provider=Microsoft.Ace.Oledb.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName

    Sht_Count = Worksheets.Count

    For i = 1 To Sht_Count - 1

        If Sql = "" Then

            Sql = "select * from [" & FullPath & "].[" & Worksheets(i).Name & "$]"

        Else

            Sql = Sql & " union all select * from [" & FullPath & "].[" & Worksheets(i).Name & "$]"

        End If

    Next i

    Set Rs = Cnn.Execute(Sql)

    Application.ScreenUpdating = False

    For i = 0 To Rs.Fields.Count - 1

        Cells(1, i + 1).Value = Rs.Fields(i).Name

    Next i

    [a2].CopyFromRecordset Rs

    Cnn.Close

    Application.ScreenUpdating = True

    Set Cnn = Nothing

End Sub

                                                                   ,