.net多sheet页导出文件

拼搏现实的明天。 2022-06-14 02:21 244阅读 0赞

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("附件");
                }
            }
        }
    }