'业务描述:将excel表格里的内容导入到DataTable中,不可使用import和importsheet函数
Excelpath="d:\data2.xls"
Set ExcelApp=createobject("excel.application")
Set Excelxls=ExcelApp.Workbooks.Open(Excelpath)
ecount=Excelxls.Worksheets.Count
dcount=datatable.GetSheetCount
a=ecount-dcount
If a>0 Then
For b = 1 To a Step 1
datatable.AddSheet("Action"&dcount+b)
Next
End If
'把excel中的表单逐个导入相应的datatable
For s = 1 To ecount Step 1
Set Excelsheet=ExcelXls.Worksheets.Item(s)'设置excel的活动表
colcount=Excelsheet.UsedRange.Columns.Count'获得活动表列的总数
rowcount=Excelsheet.UsedRange.Rows.Count'获得活动表行的总数
'在datatable表中添加新的列,并把活动表第一行的数据作为datatable表列名
For m = 1 To colcount Step 1
param=Excelsheet.Cells(1,m)
datatable.GetSheet(s).AddParameter param,""
Next
'从活动表的第二行数据开始逐个导入
For i = 2 To rowcount Step 1
datatable.GetSheet(s).SetCurrentRow i-1
For j = 1 To colcount Step 1
param=Excelsheet.Cells(i,j)
datatable.Value(j,s)=param
Next
Next
Next
Excelxls.Close
ExcelApp.Quit
Set Excelxls=nothing
Set ExcelApp=nothing
将excel表格里的内容导入到DataTable中,不可使用import和importsheet函数
最新推荐文章于 2022-11-04 16:11:45 发布