thinkyu/教育訓練系統/Web/Report/講師個人費用彙整表.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using WebLib;

namespace Education.Report
{
    public class 講師個人費用彙整表 : ReportBase
    {
        public 個人費用彙整表List data;
        public List<string> export_columns;
        public DateTime? 起日;
        public DateTime? 迄日;
        public string 身分別;
        public string 付款方式;

        private List<ExportColumn> columns = new List<ExportColumn>() {
            new ExportColumn() { Key="領款人姓名"   , Title="領款人姓名"   , Width=20  , FieldName="領款人姓名" },
            new ExportColumn() { Key="身分證字號"   , Title="身分證字號"   , Width=20  , FieldName="身分證字號" , Align=HorizontalAlignment.Center },
            new ExportColumn() { Key="堂數"         , Title="堂數"         , Width=10  , FieldName="堂數"       , Align=HorizontalAlignment.Right, MoneyFormat=true, MondyDigits=2 },
            new ExportColumn() { Key="扣繳類別"     , Title="扣繳類別"     , Width=50  , FieldName="扣繳類別名稱" },
            new ExportColumn() { Key="帳戶名"       , Title="帳戶名"       , Width=20  , FieldName="帳戶名" },
            new ExportColumn() { Key="付款金額"     , Title="付款金額"     , Width=15  , FieldName="付款金額"   , Align=HorizontalAlignment.Right, MoneyFormat=true },
            new ExportColumn() { Key="匯款帳號"     , Title="匯款帳號"     , Width=20  , FieldName="匯款帳號" },
            new ExportColumn() { Key="銀行分行代碼" , Title="銀行分行代碼" , Width=20  , FieldName="銀行分行代碼" },
            new ExportColumn() { Key="銀行分行"     , Title="銀行/分行"    , Width=20  , FieldName="銀行分行" }
        };

        public MemoryStream DoReport()
        {
            workbook.RemoveSheetAt(0);

            sheet = workbook.CreateSheet("講師個人費用彙整表");
            SetPaperAndMargin(sheet);
            sheet.RepeatingRows = new CellRangeAddress(0, 2, -1, -1);

            excel.Sheet = sheet;

            // 1. 預先過濾出需要匯出的欄位
            var activeColumns = columns.Where(c => true).ToList();

            // 報表抬頭
            int rowId = 0;
            excel.MergeCell(rowId, 0, rowId, 1, BorderStyle.Thin);
            excel.SetCellValue(rowId, 0, "活動日期：", bodyStyleLeft);
            excel.MergeCell(rowId, 2, rowId, 3, BorderStyle.Thin);
            if (起日 != null && 迄日 != null)
                excel.SetCellValue(rowId, 2, string.Format("{0:yyyy-MM-dd} ~ {1:yyyy-MM-dd}", 起日, 迄日), bodyStyleLeft);
            else if (起日 != null && 迄日 == null)
                excel.SetCellValue(rowId, 2, string.Format("{0:yyyy-MM-dd} ~ ", 起日), bodyStyleLeft);
            else if (起日 == null && 迄日 != null)
                excel.SetCellValue(rowId, 2, string.Format("~ {0:yyyy-MM-dd}", 迄日), bodyStyleLeft);
            else
                excel.SetCellValue(rowId, 2, "全部", bodyStyleLeft);
            rowId++;
            excel.MergeCell(rowId, 0, rowId, 1, BorderStyle.Thin);
            excel.SetCellValue(rowId, 0, "身分別：", bodyStyleLeft);
            excel.MergeCell(rowId, 2, rowId, 3, BorderStyle.Thin);
            if (string.IsNullOrEmpty(身分別))
                excel.SetCellValue(rowId, 2, "全部", bodyStyleLeft);
            else
                excel.SetCellValue(rowId, 2, 身分別, bodyStyleLeft);
            rowId++;
            excel.MergeCell(rowId, 0, rowId, 1, BorderStyle.Thin);
            excel.SetCellValue(rowId, 0, "付款方式：", bodyStyleLeft);
            excel.MergeCell(rowId, 2, rowId, 3, BorderStyle.Thin);
            if (string.IsNullOrEmpty(付款方式))
                excel.SetCellValue(rowId, 2, "全部", bodyStyleLeft);
            else
                excel.SetCellValue(rowId, 2, 付款方式, bodyStyleLeft);
            rowId++;

            // 表頭
            excel.SetCellValue(rowId, 0, "序號", bodyStyleCenter);
            excel.SetColumnWidthInChars(0, 8);
            int col = 1;
            int colCount = 1;
            foreach (var column in activeColumns)
            {
                excel.SetCellValue(3, col, column.Title, bodyStyleCenter);
                excel.SetColumnWidthInChars(col, column.Width);
                col++;
                colCount++;
            }
            rowId++;

            // 報表內容
            foreach (var r in data)
            {
                // 序號
                col = 0;
                excel.SetCellValue(rowId, col++, rowId - 3, bodyStyleCenter);

                // 其它欄位內容
                foreach (var column in activeColumns)
                {
                    var cellStyle = bodyStyleLeft;
                    switch (column.Align)
                    {
                        case HorizontalAlignment.Center:
                            cellStyle = bodyStyleCenter;
                            break;
                        case HorizontalAlignment.Right:
                            cellStyle = bodyStyleRight;
                            break;
                    }
                    var value = r.FieldValue(column.FieldName);
                    SetDataValue(rowId, col, column, value, cellStyle);
                    col++;
                }

                rowId++;
            }

            // 報表尾
            excel.MergeCell(rowId, 0, rowId, 1);
            excel.SetCellValue(rowId, 0, "合計", bodyStyleCenter);
            excel.SetCellValue(rowId, 2, "", bodyStyleCenter);
            excel.SetCellValue(rowId, 3, DAC.GetDouble(data.Sum(p => p.堂數)), bodyNumberStyle2);
            excel.SetCellValue(rowId, 4, "", bodyStyleCenter);
            excel.SetCellValue(rowId, 5, "", bodyStyleCenter);
            excel.SetCellValue(rowId, 6, DAC.GetDouble(data.Sum(p => p.付款金額)), bodyNumberStyle);
            excel.SetCellValue(rowId, 7, "", bodyStyleCenter);
            excel.SetCellValue(rowId, 8, "", bodyStyleCenter);
            excel.SetCellValue(rowId, 9, "", bodyStyleCenter);

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            return ms;
        }
    }
}