asp操作excel方法

合集下载

在ASP-NET中读写Excel文件有四种解决方案

在ASP-NET中读写Excel文件有四种解决方案在中读写Excel文件有四种解决方案在应用程序常常会遇到需要从Excel 文件中读取数据或将数据写入Excel的需求。

一般来讲，在中读写Excel文件有四种解决方案。

1.1.1 使用OLE DB使用OLE DB可以以查询数据库的方式来读取Excel文件，因为在某种程度上Excel表格可以看成是一张一张的数据表。

其二者的主要区别在于所使用的数据引擎不一样。

使用OLE DB访问Excel的要点是计算机上必须具有Microsoft Access Data Component 2.6(MADC2.6)以上版本，同时在连接字符串上必须声明“Extended Properties=Excel 8.0”，这里的指定Excel 版本号如果高于8.0可能会出错，所以一般来讲必须使用Excel 8.0。

然后其它的写法就和一般的访问数据库一样了，打开连接，填充数据集，再关闭连接即可。

例如下面的实现代码：//创建一个数据链接string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =c:\\sample.xls;Extended Properties=Excel 8.0" ; OleDbConnection myConn = new OleDbConnection ( strCon ) ;string strCom = " SELECT * FROM [Sheet1$] " ;myConn.Open ( ) ;//打开数据链接，得到一个数据集OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;//创建一个DataSet对象myDataSet = new DataSet ( ) ;//得到自己的DataSet对象myCommand.Fill ( myDataSet , "[Sheet1$]" ) ; //关闭此数据链接myConn.Close ( ) ;代码7-1使用这种解决方案的优点是不需要其他的服务器组件，部署非常方便，但是其缺点也是明显的，用它来读取Excel 2003格式以上的文件会存在数据丢失的情况，而且也无法生成Excel文件。

ASP对Excel的所有操作

删除方案由三部分组成：
A：同一用户生成的Excel文件用同一个文件名，文件名可用用户ID号或SessionID号等可确信不重复字符串组成。这样新文件生成时自动覆盖上一文件。
B：在Global.asa文件中设置Session_onEnd事件激发时，删除这个用户的Excel暂存文件。
C：在Global.asa文件中设置Application_onStart事件激发时，删除暂存目录下的所有文件。
6、绑定Chart图
objExcelApp.ActiveChart.Location 1
7、显示数据表
objExcelApp.ActiveChart.HasDataTable = True
8、显示图例
objExcelApp.ActiveChart.DataTable.ShowLegendKey = True
目录
一、环境配置
二、 ASP对Excel的基本操作
三、 ASP操作Excel生成数据表
四、 ASP操作E件浏览、下载、删除方案
六、附录
正文
一、环境配置
服务器端的环境配置从参考资料上看，微软系列的配置应该都行，即：
1．Win9x+PWS+Office
2、新建Excel文件
objExcelApp.WorkBooks.add
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)

使用Aspose插件对Excel操作

使⽤Aspose插件对Excel操作使⽤使⽤Aspose插件对Excel⽂档进⾏导⼊导出操作使⽤前请先下载Aspose插件引⽤Excel导⼊：前台使⽤file标签获取，submit⽅式提交。

<form id="form1" enctype="multipart/form-data" method="post"><table class="table-condensed"><tr><td class="text-right">导⼊表格：</td><td class="text-left"><input type="file" name="file1" class="btn btn-default btn-lg"/></td></tr><tr><td class="text-left"><input type="submit" id="btnImport" name="btnImport" value="导⼊" class="btn btn-default"/></td></tr></table></form>后台接收：HttpPostedFileBase fileBase = Request.Files["file1"];//这⾥获取名称与前台标签name保持⼀致if (fileBase != null){string filename = Path.GetFileName(fileBase.FileName);string extension = Path.GetExtension(filename);string path = "/Upload/Test/" + DateTime.Now.ToString("yyyyMMdd") + "/";Directory.CreateDirectory(Path.GetDirectoryName(Request.MapPath(path)));string newFilename = DateTime.Now.ToString("yyyyMMddHHmmssfff");string fullFileName = path + newFilename + extension;fileBase.SaveAs(Request.MapPath(fullFileName)); try{ Stopwatch sw = new Stopwatch();//记录导⼊操作⽤时多长sw.Start();//这⾥可放⼊BLL⽅法处理string result = new ProductBLL().ImportExcel(Request.MapPath(path), newFilename, extension);//BLL⽅法 ProductBLLpublic string ImportExcel(string path, string filename, string extension){Workbook workbook = new Workbook(path + filename + extension);Worksheet worksheet = workbook.Worksheets[0];Cells cells = worksheet.Cells;for (int i = 1; i < cells.Rows.Count; i++){try{string brand = cells[i, 0].StringValue.Trim();//获取列值string years = cells[i, 1].StringValue.Trim();}catch (Exception e){continue;}}return "OK";} sw.Stop();long runTime = sw.ElapsedMilliseconds / 1000; //获取到操作⽤时多少秒 } catch (Exception e){Log.Write("导⼊", "导⼊错误", "错误信息：" + e.Message);}}Excel导出：string path = "/Upload/Test/" + DateTime.Now.ToString("yyyyMMdd") + "/";Directory.CreateDirectory(Path.GetDirectoryName(Server.MapPath(path)));string newFilename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";string fullFileName = Server.MapPath(path + newFilename);public void ExportInfo(List<Test> list, string fullFileName){Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();Aspose.Cells.Worksheet cellSheet = workbook.Worksheets[0];cellSheet.PageSetup.LeftMargin = 0.3;//左边距cellSheet.PageSetup.RightMargin = 0.3;//右边距cellSheet.PageSetup.TopMargin = 1;//上边距cellSheet.PageSetup.BottomMargin = 0.5;//下边距cellSheet.PageSetup.FooterMargin = 0.5;//页脚cellSheet.PageSetup.HeaderMargin = 0.5;//页眉cellSheet.PageSetup.Orientation = ndscape;cellSheet.PageSetup.CenterHorizontally = true;//⽔平居中cellSheet.PageSetup.CenterVertically = true;cellSheet.Cells[0, 0].PutValue("货号");cellSheet.Cells[0, 1].PutValue("颜⾊");cellSheet.Cells[0, 2].PutValue("尺码");int i = 1;foreach (var item in list){cellSheet.Cells[i, 0].PutValue(item.productno);cellSheet.Cells[i, 1].PutValue(item.size);cellSheet.Cells[i, 2].PutValue(item.color);i++;}cellSheet.AutoFitColumns();fullFileName = Path.GetFullPath(fullFileName);workbook.Save(fullFileName);}return File(fullFileName, "application/ms-excel", UserName + "_Test单" + newFilename);// ⽅法Action⾥直接返回File⽂件下载。

在ASP中怎么把页面中的数据导出到EXCEL

使用asp怎样将数据导出到excel文件 Web注意：两个函数中的“data“是网页中要导出的table的 id<input type="hidden" name="out_word" onclick="vbscript:buildDoc" value="导出到word" class="notPrint"><input type="hidden" name="out_excel" onclick="AutomateExcel();" value="导出到excel" class="notPrint">导出到Excel代码<SCRIPT LANGUAGE="JavaScript"></SCRIPT>导出到Word代码<script language="vbscript">Sub buildDocset table = document.all.datarow = table.rows.lengthcolumn = table.rows(1).cells.lengthSet objWordDoc = CreateObject("Word.Document")'objWordDoc.Application.Documents.Add theTemplate, FalseobjWordDoc.Application.Visible=TrueDim theArray(20,10000)for i=0 to row-1for j=0 to column-1theArray(j+1,i+1) = table.rows(i).cells(j).innerTEXTnextnextobjWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("综合查询结果集") //显示表格标题objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("") Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range With rngPara.Bold = True //将标题设为粗体.ParagraphFormat.Alignment = 1 //将标题居中 = "隶书" //设定标题字体.Font.Size = 18 //设定标题字体大小End WithSet rngCurrent = objWordDoc.Application.ActiveDocument.Paragraphs(3).RangeSet tabCurrent = ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column)for i = 1 to columnobjWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.InsertAfter theArray(i,1)objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.Paragraph Format.alignment=1nextFor i =1 to columnFor j = 2 to rowobjWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.InsertAfter theArray(i,j)objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.Paragraph Format.alignment=1NextNextEnd Sub</SCRIPT>在ASP中怎么把页面中的数据导出到EXCEL直接读SQL库，我想也可以用来解决你的问题，（同理：页面上显示的内容当然也是读库的，除非你是静态的那算了）<title>生成报表</title><%dim conn,strconnset conn=server.CreateObject("adodb.connection")conn.Open ConnStrdim rs,sql,filename,fs,myfile,xSet fs = server.CreateObject("scripting.filesystemobject")filepath=Request.ServerVariables("APPL_PHYSICAL_PATH")filename = filepath&"temp_xls\"&year(now)&month(now)&day(now)&".xls"if fs.FileExists(filename) thenfs.DeleteFile(filename)end ifset myfile = fs.CreateTextFile(filename,true)Set rs = Server.CreateObject("ADODB.Recordset")sql = "select * from jdxx"rs.Open sql,conn,1,1if rs.EOF and rs.BOF thenelsedim strLine,responsestrstrLine=""For each x in rs.fieldsstrLine = strLine & & chr(9)Nextmyfile.writeline strLineDo while Not rs.EOFstrLine=""for each x in rs.FieldsstrLine = strLine & x.value & chr(9)nextmyfile.writeline strLiners.MoveNextloopend ifrs.Closeset rs = nothingremotefile="http://xxx.xxx.x.xxx/temp_xls/"&year(now)&month(now)&day(now)&".xls" response.write "<font size=2 color=blue>报表巳生成，<a href="&remotefile&">请点击这里下载该报表!</a></font>"%>在ASP中怎么把页面中的数据导出到EXCEL<%@ LANGUAGE="VBSCRIPT" CODEPAGE="950"%><%'關鍵所在Response.ContentType = "application/vnd.ms-excel"Set conn=Server.CreateObject("ADODB.Connection")Set rs=Server.CreateObject("ADODB.Recordset")strconn = "Provider = SQLOLEDB; Data Source = 192.168.0.2; Uid=gt_bbs;Pwd=gt_bbs;DataBase=gt_bbs"conn.open strconnSQL="Select top 100 id,uid,uer,bm,zw,zb,gxrq,ip,be From Gt_user order by id desc"rs.Open SQL,conn,3,1if rs.eof and rs.bof thenResponse.Write"<div align=center><br>沒有任何記錄</div>"else%><TABLE cellSpacing=0 cellPadding=0 width="100%" border=1><TR><TD width=12% height="25" class=borderon> 代 </TD> <TD width="11%" class=borderon>  名</TD><TD width="11%" class=borderon> 部門</TD><TD width="14%" class=borderon>  </TD><TD width="6%" class=borderon>  別</TD><TD width="16%" class=borderon>  登</TD><TD width="16%" class=borderon>  登 IP</TD> </TR></TABLE><TABLE width="100%" border=1 cellPadding=0 cellSpacing=0><%do while (Not RS.Eof) and (I<RS.PageSize)%><TR bgcolor=<%=bg2%>><TD class=all width=12% height=20 > <%=rs(1)%></TD> <TD width="11%" class=all> <%=rs(2)%></TD><TD width="11%" class=all> <%=rs(3)%></TD><TD width="14%" class=all> <%=rs(4)%></TD><TD width="6%" class=all> <%=rs(5)%></TD><TD width="16%" class=all> <%=rs(6)%></TD><TD width="16%" class=all> <%=rs(7)%></TD></TR><%Rs.MoveNextLoopend IFSet Conn = NothingSet Rs = Nothing%></TABLE>。

用ASP访问excel中的数据

用ASP访问excel中的数据1、建立一个链接文件名为conn。

<%db="D:\标件.xlsx" '用的是数据实际路径哈’set conn =server.CreateObject("adodb.connection")connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & db + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"conn.open connstr ’打开excel%>2、查询excel表格中的内容。

<%sqlstr="select * from [标件2010年$] order by 序列desc" ‘查询语句set rs =server.CreateObject("adodb.recordset")rs.open sqlstr,conn,0,1rs.movefirstdo while not rs.eof %><tr><td width='50' height='20'><%response.write(rs("序列"))%></td><td width='70' height='20'><%response.write(rs("标准"))%></td><td width='100' height='20'><%response.write(rs("名称"))%></td><td width='60' height='20'><%response.write(rs("材质"))%></td><td width='120' height='20'><%response.write(rs("规格型号"))%></td><td width="60" height="20"><%response.write(rs("单位"))%></td><td width="120" height="20"><%response.write(rs("入库数量"))%></td><td width="50" height="20"><%response.write(rs("入库时间"))%></td><td width="80" height="20"><%response.write(rs("出库数量"))%></td><td width="80" height="20"><%response.write(rs("出库时间"))%></td><td width="80" height="20"><%response.write(rs("领用人"))%></td><td width="70" height="20"><%response.write(rs("合同号"))%></td><td width="80" height="20"><%response.write(rs("库存数量"))%></td><td width="60" height="20"><%response.write(rs("备注"))%></td></tr><%rs.movenextloopresponse.Write"</table>"conn.close%>。

ASP导出Excel数据的四种方法

ASP导出Excel数据的四种方法一、使用OWC什么是OWC？OWC是Office Web Compent的缩写，即Microsoft的Office Web组件，它为在Web中绘制图形提供了灵活的同时也是最基本的机制。

在一个intranet环境中，如果可以假设客户机上存在特定的浏览器和一些功能强大的软件（如IE5和Office 2000），那么就有能力利用Office Web组件提供一个交互式图形开发环境。

这种模式下，客户端工作站将在整个任务中分担很大的比重。

<%Option ExplicitClass ExcelGenPrivate objSpreadsheetPrivate iColOffsetPrivate iRowOffsetSub Class_Initialize()Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")iRowOffset = 2iColOffset = 2End SubSub Class_Terminate()Set objSpreadsheet = Nothing "Clean up End SubPublic Property Let ColumnOffset(iColOff) If iColOff > 0 theniColOffset = iColOffElseiColOffset = 2End IfEnd PropertyPublic Property Let RowOffset(iRowOff) If iRowOff > 0 theniRowOffset = iRowOffElseiRowOffset = 2End IfEnd Property Sub GenerateWorksheet(objRS)"Populates the Excel worksheet based on a Recordset"s contents"Start by displaying the titlesIf objRS.EOF then Exit SubDim objField, iCol, iRowiCol = iColOffsetiRow = iRowOffsetFor Each objField in objRS.FieldsobjSpreadsheet.Cells(iRow, iCol).Value = /doc/71b0b63383c4bb4cf7ecd11a.htmlobjSpreadsheet.Columns(iCol).AutoFitColumns"设置Excel表里的字体objSpreadsheet.Cells(iRow, iCol).Font.Bold = True objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 10 objSpreadsheet.Cells(iRow, iCol).Halignment = 2 "居中iCol = iCol + 1Next "objField"Display all of the dataDo While Not objRS.EOFiRow = iRow + 1iCol = iColOffsetFor Each objField in objRS.FieldsIf IsNull(objField.Value) thenobjSpreadsheet.Cells(iRow, iCol).Value = ""ElseobjSpreadsheet.Cells(iRow, iCol).Value = objField.Value objSpreadsheet.Columns(iCol).AutoFitColumns objSpreadsheet.Cells(iRow, iCol).Font.Bold = False objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 10End IfiCol = iCol + 1Next "objFieldobjRS.MoveNextLoopEnd Sub Function SaveWorksheet(strFileName)"Save the worksheet to a specified filenameOn Error Resume NextCall objSpreadsheet.ActiveSheet.Export(strFileName, 0)SaveWorksheet = (Err.Number = 0)End FunctionEnd ClassDim objRSSet objRS = Server.CreateObject("ADODB.Recordset")objRS.Open "SELECT * FROM xxxx", "Provider=SQLOLEDB.1;Persist SecurityInfo=True;User ID=xxxx;Password=xxxx;Initial Catalog=xxxx;Data source=xxxx;"Dim SaveNameSaveName = Request.Cookies("savename")("name")Dim objExcelDim ExcelPathExcelPath = "Excel\" & SaveName & ".xls"Set objExcel = New ExcelGenobjExcel.RowOffset = 1objExcel.ColumnOffset = 1objExcel.GenerateWorksheet(objRS)If objExcel.SaveWorksheet(Server.MapPath(ExcelPath)) then "Response.Write "已保存为Excel文件.下载"ElseResponse.Write "在保存过程中有错误!"End IfSet objExcel = NothingobjRS.CloseSet objRS = Nothing%>二、用Excel的Application组件在客户端导出到Excel或Word 注意：两个函数中的“data“是网页中要导出的table的 id导出到Excel代码导出到Word代码三、直接在IE中打开，再存为EXCEL文件把读出的数据用格式，在网页中显示出来，同时，加上下一句即可把EXCEL表在客客户端显示。

ASP导入Excel表格

201412261323_ASP导入Excel表格导入EXCEL表格常用于批处理数据或者数据迁移项目。

导入代码如下:<%Set ConnExcel=Server.CreateObject("ADODB.Connection")'--利用Open 方法打开数据库StrExcelConn="Driver={Microsoft Excel Driver (*.xls)};"&"DriverId=790; DBQ="&Server.MapPath("card.xls") 'EXCEL文件路径ConnExcel.Open StrExcelConn'--建立数据集对象Rs并查询数据Set Rs = Server.CreateObject("ADODB.Recordset")Sql="select * from [default$]" '特别注意EXCEL表名要一致rs.Open Sql,ConnExcel,1,1do while not rs.eofrsx=server.createobject("adodb.recordset")setsqlx="select * from pcard"sqlx,conn,1,3rsx.openrsx.addnewrsx("cardnum")=rs(0)rsx("cardpwd")=rs(1)rsx("cardpay")=cint(rs(2))rsx.updatersx.closersx=nothingsetrs.MoveNextlooprs.closeset rs=nothingConnExcel.closeset ConnExcel=nothingCall Closeconn()response.write "<script language=JavaScript>"response.Write "alert('导入成功！'); "response.Write "</script>"Response.end()%>人生最精彩的不是实现梦想的瞬间，而是坚持梦想的过程。

ASP应用程序中实现Excel报表处理

本文针对读取Ｅｃｌｘｅ数据并写入Ｍｉｏｏｔｃｅｓｃｓｆｃｓ数ｒＡ
２编程实现Ｅｃｌｘｅ报表处理
２１ｘｅ数据导入数据库的实现．Ｅｃｌ读出Ｅｃｌｘｅ文档中数据的主要操作步骤为：）（１
获得Ｅｃｌ档的路径和文件名，打开Ｅｃｌｘｅ文ｘｅ文档；２打开工作表，（）建立记录集．这里需要特别说明的是，要打开的工作表名一定要写成 “ 名￥ｔ【表】ｔ
据库和南Ｍｉｏｏｃｅｓ数据库中的数据生成ｃｓｆＡｃｓｒｔ
Ｅｃｌ档，ቤተ መጻሕፍቲ ባይዱ出编程实现方法，ｘｅ文提并给出源程序．
１ＡＳＰ程序访问Ｅｃｌｘｅ文档的基本原理
ＡＰ是一种服务器端脚本执行环境，使用它Ｓ
可以创建和运行动态、互的Ｗｅ应用程序．Ｐ交ｂＡＳ
的形式；（）３读各单元格的值．其实现过程如下．
＜ｈｍｌｔ＞＜ｈｅｄａ＞＜ｍｅｈｔ－ｑｖＣＯｅ — ｐ ”ｃｔｎ＝”ｅ／ｔｌｃａｓｔｍｔｐｅｕｉ＝” ｎｔｍＴｙｅｏｎｅｔｔｘｔｍ；ｈｒｅ＝－ｈ－ｇｂ２３１ ” ２＞
可以结合ＨＴ、本命令和ＡｃｃＸ组件，同ＭＬ脚ｔｅｉ共创建动态的Ｗｅ页和基于ｗｅ服务器的功能强大ｂｂ
的应用程序．ＳＡＰ可以通过ＡＯＡｃｖＸＤｔＯ．Ｄ（ｔｅａｂｉａ

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

asp操作excel方法代码如下:<%‘************************************************* ******************‘使用说明‘dim a‘set a=new createexcel‘a.savepath=x ‘保存路径‘a.sheetname=工作簿名称‘多个工作表a.sheetname=array(工作簿名称一,工作簿名称二)‘a.sheettitle=表名称‘可以为空多个工作表a.sheetname=array(表名称一,表名称二)‘a.data =d ‘二维数组‘多个工作表 array(b,c) b 与c为二维数组‘dim rs‘set rs=server.createobject(adodb.recordset)‘rs.open select id, classid, classname from [class] ,conn, 1, 1‘a.adddbdata rs, 字段名一，字段名二, 工作簿名称, 表名称, true ‘true自动获取表字段名‘a.adddata c, true , 工作簿名称, 表名称‘c二维数组 true 第一行是否为标题行‘a.addtdata e, sheet1 ‘按模板生成c=array(array(aa1, 内容), array(aa2, 内容2)) ‘a.create()‘edtime 生成时间，毫秒数‘a.savepath 保存路径‘set a=nothing‘设置com组件的操作权限。

在命令行键入“dcomcnfg”，则进入com组件配置界面，选择microsoftexcel后点击属性按钮，将三个单选项一律选择自定义，编辑中将everyone 加入所有权限‘************************************************* ******************class createexcelprivate 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.applicationprivate excelbookprivate excelsheetsprivate usedtime_ rem 使用的时间public titlefirstline rem 首行是否标题private sub class_initialize()server.scripttimeout = 99999usedtime_ = timersystemstr = lc00_createexcelserverauthorstr = surnfu 31333716versionstr = 1.0if not isobjinstalled(excel.application) then inerr(服务器未安装excel.application控件)end ifset excelapp = createobject(excel.application) excelapp.displayalerts = falseexcelapp.application.visible = falsecreatetype_ = 1readpath_ = nullend subprivate sub class_terminate()excelapp.quitif isobject(excelsheets) then set excelsheets = nothingif isobject(excelbook) then set excelbook = nothingif isobject(excelapp) then set excelapp = nothing end subpublic property let readpath(byval val)if instr(val, :)<0 thenreadpath_ = trim(val)elsereadpath_=server.mappath(trim(val))end ifend propertypublic property let savepath(byval val)if instr(val, :)<0 thensavepath_ = trim(val)elsesavepath_=server.mappath(trim(val))end ifend propertypublic property let createtype(byval val) if val < 1 and val < 2 thencreatetype_ = 1elsecreatetype_ = valend ifend propertypublic property let data(byval val)if not isarray(val) theninerr(表数据设置有误)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(表名设置有误)end 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(表标题设置有误)end ifend ifsheettitle_ = valend propertyrem 检查数据private sub checkdata()if savepath_ = then inerr(保存路径不能为空)if not isarray(sheetname_) thenif sheetname_ = then inerr(表名不能为空)end ifif createtype_ = 2 thenif not isarray(exceldata) theninerr(数据载入错误，或者未载入)end ifexit subend ifif isarray(sheetname_) thenif not isarray(sheettitle_) thenif sheettitle_ < then inerr(表标题设置有误，与表名不对应)end ifend ifif not isarray(exceldata) theninerr(表数据载入有误)end ifif isarray(sheetname_) thenif getarraydim(exceldata) < 1 then inerr(表数据载入有误，数据格式错误，维度应该为一)elseif getarraydim(exceldata) < 2 then inerr(表数据载入有误，数据格式错误，维度应该为二)end ifend subrem 生成excelpublic function create()call checkdata()if not isnull(readpath_) thenexcelapp.workbooks.open(readpath_)elseexcelapp.workbooks.addend ifset excelbook = excelapp.activeworkbookset excelsheets = excelbook.worksheetsif createtype_ = 2 thendim ih_for ih_ = 0 to ubound(exceldata)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 ifexcelbook.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)elseset 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_tempsheettitle = sheettitle_temptitlefirstline = titlefirstlineend ifdim line_ : line_ = 1dim rownum_ : rownum_ = ubound(data_, 1) + 1dim 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 withwith spreadsheet.range(a1:& lastcols_ &1) .merge ‘合并单元格(单元区域)‘.interior.colorindex = 1 ‘设计单元络背景色 .horizontalalignment = 3 ‘居中end withline_ = 2rownum_ = rownum_ + 1end ifdim irow_, icol_dim drow_, dcol_dim templastrange : templastrange = getcolname(ubound(data_, 2)+1) & (rownum_) dim beginrow : beginrow = 1if tempsheettitle < then beginrow = beginrow + 1if temptitlefirstline = true then beginrow = beginrow + 1if beginrow=1 thenwith spreadsheet.range(a1:& templastrange) .borders.linestyle = 1.borderaround -4119, -4138 ‘设置外框.numberformatlocal = @ ‘文本格式.font.bold = false.font.italic = false.font.size = 10.shrinktofit=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 ifif temptitlefirstline = true thenbeginrow = 1if tempsheettitle < then beginrow = beginrow + 1with 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=2end withend iffor irow_ = line_ to rownum_for icol_ = 1 to (ubound(data_, 2) + 1)dcol_ = icol_ - 1if tempsheettitle < then drow_ = irow_ - 2 else drow_ = irow_ - 1if not isnull(data_(drow_, dcol_)) thenwith spreadsheet.cells(irow_, icol_).value = data_(drow_, dcol_)end withend ifnextnextset spreadsheet = nothingend subrem 测试组件是否已经安装private function isobjinstalled(strclassstring) on error resume nextisobjinstalled = falseerr = 0dim xtestobjset xtestobj = server.createobject(strclassstring) if 0 = err then isobjinstalled = trueset xtestobj = nothingerr = 0end functionrem 取得数组维数private function getarraydim(byval arr)getarraydim = nulldim i_, tempif isarray(arr) thenfor i_ = 1 to 60on error resume nexttemp = ubound(arr, i_)if err.number < 0 thengetarraydim = i_ - 1err.clearexit functionend ifnextgetarraydim = i_end ifend functionprivate function getnumformatlocal(datatype)select case datatypecase currency:getnumformatlocal = ￥#,##0.00_);(￥#,##0.00) case time:getnumformatlocal = [$-f800]dddd, mmmm dd, yyyy case char:getnumformatlocal = @case common:getnumformatlocal = g/通用格式case number:getnumformatlocal = #,##0.00_case else :getnumformatlocal = @end selectend functionpublic sub adddbdata(byval rsflied, byval fliedtitle, byval tempsheetname_, byval tempsheettitle_, dbtitle)if rsflied.eof then exit subdim colnum_ : colnum_ = rsflied.fields.countdim rownum_ : rownum_ = rsflied.recordcountdim arrfliedtitleif dbtitle = true thenfliedtitle =dim ig_for ig_=0 to colnum_ - 1fliedtitle = fliedtitle & rsflied.fields.item(ig_).nameif ig_ < colnum_ - 1 then fliedtitle = fliedtitle &,nextend ifif fliedtitle< thenrownum_ = rownum_ + 1arrfliedtitle = split(fliedtitle, ,)if ubound(arrfliedtitle) < colnum_ - 1 then inerr(获取数据库表有误，列数不符)end ifend ifdim tempdata : redim tempdata(rownum_ - 1, colnum_ - 1)dim ix_, iy_dim izif fliedtitle< then iz = rownum_ - 2 else iz = rownum_ - 1for ix_ = 0 to izfor iy_ = 0 to colnum_ - 1if fliedtitle< thenif ix_=0 thentempdata(ix_, iy_) = arrfliedtitle(iy_)tempdata(ix_ + 1, iy_) = rsflied(iy_)elsetempdata(ix_ + 1, iy_) = rsflied(iy_)end ifelsetempdata(ix_, iy_) = rsflied(iy_)end ifnextrsflied.movenextnextdim tempfirstlineif fliedtitle< then tempfirstline = true else tempfirstline = falsecall adddata(tempdata, tempfirstline,tempsheetname_, tempsheettitle_)end subpublic sub adddata(byval tempdate_, byval tempfirstline_, byval tempsheetname_, byval tempsheettitle_)if not isarray(exceldata) thenexceldata = tempdate_titlefirstline = tempfirstline_sheetname_ = tempsheetname_sheettitle_ = tempsheettitle_elseif getarraydim(exceldata) = 1 thendim temparrlen : temparrlen = ubound(exceldata)+1 redim preserve exceldata(temparrlen)exceldata(temparrlen) = tempdate_redim preserve titlefirstline(temparrlen)titlefirstline(temparrlen) = tempfirstline_redim preserve sheetname_(temparrlen)sheetname_(temparrlen) = tempsheetname_redim preserve sheettitle_(temparrlen)sheettitle_(temparrlen) = tempsheettitle_elsedim tempolddata : tempolddata = exceldataexceldata = array(tempolddata, tempdate_)titlefirstline = array(titlefirstline, tempfirstline_)sheetname_ = array(sheetname_, tempsheetname_) sheettitle_ = array(sheettitle_, tempsheettitle_) end ifend ifend subrem 模板增加数据方法public sub addtdata(byval tempdate_, byval tempsheetname_)createtype_ = 2if not isarray(exceldata) thenexceldata = array(tempdate_)sheetname_ = array(tempsheetname_)elsedim temparrlen : temparrlen = ubound(exceldata)+1 redim preserve exceldata(temparrlen)exceldata(temparrlen) = tempdate_redim preserve sheetname_(temparrlen)sheetname_(temparrlen) = tempsheetname_end ifend subprivate sub setsheets(byval data_, dataid_)dim spreadsheetset spreadsheet = excelbook.sheets(sheetname_(dataid_))spreadsheet.activatedim ix_for ix_ =0 to ubound(data_)if not isarray(data_(ix_)) then inerr(表数据载入有误，数据格式错误)if ubound(data_(ix_)) < 1 then inerr(表数据载入有误，数据格式错误)spreadsheet.range(data_(ix_)(0)).value = data_(ix_)(1)nextset spreadsheet = nothingend subpublic function gettime(msec_)dim retime_ : retime_=if msec_ < 1000 thenretime_ = msec_ &mselsedim second_second_ = (msec_ 1000)if (msec_ mod 1000)<0 thenmsec_ = (msec_ mod 1000) &毫秒elsemsec_ =end ifdim n_, arytime(2), arytimeunit(2)arytimeunit(0) = 秒arytimeunit(1) = 分arytimeunit(2) = 小时n_ = 0dim tempsecond_ : tempsecond_ = second_while(tempsecond_ / 60 = 1)tempsecond msec_< then retime_ = retime_ & msec_end ifgettime = retime_end functionrem 取得列名private function getcolname(byval colnum)dim arrlitter : arrlitter=split(a b c d e f g h i j k l m n o p q r s t u v w x y z, )dim revalue_if colnum <= ubound(arrlitter) + 1 thenrevalue_ = arrlitter(colnum - 1)elserevalue_ = arrlitter(((colnum-1) 26)) & arrlitter(((colnum-1) mod 26))end ifgetcolname = revalue_end functionrem 设置错误private sub inerr(errinfo)err.raise vbobjecterror + 1, systemstr &(version & versionstr &), errinfo end subend classdim b(4,6)dim c(50,20)dim i, jfor i=0 to 4for j=0 to 6b(i,j) =i&-&jnextnextfor i=0 to 50for j=0 to 20c(i,j) = i&-&j &我的nextnextdim e(20)for i=0 to 20e(i)= array(a&(i+1), i+1)next‘使用示例需要xx.xls模板支持‘set a=new createexcel‘a.readpath = xx.xls‘a.savepath=xx-1.xls‘a.addtdata e, sheet1‘a.create()‘response.write(生成& a.savepath & 使用了 & a.gettime(edtime) &<br) ‘set a=nothing‘使用示例一a.savepath=x.xlsa.adddata b, true , 测试c, 测试ca.titlefirstline = false ‘首行是否为标题行a.create()response.write(生成& a.savepath & 使用了 & a.gettime(edtime) &<br) set a=nothing‘使用示例二set a=new createexcela.savepath=y.xlsa.sheetname=工作簿名称‘多个工作表a.sheetname=array(工作簿名称一,工作簿名称二)a.sheettitle=表名称‘可以为空多个工作表a.sheetname=array(表名称一,表名称二)a.data =b ‘二维数组‘多个工作表 array(b,c) b 与c为二维数组a.create()response.write(生成& a.savepath & 使用了 & a.gettime(edtime) &<br) set a=nothing‘使用示例三生成两个表a.savepath=z.xlsa.sheetname=array(工作簿名称一,工作簿名称二)a.sheettitle=array(表名称一,表名称二)a.data =array(b, c) ‘b与c为二维数组a.titlefirstline = array(false, true) ‘首行是否为标题行a.create()response.write(生成& a.savepath & 使用了 & a.gettime(edtime) &<br) set a=nothing‘使用示例四需要数据库支持‘dim rs‘set rs=server.createobject(adodb.recordset)‘rs.open select id, classid, classname from [class] ,conn, 1, 1‘set a=new createexcel‘a.savepath=a‘a.adddbdata rs, 序号,类别序号,类别名称, 工作簿名称, 类别表, false‘a.create()‘response.write(生成& a.savepath & 使用了 & a.gettime(edtime) &<br) ‘set a=nothing‘rs.close‘set rs=nothing%。