asp操作excel方法.docx
《asp操作excel方法.docx》由会员分享,可在线阅读,更多相关《asp操作excel方法.docx(21页珍藏版)》请在冰点文库上搜索。
asp操作excel方法
asp操作excel方法
代码如下:
<%
‘*******************************************************************
‘使用说明
‘dima
‘seta=newcreateexcel
‘a.savepath=x‘保存路径
‘a.sheetname=工作簿名称‘多个工作表a.sheetname=array(工作簿名称一,工作簿名称二)
‘a.sheettitle=表名称‘可以为空多个工作表a.sheetname=array(表名称一,表名称二)
‘a.data=d‘二维数组‘多个工作表array(b,c)b与c为二维数组
‘dimrs
‘setrs=server.createobject(adodb.recordset)
‘rs.openselectid,classid,classnamefrom[class],conn,1,1
‘a.adddbdatars,字段名一,字段名二,工作簿名称,表名称,true‘true自动获取表字段名
‘a.adddatac,true,工作簿名称,表名称‘c二维数组true第一行是否为标题行
‘a.addtdatae,sheet1‘按模板生成c=array(array(aa1,内容),array(aa2,内容2))
‘a.create()
‘a.usedtime生成时间,毫秒数
‘a.savepath保存路径
‘seta=nothing
‘设置com组件的操作权限。
在命令行键入“dcomcnfg”,则进入com组件配置界面,选择microsoftexcel后点击属性按钮,将三个单选项一律选择自定义,编辑中将everyone加入所有权限
‘*******************************************************************
classcreateexcel
privatecreatetype_
privatesavepath_
privatereadpath_
privateauthorstrrem设置作者
privateversionstrrem设置版本
privatesystemstrrem设置系统名称
privatesheetname_rem设置表名
privatesheettitle_rem设置标题
privateexceldatarem设置表数据
privateexcelappremexcel.application
privateexcelbook
privateexcelsheets
privateusedtime_rem使用的时间
publictitlefirstlinerem首行是否标题
privatesubclass_initialize()
server.scripttimeout=99999
usedtime_=timer
systemstr=lc00_createexcelserver
authorstr=surnfu31333716
versionstr=1.0
ifnotisobjinstalled(excel.application)then
inerr(服务器未安装excel.application控件)
endif
setexcelapp=createobject(excel.application)
excelapp.displayalerts=false
excelapp.application.visible=false
createtype_=1
readpath_=null
endsub
privatesubclass_terminate()
excelapp.quit
ifisobject(excelsheets)thensetexcelsheets=nothing
ifisobject(excelbook)thensetexcelbook=nothing
ifisobject(excelapp)thensetexcelapp=nothing
endsub
publicpropertyletreadpath(byvalval)
ifinstr(val,:
)<0then
readpath_=trim(val)
else
readpath_=server.mappath(trim(val))
endif
endproperty
publicpropertyletsavepath(byvalval)
ifinstr(val,:
)<0then
savepath_=trim(val)
else
savepath_=server.mappath(trim(val))
endif
endproperty
publicpropertyletcreatetype(byvalval)
ifval<1andval<2then
createtype_=1
else
createtype_=val
endif
endproperty
publicpropertyletdata(byvalval)
ifnotisarray(val)then
inerr(表数据设置有误)
endif
exceldata=val
endproperty
publicpropertygetsavepath()
savepath=savepath_
endproperty
publicpropertygetusedtime()
usedtime=usedtime_
endproperty
publicpropertyletsheetname(byvalval)
ifnotisarray(val)then
ifval=then
inerr(表名设置有误)
endif
titlefirstline=true
else
redimtitlefirstline(ubound(val))
dimik_
forik_=0toubound(val)
titlefirstline(ik_)=true
next
endif
sheetname_=val
endproperty
publicpropertyletsheettitle(byvalval)
ifnotisarray(val)then
ifval=then
inerr(表标题设置有误)
endif
endif
sheettitle_=val
endproperty
rem检查数据
privatesubcheckdata()
ifsavepath_=theninerr(保存路径不能为空)
ifnotisarray(sheetname_)then
ifsheetname_=theninerr(表名不能为空)
endif
ifcreatetype_=2then
ifnotisarray(exceldata)then
inerr(数据载入错误,或者未载入)
endif
exitsub
endif
ifisarray(sheetname_)then
ifnotisarray(sheettitle_)then
ifsheettitle_<theninerr(表标题设置有误,与表名不对应)
endif
endif
ifnotisarray(exceldata)then
inerr(表数据载入有误)
endif
ifisarray(sheetname_)then
ifgetarraydim(exceldata)<1theninerr(表数据载入有误,数据格式错误,维度应该为一)
else
ifgetarraydim(exceldata)<2theninerr(表数据载入有误,数据格式错误,维度应该为二)
endif
endsub
rem生成excel
publicfunctioncreate()
callcheckdata()
ifnotisnull(readpath_)then
excelapp.workbooks.open(readpath_)
else
excelapp.workbooks.add
endif
setexcelbook=excelapp.activeworkbook
setexcelsheets=excelbook.worksheets
ifcreatetype_=2then
dimih_
forih_=0toubound(exceldata)
callsetsheets(exceldata(ih_),ih_)
next
excelbook.saveassavepath_
usedtime_=formatnumber((timer-usedtime_)*1000,3)
exitfunction
endif
ifisarray(sheetname_)then
dimik_
forik_=0toubound(exceldata)
callcreatesheets(exceldata(ik_),ik_)
next
else
callcreatesheets(exceldata,-1)
endif
excelbook.saveassavepath_
usedtime_=formatnumber((timer-usedtime_)*1000,3)
endfunction
privatesubcreatesheets(byvaldata_,dataid_)
dimspreadsheet
dimtempsheettitle
dimtemptitlefirstline
ifdataid_<-1then
ifdataid_excelsheets.count-1then
excelsheets.add()
setspreadsheet=excelbook.sheets
(1)
else
setspreadsheet=excelbook.sheets(dataid_+1)
endif
ifisarray(sheettitle_)then
tempsheettitle=sheettitle_(dataid_)
else
tempsheettitle=
endif
temptitlefirstline=titlefirstline(dataid_)
spreadsheet.name=sheetname_(dataid_)
else
setspreadsheet=excelbook.sheets
(1)
spreadsheet.name=sheetname_
tempsheettitle=sheettitle_
temptitlefirstline=titlefirstline
endif
dimline_:
line_=1
dimrownum_:
rownum_=ubound(data_,1)+1
dimlastcols_
iftempsheettitle<then
‘spreadsheet.columns
(1).shrinktofit=true‘设定是否自动适应表格单元大小(单元格宽不变)
lastcols_=getcolname(ubound(data_,2)+1)
withspreadsheet.cells(1,1)
.value=tempsheettitle
‘设置excel表里的字体
.font.bold=true‘单元格字体加粗
.font.italic=false‘单元格字体倾斜
.font.size=20‘设置单元格字号
.font.name=宋体‘设置单元格字体
‘.font.colorindex=2‘设置单元格文字的颜色,颜色可以查询,2为白色
endwith
withspreadsheet.range(a1:
&lastcols_&1)
.merge‘合并单元格(单元区域)
‘.interior.colorindex=1‘设计单元络背景色
.horizontalalignment=3‘居中
endwith
line_=2
rownum_=rownum_+1
endif
dimirow_,icol_
dimdrow_,dcol_
dimtemplastrange:
templastrange=getcolname(ubound(data_,2)+1)&(rownum_)
dimbeginrow:
beginrow=1
iftempsheettitle<thenbeginrow=beginrow+1
iftemptitlefirstline=truethenbeginrow=beginrow+1
ifbeginrow=1then
withspreadsheet.range(a1:
&templastrange)
.borders.linestyle=1
.borderaround-4119,-4138‘设置外框
.numberformatlocal=@‘文本格式
.font.bold=false
.font.italic=false
.font.size=10
.shrinktofit=true
endwith
else
withspreadsheet.range(a1:
&templastrange)
.borders.linestyle=1
.borderaround-4119,-4138
.shrinktofit=true
endwith
withspreadsheet.range(a&beginrow&:
&templastrange)
.numberformatlocal=@
.font.bold=false
.font.italic=false
.font.size=10
endwith
endif
iftemptitlefirstline=truethen
beginrow=1
iftempsheettitle<thenbeginrow=beginrow+1
withspreadsheet.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
endwith
endif
forirow_=line_torownum_
foricol_=1to(ubound(data_,2)+1)
dcol_=icol_-1
iftempsheettitle<thendrow_=irow_-2elsedrow_=irow_-1
ifnotisnull(data_(drow_,dcol_))then
withspreadsheet.cells(irow_,icol_)
.value=data_(drow_,dcol_)
endwith
endif
next
next
setspreadsheet=nothing
endsub
rem测试组件是否已经安装
privatefunctionisobjinstalled(strclassstring)
onerrorresumenext
isobjinstalled=false
err=0
dimxtestobj
setxtestobj=server.createobject(strclassstring)
if0=errthenisobjinstalled=true
setxtestobj=nothing
err=0
endfunction
rem取得数组维数
privatefunctiongetarraydim(byvalarr)
getarraydim=null
dimi_,temp
ifisarray(arr)then
fori_=1to60
onerrorresumenext
temp=ubound(arr,i_)
iferr.number<0then
getarraydim=i_-1
err.clear
exitfunction
endif
next
getarraydim=i_
endif
endfunction
privatefunctiongetnumformatlocal(datatype)
selectcasedatatype
casecurrency:
getnumformatlocal=¥#,##0.00_);(¥#,##0.00)
casetime:
getnumformatlocal=[$-f800]dddd,mmmmdd,yyyy
casechar:
getnumformatlocal=@
casecommon:
getnumformatlocal=g/通用格式
casenumber:
getnumformatlocal=#,##0.00_
caseelse:
getnumformatlocal=@
endselect
endfunction
publicsubadddbdata(byvalrsflied,byvalfliedtitle,byvaltempsheetname_,byvaltempsheettitle_,dbtitle)
ifrsflied.eofthenexitsub
dimcolnum_:
colnum_=rsflied.fields.count
dimrownum_:
rownum_=rsflied.recordcount
dimarrfliedtitle
ifdbtitle=truethen
fliedtitle=
dimig_
forig_=0tocolnum_-1
fliedtitle=fliedtitle&rsflied.fields.item(ig_).name
ifig_<colnum_-1thenfliedtitle=fliedtitle&,
next
endif
iffliedtitle<then
rownum_=rownum_+1
arrfliedtitle=split(fliedtitle,,)
ifubound(arrfliedtitle)<colnum_-1then
inerr(获取数据库表有误,列数不符)
endif
endif
dimtempdata:
redimtempdata(rownum_-1,colnum_-1)
dimix_,iy_
dimiz
iffliedtitle<theniz=rownum_-2elseiz=rownum_-1
forix_=0toiz
foriy_=0tocolnum_-1
iffliedtitle<then
ifix_=0then
tempdata(ix_,iy_)=arrfliedtitle(iy_)
tempdata(ix_+1,iy_)=rsflied(iy_)
else
tempdata(ix_+1,iy_)=rsflied(iy_)
endif
else
tempdata(ix_,iy_)=rsflied(iy_)
endif
next
rsflied.movenext
next
dimtempfirstline
iffliedtitle<thentempfirstline=trueelsetempfirstline=false
calladddata(tempdata,tempfirstline,tempsheetname_,tempsheettitle_)
endsub
publicsubadddata(byvaltempdate_,byvaltempfirstline_,byvaltempsheetname_,byvaltempsheettitle_)
ifnotisarray(exceldata)then
exceldata=tempdate_
titlefirstline=tempfirstline_
sheetname_=tempsheetname_
sheettitle_=tempsh