226 lines
12 KiB
C#
226 lines
12 KiB
C#
using NPOI.SS.UserModel;
|
|
using NPOI.SS.Util;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Web;
|
|
|
|
namespace Education.Report
|
|
{
|
|
public class 領據匯出 : ReportBase
|
|
{
|
|
public 領據List data;
|
|
public bool 範本 = false;
|
|
|
|
public List<ExportColumn> Columns = new List<ExportColumn>() {
|
|
new ExportColumn() { Key="活動日期" , Title="活動日期" , Width=14 , FieldName="活動日期", Format="{0:yyyy-MM-dd}" },
|
|
new ExportColumn() { Key="身分別" , Title="身分別" , Width=12 , FieldName="身分別", Comment="講師\n非講師" },
|
|
new ExportColumn() { Key="領款人姓名" , Title="領款人姓名" , Width=12 , FieldName="領款人姓名" },
|
|
new ExportColumn() { Key="身分證字號" , Title="身分證字號" , Width=16 , FieldName="身分證字號" },
|
|
new ExportColumn() { Key="憑證類別" , Title="憑證類別" , Width=10 , FieldName="憑證類別" , Comment="領據\n發票\n收據"},
|
|
new ExportColumn() { Key="講師評價" , Title="講師評價" , Width=10 , FieldName="講師評價" },
|
|
new ExportColumn() { Key="計畫代號" , Title="計畫代號" , Width=14 , FieldName="計畫代號" },
|
|
new ExportColumn() { Key="活動地點" , Title="活動地點" , Width=30 , FieldName="活動地點" },
|
|
new ExportColumn() { Key="活動類別" , Title="活動類別" , Width=12 , FieldName="活動類別" },
|
|
new ExportColumn() { Key="付款方式" , Title="付款方式" , Width=12 , FieldName="付款方式" , Comment="匯款\n支票\n現金"},
|
|
new ExportColumn() { Key="活動名稱/事由" , Title="活動名稱/事由" , Width=40 , FieldName="活動名稱事由" },
|
|
new ExportColumn() { Key="領據作廢" , Title="領據作廢" , Width=10 , FieldName="作廢", Align = HorizontalAlignment.Center },
|
|
new ExportColumn() { Key="勞務費領據編號" , Title="勞務費領據編號" , Width=16 , FieldName="勞務費_編號" },
|
|
new ExportColumn() { Key="勞務費費用別" , Title="勞務費費用別" , Width=12 , FieldName="勞務費_費用別" , Comment="講師費\n諮詢費\n工作人員費\n勞務費\n主持人費\n出席費\n演講費\n撰稿費"},
|
|
new ExportColumn() { Key="扣繳類別" , Title="扣繳類別" , Width=12 , FieldName="勞務費_扣繳類別名稱" , Comment="50 薪資\n9A55 執行業務所得-心理師\n9A70 執行業務所得-表演人\n9A90 執行業務所得-其他\n9A92 執行業務所得-程式設計師\n9B98A 執行業務所得-稿費\n9B98B 執行業務所得-演講之鐘點費"},
|
|
new ExportColumn() { Key="單價" , Title="單價" , Width=12 , FieldName="勞務費_單價", MoneyFormat=true },
|
|
new ExportColumn() { Key="數量" , Title="數量" , Width=12 , FieldName="勞務費_數量", MoneyFormat=true, MondyDigits=2 },
|
|
new ExportColumn() { Key="單位" , Title="單位" , Width=12 , FieldName="勞務費_單位" },
|
|
new ExportColumn() { Key="勞務費合計" , Title="勞務費合計" , Width=12 , FieldName="勞務費_合計金額", MoneyFormat=true },
|
|
new ExportColumn() { Key="應扣繳稅額" , Title="應扣繳稅額" , Width=12 , FieldName="勞務費_應扣繳稅額", MoneyFormat=true },
|
|
new ExportColumn() { Key="服務費" , Title="服務費" , Width=12 , FieldName="勞務費_服務費", MoneyFormat=true },
|
|
new ExportColumn() { Key="勞務費作廢" , Title="勞務費作廢" , Width=10 , FieldName="勞務費_作廢", Align = HorizontalAlignment.Center },
|
|
new ExportColumn() { Key="差旅費領據編號" , Title="差旅費領據編號" , Width=16 , FieldName="差旅費_編號" },
|
|
new ExportColumn() { Key="差旅費費用別" , Title="差旅費費用別" , Width=12 , FieldName="差旅費_費用別" },
|
|
new ExportColumn() { Key="差旅票價資訊" , Title="差旅票價資訊" , Width=20 , FieldName="差旅費_票價資訊" },
|
|
new ExportColumn() { Key="起點" , Title="起點" , Width=12 , FieldName="差旅費_起點" },
|
|
new ExportColumn() { Key="訖點" , Title="訖點" , Width=12 , FieldName="差旅費_訖點" },
|
|
new ExportColumn() { Key="捷運公車" , Title="捷運/公車" , Width=12 , FieldName="差旅費_捷運公車", MoneyFormat=true },
|
|
new ExportColumn() { Key="火車" , Title="火車" , Width=12 , FieldName="差旅費_火車", MoneyFormat=true },
|
|
new ExportColumn() { Key="計程車" , Title="計程車" , Width=12 , FieldName="差旅費_計程車", MoneyFormat=true },
|
|
new ExportColumn() { Key="高鐵飛機" , Title="高鐵/飛機" , Width=12 , FieldName="差旅費_高鐵飛機", MoneyFormat=true },
|
|
new ExportColumn() { Key="自行開車" , Title="自行開車" , Width=12 , FieldName="差旅費_自行開車", MoneyFormat=true },
|
|
new ExportColumn() { Key="過路費停車費" , Title="過路費/停車費" , Width=12 , FieldName="差旅費_過路費停車費", MoneyFormat=true },
|
|
new ExportColumn() { Key="住宿費" , Title="住宿費" , Width=12 , FieldName="差旅費_住宿費", MoneyFormat=true },
|
|
new ExportColumn() { Key="差旅費合計" , Title="差旅費合計" , Width=12 , FieldName="差旅費_合計", MoneyFormat=true },
|
|
new ExportColumn() { Key="差旅費作廢" , Title="差旅費作廢" , Width=10 , FieldName="差旅費_作廢", Align = HorizontalAlignment.Center },
|
|
};
|
|
|
|
public MemoryStream DoReport()
|
|
{
|
|
workbook.RemoveSheetAt(0);
|
|
|
|
sheet = workbook.CreateSheet(範本 ? "領據匯入範本" : "領據匯出");
|
|
SetPaperAndMargin(sheet);
|
|
sheet.RepeatingRows = new CellRangeAddress(0, 2, -1, -1);
|
|
|
|
excel.Sheet = sheet;
|
|
|
|
if (範本)
|
|
{
|
|
Columns.RemoveAll(x => x.Key == "勞務費領據編號");
|
|
Columns.RemoveAll(x => x.Key == "差旅費領據編號");
|
|
Columns.RemoveAll(x => x.Key == "勞務費合計");
|
|
Columns.RemoveAll(x => x.Key == "應扣繳稅額");
|
|
Columns.RemoveAll(x => x.Key == "差旅費費用別");
|
|
Columns.RemoveAll(x => x.Key == "差旅費合計");
|
|
Columns.RemoveAll(x => x.Key == "講師評價");
|
|
Columns.RemoveAll(x => x.Key == "領據作廢");
|
|
Columns.RemoveAll(x => x.Key == "勞務費作廢");
|
|
Columns.RemoveAll(x => x.Key == "差旅費作廢");
|
|
}
|
|
|
|
// 報表抬頭
|
|
int col = 0;
|
|
int colCount = 0;
|
|
foreach (var column in Columns)
|
|
{
|
|
excel.SetCellValue(0, col, column.Title, bodyStyleCenter);
|
|
excel.SetColumnWidthInChars(col, column.Width);
|
|
if (!string.IsNullOrEmpty(column.Comment))
|
|
{
|
|
excel.SetCellComment(0, col, column.Comment);
|
|
}
|
|
col++;
|
|
colCount++;
|
|
}
|
|
|
|
if (!範本)
|
|
{
|
|
int rowId = 1;
|
|
foreach (var r in data)
|
|
{
|
|
// 報表內容
|
|
col = 0;
|
|
|
|
foreach (var column in Columns)
|
|
{
|
|
var cellStyle = bodyStyleLeft;
|
|
switch (column.Align)
|
|
{
|
|
case HorizontalAlignment.Center:
|
|
cellStyle = bodyStyleCenter;
|
|
break;
|
|
case HorizontalAlignment.Right:
|
|
cellStyle = bodyStyleRight;
|
|
break;
|
|
}
|
|
|
|
var value = GetColumnValue(r, column);
|
|
SetDataValue(rowId, col, column, value, cellStyle);
|
|
col++;
|
|
}
|
|
|
|
rowId++;
|
|
}
|
|
|
|
// 報表尾
|
|
excel.SetCellValue(rowId + 1, 0, "總筆數", bodyStyleCenter);
|
|
excel.SetCellValue(rowId + 1, 1, data.Count, bodyStyleCenter);
|
|
}
|
|
else
|
|
{
|
|
// 範本要做另一個工作表，內容是資料填寫範例
|
|
sheet = workbook.CreateSheet("填寫範例 (本工作表不會匯入)");
|
|
SetPaperAndMargin(sheet);
|
|
sheet.RepeatingRows = new CellRangeAddress(0, 2, -1, -1);
|
|
excel.Sheet = sheet;
|
|
|
|
// 報表抬頭
|
|
col = 0;
|
|
colCount = 0;
|
|
foreach (var column in Columns)
|
|
{
|
|
excel.SetCellValue(0, col, column.Title, bodyStyleCenter);
|
|
excel.SetColumnWidthInChars(col, column.Width);
|
|
col++;
|
|
colCount++;
|
|
}
|
|
|
|
var demoData = new 領據List();
|
|
demoData.Add(new 領據Item()
|
|
{
|
|
活動日期 = new DateTime(2026, 6, 1),
|
|
身分別 = "非講師",
|
|
領款人姓名 = "貓老大",
|
|
身分證字號 = "A123456789",
|
|
憑證類別 = "領據",
|
|
計畫代號 = "115AG115001",
|
|
活動地點 = "新譽總公司",
|
|
活動類別 = "課程",
|
|
付款方式 = "匯款",
|
|
活動名稱事由 = "領據系統說明會",
|
|
勞務費_費用別 = "講師費",
|
|
勞務費_扣繳類別 = "9B98B 執行業務所得-演講之鐘點費",
|
|
勞務費_單價 = 3600,
|
|
勞務費_數量 = 1,
|
|
勞務費_單位 = "時",
|
|
勞務費_服務費 = 0,
|
|
差旅費_票價資訊 = "高雄=桃園",
|
|
差旅費_起點 = "高雄",
|
|
差旅費_訖點 = "桃園",
|
|
差旅費_捷運公車 = 0,
|
|
差旅費_火車 = 0,
|
|
差旅費_計程車 = 0,
|
|
差旅費_高鐵飛機 = 1290,
|
|
差旅費_自行開車 = 0,
|
|
差旅費_過路費停車費 = 0,
|
|
差旅費_住宿費 = 0
|
|
});
|
|
|
|
int rowId = 1;
|
|
foreach (var r in demoData)
|
|
{
|
|
// 報表內容
|
|
col = 0;
|
|
|
|
foreach (var column in Columns)
|
|
{
|
|
var cellStyle = bodyStyleLeft;
|
|
switch (column.Align)
|
|
{
|
|
case HorizontalAlignment.Center:
|
|
cellStyle = bodyStyleCenter;
|
|
break;
|
|
case HorizontalAlignment.Right:
|
|
cellStyle = bodyStyleRight;
|
|
break;
|
|
}
|
|
|
|
var value = GetColumnValue(r, column);
|
|
SetDataValue(rowId, col, column, value, cellStyle);
|
|
col++;
|
|
}
|
|
|
|
rowId++;
|
|
}
|
|
}
|
|
|
|
MemoryStream ms = new MemoryStream();
|
|
workbook.Write(ms);
|
|
return ms;
|
|
}
|
|
|
|
object GetColumnValue(領據Item r, ExportColumn column)
|
|
{
|
|
switch (column.Key)
|
|
{
|
|
case "領據作廢":
|
|
return r.作廢.GetValueOrDefault() == 1 ? "V" : string.Empty;
|
|
case "勞務費作廢":
|
|
return r.勞務費_作廢.GetValueOrDefault() == 1 ? "V" : string.Empty;
|
|
case "差旅費作廢":
|
|
return r.差旅費_作廢.GetValueOrDefault() == 1 ? "V" : string.Empty;
|
|
default:
|
|
return r.FieldValue(column.FieldName);
|
|
}
|
|
}
|
|
}
|
|
}
|