.Net 自定义Excel模板导出数据

浏览数：39 / 时间：2015年06月09日

1.需要添加引用Aspose.Cells.dll

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>

View Code

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 }