导出合并后的多个工作表在一个EXCEL中,首先引用CarlosAg.ExcelXmlWriter.dll

最新推荐文章于 2026-06-20 20:47:01 发布

原创最新推荐文章于 2026-06-20 20:47:01 发布 · 1.4k 阅读

本内容遵循CC 4.0 BY-SA版权协议

标签

#excel #工作 #styles #properties #merge

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Diagnostics;

using CarlosAg.ExcelXmlWriter;

public partial class Default4 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Workbook book = new Workbook();

        #region 设置样式
        // Specify which Sheet should be opened and the size of window by default
        book.ExcelWorkbook.ActiveSheetIndex = 1;
        book.ExcelWorkbook.WindowTopX = 100;
        book.ExcelWorkbook.WindowTopY = 200;
        book.ExcelWorkbook.WindowHeight = 768;
        book.ExcelWorkbook.WindowWidth = 1024;

        // Some optional properties of the Document
        book.Properties.Author = "Fangyw";
        book.Properties.Title = "PK" + "项目UES统计表";
        book.Properties.Created = DateTime.Now;

        // Add some styles to the Workbook
        WorksheetStyle style = book.Styles.Add("HeaderStyle1");
        style.Font.FontName = "宋体";
        style.Font.Size = 11;
        style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
        style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
        style.Interior.Color = "#ffff99";
        style.Interior.Pattern = StyleInteriorPattern.Solid;
        style.Alignment.WrapText = true;

        style = book.Styles.Add("HeaderStyle2");
        style.Font.FontName = "宋体";
        style.Font.Size = 11;
        style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
        style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");
        style.Interior.Color = "#ffcc99";
        style.Interior.Pattern = StyleInteriorPattern.Solid;

        // Create the Default Style to use for everyone
        style = book.Styles.Add("Common");
        style.Font.FontName = "宋体";
        style.Font.Size = 11;
        style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
        style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "#000000");
        style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "#000000");

        style = book.Styles.Add("Default");
        style.Font.FontName = "宋体";
        style.Font.Size = 11;
        style.Alignment.Horizontal = StyleHorizontalAlignment.Center;

// Add a Worksheet with some data
Worksheet sheet = book.Worksheets.Add("统计表");

        // we can optionally set some column settings
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        #endregion

        //标题
        WorksheetRow row = sheet.Table.Rows.Add();
        WorksheetCell cell = row.Cells.Add("PK" + "项目UES统计表（截止" + DateTime.Now.ToShortDateString() + "）");
        cell.MergeAcross = 12;            // Merge two cells together
        cell.StyleID = "Default";

        #region 列头
        //第一行
        row = sheet.Table.Rows.Add();
        cell = row.Cells.Add("专业处");
        cell.MergeDown = 1;            // Merge two cells together
        cell.StyleID = "Common";
        cell = row.Cells.Add("P板块当前流转数");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle1";
        cell = row.Cells.Add("P板块责任UES总数");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle1";
        cell = row.Cells.Add("P板块责任UES关闭数");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle1";
        cell = row.Cells.Add("P板块责任UES关闭率");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle1";
        cell = row.Cells.Add("P板块责任UES已处理数");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle1";
        cell = row.Cells.Add("P板块责任UES处理率");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle1";
        cell = row.Cells.Add("流转中超期项（X天）");
        cell.MergeAcross = 2;
        cell.StyleID = "HeaderStyle2";
        cell = row.Cells.Add("上周关闭数");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle2";
        cell = row.Cells.Add("上周处理数");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle2";
        cell = row.Cells.Add("上周新增数");
        cell.MergeDown = 1;
        cell.StyleID = "HeaderStyle2";

        //第二行
        row = sheet.Table.Rows.Add();
        cell = row.Cells.Add("30≤X<60");
        cell.StyleID = "HeaderStyle2";
        cell.Index = 8;
        cell = row.Cells.Add("60≤X<90");
        cell.StyleID = "HeaderStyle2";
        cell.Index = 9;
        cell = row.Cells.Add("90≤X");
        cell.StyleID = "HeaderStyle2";
        cell.Index = 10;
        #endregion

        #region 数据行
        //for (int j = 0; j < dtStat.Rows.Count; j++)
        //{
        row = sheet.Table.Rows.Add();
        //row.Cells.Add(new WorksheetCell(dtStat.Rows[j]["Dept"].ToString(), "Common"));
        row.Cells.Add(new WorksheetCell("Dept", "Common"));
        row.Cells.Add(new WorksheetCell("P_Cur", "Common"));
        row.Cells.Add(new WorksheetCell("P_All", "Common"));
        row.Cells.Add(new WorksheetCell("P_Close", "Common"));
        row.Cells.Add(new WorksheetCell("P_Close_Radio", "Common"));
        row.Cells.Add(new WorksheetCell("P_Dealed", "Common"));
        row.Cells.Add(new WorksheetCell("P_Deal_Radio", "Common"));
        row.Cells.Add(new WorksheetCell("Over_OneToTwo", "HeaderStyle2"));
        row.Cells.Add(new WorksheetCell("Over_TwoToThree", "HeaderStyle2"));
        row.Cells.Add(new WorksheetCell("Over_ThreeAbove", "HeaderStyle2"));
        row.Cells.Add(new WorksheetCell("Last_Closed", "HeaderStyle2"));
        row.Cells.Add(new WorksheetCell("Last_Dealed", "HeaderStyle2"));
        row.Cells.Add(new WorksheetCell("Last_NewAdd", "HeaderStyle2"));
        //}
        #endregion

        //输出
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "UTF-8"; //设置了类型为中文防止乱码的出现
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + "PK" + "UES.xls"); //定义输出文件和文件名
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");//设置输出流为简体中文
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
        book.Save(Response.OutputStream);
        Response.End();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        Workbook book = new Workbook();

        // Some optional properties of the Document
        book.Properties.Author = "Fangyw";
        book.Properties.Title = "PK" + "项目UES统计表";
        book.Properties.Created = DateTime.Now;

        style = book.Styles.Add("Default");
        style.Font.FontName = "宋体";
        style.Font.Size = 11;
        style.Alignment.Horizontal = StyleHorizontalAlignment.Center;

// Add a Worksheet with some data
Worksheet sheet = book.Worksheets.Add("FW_P版块统计");

        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));
        sheet.Table.Columns.Add(new WorksheetColumn(60));

#endregion

        //标题
        WorksheetRow row = sheet.Table.Rows.Add();
        WorksheetCell cell = row.Cells.Add("PK" + "项目FW尾项统计报表（截止" +DateTime.Now.ToShortDateString() + "）");
        cell.MergeAcross = 8;            // Merge two cells together
        cell.StyleID = "Default";

        #region FW_P版块_列头
        //第一行
        row = sheet.Table.Rows.Add();
        cell = row.Cells.Add("专业处");
        cell.MergeDown = 1;            // Merge two cells together
        cell.StyleID = "Common";
        cell = row.Cells.Add("初始责任为[PSE]的FW");
        cell.MergeAcross = 6;

        cell.StyleID = "HeaderStyle1";
        cell = row.Cells.Add("初始责任非[PSE]的FW");
        cell.MergeAcross = 1;
        cell.StyleID = "HeaderStyle2";
        //第二行
        row = sheet.Table.Rows.Add();
        cell = row.Cells.Add("总数");
        cell.StyleID = "HeaderStyle1";
        cell.Index = 2;
        cell = row.Cells.Add("已关闭数");
        cell.StyleID = "HeaderStyle1";
        cell.Index = 3;
        cell = row.Cells.Add("未关闭数");
        cell.StyleID = "HeaderStyle1";
        cell.Index = 4;
        cell = row.Cells.Add("关闭率");
        cell.StyleID = "HeaderStyle1";
        cell.Index = 5;
        cell = row.Cells.Add("已处理数");
        cell.StyleID = "HeaderStyle1";
        cell.Index = 6;
        cell = row.Cells.Add("未处理数");
        cell.StyleID = "HeaderStyle1";
        cell.Index = 7;
        cell = row.Cells.Add("处理率");
        cell.StyleID = "HeaderStyle1";
        cell.Index = 8;
        cell = row.Cells.Add("未处理数");
        cell.StyleID = "HeaderStyle2";
        cell.Index = 9;
        #endregion

Worksheet fwOtherSheet = book.Worksheets.Add("其它处及供应商明细信息");

Worksheet fwNoDoCountSheet = book.Worksheets.Add("未处理明细信息");

        #region P版块统计数据行
        for (int j = 0; j < 2; j++)
        {
            //row.Cells.Add(new WorksheetCell(dtStat.Rows[j]["Dept"].ToString(), "Common"));
            row = sheet.Table.Rows.Add();
            row.Cells.Add(new WorksheetCell("Dept", "Common"));
            row.Cells.Add(new WorksheetCell("P_Cur", "HeaderStyle1"));
            row.Cells.Add(new WorksheetCell("P_All", "HeaderStyle1"));

            row.Cells.Add(new WorksheetCell("P_Close", "HeaderStyle1"));
            row.Cells.Add(new WorksheetCell("P_Close_Radio", "HeaderStyle1"));
            row.Cells.Add(new WorksheetCell("P_Dealed", "HeaderStyle1"));

            row.Cells.Add(new WorksheetCell("P_Deal_Radio", "HeaderStyle1"));
            row.Cells.Add(new WorksheetCell("Over_OneToTwo", "HeaderStyle1"));
            row.Cells.Add(new WorksheetCell("Over_TwoToThree", "HeaderStyle2"));
        }
        #endregion

        #region 其它处及供应商明细信息数据行
        for (int j = 0; j < 3; j++)
        {
            //row.Cells.Add(new WorksheetCell(dtStat.Rows[j]["Dept"].ToString(), "Common"));
            row = fwOtherSheet.Table.Rows.Add();
            row.Cells.Add(new WorksheetCell("Dept", "Common"));
            row.Cells.Add(new WorksheetCell("P_Cur", "HeaderStyle1"));
            row.Cells.Add(new WorksheetCell("P_All", "HeaderStyle1"));

        #region 未处理明细信息
        for (int j = 0; j < 1; j++)
        {
            //row.Cells.Add(new WorksheetCell(dtStat.Rows[j]["Dept"].ToString(), "Common"));
            row = fwNoDoCountSheet.Table.Rows.Add();
            row.Cells.Add(new WorksheetCell("Dept", "Common"));
            row.Cells.Add(new WorksheetCell("P_Cur", "HeaderStyle1"));
            row.Cells.Add(new WorksheetCell("P_All", "HeaderStyle1"));

        //输出
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "UTF-8"; //设置了类型为中文防止乱码的出现
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + "PK" +"123.xls"); //定义输出文件和文件名
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");//设置输出流为简体中文
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
        book.Save(Response.OutputStream);
        Response.End();
    }
}