VBS 封装Excel函数

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

Function Xls_CreateExcelApp(boolVisible)
	On Error Resume Next
	Dim excelShet
	Set ExcelApp = CreateObject("Excel.Application")
	If(boolVisible = "True") Then 
			ExcelApp.Visible = True
	ElseIf(boolVisible = "False") Then 
			ExcelApp.Visible = False
	Else
		Xls_CreateExcelApp = "call the method params is incorrect"
	End If  	
	'return 
	Set Xls_CreateExcelApp = ExcelApp
	'destory
	Set ExcelApp = Nothing 
End Function

Function Xls_OpenWorkbook(ByRef ExcelApp,filepath)
	On Error Resume Next
	Set NewWorkbook = ExcelApp.Workbooks.Open(filepath)
	Set Xls_OpenWorkbook = NewWorkbook
	Set NewWorkbook = nothing
End Function

Function Xls_ActivateWorkbook(ByRef ExcelApp, strWorkBookName)
	On Error Resume Next
	ExcelApp.Workbooks(strWorkBookName).Activate
End Function

Function Xls_GetSheet(ByRef ExcelApp, strSheetName)
	Err.Clear
	Dim worksheet
	On Error Resume Next
	Set worksheet = ExcelApp.Worksheets.Item(strSheetName)
	Set Xls_GetSheet = worksheet
	Set worksheet  = nothing
End Function 

Function Xls_GetSheetUsedColumnsCount(strFileName, strSheetName)
	Dim oExcel
	Dim workbook
	Dim worksheet
	Set oExcel = Xls_CreateExcelApp("False")
	Set workbook = Xls_OpenWorkbook(oExcel,strFileName)
	Set worksheet = Xls_GetSheet(oExcel,strSheetName)
	Xls_GetSheetUsedColumnsCount = worksheet.UsedRange.Columns.Count
	oExcel.Quit
	Set worksheet = Nothing
	Set workbook = Nothing
	Set oExcel = Nothing 
End Function

Function Xls_GetSheetUsedRowsCount(strFilename, strSheetName)
	Dim oExcel, workbook, worksheet
	Set oExcel = Xls_CreateExcelApp("False")
	Set workbook = oExcel.Workbooks.Open(strFileName)
	Set worksheet = Xls_GetSheet(oExcel,strSheetName)
	Xls_GetSheetUsedRowsCount = worksheet.UsedRange.Rows.Count
	oExcel.Quit
	Set worksheet = Nothing
	Set workbook = Nothing
	Set oExcel = Nothing
End Function

Function Xls_GetCellvalue(ByRef ExcelSheet, intRow, intColumn)
	'On Error Resume Next
	Xls_GetCellvalue = ExcelSheet.Cells(intRow, intColumn)
End Function

Function Xls_GetSheetData2Array(strFileName, strSheetName)
	Dim Columnscount, RowsCount
	Columnscount = Xls_GetSheetUsedColumnsCount(strFileName,strSheetName)
	RowsCount = Xls_GetSheetUsedRowsCount(strFileName, strSheetName)
	
	Dim oExcel, workbook, worksheet
	Set oExcel = Xls_CreateExcelApp("False")
	Set workbook = oExcel.Workbooks.Open(strFileName)
	Xls_ActivateWorkbook oExcel,strSheetName
	Set worksheet = Xls_GetSheet(oExcel,strSheetName)
	
	ReDim scriptItemArray(RowsCount-1,Columnscount-1)
	Dim Actual
	Actual = 0
	For i=2 To RowsCount-1
			number = Trim(Xls_GetCellvalue(worksheet,i,1))
			If(IsEmpty(number) Or number = "" Or Not (IsNumeric(number))) Then
					WSH.Echo number 
					Exit For 
			End If
			Actual = Actual + 1
		For j=1 To Columnscount-1
		    scriptItemArray(i-2,j-1) = Trim(Xls_GetCellvalue(worksheet,i,j))
			WSH.Echo Xls_GetCellvalue(worksheet,i,j)
		Next 
	Next
	
	ReDim actualScriptItemArray(Actual-1, Columnscount-1)

	For i=0 To Actual-1 
		For j = 0 To Columnscount-1
			actualScriptItemArray(i,j) = scriptItemArray(i,j)
		Next
	Next

	oExcel.Quit
	Set worksheet = Nothing
	Set workbook =  Nothing
	Set oExcel = Nothing 
	
	Xls_GetSheetData2Array = actualScriptItemArray
	
End Function 

Dim strFileName, strSheetName

strFileName = "D:\VBS Libary\EOM\Case.xlsx"
strSheetName = "script"
Dim arrData

arrData = Xls_GetSheetData2Array(strFileName, strSheetName)