asp操作excel方法

★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创1/27asp操作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自动获取表字段名★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创2/27‘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加入所有权限‘*******************************************************************classcreateexcelprivatecreatetype_privatesavepath_privatereadpath_privateauthorstrrem设置作者privateversionstrrem设置版本privatesystemstrrem设置系统名称★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创3/27privatesheetname_rem设置表名privatesheettitle_rem设置标题privateexceldatarem设置表数据privateexcelappremexcel.applicationprivateexcelbookprivateexcelsheetsprivateusedtime_rem使用的时间publictitlefirstlinerem首行是否标题privatesubclass_initialize()server.scripttimeout=99999usedtime_=timersystemstr=lc00_createexcelserverauthorstr=surnfu31333716versionstr=1.0ifnotisobjinstalled(excel.application)theninerr(服务器未安装excel.application控件)endifsetexcelapp=createobject(excel.application)excelapp.displayalerts=falseexcelapp.application.visible=falsecreatetype_=1readpath_=null★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创4/27endsubprivatesubclass_terminate()excelapp.quitifisobject(excelsheets)thensetexcelsheets=nothingifisobject(excelbook)thensetexcelbook=nothingifisobject(excelapp)thensetexcelapp=nothingendsubpublicpropertyletreadpath(byvalval)ifinstr(val,:)<0thenreadpath_=trim(val)elsereadpath_=server.mappath(trim(val))endifendpropertypublicpropertyletsavepath(byvalval)ifinstr(val,:)<0thensavepath_=trim(val)elsesavepath_=server.mappath(trim(val))endif★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创5/27endpropertypublicpropertyletcreatetype(byvalval)ifval<1andval<2thencreatetype_=1elsecreatetype_=valendifendpropertypublicpropertyletdata(byvalval)ifnotisarray(val)theninerr(表数据设置有误)endifexceldata=valendpropertypublicpropertygetsavepath()savepath=savepath_endpropertypublicpropertygetusedtime()usedtime=usedtime_endpropertypublicpropertyletsheetname(byvalval)ifnotisarray(val)then★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创6/27ifval=theninerr(表名设置有误)endiftitlefirstline=trueelseredimtitlefirstline(ubound(val))dimik_forik_=0toubound(val)titlefirstline(ik_)=truenextendifsheetname_=valendpropertypublicpropertyletsheettitle(byvalval)ifnotisarray(val)thenifval=theninerr(表标题设置有误)endifendifsheettitle_=valendpropertyrem检查数据★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创7/27privatesubcheckdata()ifsavepath_=theninerr(保存路径不能为空)ifnotisarray(sheetname_)thenifsheetname_=theninerr(表名不能为空)endififcreatetype_=2thenifnotisarray(exceldata)theninerr(数据载入错误，或者未载入)endifexitsubendififisarray(sheetname_)thenifnotisarray(sheettitle_)thenifsheettitle_<theninerr(表标题设置有误，与表名不对应)endifendififnotisarray(exceldata)theninerr(表数据载入有误)endififisarray(sheetname_)thenifgetarraydim(exceldata)<1theninerr(表数★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创8/27据载入有误，数据格式错误，维度应该为一)elseifgetarraydim(exceldata)<2theninerr(表数据载入有误，数据格式错误，维度应该为二)endifendsubrem生成excelpublicfunctioncreate()callcheckdata()ifnotisnull(readpath_)thenexcelapp.workbooks.open(readpath_)elseexcelapp.workbooks.addendifsetexcelbook=excelapp.activeworkbooksetexcelsheets=excelbook.worksheetsifcreatetype_=2thendimih_forih_=0toubound(exceldata)callsetsheets(exceldata(ih_),ih_)nextexcelbook.saveassavepath_★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创9/27usedtime_=formatnumber((timer-usedtime_)*1000,3)exitfunctionendififisarray(sheetname_)thendimik_forik_=0toubound(exceldata)callcreatesheets(exceldata(ik_),ik_)nextelsecallcreatesheets(exceldata,-1)endifexcelbook.saveassavepath_usedtime_=formatnumber((timer-usedtime_)*1000,3)endfunctionprivatesubcreatesheets(byvaldata_,dataid_)dimspreadsheetdimtempsheettitledimtemptitlefirstlineifdataid_<-1thenifdataid_excelsheets.count-1then★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创10/27excelsheets.add()setspreadsheet=excelbook.sheets(1)elsesetspreadsheet=excelbook.sheets(dataid_+1)endififisarray(sheettitle_)thentempsheettitle=sheettitle_(dataid_)elsetempsheettitle=endiftemptitlefirstline=titlefirstline(dataid_)spreadsheet.name=sheetname_(dataid_)elsesetspreadsheet=excelbook.sheets(1)spreadsheet.name=sheetname_tempsheettitle=sheettitle_temptitlefirstline=titlefirstlineendifdimline_:line_=1dimrownum_:rownum_=ubound(data_,1)+1dimlastcols_iftempsheettitle<then★精品文档★2016全新精品资料-全新公文范文-全程指导写作–独家原创11/27‘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=宋