VBS 封装Excel函数

支持原创工作，转载请加上地址。

Function Xls_CreateExcelApp(boolVisible)On Error Resume NextDim excelShetSet ExcelApp = CreateObject("Excel.Application")If(boolVisible = "True") Then ExcelApp.Visible = TrueElseIf(boolVisible = "False") Then ExcelApp.Visible = FalseElseXls_CreateExcelApp = "call the method params is incorrect"End If  'return Set Xls_CreateExcelApp = ExcelApp'destorySet ExcelApp = Nothing End FunctionFunction Xls_OpenWorkbook(ByRef ExcelApp,filepath)On Error Resume NextSet NewWorkbook = ExcelApp.Workbooks.Open(filepath)Set Xls_OpenWorkbook = NewWorkbookSet NewWorkbook = nothingEnd FunctionFunction Xls_ActivateWorkbook(ByRef ExcelApp, strWorkBookName)On Error Resume NextExcelApp.Workbooks(strWorkBookName).ActivateEnd FunctionFunction Xls_GetSheet(ByRef ExcelApp, strSheetName)Err.ClearDim worksheetOn Error Resume NextSet worksheet = ExcelApp.Worksheets.Item(strSheetName)Set Xls_GetSheet = worksheetSet worksheet  = nothingEnd Function Function Xls_GetSheetUsedColumnsCount(strFileName, strSheetName)Dim oExcelDim workbookDim worksheetSet oExcel = Xls_CreateExcelApp("False")Set workbook = Xls_OpenWorkbook(oExcel,strFileName)Set worksheet = Xls_GetSheet(oExcel,strSheetName)Xls_GetSheetUsedColumnsCount = worksheet.UsedRange.Columns.CountoExcel.QuitSet worksheet = NothingSet workbook = NothingSet oExcel = Nothing End FunctionFunction Xls_GetSheetUsedRowsCount(strFilename, strSheetName)Dim oExcel, workbook, worksheetSet oExcel = Xls_CreateExcelApp("False")Set workbook = oExcel.Workbooks.Open(strFileName)Set worksheet = Xls_GetSheet(oExcel,strSheetName)Xls_GetSheetUsedRowsCount = worksheet.UsedRange.Rows.CountoExcel.QuitSet worksheet = NothingSet workbook = NothingSet oExcel = NothingEnd FunctionFunction Xls_GetCellvalue(ByRef ExcelSheet, intRow, intColumn)'On Error Resume NextXls_GetCellvalue = ExcelSheet.Cells(intRow, intColumn)End FunctionFunction Xls_GetSheetData2Array(strFileName, strSheetName)Dim Columnscount, RowsCountColumnscount = Xls_GetSheetUsedColumnsCount(strFileName,strSheetName)RowsCount = Xls_GetSheetUsedRowsCount(strFileName, strSheetName)Dim oExcel, workbook, worksheetSet oExcel = Xls_CreateExcelApp("False")Set workbook = oExcel.Workbooks.Open(strFileName)Xls_ActivateWorkbook oExcel,strSheetNameSet worksheet = Xls_GetSheet(oExcel,strSheetName)ReDim scriptItemArray(RowsCount-1,Columnscount-1)Dim ActualActual = 0For i=2 To RowsCount-1number = Trim(Xls_GetCellvalue(worksheet,i,1))If(IsEmpty(number) Or number = "" Or Not (IsNumeric(number))) ThenWSH.Echo number Exit For End IfActual = Actual + 1For j=1 To Columnscount-1    scriptItemArray(i-2,j-1) = Trim(Xls_GetCellvalue(worksheet,i,j))WSH.Echo Xls_GetCellvalue(worksheet,i,j)Next NextReDim actualScriptItemArray(Actual-1, Columnscount-1)For i=0 To Actual-1 For j = 0 To Columnscount-1actualScriptItemArray(i,j) = scriptItemArray(i,j)NextNextoExcel.QuitSet worksheet = NothingSet workbook =  NothingSet oExcel = Nothing Xls_GetSheetData2Array = actualScriptItemArrayEnd Function Dim strFileName, strSheetNamestrFileName = "D:\VBS Libary\EOM\Case.xlsx"strSheetName = "script"Dim arrDataarrData = Xls_GetSheetData2Array(strFileName, strSheetName)