第十一节 导出Excel
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <a href="DowloadExcel.ashx">下载</a> </div> <div> <a href="DowloadTableExcel.ashx">下载数据库文件</a> </div> </form> </body> </html>
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string dataDir = AppDomain.CurrentDomain.BaseDirectory; if (dataDir.EndsWith(@"\bin\Debug\") || dataDir.EndsWith(@"\bin\Release\")) { dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName; AppDomain.CurrentDomain.SetData("DataDirectory", dataDir); } } }
<%@ WebHandler Language="C#" Class="DowloadExcel" %> using System; using System.Web; using NPOI.HSSF.UserModel; public class DowloadExcel : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "application/x-excel"; string filename = HttpUtility.UrlEncode("动态数据.xls"); //神啊!调试了一个晚上才是这句话的错 context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + filename); //请注意,下面这句话是错误的 //context.Response.AddHeader("Content-Displsition", "attachment;filename=" + filename); HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个xls; HSSFSheet sheet = workbook.CreateSheet(); //创建一个Sheet HSSFRow row = sheet.CreateRow(0); row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("hello"); workbook.Write(context.Response.OutputStream); } public bool IsReusable { get { return false; } } }
<%@ WebHandler Language="C#" Class="DowloadTableExcel" %> using System; using System.Web; using NPOI.HSSF.UserModel; using System.Data.SqlClient; public class DowloadTableExcel : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "application/x-excel"; //context.Response.Write("Hello World"); string filename = HttpUtility.UrlEncode("用户表.xls");//url编码,防止乱码 context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + filename); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(); using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\MyProjects\C#net传智播客\第十一节asp.net中级\导出Excel\App_Data\Database.mdf;Integrated Security=True;User Instance=True")) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select * FROM T_User"; using(SqlDataReader reader = cmd.ExecuteReader()) { HSSFRow _row = sheet.CreateRow(0); _row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("ID"); _row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue("用户名"); _row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue("性别"); int rownum = 1; while (reader.Read()) { Int64 id = reader.GetInt64(reader.GetOrdinal("id")); string name = reader.GetString(reader.GetOrdinal("name")); string sex = reader.GetString(reader.GetOrdinal("sex")); HSSFRow row = sheet.CreateRow(rownum); row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(id); row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(name); row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue(sex); rownum++; //context.Response.Write("id:"+id+", name:"+name+", sex="+sex+"<BR>"); } } } } workbook.Write(context.Response.OutputStream); } public bool IsReusable { get { return false; } } }