如何从Excel文件中导入所有Sheet到DataTable中？

如果需要从外部存储的测试数据文件例如Excel中导入Sheet页到DataTable中，并且判断DataTable中是否存在指定的Sheet，如果不存在则添加Sheet。可以例如DataTable对象的GetSheet 、AddSheet以及Excel的COM对象编程来实现：

 

Function ImportAllSheets(ByVal FileName)

       DimoExcel, oBook

 

       'Launchexcel

       SetoExcel = GetObject("", "Excel.Application")

 

       'Openthe file in read only mode

       SetoBook = oExcel.WorkBooks.Open(FileName,,True)

 

       'Enumeratethrough all the sheets present in the file

       Foreach oSheet in oBook.WorkSheets

 

              'Checkif a DataTable with current name already exists

              IfNot IfDataSheetExist(oSheet.Name) Then

                     'DataTablecannot be imported if the sheet does not exist

                     DataTable.AddSheetoSheet.Name

              EndIf

 

              'Importthe sheet

              DataTable.ImportSheetFileName, oSheet.Name,oSheet.Name

       Next

 

       SetoBook = Nothing

 

       'QuitExcel

       oExcel.Quit

       SetoExcel = Nothing

End Function

 

 

Function IfDataSheetExist(ByVal SheetName)

       IfDataSheetExist= True

       Onerror resume next

       DimoTest

       SetoTest = DataTable.GetSheet(SheetName)

       Iferr.number Then IfDataSheetExist = False

       Onerror goto 0

End Function