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中的数据导入数据库

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
%><!--#include file="conn.asp"--><%
'循环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"><!--function AutomateExcel(){// Start Excel and get Application object.var oXL = new ActiveXObject("Excel.Application");// Get a new workbook.var oWB = oXL.Workbooks.Add();var oSheet = oWB.ActiveSheet;var table = document.all.data;var hang = table.rows.length;var lie = table.rows(0).cells.length;// Add table headers going cell by cell.for (i=0;i<hang;i++){for (j=0;j<lie;j++){oSheet.Cells(i+1,j+1).Value = table.rows(i).cells(j).innerText;}}oXL.Visible = true;erControl = true;}//--></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库,我想也可以用来解决你的问题,(同理:页面上显示的内容当然也是读库的,除非你是静态的那算了)<!--#include file="../opendb.asp"--><!--写链接的事不用我弄了吧?--><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表格常用于批处理数据或者数据迁移项目。
导入代码如下:<!--#include file="conn.asp" --><%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.net]C#操作Excel的几种方式优缺点比较
![[Asp.net]C#操作Excel的几种方式优缺点比较](https://img.taocdn.com/s3/m/b4e22e1ccd7931b765ce0508763231126edb77f9.png)
[]C#操作Excel的⼏种⽅式优缺点⽐较在项⽬中我们常常需要将数据库中的数据导出成Excel⽂件有⼀次⼯作中我的⽬的就是读取Excel到内存中,整理成指定格式整理后再导出到Excel。
因为我要处理的每个Excel表格⽂件很⼤。
⼀个表格多个sheet,每个sheet-⼀千到上W⾏不等。
列数超过300列。
所有在这⾥我将在使⽤⼀些处理Excel的⽅法的时候,所遇到的⼀些问题记录下来,也做⼀个⼤致的⽐较。
主要是针对此次数据处理NPOI⽬前⽐较流⾏的⼀款操作Excel的组件。
移植于Java的POI,是⼀个开源项⽬,对Excel的操作很全⾯。
官⽹优势:1、免费2、机器不⽤安装Office也可以直接操作Excel,免去很多事。
3、现在已⾄此的⽂件格式包括Excel2003和2007之后的xls,xlsx以及docx。
4、⽀持⽂件的导⼊和导出5、⽹上有丰富的实例代码,遇到基本的问题可以上⽹参考⽹友的解决⽅法。
6、NPOI能⽀持绝⼤多数Excel⾥⾯的功能操作(Excel内的公式函数、设置单元格的格式样式)7、导⼊导出速度快。
内存占⽤⼤。
特定优势:⽀持读取超过256列的Excel表格。
缺点:参考⽬前很多⽹上的⽂档,npoi导出xlsx,使⽤ XSSFWorkbook 使⽤ Write ⽅法写⼊内存流后,返回的 MemoryStream 已经被关闭了,不能再使⽤了。
你需要花⼀点时间解决这个问题。
可以参考:xlsx格式导出推荐使⽤Epplus组件NPOI辅助类:1 using System;2 using System.Collections;3 using System.Collections.Generic;4 using System.Text;5 using System.IO;6 using System.Data;7 using System.Windows.Forms;8 using erModel;9 using erModel;10 using erModel;1112 namespace TEMS.Service13 {14 public static class ExcelHelperForCs15 {16 #region 私有⽅法1718 /// <summary>19 /// 获取要保存的⽂件名称(含完整路径)20 /// </summary>21 /// <returns></returns>22 private static string GetSaveFilePath()23 {24 SaveFileDialog saveFileDig = new SaveFileDialog();25 saveFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";26 saveFileDig.FilterIndex = 0;28 saveFileDig.OverwritePrompt = true;29 saveFileDig.InitialDirectory = Common.DesktopDirectory;30 string filePath = null;31 if (saveFileDig.ShowDialog() == DialogResult.OK)32 {33 filePath = saveFileDig.FileName;34 }3536 return filePath;37 }3839 /// <summary>40 /// 获取要打开要导⼊的⽂件名称(含完整路径)41 /// </summary>42 /// <returns></returns>43 private static string GetOpenFilePath()44 {45 OpenFileDialog openFileDig = new OpenFileDialog();46 openFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";47 openFileDig.FilterIndex = 0;48 openFileDig.Title = "打开";49 openFileDig.CheckFileExists = true;50 openFileDig.CheckPathExists = true;51 openFileDig.InitialDirectory = Common.DesktopDirectory;52 string filePath = null;53 if (openFileDig.ShowDialog() == DialogResult.OK)54 {55 filePath = openFileDig.FileName;56 }5758 return filePath;59 }6061 /// <summary>62 /// 判断是否为兼容模式63 /// </summary>64 /// <param name="filePath"></param>65 /// <returns></returns>66 private static bool GetIsCompatible(string filePath)67 {68 return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);69 }7071 /// <summary>72 /// 创建⼯作薄73 /// </summary>74 /// <param name="isCompatible"></param>75 /// <returns></returns>76 private static IWorkbook CreateWorkbook(bool isCompatible)77 {78 if (isCompatible)79 {80 return new HSSFWorkbook();81 }82 else83 {84 return new XSSFWorkbook();85 }86 }8788 /// <summary>89 /// 创建⼯作薄(依据⽂件流)90 /// </summary>91 /// <param name="isCompatible"></param>92 /// <param name="stream"></param>93 /// <returns></returns>94 private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)95 {96 if (isCompatible)97 {98 return new HSSFWorkbook(stream);99 }100 else101 {102 return new XSSFWorkbook(stream);103 }104 }105106 /// <summary>107 /// 创建表格头单元格108 /// </summary>109 /// <param name="sheet"></param>110 /// <returns></returns>111 private static ICellStyle GetCellStyle(IWorkbook workbook)112 {113 ICellStyle style = workbook.CreateCellStyle();114 style.FillPattern = FillPattern.SolidForeground;115 style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;116117 return style;118 }120 /// <summary>121 /// 从⼯作表中⽣成DataTable122 /// </summary>123 /// <param name="sheet"></param>124 /// <param name="headerRowIndex"></param>125 /// <returns></returns>126 private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex) 127 {128 DataTable table = new DataTable();129130 IRow headerRow = sheet.GetRow(headerRowIndex);131 int cellCount = stCellNum;132133 for (int i = headerRow.FirstCellNum; i < cellCount; i++)134 {135 if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "") 136 {137 // 如果遇到第⼀个空列,则不再继续向后读取138 cellCount = i;139 break;140 }141 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); 142 table.Columns.Add(column);143 }144145 for (int i = (headerRowIndex + 1); i <= stRowNum; i++)146 {147 IRow row = sheet.GetRow(i);148 //如果遇到某⾏的第⼀个单元格的值为空,则不再继续向下读取149 if (row != null && !string.IsNullOrEmpty(row.GetCell(0).ToString()))150 {151 DataRow dataRow = table.NewRow();152153 for (int j = row.FirstCellNum; j < cellCount; j++)154 {155 dataRow[j] = row.GetCell(j).ToString();156 }157158 table.Rows.Add(dataRow);159 }160 }161162 return table;163 }164165 #endregion166167 #region 公共导出⽅法168169 /// <summary>170 /// 由DataSet导出Excel171 /// </summary>172 /// <param name="sourceTable">要导出数据的DataTable</param>173 /// <returns>Excel⼯作表</returns>174 public static string ExportToExcel(DataSet sourceDs, string filePath = null)175 {176177 if (string.IsNullOrEmpty(filePath))178 {179 filePath = GetSaveFilePath();180 }181182 if (string.IsNullOrEmpty(filePath)) return null;183184 bool isCompatible = GetIsCompatible(filePath);185186 IWorkbook workbook = CreateWorkbook(isCompatible);187 ICellStyle cellStyle = GetCellStyle(workbook);188189 for (int i = 0; i < sourceDs.Tables.Count; i++)190 {191 DataTable table = sourceDs.Tables[i];192 string sheetName = "result" + i.ToString();193 ISheet sheet = workbook.CreateSheet(sheetName);194 IRow headerRow = sheet.CreateRow(0);195 // handling header.196 foreach (DataColumn column in table.Columns)197 {198 ICell cell = headerRow.CreateCell(column.Ordinal);199 cell.SetCellValue(column.ColumnName);200 cell.CellStyle = cellStyle;201 }202203 // handling value.204 int rowIndex = 1;205206 foreach (DataRow row in table.Rows)207 {208 IRow dataRow = sheet.CreateRow(rowIndex);209210 foreach (DataColumn column in table.Columns)212 dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());213 }214215 rowIndex++;216 }217 }218219 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);220 workbook.Write(fs);221 fs.Dispose();222 workbook = null;223224 return filePath;225226 }227228 /// <summary>229 /// 由DataTable导出Excel230 /// </summary>231 /// <param name="sourceTable">要导出数据的DataTable</param>232 /// <returns>Excel⼯作表</returns>233 public static string ExportToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null)234 {235 if (sourceTable.Rows.Count <= 0) return null;236237 if (string.IsNullOrEmpty(filePath))238 {239 filePath = GetSaveFilePath();240 }241242 if (string.IsNullOrEmpty(filePath)) return null;243244 bool isCompatible = GetIsCompatible(filePath);245246 IWorkbook workbook = CreateWorkbook(isCompatible);247 ICellStyle cellStyle = GetCellStyle(workbook);248249 ISheet sheet = workbook.CreateSheet(sheetName);250 IRow headerRow = sheet.CreateRow(0);251 // handling header.252 foreach (DataColumn column in sourceTable.Columns)253 {254 ICell headerCell = headerRow.CreateCell(column.Ordinal);255 headerCell.SetCellValue(column.ColumnName);256 headerCell.CellStyle = cellStyle;257 }258259 // handling value.260 int rowIndex = 1;261262 foreach (DataRow row in sourceTable.Rows)263 {264 IRow dataRow = sheet.CreateRow(rowIndex);265266 foreach (DataColumn column in sourceTable.Columns)267 {268 dataRow.CreateCell(column.Ordinal).SetCellValue((row[column]??"").ToString());269 }270271 rowIndex++;272 }273 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);274 workbook.Write(fs);275 fs.Dispose();276277 sheet = null;278 headerRow = null;279 workbook = null;280281 return filePath;282 }283284 /// <summary>285 /// 由List导出Excel286 /// </summary>287 /// <typeparam name="T">类型</typeparam>288 /// <param name="data">在导出的List</param>289 /// <param name="sheetName">sheet名称</param>290 /// <returns></returns>291 public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "result", string filePath = null) where T : class 292 {293 if (data.Count <= 0) return null;294295 if (string.IsNullOrEmpty(filePath))296 {297 filePath = GetSaveFilePath();298 }299300 if (string.IsNullOrEmpty(filePath)) return null;301302 bool isCompatible = GetIsCompatible(filePath);304 IWorkbook workbook = CreateWorkbook(isCompatible);305 ICellStyle cellStyle = GetCellStyle(workbook);306 ISheet sheet = workbook.CreateSheet(sheetName);307 IRow headerRow = sheet.CreateRow(0);308309 for (int i = 0; i < headerNameList.Count; i++)310 {311 ICell cell = headerRow.CreateCell(i);312 cell.SetCellValue(headerNameList[i].Value);313 cell.CellStyle = cellStyle;314 }315316 Type t = typeof(T);317 int rowIndex = 1;318 foreach (T item in data)319 {320 IRow dataRow = sheet.CreateRow(rowIndex);321 for (int n = 0; n < headerNameList.Count; n++)322 {323 object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null);324 dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString());325 }326 rowIndex++;327 }328 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);329 workbook.Write(fs);330 fs.Dispose();331332 sheet = null;333 headerRow = null;334 workbook = null;335336 return filePath;337 }338339 /// <summary>340 /// 由DataGridView导出341 /// </summary>342 /// <param name="grid"></param>343 /// <param name="sheetName"></param>344 /// <param name="filePath"></param>345 /// <returns></returns>346 public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null) 347 {348 if (grid.Rows.Count <= 0) return null;349350 if (string.IsNullOrEmpty(filePath))351 {352 filePath = GetSaveFilePath();353 }354355 if (string.IsNullOrEmpty(filePath)) return null;356357 bool isCompatible = GetIsCompatible(filePath);358359 IWorkbook workbook = CreateWorkbook(isCompatible);360 ICellStyle cellStyle = GetCellStyle(workbook);361 ISheet sheet = workbook.CreateSheet(sheetName);362363 IRow headerRow = sheet.CreateRow(0);364365 for (int i = 0; i < grid.Columns.Count; i++)366 {367 ICell cell = headerRow.CreateCell(i);368 cell.SetCellValue(grid.Columns[i].HeaderText);369 cell.CellStyle = cellStyle;370 }371372 int rowIndex = 1;373 foreach (DataGridViewRow row in grid.Rows)374 {375 IRow dataRow = sheet.CreateRow(rowIndex);376 for (int n = 0; n < grid.Columns.Count; n++)377 {378 dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());379 }380 rowIndex++;381 }382383 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);384 workbook.Write(fs);385 fs.Dispose();386387 sheet = null;388 headerRow = null;389 workbook = null;390391 return filePath;392 }393394 #endregion396 #region 公共导⼊⽅法397398 /// <summary>399 /// 由Excel导⼊DataTable400 /// </summary>401 /// <param name="excelFileStream">Excel⽂件流</param>402 /// <param name="sheetName">Excel⼯作表名称</param>403 /// <param name="headerRowIndex">Excel表头⾏索引</param>404 /// <param name="isCompatible">是否为兼容模式</param>405 /// <returns>DataTable</returns>406 public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible) 407 {408 IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);409 ISheet sheet = null;410 int sheetIndex = -1;411 if (int.TryParse(sheetName, out sheetIndex))412 {413 sheet = workbook.GetSheetAt(sheetIndex);414 }415 else416 {417 sheet = workbook.GetSheet(sheetName);418 }419420 DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);421422 excelFileStream.Close();423 workbook = null;424 sheet = null;425 return table;426 }427428 /// <summary>429 /// 由Excel导⼊DataTable430 /// </summary>431 /// <param name="excelFilePath">Excel⽂件路径,为物理路径。
asp.net实现Excel数据批量导入Web系统数据库word精品文档6页

asp实现Excel数据批量导入Web系统数据库【摘〓要】本文以学生信息管理系统中导入成绩表为例,介绍了利用asp将Excel数据批量导入到Web系统的sql数据库的实现过程。
目前,基于Web的信息管理系统,有的涉及到大量数据录入的问题,如学生信息管理系统,有学籍表、各班成绩表等信息,这些数据都已经存在Excel表中,如果能直接导入是最好的解决办法,就能避免了人工录入过程中的工作量大、效率低、容易出错等问题,其实Asp动态网页开发技术就能解决这一问题。
一般学籍表的格式和数据库的库结构的设计基本是一样的,Excel的表头和数据库的字段能够一一对应,但成绩表就不一样了,由于全校班级不同,所上的课程不同,在数据库设计时就不能和Excel 成绩表相同,下面我就以导入成绩表为例来说明如何实现将Excel数据批量导入到Web系统的Sql数据库中。
1、数据结构设计Excel成绩表结构如下:Sql数据库成绩表t_scores结构设计如下:由于学号是唯一的,所以不用设置姓名、班级、系部等字段,根据学号在学籍表中都能查到,避免了数据冗余。
通过上述表结构可以看出,需要把Excel表的不同的课程名作为Sql数据库成绩表中的一条记录中的一个内容导入,即一门课程占一条记录,以下来说明具体实现过程。
2、导入成绩界面设计导入数据必须把数据文件上传,上传所用控件为FileUpload,用来浏览上传文件,然后再用一个Button按钮控件,负责执行导入成绩这一过程,当点击“导入成绩”按钮时,系统自动执行Button1_Click事件程序。
再设计几个Label控件用来显示提示信息。
HTML代码如下:3、导入Excel数据程序分析3.1、添加引用为了能够上传、连接、读取Excel文件,要引入System.IO、System.Data、System.Data.OleDb命名空间,若要对SQL Server数据库操作,必须引入System.Data.Sql、System.Data.SqlClient命名空间。
ASP读取xls格式的Excel文件

ASP读取xls格式的Excel文件2011年6月28日ASP12 评论Excel也是一种数据库,跟Access类似,其读取方法也类似。
这里有一个简单的Excel表,文件名字为Excel.xls:咱就这个为例子来说明。
创建Excel连接代码:Dim XlsConn,xrsSet XlsConn=Server.CreateO bject("Adodb.Connection")XlsConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&Server.MapPath("Excel.xls")&";Extended Properties=Excel 8.0" 是不是跟连接Access文件很像呢?呵呵~连接创建好了,就可以创建记录集查询Excel里面的数据了。
Set xrs=XlsConn.Execute("Select * From [Sheet1$]")跟其他数据库的查询几乎是一样的,要注意的地方就是表名,Excel里面,默认会有三张表,Sheet1、Sheet2、Sheet3,从上面的例子也可以看到,需要注意查询时表名后面要加“$”符;查询结果:xrs(0)="数据列0"xrs(1)="1"xrs(2)="2"xrs(3)="3"xrs(4)="4"xrs(5)="5"这里有一点需要注意,如果一列中有多行,有的行的值是数字,有的行是字符,ASP去取值的时候数字行会读取出错,是为空的。
所以得想办法把数字行也变成字符型,比如加个’号去,读取出来之后再替换回去。
多行自然是用xrs.Movenext去循环取得了。
够简单吧!最后贴一段代码:Dim rsSet rs=Server.CreateObject("Adodb.Recordset")rs.Open "Select Top 1 * From Pro Where 1=2",Conn,3,2Dim XlsConn,xrsSet XlsConn=Server.CreateO bject("Adodb.Connection")XlsConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&Server.MapPath("Excel.xls")&";Extended Properties=Excel 8.0"Set xrs=XlsConn.Execute("Select * From [Sheet1$]")Do While Not xrs.Eofrs.Addnewrs("iClear")= xrs(0)rs("Detail")= xrs(1)rs.Updatexrs.MovenextLoopxrs.Close()Set xrs=NothingXlsConn.Close()Set XlsConn=Nothingrs.Close()Set rs=NothingASP操作xls文件(2005-11-10 14:15:12)转载▼标签:杂谈分类:ASP只写一种方法。
asp生成excel源代码

<!--#include file="../conn.asp"--><%dim s,sql,filename,fs,myfile,xSet fs = server.CreateObject("scripting.filesystemobject")'--假设你想让生成的EXCEL文件做如下的存放filename = Server.MapPath("班班通使用记录登记表.xls")'--如果原来的EXCEL文件存在的话删除它if fs.FileExists(filename) thenfs.DeleteFile(filename)end if'--创建EXCEL文件set myfile = fs.CreateTextFile(filename,true)Set rs = Server.CreateObject("ADODB.Recordset")'--从数据库中把你想放到EXCEL中的数据查出来sql = "select teacher_Name,teacher_use_time,teacher_use_class,teacher_use_snumber,teacher_use_jc,teacher_ use_ctitle,teacher_use_couse from teacher_use_table order by teacher_use_ID desc"rs.Open sql,connStartTime = Request("StartTime")EndTime = Request("EndTime")StartEndTime = "AddTime between #"& StartTime &" 00:00:00# and #"& EndTime &" 23:59:59#"strSql = "select teacher_Name,teacher_use_time,teacher_use_class,teacher_use_snumber,teacher_use_jc,teacher_ use_ctitle,teacher_use_couse from teacher_use_table"Set rstData =conn.execute(strSql)if not rstData.EOF and not rstData.BOF thendim trLine,responsestrstrLine=""strLine = strLine & "上课教师" & chr(9)strLine = strLine & "上课时间" & chr(9)strLine = strLine & "使用班级" & chr(9)strLine = strLine & "学生人数" & chr(9)strLine = strLine & "使用节次" & chr(9)strLine = strLine & "上课学科" & chr(9)strLine = strLine & "课程名称" & chr(9)'--将表的列名先写入EXCELmyfile.writeline strLineDo while Not rstData.EOFstrLine=""for each x in rstData.FieldsstrLine = strLine & x.value & chr(9)nextmyfile.writeline strLinerstData.MoveNextloopend ifResponse.Write "<br> 生成EXCEL文件成功,点击<a href=""班班通使用记录登记表.xls"" target=""_blank"">下载</a>!"rstData.Closeset rstData = nothingConn.CloseSet Conn = nothing%>。
用asp将excel表数据导入access数据库的代码

Set rst = Server.CreateObject("ADODB.Recordset")
Sql2="select usrname,usrpwd,question,answer,sj from company"
rst.Open Sql2,sqlconn,1,3
if rst.Fields.Count-1=rs.Fields.Count-1 then '如果字段个数据相同则
Sql2="select id,usrname,usrpwd,question,answer,sj from company"
rst.Open Sql2,sqlcields.Count-1
%>
<td nowrap bgcolor="#999999"> <div align="center"><font color="#FFFFFF"><%=rst(i).Name%>[/red]</div></td>
sqlconn.Open"provider=microsoft.jet.oledb.4.0;data source="& Server.MapPath("../#bijiadata/company.mdb")
sqlconn.execute("delete * from company")
%>
导入的access数据库的数据如下:
<table border="1" align="center" cellpadding="4" cellspacing="4" bordercolordark="#FFFFFF">
ASP导出为Word或Excel的最简单方法

ASP导出为Word或Excel的最简单方法2010-09-04 15:05方法1:把一个Word文档另存为XML格式(可能需要Office2003才能支持),然后你就可以用操作XML文件的方法,来对这个文件中指定的文字替换成你需要的内容。
重新打开这个文件,你会发现它是在Word中打开的,这样可以用Word来继续编辑或者打印。
方法2:我在做一项目时,客户要求要将从数据库中获取数据后的ASP页面导出成EXCEL或WORD文档。
经本人试验后找出了最简单的方法:在ASP文件的最开头位置加入下面的代码就可以了,非常简单。
Asp代码1.EXCEL2.<%3.Response.ContentType ="application/vnd.ms-excel"4.Response.AddHeader "Content-Disposition", "attachment; filename=红宝网络表格.xls"5.%>6.7.WORD8.<%9.Response.ContentType ="application/vnd.ms-word"10.Response.AddHeader "Content-Disposition", "attachment; filename=红宝网络文档.doc"11.%>导出为WORD时,若文档中含有表格,需要打打印,则要在导出的页面中加入下面的样式。
Css代码1.<style type="text/css">2.<!--3.table{4.border-collapse:collapse;border:none;mso-border-alt:solidwindowtext .5pt;5.mso-yfti-tbllook:480;mso-padding-alt:0cm 5.4pt 0cm5.4pt;mso-border-insideh:6..5pt solid windowtext;mso-border-insidev:.5pt solidwindowtext;border-left:solid windowtext 1.0pt;border-top:solid windowtext 1.0pt;7.}8.td{9.border-top:none;border-left:10.none;border-bottom:solid windowtext 1.0pt;border-right:solidwindowtext 1.0pt;11.mso-border-top-alt:solidwindowtext .5pt;mso-border-left-alt:solid windowtext .5pt;12.mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm5.4pt;13.}14.-->15.</style>我打字系统项目中的一个导出为EXCEL文件的实例代码如下:Asp代码1.<!--#include file="hbwlConfig.asp" -->2.<%Response.ContentType ="application/vnd.ms-excel"3.Response.AddHeader "Content-Disposition", "attachment;filename=chengji.xls"%>4.<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""/TR/xhtml1/DTD/xhtml1-transitional.dtd">5.<html xmlns="/1999/xhtml">6.<head>7.<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />8.<title>打字成绩</title>9.<style type="text/css">10..tableWg {border:1px solid #9bbde6;}11..tableWg tr{text-align:center;}12..tableWg td{ border-bottom:1px dotted #9bbde6; border-right:1pxdotted #9bbde6;}13.</style>14.</head>15.<body>16.<%sql=session("chengjisql")17.response.Write hbwl.dbSelect(sql,0,1,"",0,"",0,"tableWg")%>18.</body>19.</html>20.<%set hbwl=nothing%>文章来源:季长旭博客鹿胎膏。
ASP连接excel的方法

asp连接Excel的方法一、asp连接Excel的方法与代码:<%dim connxls,connstrxlsconnstrxls="DBQ=d:\newexcel.xls;DefaultDir=;DRIVER={Microsoft Excel Driver (*.xls)};"Set connxls=Server.CreateObject("ADODB.Connection")connxls.Open connstrxlsSet rsxls=Server.CreateObject("ADODB.Recordset")SQLxls = "select * from [new$]"%>注意:写法和连接ACCESS基本相同,不同的是,在调用工作表的时候,不能直接用表名,工作表名后要加$,而且要用“[]”括起来。
二、asp操作Excel的总结:1、建立Excel对象setobjExcelApp=CreateObject("Excel.Application")objExcelApp.DisplayAlerts=false不显示警告objExcelApp.Application.Visible=false不显示界面2、新建Excel文件objExcelApp.WorkBooks.addsetobjExcelBook=objExcelApp.ActiveWorkBooksetobjExcelSheets=objExcelBook.WorksheetssetobjExcelSheet=objExcelBook.Sheets(1)3、读取已有Excel文件strAddr=Server.MapPath(".")objExcelApp.WorkBooks.Open(strAddr&"\Templet\Table.xls")Set objExcelBook=objExcelApp.ActiveWorkBookSet objExcelSheets=objExcelBook.WorksheetsSet objExcelSheet=objExcelBook.Sheets(1)4、另存Excel文件objExcelBook.SaveAsstrAddr&"\Temp\Table.xls"5、保存Excel文件objExcelBook.Save(笔者测试时保存成功,页面报错。
在ASP网页中访问Excel文件

在ASP网页中访问Excel文件在ASP网页中访问Excel文件Excel文件可以作为活动文档(ActiveX Document)在活动文档容器(ActiveX Document Container)中打开,而IE就是一个活动文档容器,所以当页面中的链接指向一个Excel文件时,单击此链接即可启动Excel,最终将文件显示在IE窗口中。
这种方法虽然简单,但存在不少缺陷:客户机内必须安装Excel。
在企业内部网上还有可能做到,但Internet上就不现实了。
访问文档时将启动Excel,速度较慢。
若览器不是IE,则可能不是活动文档容器,因此将不能正确显示文件。
下面介绍一种在ASP文件中通过ADO访问Excel文件的方法。
----ADO是微软发布的基于OLEDB技术的数据库编程接口,通过ODBC驱动程序可以访问多种不同格式的数据文件,其中包括Excel文件。
要获得Excel的ODBC驱动程序,可以通过安装Office97,也可以去微软的站点下载。
正确安装后在控制面板中会出现ODBC数据源管理器,打开它确认在驱动程序页中有Excel的驱动程序。
然后编写Brow.ASP程序,如下例所示。
例中假设在同一目录下有Excel文件01.xls,且包含名为sheet1的工作表,工作表的第一行为列标题,以后为相应的数据。
Brow.asp:程序代码:<%Const adOpenStatic = 3Dim conn Dim rs ,dbPath Set conn = erver.CreateObject("ADODB.Connection") ‘ 生成实际路dbPath=server.MapPath("01.xls") ‘ 驱动程序名称必须与ODBC中的驱动程序名完全一致,包括空格conn.Open "driver={Microsoft Excel Driver (*.xls)};dbq=" & dbPath Set rs = Server.CreateObject("ADODB.Rec ordset") ‘ 在工作表后加上$,同时必须在工作表名外加括号rs.Open "[sheet1$]", conn, adOpenStatic% >< html >< head >< meta http-equiv="Content-Type" content="text/html; charset=gb_2312-80" >< meta name="GENERATOR" content="Microsoft FrontPage Express 2.0" >< title > 浏览Excel数据表< /title >< /head >< body bgcolor="#FFFFFF" >< h2 align="center" >Brow.asp - 浏览Excel数据表< /h2 >< hr >< %Response.Write "< CENTER >< TABLE BORDER=1 >"Response.Write "< TR BGCOLOR=#00FFFF >"Response.WRITE "< TD >编号< /TD >" ‘ 显示每列的标题For i=0 to rs.Fields.Count-1Response.WRITE "< TD >" & rs.Fields(i).Name & "< /TD >"NextResponse.Write "< /TR >" RecNo=1‘ 显示工作表中的所有数据Do While Not rs.EOFResponse.Write "< TR > "Response.Write "< TD >" & RecNo & "< /TD >"‘显示各列的数据For i=0 to rs.Fields.Count-1Response.WRITE "< TD >" & rs.Fields(i).Value & "< /TD >"NextResponse.Write "< /TR >"rs.MoveNext RecNo=RecNo+1 LoopResponse.Write "< /TABLE >< /CENTER >"% >< hr > < /body > < /html >ODBC 将指定的工作表的第一行各列的内容识别成字段名,以下各行作为相应的数据,若打开的工作表中数据将没有按此规律出现,则需要指定范围。
ASP.NET之Excel下载模板、导入、导出操作

之Excel下载模板、导⼊、导出操作本⽂介绍了下Excel下载模板、导⼊、导出操作,供⼤家参考,具体内容如下1.下载模板功能protected void btnDownload_Click(object sender, EventArgs e){var path = Server.MapPath(("upfiles\\") + "test.xlt"); //upfiles-⽂件夹 test.xlt-⽂件var name = "test.xlt";try{var file = new FileInfo(path);Response.Clear();Response.Charset = "GB2312";Response.ContentEncoding = System.Text.Encoding.UTF8;Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name)); //头信息,指定默认⽂件名Response.AddHeader("Content-Length", file.Length.ToString());//显⽰下载进度Response.ContentType = "application/ms-excel"; // 指定返回的是⼀个不能被客户端读取的流,必须被下载Response.WriteFile(file.FullName); // 把⽂件流发送到客户端pleteRequest();}catch (Exception ex){Response.Write("<script>alert('错误:" + ex.Message + ",请尽快与管理员联系')</script>");}}2.导⼊数据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%>。