一、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
,
