.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 初始化
/// <summary>
/// 初始化
/// </summary>
/// <param name="page">程序单元
public void Initial(SlnSuwfPage page)
{
_page = page;
_page.Load += new EventHandler(this.Page_Load);
BindButtoEvent("btnQuey1"); //绑定按钮
}
#endregion

#region 绑定按钮事件
/// <summary>
/// 绑定按钮事件
/// </summary>
/// <param name="strBtnName">按钮名称
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("附件");
}
}
}
}
————————————————
版权声明：本文为CSDN博主「LaughingSister」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/laughingsister/article/details/72900883