asp数据库成excel文件代码
- 格式:doc
- 大小:31.00 KB
- 文档页数:2
本篇中将简单记录下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为:这就完成了我们的一个简单的多表头数据导出报表。
aspose.cells根据模板导出excel ⼜隔⼗多天没写博客了,最近都在忙项⽬的事情,公司⼈事变动也⽐较⼤,⼿头上就⼜多了⼀个项⽬。
最近做⽤aspose.cells根据模板导出excel报价单的功能,顺便把相关的核⼼记下来,先上模板和导出的效果图吧,如下: 导出效果图(看到产品图,打不死的程(diao)序(si)员(猿)骚动吧,有没有不禁看了看⾃⼰粗⼤的右⼿): (其中红⾊框框起来的是动态填充的内容,⼯作薄名(产品信息)也是动态输出的) 模板如下: ⼤概流程是这样,先把需要输出到excel的内容存到⼀个model⾥⾯,然后把该model编程⼀个datatable,然后就直接绑定在指定的excel模板上,然后把excel转成流根据excel格式输出到客户端。
以下为将置顶的model绑定在指定的模板上(模板后⾯附上)///<summary>///返回Excel⽂件流///</summary>///<param name="model">数据</param>///<param name="templateFileName">模板⽂件,要全路径</param>///<param name="sheetName">⼯作簿名称</param>///<param name="outDesigner">⼯作簿名称</param>///<returns></returns>public static MemoryStream OutModelFileToStream(DataTable model, string templateFileName, string sheetName, Action<WorkbookDesigner, DataTable> action) {WorkbookDesigner designer = new WorkbookDesigner();designer.Open(templateFileName);designer.SetDataSource(model);//对designer做额外操作if (action != null){action(designer, model);}//SetCellWithPicture(designer, model.Rows[0]["ProductLogo"].ToString(), 3, 5, 17, 8);designer.Process();if (!string.IsNullOrEmpty(sheetName)){designer.Workbook.Worksheets[0].Name = sheetName;}return designer.Workbook.SaveToStream();} 最后⼀个参数action是为了让⽤户可以在将基本的数据绑定在excel表上之后,可以做⼀些额外的补充操作,⽐如说控制某些特殊部分的格式或者说是某个位置要输出图⽚等,我做的时候,就是要在excel表的某个位置动态输出⼀个图⽚才做了如此操作。
Core导⼊导出Excelxlsx⽂件实例 Core 使⽤EPPlus.Core导⼊导出Excel xlsx ⽂件,EPPlus.Core⽀持Excel 2007/2010 xlsx⽂件导⼊导出,可以运⾏在Windows, Linux和Mac。
EPPlus.Core 是基于EPPlus 更改⽽来,在Linux 下需要安装libgdiplus 。
下⾯在 Core 中导⼊导出Excel xlsx ⽂件。
新建项⽬新建⼀个 Core Web Application 项⽬ASPNETCoreExcel,选择Web 应⽤程序不进⾏⾝份验证。
然后添加EPPlus.Core 引⽤。
使⽤NuGet 命令⾏:Install-Package EPPlus.Core也可以使⽤NuGet包管理器安装。
导出xlsx⽂件新建⼀个XlsxController ,添加Export 操作。
public class XlsxController : Controller{private IHostingEnvironment _hostingEnvironment;public XlsxController(IHostingEnvironment hostingEnvironment){_hostingEnvironment = hostingEnvironment;}public IActionResult Index(){return View();}public IActionResult Export(){string sWebRootFolder = _hostingEnvironment.WebRootPath;string sFileName = $"{Guid.NewGuid()}.xlsx";FileInfo file = new FileInfo(bine(sWebRootFolder, sFileName));using (ExcelPackage package = new ExcelPackage(file)){// 添加worksheetExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");//添加头worksheet.Cells[1, 1].Value = "ID";worksheet.Cells[1, 2].Value = "Name";worksheet.Cells[1, 3].Value = "Url";//添加值worksheet.Cells["A2"].Value = 1000;worksheet.Cells["B2"].Value = "LineZero";worksheet.Cells["C2"].Value = "/linezero/";worksheet.Cells["A3"].Value = 1001;worksheet.Cells["B3"].Value = "LineZero GitHub";worksheet.Cells["C3"].Value = "https:///linezero";worksheet.Cells["C3"].Style.Font.Bold = true;package.Save();}return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");}}通过依赖注⼊获取HostingEnvironment,对应可以获取程序的相关⽬录及属性。
使⽤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文件 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>。
⽣成Excel并导出下载五种实现⽅法通过GridView(简评:⽅法⽐较简单,但是只适合⽣成格式简单的Excel,且⽆法保留VBA代码),页⾯⽆刷新aspx.cs部分复制代码代码如下:using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Text; public partial class DataPage_NationDataShow : System.Web.UI.Page { private Data_Link link = new Data_Link(); private string sql; protected void Page_Load(object sender, EventArgs e) { Ajax.Utility.RegisterTypeForAjax(typeof(DataPage_NationDataShow)); } protected void btnExcel_Click(object sender, EventArgs e) { string strExcelName = "MyExcel"; strExcelName = strExcelName.Replace(@"/", ""); Data_Link link = new Data_Link(); string strSQL = this.hidParam.Value; DataSet ds = new DataSet(); ds = link.D_DataSet_Return(strSQL);//获得想要放⼊Excel的数据 gvExcel.Visible = true; gvExcel.DataSource = null; gvExcel.DataMember = ds.Tables[0].TableName; gvExcel.DataSource = ds.Tables[0]; gvExcel.DataBind(); ExportToExcel(this.Page, gvExcel, strExcelName); } protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e) { } public override void VerifyRenderingInServerForm(Control control) { } /// <summary> /// ⼯具⽅法,Excel出⼒(解决乱码问题) /// </summary> ///<param name="page">调⽤页⾯</param> /// <param name="excel">Excel数据</param> /// <param name="fileName">⽂件名</param> public void ExportToExcel(System.Web.UI.Page page, GridView excel, string fileName) { try { foreach (GridViewRow row in excel.Rows) { for (int i = 0; i < row.Cells.Count; i++) { excel.HeaderRow.Cells[i].BackColor = System.Drawing.Color.Yellow; } } excel.Font.Size = 10; excel.AlternatingRowStyle.BackColor =System.Drawing.Color.LightCyan; excel.RowStyle.Height = 25; page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); page.Response.Charset = "utf-8"; page.Response.ContentType = "application/vnd.ms-excel"; page.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");excel.Page.EnableViewState = false; excel.Visible = true; excel.HeaderStyle.Reset(); excel.AlternatingRowStyle.Reset(); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); excel.RenderControl(oHtmlTextWriter);page.Response.Write(oStringWriter.ToString()); page.Response.End(); excel.DataSource = null; excel.Visible = false; } catch (Exception e) { } } }aspx部分复制代码代码如下:<head runat="server"> <script type="text/javascript"> //Excel DownLoad function excelExport(){ var hidText = document.getElementById("hidParam"); hidText.value = "some params"; document.getElementById("ExcelOutput").click(); } </script> </head> <body onload="pageInit()"> <form id="form1"runat="server"> <input type="button" value="EXCEL下载" style="width:100px;" onclick="excelExport()" id="excelBut" /><input id="hidParam" type="text" runat="server" style="display:none;"/> <asp:Button runat="server" ID="ExcelOutput"style="display:none" Text= "EXCEL出⼒" Width="0px" onclick="btnExcel_Click" UseSubmitBehavior="false"/><asp:GridView ID="gvExcel" runat="server" Height="95px" OnRowDataBound="gvExcel_RowDataBound" Visible="False"> </asp:GridView> </form> </body>在刚才的aspx.cs代码中复制代码代码如下:foreach (GridViewRow row in excel.Rows) { for (int i = 0; i < row.Cells.Count; i++) { excel.HeaderRow.Cells[i].BackColor = System.Drawing.Color.Yellow; } }这部分是给表头添加样式。
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表在客客户端显示。
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导出E xcel数据的四种方法什么是OWC?OWC是O ffice WebCompe nt的缩写,即Mic rosof t的Off ice W eb组件,它为在We b中绘制图形提供了灵活的同时也是最基本的机制。
在一个intr anet环境中,如果可以假设客户机上存在特定的浏览器和一些功能强大的软件(如IE5和Off ice 2000),那么就有能力利用Of ficeWeb组件提供一个交互式图形开发环境。
这种模式下,客户端工作站将在整个任务中分担很大的比重。
<%Opti on Ex plici tCl ass E xcelG enP rivat e obj Sprea dshee tPr ivate iCol Offse tP rivat e iRo wOffs etS ub Cl ass_I nitia lize()Se t obj Sprea dshee t = S erver.Crea teObj ect("OWC.S pread sheet")i RowOf fset= 2 iColO ffset = 2EndSub SubClass_Term inate()S et ob jSpre adshe et =Nothi ng 'C leanupE nd Su bP ublic Prop ertyLet C olumn Offse t(iCo lOff)IfiColO ff >0 the niC olOff set = iCol Off ElseiCol Offse t = 2End If End P roper tyPubli c Pro perty LetRowOf fset(iRowO ff) If iR owOff> 0theniRow Offse t = i RowOf fEl sei RowOf fset= 2 End I fEn d Pro perty SubGener ateWo rkshe et(ob jRS)'Pop ulate s the Exce l wor kshee t bas ed on a Re cords et'sconte nts 'Star t bydispl aying thetitle sIf objR S.EOF then Exit SubDimobjFi eld,iCol, iRowiCo l = i ColOf fsetiRow = iR owOff set For E ach o bjFie ld in objR S.Fie lds objSp reads heet.Cells(iRow, iCo l).Va lue = objF ield.NameobjS pread sheet.Colu mns(i Col).AutoF itCol umns'设置E xcel表里的字体objS pread sheet.Cell s(iRo w, iC ol).F ont.B old = Trueobj Sprea dshee t.Cel ls(iR ow, i Col).Font.Itali c = F alseobjS pread sheet.Cell s(iRo w, iC ol).F ont.S ize = 10 objSp reads heet.Cells(iRow, iCo l).Ha lignm ent = 2 '居中iC ol =iCol+ 1 Next'objF ield'Dis playall o f the dataDoWhile NotobjRS.EOFiRow = iR ow +1iC ol =iColO ffsetFor Each objF ieldin ob jRS.F ieldsIfIsNul l(obj Field.Valu e) th eno bjSpr eadsh eet.C ells(iRow, iCol).Val ue =""E lse objSp reads heet.Cells(iRow, iCo l).Va lue = objF ield.Valueobj Sprea dshee t.Col umns(iCol).Auto FitCo lumnsobj Sprea dshee t.Cel ls(iR ow, i Col).Font.Bold= Fal seo bjSpr eadsh eet.C ells(iRow, iCol).Fon t.Ita lic = Fals eob jSpre adshe et.Ce lls(i Row,iCol).Font.Size = 10End If iCol= iCo l + 1Nex t 'ob jFiel dob jRS.M oveNe xtL oop End S ub Fu nctio n Sav eWork sheet(strF ileNa me) 'Sav e the work sheet to a spec ified file nameOn E rrorResum e Nex tCa ll ob jSpre adshe et.Ac tiveS heet.Expor t(str FileN ame,0)S aveWo rkshe et =(Err.Numbe r = 0)En d Fun ctionEnd Clas sD im ob jRS Set o bjRS= Ser ver.C reate Objec t("AD ODB.R ecord set")obj RS.Op en "S ELECT * FR OM xx xx","Prov ider=SQLOL EDB.1;Pers ist S ecuri ty Info=True;UserID=xx xx;Pa sswor d=xxx x;Ini tialCatal og=xx xx;Da ta so urce=xxxx;" Di m Sav eNameSav eName = Re quest.Cook ies("saven ame")("nam e") Dim o bjExc elD im Ex celPa thE xcelP ath = "Exc el\"& Sav eName & ".xls"SetobjEx cel = NewExcel Gen objEx cel.R owOff set = 1o bjExc el.Co lumnO ffset = 1objE xcel.Gener ateWo rkshe et(ob jRS)If o bjExc el.Sa veWor kshee t(Ser ver.M apPat h(Exc elPat h)) t hen 'Resp onse.Write "<ht ml><b ody b gcolo r='ga insbo ro' t ext='#000000'>已保存为Ex cel 文件.<a hre f='"& ser ver.U RLEnc ode(E xcelP ath)& "'>下载</a>"E lse Respo nse.W rite"在保存过程中有错误!"E nd IfSet objE xcel= Not hingobjR S.Clo seS et ob jRS = Noth ing %>二、用Excel的Appl icati on组件在客户端导出到Exce l或Wor d注意:两个函数中的“d ata“是网页中要导出的tab le的i d<i nputtype="hidd en" n ame="out_w ord"oncli ck="v bscri pt:bu ildDo c" va lue="导出到wo rd" c lass="notP rint"><i nputtype="hidd en" n ame="out_e xcel" oncl ick="Autom ateEx cel();" va lue="导出到ex cel"class="not Print">导出到Exce l代码<SCRI PT LA NGUAG E="ja vascr ipt"><!--fu nctio n Aut omate Excel(){//Start Exce l and getAppli catio n obj ect.varoXL = newActiv eXObj ect("Excel.Appl icati on");//Get a newworkb ook.varoWB = oXL.Workb ooks.Add();va r oSh eet = oWB.Activ eShee t;v ar ta ble = docu ment.all.d ata;varhang= tab le.ro ws.le ngth;va r lie = ta ble.r ows(0).cel ls.le ngth;// Addtable head ers g oingcellby ce ll. for (i=0;i<hang;i++){ for (j=0;j<lie;j++)o Sheet.Cell s(i+1,j+1).valu e = t able.rows(i).ce lls(j).inn erTex t;}}oXL.Visib le =true;oXer Contr ol =true;} //--></S CRIPT>导出到Word代码<s cript lang uage="vbsc ript">Su b bui ldDocset tabl e = d ocume nt.al l.dat aro w = t able.rows.lengt hco lumn= tab le.ro ws(1).cell s.len gth SetobjWo rdDoc = Cr eateO bject("Wor d.Doc ument")objWo rdDoc.Appl icati on.Do cumen ts.Ad d the Templ ate,Falseobj WordD oc.Ap plica tion.Visib le=Tr ueDim t heArr ay(20,10000)f or i=0 torow-1for j=0to co lumn-1th eArra y(j+1,i+1) = ta ble.r ows(i).cel ls(j).inne rTEXTnex tne xto bjWor dDoc.Appli catio n.Act iveDo cumen t.Par agrap hs.Ad d.Ran ge.In sertB efore("综合查询结果集") //显示表格标题ob jWord Doc.A pplic ation.Acti veDoc ument.Para graph s.Add.Rang e.Ins ertBe fore("") Set r ngPar a = o bjWor dDoc.Appli catio n.Act iveDo cumen t.Par agrap hs(1).Rang eWi th rn gPara.Bo ld =True//将标题设为粗体.Par agrap hForm at.Al ignme nt =1 //将标题居中.Fon t.Nam e = "隶书" //设定标题字体.Font.Size= 18//设定标题字体大小End WithSet rngC urren t = o bjWor dDoc.Appli catio n.Act iveDo cumen t.Par agrap hs(3).Rang eSe t tab Curre nt =ObjWo rdDoc.Appl icati on.Ac tiveD ocume nt.Ta bles.Add(r ngCur rent,row,c olumn)f or i= 1 t o col umn objW ordDo c.App licat ion.A ctive Docum ent.T ables(1).R ows(1).Cel ls(i).Rang e.Ins ertAf ter theArr ay(i,1)o bjWor dDoc.Appli catio n.Act iveDo cumen t.Tab les(1).Row s(1).Cells(i).R ange.Parag raphF ormat.alig nment=1n ext For i =1 t o col umn For j = 2to ro wob jWord Doc.A pplic ation.Acti veDoc ument.Tabl es(1).Rows(j).C ells(i).Ra nge.I nsert After theA rray(i,j)objW ordDo c.App licat ion.A ctive Docum ent.T ables(1).R ows(j).Cel ls(i).Rang e.Par agrap hF orm at.al ignme nt=1NextNex tE nd Su b</SCRIP T>三、直接在IE中打开,再存为EXCEL文件把读出的数据用<t able>格式,在网页中显示出来,同时,加上下一句即可把EX CEL表在客客户端显示。
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只写一种方法。
报表中的Excel操作之Aspose.Cells(Excel模板)本篇中将简单记录下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 Code1 var 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.Excel2003, Response);18 Response.Flush();19 Response.Close();20 designer = null;21 Response.End();代码很简单,就是添加了⼀个datatable,和⼏个变量的数据源,我们所⽣成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中的数据导⼊数据库asp实现excel中的数据导⼊数据库<% Response.CodePage=65001%><% Response.Charset="UTF-8" %><%wenjian = request.Form("select")'获取⽂件扩展名ext = FileExec(wenjian)'判断⽂件扩展名if ext <> "xls" thenresponse.Write("<script>alert('⽂件类型不对,请核实!');window.location.href='index.html';</script>")response.End()end ifDim objConn,objRSDim strConn,strSqlset objConn=Server.CreateObject("ADODB.Connection")set objRS=Server.CreateObject("ADODB.Recordset")excelFile = server.mappath(wenjian)'针对excel 2007strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & excelFile & ";" & "Extended Properties=Excel 8.0;"objConn.Open strConnstrSql="SELECT * FROM [Sheet1$]"objRS.Open strSql,objConn,1,1objRS.MoveFirst%><!--#include file="conn.asp"--><%'循环excel中所有记录while not objRS.eofset rs = Server.CreateObject("Adodb.Recordset")'查询语句sql_s = "select * from ceshi where lname='" & objRS(0) & "' and old='" & objRS(1) & "' and sex='" & objRS(2) & "' and guojia='" & objRS(3) & "' and QQ='" & objRS(4) & "'" rs.open sql_s, conn, 1, 1'重复的数据不做录⼊操作if rs.eof then'插⼊语句'****excel中第⼀条不会被录⼊****sql = "insert into ceshi (lname, old, sex, guojia, QQ)values ('" & objRS(0) & "', '" & objRS(1) & "', '" & objRS(2) & "', '" & objRS(3) & "', '" & objRS(4) & "')"'执⾏插⼊conn.execute(sql)end ifobjRS.MoveNextrs.closeset rs = nothingwend'⼜到了各种关闭的时候conn.closeset conn = nothingobjRS.CloseobjConn.Closeset objRS = Nothingset objConn = Nothingresponse.Write("<script>alert('导⼊成功');window.location.href='index.html';</script>")response.End()Function FileExec(fileName)FileExec = Mid(fileName,Instr(fileName,".")+1,Len(fileName)-Instr(fileName,"."))End Function%>再分享⼀个简化版的代码wenjian=request.Form("floor")fileext=mid(wenjian,InStrRev(wenjian,".")+1)if lcase(fileext)<>"xls" thenresponse.write "<script>alert ('⽂件格式不对,请上传Excel⽂件');window.location.href='updateFloor.asp';</script>"response.endend ifset conne=server.CreateObject("ADODB.Connection")connStre="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath( ""&wenjian&"" )&";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"conne.open connStreSqle="select * from [sheet1$] "Set rse = Server.CreateObject("ADODB.Recordset")rse.open sqle,conne,1,1'验证hang=2do while not rse.eof'名称不能为空if trim(rse(0))<>"" thenelsemess="第"& hang &"⾏名称为空,请检查!"response.Write"<script>alert('"& mess &"').window.location.href='updateFloor.asp'</script>"response.End()end ifrse.movenexthang=hang+1looprse.movefirstdo while not rse.eofset rst=server.CreateObject("adodb.recordset")sqlt="select * from Sellman"rst.open sqlt,conn,1,3rst.addnew()rst("CompanyName")=c2(rse(0))rst("CompanyInfo")=c2(rse(1))rst("address")=c2(rse(2))rst("tel")=c2(rse(3))&" "&c2(rse(7))rst("Fax")=c2(rse(4))rst("linkman")=c2(rse(5))rst("Homepage")=c2(rse(8))rst("Email")=c2(rse(6))rst.update()rst.closeset rst=nothingrse.movenextlooprse.closeset rse=nothingresponse.Write "<script>alert('导⼊成功!');location.href='updateFloor.asp';</script>"其实简单的说象access 数据库⼀样,把excel⽂件打开,再进⾏读再写到access中你要写到sqlserver中就把写的过程改⼀下就成了看下代码:dim conndim conn2set conn=CreateObject("ADODB.Connection")conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=c:\book1.mdb"set conn2=CreateObject("ADODB.Connection")conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source=c:\book1.xls"sql = "SELECT * FROM [Sheet1$]"set rs = conn2.execute(sql)while not rs.eofsql = "insert into xxx([a],[b],[c],[d]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"')"conn.execute(sql)rs.movenextwendconn.closeset conn = nothingconn2.closeset conn2 = nothingfunction fixsql(str)dim newstrnewstr = strif isnull(newstr) thennewstr = ""elsenewstr = replace(newstr,"'","''")end iffixsql = newstrend function。
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的方法与代码:<%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文件:
<HTML>
<HEAD>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<TITLE>生成EXCEL文件实例</TITLE>
</HEAD>
<body>
<a href="dbtoexcel.asp?act=make">在线生成EXCEL</a>
<hr size=1 align=left width=300px>
<%
if Request("act") = "" then
Response.Write "生成EXCEL文件实例"
else
dim conn,strconn
strconn="driver={SQL Server};server=wen;uid=sa;pwd=;database=DB_Test" set conn=server.CreateObject("adodb.connection")
conn.Open strconn
dim rs,sql,filename,fs,myfile,x
Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename = Server.MapPath("online.xls")
'--如果原来的EXCEL文件存在的话就删除
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)
Set rs = Server.CreateObject("ADODB.Recordset")
'--从数据库中把你想放到EXCEL中的数据读出来
sql = "select * from Tb_Execl order by id desc"
rs.Open sql,conn,1,1
if rs.EOF and rs.BOF then
else
dim strLine,responsestr
strLine=""
For each x in rs.fields
strLine = strLine & & chr(9)
Next
'--将表的列名先写入EXCEL
myfile.writeline strLine
Do while Not rs.EOF
strLine=""
for each x in rs.Fields
strLine = strLine & x.value & chr(9) next
'--将表的数据写入EXCEL myfile.writeline strLine
rs.MoveNext
loop
end if
rs.Close
set rs = nothing
End If
%>
以上信息更具具体情况修改。