.Net web 实现数据导出到Excel中

数据库相关

　　数据库名称：EHotelDb　　　　表名称：Booking

　　

后台代码相关：（节选）

　　自己封装的Function类，用于数据库操作

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace EHotelMS.Models
{
    public class Functions
    {
        private SqlConnection Con;
        private SqlCommand Cmd;
        private DataTable dt;
        private string ConStr;
        private SqlDataAdapter sda;

        public int setData(string Query)
        {
            int Cnt;
            if(Con.State == ConnectionState.Closed)
            {
                Con.Open();
            }
            Cmd.CommandText = Query;
            Cnt = Cmd.ExecuteNonQuery();
            Con.Close();
            return Cnt;
        }

        public DataTable GetData(string Query)
        {
            dt = new DataTable();
            sda = new SqlDataAdapter(Query, ConStr);
            sda.Fill(dt);
            return dt;
        }

        public Functions()//连接数据库
        {
            ConStr = @"Data Source=.;Initial Catalog=EHotelDb;Integrated Security=True";
            Con = new SqlConnection(ConStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Con;
        }
    }
}

　　后台页面XXX.aspx.cs中调用Function类，实现数据库的连接等操作

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;

namespace EHotelMS.View.Users
{
    public partial class Booking : System.Web.UI.Page
    {
        Models.Functions Con;
        protected void Page_Load(object sender, EventArgs e)
        {
            Con = new Models.Functions();
        }
    ｝
｝

　　下面的代码也都是写在上面说的XXX.aspx.cs中

　　在控件（导出按钮）的监听事件中，将查询出数据的DataTable和自定义的文件名传给NpoiExcel函数

protected void BookingGV_SelectedIndexChanged(object sender, EventArgs e)
        {
            string Query = "select * from BookingTb1 ";
            NpoiExcel(Con.GetData(Query), "文件名");
        }

　　NpoiExcel函数

public void NpoiExcel(DataTable dt, string title)
        {
            HSSFWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet("工作簿1");

            IRow headerrow = sheet.CreateRow(0);
            ICellStyle style = book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            string[] headName = {"订单号","当前时间","房间序号","用户ID","入住时间","退房时间","金额" };
            //设置表头：查询有几列，然后给每列赋值上字段名称
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = style;
                //cell.SetCellValue(dt.Columns[i].ColumnName);//将表头设置为数据库的字段名
                cell.SetCellValue(headName[i]);//自定义表头，从自定义的数组中取值

            }
            //设置表中数据
            for(int i = 0; i < dt.Rows.Count; i++)//控制行
            {
                IRow LineRow = sheet.CreateRow(i+1);
                for(int j = 0; j < dt.Columns.Count; j++)//控制列
                {
                    
                    ICell lineCell = LineRow.CreateCell(j);
                    lineCell.CellStyle = style;
                    lineCell.SetCellValue(dt.Rows[i][j].ToString());
                    if (j == 1 || j == 4 || j == 5)//这些列和时间相关，需要转换时间格式
                    {
                        lineCell.SetCellValue(Convert.ToDateTime(dt.Rows[i][j]).ToString("yyyy-MM-dd"));
                    }
                }
            }
            
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }