asp讀取excel代碼(舉了四個(gè)excel不同實(shí)例應(yīng)用)

1、asp? de ?excel ?(? ud ?excel2? i ?ed y o | o ?).txt37?3? e ? d ? ? Y? ? ? do ?3? e ?e ? u ? |? d?2? ou ? dd ? ?3? e ? e ? ? oue ? ?e ?o Dod ?public property let createtype(byval val)if val 1 and val 2 thencreatetype_ = 1elsecreatetype_ = valend ifend propertypublic property let data(byval val)if not

2、 isarray(val) theninerr(ie y?Yee ? o D?o )end ifexceldata = valend propertypublic property get savepath()savepath = savepath_end propertypublic property get usedtime()usedtime = usedtime_end propertypublic property let sheetname(byval val)if not isarray(val) thenif val = theninerr( ? ee ? o D?o )end

3、 iftitlefirstline = trueelseredim titlefirstline(ubound(val)dim ik_for ik_ = 0 to ubound(val)titlefirstline(ik_) = truenextend ifsheetname_ = valend propertypublic property let sheettitle(byval val)if not isarray(val) thenif val = theninerr(土 ei aee ? o D?o )end ifend ifsheettitle_ = valend property

4、rem ?1 2ee y?Yprivate sub checkdata()if savepath_ = then in err( ? ?2? u ?a?)if not isarray(sheetname_) thenif sheetname_ = then inerr( ?2? u ?a?)end ifif createtype_ = 2 thenif not isarray(exceldata) theninerr(e y?Y?e ?i ?o ? o ? ? e ?)end ifexit subend ifif isarray(sheetname_) thenif not isarray(s

5、heettitle_) thenthenthenthenifsheettitle_in err(土ei aee ? o D?o ?o ? ?2? o |)end ifend ifif not isarray(exceldata) theninerr(e y?Y?e ?o D?o )end ifif isarray(sheetname_) thenifgetarraydim(exceldata)1inerr(e y?Y?e ?o D?o ?e y?Y?e ?i ?o ? eo |?a o ?)elseifgetarraydim(exceldata)2inerr(e y?Y? e ?o D?o ?

6、e y?Y? e ?i ?o ? eo |?a?t)end ifend subrem eu 3 e excelpublic function create()call checkdata()if not isnull(readpath_) thenexcelapp.workbooks.open(readpath_)elseend ifset excelbook = excelapp.activeworkbookset excelsheets = excelbook.worksheetsif createtype_ = 2 thendim ih_for ih_ = 0 to ubound(exc

7、eldata)call setsheets(exceldata(ih_), ih_)nextexcelbook.saveas savepath_usedtime_ = formatnumber(timer - usedtime_)*1000, 3) exit functionend ifif isarray(sheetname_) thendim ik_for ik_ = 0 to ubound(exceldata)call createsheets(exceldata(ik_), ik_)nextelsecall createsheets(exceldata, -1)end ifexcelb

8、ook.saveas savepath_ usedtime_ = formatnumber(timer - usedtime_)*1000, 3) end functionprivate sub createsheets(byval data_, dataid_)dim spreadsheetdim tempsheettitledim temptitlefirstlineif dataid_-1 thenif dataid_ excelsheets.count - 1 thenexcelsheets.add()set spreadsheet = excelbook.sheets(1)elses

9、et spreadsheet = excelbook.sheets(dataid_ + 1)end ifif isarray(sheettitle_) thentempsheettitle = sheettitle_(dataid_)elsetempsheettitle = end iftemptitlefirstline = titlefirstline(dataid_) = sheetname_(dataid_)elseset spreadsheet = excelbook.sheets(1) = sheetname_ tem

10、psheettitle = sheettitle_ temptitlefirstline = titlefirstline end if dim line_ : line_ = 1 dim rownum_ : rownum_ = ubound(data_, 1) + 1 dim lastcols_ if tempsheettitle thenspreadsheet.columns(1).shrinktofit=trueee ?e ? ?X ? eeo | i ? ?a6 D?(?a? i 2? ?lastcols_ = getcolname(ubound(data_, 2) + 1)with

11、spreadsheet.cells(1, 1) .value = tempsheettitlee e ?excel.font.bold = true .font.italic = false.font.size = 20 .=?d ?卩?x ? i ?ii ?a? x ? i ? o ?ii ?a? x ? i ?Dee ?卩 ?a? x ?o?i ? ee ?卩 ?a? x ? i ?.font.colorindex=2ee ?卩 ?a? x ?卩? e ? ? e ? eo ?2 e ?_ ?2?axe end withwith spreadsheet.range(a1:

12、& lastcols_ &1).merge o?2C ?a?(卩 ?a? oo ).i nterior.colori ndex = 1 ee ?卩 ?a? 3?e.horizontalalignment = 3 ?o?Dend withline_ = 2rownum_ = rownum_ + 1end ifdim irow_, icol_dim drow_, dcol_dim templastrange : templastrange = getcolname(ubound(data_,2)+1) & (rownum_)dim beginrow : beginrow = 1if tempshe

13、ettitle then beginrow = beginrow + 1if temptitlefirstline = true then beginrow = beginrow + 1if beginrow=1 thenwith spreadsheet.range(a1:& templastrange)ee ? i a? o?e.borders.linestyle = 1 .borderaround -4119, -4138 .numberformatlocal = ? .font.bold = false.font.italic = false.font.size = 10.shrinkt

14、ofit=trueend withelsewith spreadsheet.range(a1:& templastrange).borders.linestyle = 1.borderaround -4119, -4138 .shrinktofit=trueend withwith spreadsheet.range(a& beginrow &:& templastrange) .numberformatlocal = .font.bold = false .font.italic = false.font.size = 10end withend ifbeginrow&:&if tempti

15、tlefirstline = true then beginrow = 1 if tempsheettitle then beginrow = beginrow + 1with spreadsheet.range(a& getcolname(ubound(data_, 2)+1) & (beginrow) .numberformatlocal = .font.bold = true .font.italic = false .font.size = 12 .interior.colorindex = 37.horizontalalignment = 3 ?o ?D.font.colorinde

16、x=2end withend iffor irow_ = line_ to rownum_for icol_ = 1 to (ubound(data_, 2) + 1)dcol_ = icol_ - 1drow_iftempsheettitle then drow_ = irow_ - 2 else= irow_ - 1if not isnull(data_(drow_, dcol_) thenwith spreadsheet.cells(irow_, icol_).value = data_(drow_, dcol_)end withend if next nextset spreadshe

17、et = nothing end subrem 2a e ?Xe ?t e ? ?o ?- 2Xprivate function isobjinstalled(strclassstring)on error resume nextisobjinstalled = falseerr = 0dim xtestobjset xtestobj = server.createobject(strclassstring) if 0 = err then isobjinstalled = trueset xtestobj = nothingerr = 0end functionrem e ?卩? e yxe

18、 ? e yprivate function getarraydim(byval arr) getarraydim = null dim i_, tempif isarray(arr) thenfor i_ = 1 to 60on error resume nexttemp = ubound(arr, i_) if err.number 0 thengetarraydim = i_ - 1 err.clear exit functionend ifnext getarraydim = i_end ifend functionee ?X- ?ee?ee ?叮 3?3?ee ?+ ?ee ?+ei

19、 aee ?ie y?Yclass createexcel private createtype_ private savepath_ private readpath_ private authorstrremprivate versionstr rem private systemstrremprivate sheetname_remprivate sheettitle_ rem private exceldataremprivate excelbooke 1 o ? e ? eX DDe ? ?ei aprivate excelsheets private usedtime_ rem public titlefirstline rem private sub class_initialize() server.scripttimeout = 99999 usedtime_ = timersystemstr= lc00_createexcelserverauthorstr = surnfu surnfu126 31333716 versionstr= 1.0if not isobjinstalled(excel.application) the