asp读取excel代码(举了四个excel不同实例应用).doc

aspexcel(excel).txt37 public property let createtype(byval val) if val 1 and val 2 then createtype_ = 1 else createtype_ = val end if end property public property let data(byval val) if not isarray(val) then inerr() end if exceldata = val end property public property get savepath() savepath = savepath_ end property public property get usedtime() usedtime = usedtime_ end property public property let sheetname(byval val) if not isarray(val) then if val = then inerr() end if titlefirstline = true else redim titlefirstline(ubound(val) dim ik_ for ik_ = 0 to ubound(val) titlefirstline(ik_) = true next end if sheetname_ = val end property public property let sheettitle(byval val) if not isarray(val) then if val = then inerr() end if end if sheettitle_ = val end property rem private sub checkdata() if savepath_ = then inerr() if not isarray(sheetname_) then if sheetname_ = then inerr() end if if createtype_ = 2 then if not isarray(exceldata) then inerr() end if exit sub end if if isarray(sheetname_) then if not isarray(sheettitle_) then if sheettitle_ then inerr() end if end if if not isarray(exceldata) then inerr() end if if isarray(sheetname_) then if getarraydim(exceldata) 1 then inerr() else if getarraydim(exceldata) 2 then inerr() end if end sub rem excel public function create() call checkdata() if not isnull(readpath_) then excelapp.workbooks.open(readpath_) else excelapp.workbooks.add end if set excelbook = excelapp.activeworkbook set excelsheets = excelbook.worksheets if createtype_ = 2 then dim ih_ for ih_ = 0 to ubound(exceldata) call setsheets(exceldata(ih_), ih_) next excelbook.saveas savepath_ usedtime_ = formatnumber(timer - usedtime_)*1000, 3) exit function end if if isarray(sheetname_) then dim ik_ for ik_ = 0 to ubound(exceldata) call createsheets(exceldata(ik_), ik_) next else call createsheets(exceldata, -1) end if excelbook.saveas savepath_ usedtime_ = formatnumber(timer - usedtime_)*1000, 3) end function private sub createsheets(byval data_, dataid_) dim spreadsheet dim tempsheettitle dim temptitlefirstline if dataid_-1 then if dataid_ excelsheets.count - 1 then excelsheets.add() set spreadsheet = excelbook.sheets(1) else set spreadsheet = excelbook.sheets(dataid_ + 1) end if if isarray(sheettitle_) then tempsheettitle = sheettitle_(dataid_) else tempsheettitle = end if temptitlefirstline = titlefirstline(dataid_) = sheetname_(dataid_) else set spreadsheet = excelbook.sheets(1) = sheetname_ tempsheettitle = sheettitle_ temptitlefirstline = titlefirstline end if dim line_ : line_ = 1 dim rownum_ : rownum_ = ubound(data_, 1) + 1 dim lastcols_ if tempsheettitle then spreadsheet.columns(1).shrinktofit=true ( lastcols_ = getcolname(ubound(data_, 2) + 1) with spreadsheet.cells(1, 1) .value = tempsheettitle excel .font.bold = true .font.italic = false .font.size = 20 .= .font.colorindex=2 2 end with with spreadsheet.range(a1:& lastcols_ &1) .merge () .interior.colorindex = 1 .horizontalalignment = 3 end with line_ = 2 rownum_ = rownum_ + 1 end if dim irow_, icol_ dim drow_, dcol_ dim templastrange : templastrange = getcolname(ubound(data_, 2)+1) & (rownum_) dim beginrow : beginrow = 1 if tempsheettitle then beginrow = beginrow + 1 if temptitlefirstline = true then beginrow = beginrow + 1 if beginrow=1 then with spreadsheet.range(a1:& templastrange) .borders.linestyle = 1 .borderaround -4119, -4138 .numberformatlocal = .font.bold = false .font.italic = false .font.size = 10 .shrinktofit=true end with else with spreadsheet.range(a1:& templastrange) .borders.linestyle = 1 .borderaround -4119, -4138 .shrinktofit=true end with with spreadsheet.range(a& beginrow &:& templastrange) .numberformatlocal = .font.bold = false .font.italic = false .font.size = 10 end with end if if temptitlefirstline = true then beginrow = 1 if tempsheettitle then beginrow = beginrow + 1 with spreadsheet.range(a& beginrow &:& getcolname(ubound(data_, 2)+1) & (beginrow) .numberformatlocal = .font.bold = true .font.italic = false .font.size = 12 .interior.colorindex = 37 .horizontalalignment = 3 .font.colorindex=2 end with end if for irow_ = line_ to rownum_ for icol_ = 1 to (ubound(data_, 2) + 1) dcol_ = icol_ - 1 if tempsheettitle then drow_ = irow_ - 2 else drow_ = irow_ - 1 if not isnull(data_(drow_, dcol_) then with spreadsheet.cells(irow_, icol_) .value = data_(drow_, dcol_) end with end if next next set spreadsheet = nothing end sub rem private function isobjinstalled(strclassstring) on error resume next isobjinstalled = false err = 0 dim xtestobj set xtestobj = server.createobject(strclassstring) if 0 = err then isobjinstalled = true set xtestobj = nothing err = 0 end function rem private function getarraydim(byval arr) getarraydim = null dim i_, temp if isarray(arr) then for i_ = 1 to 60 on error resume next temp = ubound(arr, i_) if err.number 0 then getarraydim = i_ - 1 err.clear exit function end if next getarraydim = i_ end if end functionclass createexcel private createtype_ private savepath_ private readpath_ private authorstr rem private versionstr rem private systemstr rem private sheetname_ rem private sheettitle_ rem private exceldata rem private excelapp rem excel.application private excelbook private excelsheets private usedtime_ rem public titlefirstline rem private sub class_initialize() server.scripttimeout = 99999 usedtime_ = timer systemstr = lc00_createexcelserver authorstr = surnfu 31333716 versionstr = 1.0 if not isobjinstalled(excel.application) then inerr(excel.application) end if set excelapp = createobject(excel.a