313 lines
12 KiB
C#
313 lines
12 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Web;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using NPOI.SS.UserModel;
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.HSSF.Util;
|
|
using System.IO;
|
|
using NPOI.SS.Util;
|
|
|
|
/// <summary>
|
|
/// 報表基礎類別
|
|
/// </summary>
|
|
public abstract class ExcelReportBase
|
|
{
|
|
protected HSSFWorkbook workbook;
|
|
protected HSSFSheet sheet;
|
|
protected Excel excel;
|
|
protected IFont headerFont;
|
|
protected IFont footerFont;
|
|
protected IFont titleFont;
|
|
protected IFont titleFontBoldUnderLine;
|
|
protected IFont bodyFont;
|
|
protected IFont bodyFontS;
|
|
protected IFont bodyFont6PT;
|
|
protected IFont bodyFont8PT;
|
|
protected ICellStyle headerStyle;
|
|
protected ICellStyle footerStyle;
|
|
protected ICellStyle titleStyleLeft;
|
|
protected ICellStyle titleStyleRight;
|
|
protected ICellStyle titleStyleCenter;
|
|
protected ICellStyle titleStyleCenterYellowBGC;
|
|
protected ICellStyle titleStyleCenterBottom;
|
|
protected ICellStyle titleStyleLeftNoBorder;
|
|
protected ICellStyle titleStyleRightNoBorder;
|
|
protected ICellStyle titleStyleCenterNoBorder;
|
|
protected ICellStyle titleStyleCenterBottomNoBorder;
|
|
protected ICellStyle titleStyleCenterUDBorder;
|
|
protected ICellStyle bodyStyleLeft;
|
|
protected ICellStyle bodyStyleRight;
|
|
protected ICellStyle bodyStyleCenter;
|
|
protected ICellStyle bodyStyleLeftNoBorder;
|
|
protected ICellStyle bodyStyleRightNoBorder;
|
|
protected ICellStyle bodyStyleCenterNoBorder;
|
|
|
|
protected ICellStyle bodyNumberStyle;
|
|
protected ICellStyle bodyNumberStyleNoBorder;
|
|
protected ICellStyle bodyNumberStyleUDBorder;
|
|
protected ICellStyle bodyNumberStyle2;
|
|
protected ICellStyle bodyStyleCenterS;
|
|
protected ICellStyle bodyStyleCenterXS;
|
|
protected int HeaderRowHeight = 27;
|
|
protected int BodyRowHeight = 24;
|
|
protected int thisYy;
|
|
protected int thisMm;
|
|
protected int prevYy;
|
|
protected int prevMm;
|
|
|
|
public ExcelReportBase()
|
|
{
|
|
Init();
|
|
}
|
|
|
|
public DataSet 資料來源
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 公司名稱
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 客戶名稱
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 專案名稱
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 報表名稱
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public DateTime 報表時間
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 客戶編號
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 專案編號
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 年度
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public string 月份
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public Dictionary<string, string> 報表參數
|
|
{
|
|
get;
|
|
set;
|
|
}
|
|
|
|
public bool 以時分顯示時數 { get; set; }
|
|
|
|
protected virtual void Init()
|
|
{
|
|
workbook = new HSSFWorkbook();
|
|
sheet = workbook.CreateSheet("報表") as HSSFSheet;
|
|
sheet.ForceFormulaRecalculation = true;
|
|
excel = new Excel();
|
|
excel.WorkBook = workbook;
|
|
excel.Sheet = sheet;
|
|
numberFormat = workbook.CreateDataFormat();
|
|
|
|
SetPaperAndMargin(sheet);
|
|
|
|
headerFont = excel.NewFont("標楷體", 16, FontBoldWeight.Bold);
|
|
footerFont = excel.NewFont("標楷體", 16);
|
|
titleFont = excel.NewFont("標楷體", 12);
|
|
titleFontBoldUnderLine = excel.NewFont("標楷體", 12, FontBoldWeight.Bold);
|
|
titleFontBoldUnderLine.Underline = FontUnderlineType.Single;
|
|
bodyFont = excel.NewFont("標楷體", 12);
|
|
bodyFontS = excel.NewFont("標楷體", 10);
|
|
bodyFont6PT = excel.NewFont("標楷體", 6);
|
|
bodyFont8PT = excel.NewFont("標楷體", 8);
|
|
|
|
headerStyle = excel.NewCellStyle(headerFont, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
footerStyle = excel.NewCellStyle(footerFont, HorizontalAlignment.Left, VerticalAlignment.Top, true);
|
|
|
|
titleStyleLeft = excel.NewCellStyle(titleFont, HorizontalAlignment.Left, VerticalAlignment.Top, true);
|
|
titleStyleLeft.BorderTop = BorderStyle.Thin;
|
|
titleStyleLeft.BorderBottom = BorderStyle.Thin;
|
|
titleStyleLeft.BorderLeft = BorderStyle.Thin;
|
|
titleStyleLeft.BorderRight = BorderStyle.Thin;
|
|
|
|
titleStyleRight = excel.NewCellStyle(titleFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
titleStyleRight.BorderTop = BorderStyle.Thin;
|
|
titleStyleRight.BorderBottom = BorderStyle.Thin;
|
|
titleStyleRight.BorderLeft = BorderStyle.Thin;
|
|
titleStyleRight.BorderRight = BorderStyle.Thin;
|
|
|
|
titleStyleCenter = excel.NewCellStyle(titleFont, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
titleStyleCenter.BorderTop = BorderStyle.Thin;
|
|
titleStyleCenter.BorderBottom = BorderStyle.Thin;
|
|
titleStyleCenter.BorderLeft = BorderStyle.Thin;
|
|
titleStyleCenter.BorderRight = BorderStyle.Thin;
|
|
|
|
titleStyleCenterYellowBGC = excel.NewCellStyle(titleFont, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
titleStyleCenterYellowBGC.BorderTop = BorderStyle.Thin;
|
|
titleStyleCenterYellowBGC.BorderBottom = BorderStyle.Thin;
|
|
titleStyleCenterYellowBGC.BorderLeft = BorderStyle.Thin;
|
|
titleStyleCenterYellowBGC.BorderRight = BorderStyle.Thin;
|
|
titleStyleCenterYellowBGC.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
|
|
titleStyleCenterYellowBGC.FillPattern = FillPattern.SolidForeground;
|
|
titleStyleCenterYellowBGC.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
|
|
|
|
titleStyleCenterBottom = excel.NewCellStyle(titleFont, HorizontalAlignment.Center, VerticalAlignment.Bottom, true);
|
|
titleStyleCenterBottom.BorderTop = BorderStyle.Thin;
|
|
titleStyleCenterBottom.BorderBottom = BorderStyle.Thin;
|
|
titleStyleCenterBottom.BorderLeft = BorderStyle.Thin;
|
|
titleStyleCenterBottom.BorderRight = BorderStyle.Thin;
|
|
|
|
titleStyleLeftNoBorder = excel.NewCellStyle(titleFont, HorizontalAlignment.Left, VerticalAlignment.Top, true);
|
|
titleStyleRightNoBorder = excel.NewCellStyle(titleFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
titleStyleCenterNoBorder = excel.NewCellStyle(titleFont, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
titleStyleCenterBottomNoBorder = excel.NewCellStyle(titleFont, HorizontalAlignment.Center, VerticalAlignment.Bottom, true);
|
|
|
|
titleStyleCenterUDBorder = excel.NewCellStyle(titleFont, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
titleStyleCenterUDBorder.BorderTop = BorderStyle.Thin;
|
|
titleStyleCenterUDBorder.BorderBottom = BorderStyle.Thin;
|
|
|
|
bodyStyleLeft = excel.NewCellStyle(bodyFont, HorizontalAlignment.Left, VerticalAlignment.Top, true);
|
|
bodyStyleLeft.BorderTop = BorderStyle.Thin;
|
|
bodyStyleLeft.BorderBottom = BorderStyle.Thin;
|
|
bodyStyleLeft.BorderLeft = BorderStyle.Thin;
|
|
bodyStyleLeft.BorderRight = BorderStyle.Thin;
|
|
bodyStyleLeft.WrapText = true;
|
|
|
|
bodyStyleRight = excel.NewCellStyle(bodyFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
bodyStyleRight.BorderTop = BorderStyle.Thin;
|
|
bodyStyleRight.BorderBottom = BorderStyle.Thin;
|
|
bodyStyleRight.BorderLeft = BorderStyle.Thin;
|
|
bodyStyleRight.BorderRight = BorderStyle.Thin;
|
|
bodyStyleRight.WrapText = true;
|
|
|
|
bodyStyleCenter = excel.NewCellStyle(bodyFont, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
bodyStyleCenter.BorderTop = BorderStyle.Thin;
|
|
bodyStyleCenter.BorderBottom = BorderStyle.Thin;
|
|
bodyStyleCenter.BorderLeft = BorderStyle.Thin;
|
|
bodyStyleCenter.BorderRight = BorderStyle.Thin;
|
|
bodyStyleCenter.WrapText = true;
|
|
|
|
bodyStyleLeftNoBorder = excel.NewCellStyle(bodyFont, HorizontalAlignment.Left, VerticalAlignment.Top, true);
|
|
bodyStyleLeftNoBorder.WrapText = true;
|
|
|
|
bodyStyleRightNoBorder = excel.NewCellStyle(bodyFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
bodyStyleRightNoBorder.WrapText = true;
|
|
|
|
bodyStyleCenterNoBorder = excel.NewCellStyle(bodyFont, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
bodyStyleCenterNoBorder.WrapText = true;
|
|
|
|
bodyStyleCenterS = excel.NewCellStyle(bodyFontS, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
bodyStyleCenterS.BorderTop = BorderStyle.Thin;
|
|
bodyStyleCenterS.BorderBottom = BorderStyle.Thin;
|
|
bodyStyleCenterS.BorderLeft = BorderStyle.Thin;
|
|
bodyStyleCenterS.BorderRight = BorderStyle.Thin;
|
|
bodyStyleCenterS.WrapText = true;
|
|
|
|
bodyStyleCenterXS = excel.NewCellStyle(bodyFont6PT, HorizontalAlignment.Center, VerticalAlignment.Top, true);
|
|
bodyStyleCenterXS.BorderTop = BorderStyle.Thin;
|
|
bodyStyleCenterXS.BorderBottom = BorderStyle.Thin;
|
|
bodyStyleCenterXS.BorderLeft = BorderStyle.Thin;
|
|
bodyStyleCenterXS.BorderRight = BorderStyle.Thin;
|
|
bodyStyleCenterXS.WrapText = true;
|
|
|
|
bodyNumberStyle = excel.NewCellStyle(bodyFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
bodyNumberStyle.DataFormat = numberFormat.GetFormat("###,###,##0");
|
|
bodyNumberStyle.BorderTop = BorderStyle.Thin;
|
|
bodyNumberStyle.BorderBottom = BorderStyle.Thin;
|
|
bodyNumberStyle.BorderLeft = BorderStyle.Thin;
|
|
bodyNumberStyle.BorderRight = BorderStyle.Thin;
|
|
bodyNumberStyle.WrapText = true;
|
|
|
|
bodyNumberStyle2 = excel.NewCellStyle(bodyFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
bodyNumberStyle2.DataFormat = numberFormat.GetFormat("###,###,##0.00");
|
|
bodyNumberStyle2.BorderTop = BorderStyle.Thin;
|
|
bodyNumberStyle2.BorderBottom = BorderStyle.Thin;
|
|
bodyNumberStyle2.BorderLeft = BorderStyle.Thin;
|
|
bodyNumberStyle2.BorderRight = BorderStyle.Thin;
|
|
bodyNumberStyle2.WrapText = true;
|
|
|
|
bodyNumberStyleNoBorder = excel.NewCellStyle(bodyFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
bodyNumberStyleNoBorder.DataFormat = numberFormat.GetFormat("###,###,##0");
|
|
bodyNumberStyleNoBorder.WrapText = true;
|
|
|
|
bodyNumberStyleUDBorder = excel.NewCellStyle(bodyFont, HorizontalAlignment.Right, VerticalAlignment.Top, true);
|
|
bodyNumberStyleUDBorder.DataFormat = numberFormat.GetFormat("###,###,##0");
|
|
bodyNumberStyleUDBorder.BorderTop = BorderStyle.Thin;
|
|
bodyNumberStyleUDBorder.BorderBottom = BorderStyle.Thin;
|
|
bodyNumberStyleUDBorder.WrapText = true;
|
|
|
|
資料來源 = new DataSet();
|
|
}
|
|
|
|
private IDataFormat numberFormat;
|
|
protected IDataFormat NumberFormat { get { return numberFormat; } }
|
|
|
|
protected void SetPaperAndMargin(HSSFSheet Asheet)
|
|
{
|
|
// 設定紙張
|
|
Asheet.PrintSetup.PaperSize = 9; // A4
|
|
Asheet.PrintSetup.Landscape = false; // 橫向
|
|
|
|
// 列印格式
|
|
Asheet.PrintSetup.UsePage = true;
|
|
Asheet.PrintSetup.FitWidth = 1; // 將所有欄擠進 1 頁寬
|
|
Asheet.PrintSetup.FitHeight = 0; // 0 表示不指定幾頁
|
|
|
|
// 頁首頁尾
|
|
Asheet.Footer.Left = "列印日期：&D";
|
|
Asheet.Footer.Right = "頁數：&P";
|
|
|
|
// 紙張邊界
|
|
Asheet.PrintSetup.HeaderMargin = 0d;
|
|
Asheet.PrintSetup.FooterMargin = 0.3d;
|
|
Asheet.SetMargin(MarginType.TopMargin, 0.5d);
|
|
Asheet.SetMargin(MarginType.BottomMargin, 0.5d);
|
|
Asheet.SetMargin(MarginType.LeftMargin, 0.3d);
|
|
Asheet.SetMargin(MarginType.RightMargin, 0.3d);
|
|
}
|
|
|
|
protected string MinuteToTime(int minute)
|
|
{
|
|
int hh = minute / 60;
|
|
int mm = minute % 60;
|
|
|
|
if (以時分顯示時數)
|
|
return String.Format("{0:##0}:{1:00}", hh, mm);
|
|
else
|
|
return hh.ToString();
|
|
}
|
|
|
|
public abstract MemoryStream 產生報表();
|
|
|
|
} |