.net多sheet页导出文件

using System;
using System.Data;
using System.Linq;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
//using System.Web.UI.WebControls.WebParts;
//using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Text;
//using MessageDeliver;
using System.Data.SqlClient;
//添加的引用
using Salien.Utility.SUWF;
using Salien.Utility;

namespace btnExportToExcel
{
    public class ClsQueyMsg_BJXX : ISuwfBus
    {
        #region 变量声明
        private SlnSuwfPage _page;
        #endregion

        #region 初始化
        /// 

        /// 初始化
        /// 
        /// 程序单元
        public void Initial(SlnSuwfPage page)
        {
            _page = page;
            _page.Load += new EventHandler(this.Page_Load);
            BindButtoEvent("btnQuey1");   //绑定按钮
        }
        #endregion

        #region 绑定按钮事件
        /// 

        /// 绑定按钮事件
        /// 
        /// 按钮名称
        public void BindButtoEvent(string strBtnName)
        {
            Control crl = _page.FindControl(strBtnName);
            if (crl == null) return;
            Button btnQuey1 = (Button)crl;
            if (btnQuey1 == null) return;
            btnQuey1.Click += new EventHandler(btnQuey1_Click);
        }
        #endregion


        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnQuey1_Click(object sender, EventArgs e)
        {
            string ORG_ID = _page.GetControlValue("val_ORG_ID");
            string user_id = _page.GetControlValue("val_user_id");
            string daydate = _page.GetControlValue("val_dayreport_date");
            string daydate2 = _page.GetControlValue("val2_DAYREPORT_DATE");
            string daytype = _page.GetControlValue("val_dayreport_type");
            DataTable td = new DataTable();
            string sql = "select pp.user_id,decode(pp.rs,1,t.u_name_full,2,t.u_name_full||'（'||b.u_name_full||'）') as name_full from auth_user_tb t,auth_organization_tb b,(select t.user_id,count(*) over(partition by t.u_name_full) as rs from auth_user_tb t) pp where pp.user_id=t.user_id and t.org_id=b.org_id and t.u_validate=1";
            string sqlDetail = @"select b.u_name_full as u_deptName,
       c.u_name_full,c.user_id,
       a.dayreport_date,
       a.dayreport_type,
       a.dayreport_content,
       a.dayreport_hour,
       a.dayreport_product
  from AUTH_DAYREPORT_TB a,auth_organization_tb b,auth_user_tb c
  where a.org_id=b.org_id
  and a.user_id=c.user_id";

            if (!string.IsNullOrWhiteSpace(ORG_ID))
            {
                sqlDetail += " and b.u_name_full=" + ORG_ID;
            }
            if (!string.IsNullOrWhiteSpace(user_id))
            {
                sqlDetail += " and c.u_name_full=" + user_id;
            }
            if (!string.IsNullOrWhiteSpace(daydate))
            {
                sqlDetail += " and a.dayreport_date>=to_date( " + "'" + daydate + "'," + "'yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrWhiteSpace(daydate2))
            {
                sqlDetail += " and a.dayreport_date<=to_date( " + "'" + daydate2 + "'," + "'yyyy-mm-dd hh24:mi:ss')";
            }
            if (!string.IsNullOrWhiteSpace(daytype))
            {
                sqlDetail += " and a.dayreport_type=" + daytype;
            }



            td = SlnDataAccess.GetDataTable(sql);
            DataTable tdDetail = SlnDataAccess.GetDataTable(sqlDetail);
            HSSFWorkbook workbook = new HSSFWorkbook();
            for (int i = 0; i < td.Rows.Count; i++)
            {
                createSheet(workbook, td.Rows[i]["user_id"].ToString(), td.Rows[i]["name_full"].ToString(), tdDetail);
            }


            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            workbook.Write(ms);
            ms.Position = 0;

            string filename = "周报" + DateTime.Parse(daydate).ToString("yyyyMMdd") + "-" + DateTime.Parse(daydate2).ToString("yyyyMMdd") + ".xls";
            var context = HttpContext.Current;

            context.Response.Clear();
            context.Response.Buffer = true;
            context.Response.Charset = "GB2312";
            context.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8).ToString());
            context.Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
            context.Response.ContentEncoding = System.Text.Encoding.Default;//.GetEncoding("GB2312");

            MemoryStream file = new MemoryStream();
            workbook.Write(file);
            //context.Response.BinaryWrite(file.GetBuffer());
            context.Response.End();
        }
        private void createSheet(HSSFWorkbook workbook, string userID, string sheetName, DataTable tdDetail)
        {
            DataRow[] rows = tdDetail.Select("user_id=" + userID);
            if (rows == null || rows.Count() == 0)
            {
                return;
            }
            ISheet sheet1 = workbook.CreateSheet(sheetName);
            IRow rowHead = sheet1.CreateRow(0);
            IRow row = sheet1.CreateRow(0);
            row.CreateCell(0).SetCellValue("序号");
            row.CreateCell(1).SetCellValue("组织机构");
            row.CreateCell(2).SetCellValue("姓名");
            row.CreateCell(3).SetCellValue("日期");
            row.CreateCell(4).SetCellValue("工作类型");
            row.CreateCell(5).SetCellValue("工作内容");
            row.CreateCell(6).SetCellValue("工时");
            row.CreateCell(7).SetCellValue("工作成果");
            row.CreateCell(8).SetCellValue("附件");

            sheet1.SetColumnWidth(1, 5000);
            sheet1.SetColumnWidth(2, 5000);
            sheet1.SetColumnWidth(3, 5000);
            sheet1.SetColumnWidth(4, 5000);
            sheet1.SetColumnWidth(5, 5000);
            sheet1.SetColumnWidth(6, 5000);
            sheet1.SetColumnWidth(7, 5000);
            sheet1.SetColumnWidth(8, 5000);

            for (var i = 0; i < rows.Count(); i++)
            {
                IRow row1 = sheet1.CreateRow(i + 1);
                row1.CreateCell(0).SetCellValue(i + 1);
                row1.CreateCell(1).SetCellValue(rows[i]["u_deptName"].ToString());
                row1.CreateCell(2).SetCellValue(rows[i]["u_name_full"].ToString());
                row1.CreateCell(3).SetCellValue(rows[i]["dayreport_date"].ToString());
                row1.CreateCell(4).SetCellValue(rows[i]["dayreport_type"].ToString());
                row1.CreateCell(5).SetCellValue(rows[i]["dayreport_content"].ToString());
                row1.CreateCell(6).SetCellValue(rows[i]["dayreport_hour"].ToString());
                row1.CreateCell(7).SetCellValue(rows[i]["dayreport_product"].ToString());
                row1.CreateCell(8).SetCellValue("附件");
            }
        }
    }
}