1.需要添加引用Aspose.Cells.dll
2.这是代码压缩包下载地址：自定义Excel模板导出数据.rar
3.前台界面
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="daochu.aspx.cs" Inherits="Oceansoft.Net.CeSWeb.Module.导出Excel_通过模板_.daochu" %> 2 3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 4 5 <html xmlns="http://www.w3.org/1999/xhtml"> 6 <head runat="server"> 7 <title></title> 8 </head> 9 <body> 10 <form id="form1" runat="server"> 11 <div> 12 <asp:Button ID="btnDaochu" runat="server" Text="Excel自定义模板填充数据导出" OnClick="btnDaochu_ServerClick" /> 13 </div> 14 </form> 15 </body> 16 </html>
4.后台代码：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using Aspose.Cells; 8 using System.Text; 9 using System.Data; 10 11 namespace Oceansoft.Net.CeSWeb.Module.导出Excel_通过模板_ 12 { 13 public partial class daochu : System.Web.UI.Page 14 { 15 protected void Page_Load(object sender, EventArgs e) 16 { 17 18 } 19 /// <summary> 20 /// 单击导出 21 /// </summary> 22 /// <param name="sender"></param> 23 /// <param name="e"></param> 24 public void btnDaochu_ServerClick(object sender, EventArgs e) 25 { 26 string file = Server.MapPath("~/Excel/ZWxxtj.xls"); 27 DataSet ds = new DataSet(); 28 DataTable dtout = new DataTable(); 29 dtout.Columns.Add("单位", typeof(string)); 30 dtout.Columns.Add("数量", typeof(string)); 31 dtout.Columns.Add("分数", typeof(string)); 32 dtout.Columns.Add("看看", typeof(string)); 33 34 List<DataTable> lst = null; 35 for (int i = 0; i < 3; i++) 36 { 37 lst = new List<DataTable>(); 38 39 DataRow dr = dtout.NewRow(); 40 dr["单位"] = "AAAA" + i; 41 dr["数量"] = "BBBB" + i; 42 dr["分数"] = "CCCC" + i; 43 dr["看看"] = "DDDD" + i; 44 dtout.Rows.Add(dr); 45 46 } 47 ds.Tables.Add(dtout); 48 49 lst.Add(ds.Tables[0]); 50 51 52 ExportExcelModel(this.Response, lst, file, 4);//从第四行开始填充数据 53 } 54 55 public void ExportExcelModel(HttpResponse res, List<System.Data.DataTable> Datas, string ExcelTemplatePath, int FirstRow) 56 { 57 //Excel的路径 是放excel模板的路径 58 WorkbookDesigner designer = new WorkbookDesigner(); 59 designer.Open(ExcelTemplatePath); 60 61 Worksheet sheet = designer.Workbook.Worksheets[0]; 62 sheet.Cells.ImportDataTable(Datas[0], false, FirstRow, 0, true); 63 64 var c11 = sheet.Cells[0, 0];//第一行 第一列 65 66 c11.PutValue("我是标题，大家新年快乐。。。"); 67 68 SaveOptions s = new XlsSaveOptions(SaveFormat.Excel97To2003); 69 string str = ""; 70 71 str = HttpUtility.UrlEncode("测试.xls", Encoding.UTF8).ToString(); 72 73 74 designer.Workbook.Save(res, str, ContentDisposition.Attachment, s); 75 76 } 77 } 78 }