ASP实例代码 asp操作Excel类

合集下载

Aspose.Cells操作导出实例(模板导出)

本篇中将简单记录下Aspose.Cells这个强大的Excel操作组件。

这个组件的强大之处，就不多说，对于我们的报表总是会有导出Excel的处理，如果你使用微软Excel的com组件，那么对于简单的操作还行，但是多余复杂的模板，那将是一个令人头疼的事。

在Aspose.Cells之下，将是一个简单的事情。

他可以导入导出excel操作，在本节将要说的就是他的际遇excel模板的导出强大功能。

多的不说，我们先来利用Northwind做两个小demo。

先说说Aspose.Cells的模板语法：1.&=DataSource.Field，&=[DataSource].[Field]是对DataTable和几何类型的引用，将会从当前行开始竖直向下生成多行数据。

2.&=$data:是对变量或数组的引用。

数组存在skip，horizontal等属性，具体参见官方网站3.&=&=动态公式计算；{r}当前行，{c}当前列，{-n}，{n}当前行或列的偏移量前n或后n。

4.&==是动态计算，如excel，if等语句。

（if（logic_test,true_value,false_value））还有其他更为复杂的汇总计算的表达式，这里也不在这节多讲，有兴趣的朋友，可以去官网看看。

我们先来个简单的例子，光说，总是不行的，还是要代码实践才能说明一切：Excel模板1：代码：在我们的代码中添加数据源：如下：View Code1var sql = @"select * from Customers2 where Customers.City ='London'";3 var dt=GetDataTable(sql);4 dt.TableName = "Customers";5 WorkbookDesigner designer = new WorkbookDesigner();6 designer.Open(MapPath("~/1.xls"));7//数据源8 designer.SetDataSource(dt);9//报表单位10 designer.SetDataSource("ReportUtils", "xxxxx有限公司客户信息");11 designer.SetDataSource("ReportAdd", "London");12//截止日期13 designer.SetDataSource("ReportDate", DateTime.Now.ToString("yyyy年MM月dd日"));1415 designer.Process();1617 designer.Save(string.Format("report.xls"), SaveType.OpenInExcel, FileFormatType.E xcel2003, Response);18 Response.Flush();19 Response.Close();20 designer = null;21 Response.End();代码很简单，就是添加了一个datatable,和几个变量的数据源，我们所生成excel为：这就完成了我们的一个简单的多表头数据导出报表。

c#Aspose.Cells通过Excel模板生产excel数据再打印

c#Aspose.Cells通过Excel模板⽣产excel数据再打印多的不说，我们先来利⽤Northwind做两个⼩demo。

先说说Aspose.Cells的模板语法：1. &=DataSource.Field，&=[DataSource].[Field]是对DataTable和⼏何类型的引⽤，将会从当前⾏开始竖直向下⽣成多⾏数据。

2. &=$data:是对变量或数组的引⽤。

数组存在skip，horizontal等属性，具体参见3. &=&=动态公式计算；{r}当前⾏，{c}当前列，{-n}，{n}当前⾏或列的偏移量前n或后n。

4. &==是动态计算，如excel，if等语句。

（if（logic_test,true_value,false_value））还有其他更为复杂的汇总计算的表达式，这⾥也不在这节多讲，有兴趣的朋友，可以去官⽹看看。

我们先来个简单的例⼦，光说，总是不⾏的，还是要代码实践才能说明⼀切：代码如下：App_Code.SqlDbHelper help = new App_Code.SqlDbHelper();var s = Aspose.Cells.CellsHelper.GetVersion();var sql = "select WL_TZ,WL_CL,RPCJH_SL,WL_MC,RPCJH_JHKGRQ,RPCJH_JHID,RPCJH_JHID asRPCJH_JHID1,RPCJH_GXID,U_RPCJH_KH,(select rtrim((cast(GYLX_GXID as char(2)))+' '+ltrim(GYLX_GXMC)) from GYLX whereGYLX_GYLXID=wo.WO_GYLXID and GYLX_GXID=RPCJH_GXID) as gxmc from RPCJH left join wl on RPCJH_WLID=WL_WLID left join wo on wo.WO_WOID=RPCJH_WOID where RPCJH_JHID='JHA1909152' and RPCJH_BZGXID!='888' ";var dt = help.ExecuteDataTable(sql);dt.TableName = "Customers";Workbook workBook = new Workbook(@"E:\KaBoProject\GTDB\GTDB\bin\Debug\机加作业计划卡.xls");WorkbookDesigner designer = new WorkbookDesigner(workBook);//数据源designer.SetDataSource(dt);//报表单位designer.SetDataSource("RPCJH_JHID", dt.Rows[0]["RPCJH_JHID"].ToString().Trim());designer.SetDataSource("U_RPCJH_KH", dt.Rows[0]["U_RPCJH_KH"].ToString().Trim());designer.SetDataSource("RPCJH_JHID1", "计划编号：" + dt.Rows[0]["RPCJH_JHID1"].ToString().Trim() + " 领卡⼈：");designer.SetDataSource("RPCJH_JHKGRQ", "派⼯⽇期:" +Convert.ToDateTime(dt.Rows[0]["RPCJH_JHKGRQ"].ToString().Trim()).ToShortDateString());designer.SetDataSource("WL_MC", "名称：" + dt.Rows[0]["WL_MC"].ToString().Trim());designer.SetDataSource("WL_TZ", dt.Rows[0]["WL_TZ"].ToString().Trim());designer.SetDataSource("WL_CL", dt.Rows[0]["WL_CL"].ToString().Trim());designer.SetDataSource("RPCJH_SL", dt.Rows[0]["RPCJH_SL"].ToString().Trim());designer.Process();workBook.Save(@"E:\KaBoProject\GTDB\GTDB\bin\Debug\2.xls", SaveFormat.Xlsx);designer = null;⽣成的excel如下打印代码如下：Workbook workbook = new Workbook(@"E:\KaBoProject\GTDB\GTDB\bin\Debug\2.xls");//Get the worksheet to be printedWorksheet worksheet = workbook.Worksheets[0];//获取该Excel⽂档的第⼀个⼯作表//PageSetup pageSetup = worksheet.PageSetup;//pageSetup.Orientation = ndscape;//pageSetup.LeftMargin = 0;//pageSetup.RightMargin = 0.1;//pageSetup.BottomMargin = 0.3;//pageSetup.PrintArea = "A2:J29";//Apply different Image / Print options.Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions(); //Set the Printing page property//options.PrintingPage = PrintingPageType.IgnoreStyle;//Render the worksheetSheetRender sr = new SheetRender(worksheet, options);System.Drawing.Printing.PrinterSettings printSettings = new System.Drawing.Printing.PrinterSettings();string strPrinterName = printSettings.PrinterName;//send to printer//System.Drawing.Image map = sr.ToImage(0);sr.ToPrinter(strPrinterName);。

asp实现excel中的数据导入数据库

hang=hang+1
loop
rse.movefirst
do while not rse.eof
setrst=server.CreateObject("adodb.recordset")
sqlt="select * fromSellman"
rst.opensqlt,conn,1,3
rst.addnew()
objConn.OpenstrConn
strSql="SELECT * FROM [Sheet1$]"
objRS.OpenstrSql,objConn,1,1
objRS.MoveFirst
%><%
'循环excel中所有记录
while not objRS.eof
setrs=Server.CreateObject("Adodb.Recordset")
'查询语句
sql_s= "select * fromceshiwherelname='"&objRS(0) & "' and old='"&objRS(1) & "' and sex='"&objRS(2) & "' andguojia='"&objRS(3) & "' and QQ='"&objRS(4) & "'"
rst("Homepage")=c2(rse(8))

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表在客客户端显示。

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

ASP实例代码asp操作Excel类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_CreateExcelServer"AuthorStr = "Surnfu surnfu@ 31333716"VersionStr = "1.0"if not IsObjInstalled("Excel.Application") thenInErr("服务器未安装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 = Nothing If 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 V al <> 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 talled = 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 thenInErr("获取数据库表有误，列数不符")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)+1ReDim 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)+1ReDim 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_ &"MS"elseDim 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_ = Fix(tempSecond_ / 60 * 100) / 100n_ = n_ + 1WEndDim m_For m_ = n_ To 0 Step -1aryTime(m_) = second_ \ (60 ^ m_)second_ = second_ mod (60 ^ m_)ReTime_ = ReTime_ & aryTime(m_) & aryTimeunit(m_)Nextif 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'使用示例一Set a=new CreateExcela.SavePath="x.xls"a.AddData b, true , "测试c", "测试c"a.TitleFirstLine = false '首行是否为标题行a.Create()response.Write("生成"& a.SavePath &"使用了"& a.GetTime(edTime) &"<br>")Set a=nothing'使用示例二Set a=new CreateExcela.SavePath="y.xls"a.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'使用示例三生成两个表Set a=new CreateExcela.SavePath="z.xls"a.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%>。