public string ExciseRpt(List <ExciseFreeApply> data)
{
string templateFilePath = Path.Combine(HostingEnvironment.MapPath("~/ExcelTemplate"), "ExciseFreeApplyTemplate.xlsx");
// Open Template
FileStream fs = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);
// Load the template into a NPOI workbook
XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs);
// Load the sheet you are going to use as a template into NPOI
XSSFSheet sheet = (XSSFSheet)templateWorkbook.GetSheetAt(0);
// 建立新頁籤並命名Rpt1
templateWorkbook.CreateSheet("Rpt1");
XSSFSheet sheet2 = (XSSFSheet)templateWorkbook.GetSheetAt(1);
//寬度格式設定
int[] ColWidthSetArr = new int[] { 1, 13, 32, 4, 7,
9, 10, 1, 11, 1 };
SetSheetColumnWidth(ref sheet2, ColWidthSetArr);
//設定將所有欄放入單一頁面中
sheet2.FitToPage = true;
sheet2.PrintSetup.FitWidth = 1;
sheet2.PrintSetup.FitHeight = 0;
//設定列印格式為A4(A3=8,A4=9,Letter=1)
sheet2.PrintSetup.PaperSize = 9;
//基本參數
int originalSheetLastRow = sheet.LastRowNum;
int headerRowLen = 7;
int templateBodyRow = 7;
int footerRowStart = 14;
int footerRowLen = originalSheetLastRow - footerRowStart;
//欄位資訊
int newSheetRowLen = headerRowLen + data.Count + (sheet.LastRowNum - footerRowStart);
int newSheetPageNum = Convert.ToInt16(Math.Ceiling(newSheetRowLen / 44.0));
var tempdata = new ExciseFreeApply();
tempdata.ID = data.Count == 0 ? "" : data[0].ID;
tempdata.Name = data.Count == 0 ? "" : data[0].Name;
tempdata.Address = data.Count == 0 ? "" : data[0].Address;
//表頭
ReplaceRowCellValue(ref sheet, 5, 1, "#0", tempdata.ID);
//表尾
ReplaceRowCellValue(ref sheet, 17, 4, "#1", tempdata.Name);
ReplaceRowCellValue(ref sheet, 18, 4, "#2", tempdata.Address);
for (var i = 0; i < headerRowLen; i++)
{
CopySheetRow(ref sheet, ref sheet2, i, i, false, false, true, false);
}
int sheet2Last = sheet2.LastRowNum + 1;
for (var i = 0; i < data.Count; i++)
{
if (i == 0)
{
SetRowCellValue(ref sheet, templateBodyRow, 1, data[i].ProdTaxNumber);
SetRowCellValue(ref sheet, templateBodyRow, 2, data[i].ProdEngName + data[i].ProdChName);
SetRowCellValue(ref sheet, templateBodyRow, 3, data[i].TaxUnits);
SetRowCellValue(ref sheet, templateBodyRow, 4, (double)data[i].Qty);
SetRowCellValue(ref sheet, templateBodyRow, 5, ToSimpleTaiwanDate(data[i].ProcessDate));
SetRowCellValue(ref sheet, templateBodyRow, 6, data[i].SheetNumber);
SetRowCellValue(ref sheet, templateBodyRow, 7, data[i].Mode);
CopySheetRow(ref sheet, ref sheet2, templateBodyRow, sheet2Last + i, false, false, true, true);
}
else
{
SetRowCellValue(ref sheet, templateBodyRow + 1, 1, data[i].ProdTaxNumber);
SetRowCellValue(ref sheet, templateBodyRow + 1, 2, data[i].ProdEngName + data[i].ProdChName);
SetRowCellValue(ref sheet, templateBodyRow + 1, 3, data[i].TaxUnits);
SetRowCellValue(ref sheet, templateBodyRow + 1, 4, (double)data[i].Qty);
SetRowCellValue(ref sheet, templateBodyRow + 1, 5, ToSimpleTaiwanDate(data[i].ProcessDate));
SetRowCellValue(ref sheet, templateBodyRow + 1, 6, data[i].SheetNumber);
SetRowCellValue(ref sheet, templateBodyRow + 1, 7, data[i].Mode);
CopySheetRow(ref sheet, ref sheet2, templateBodyRow + 1, sheet2Last + i, false, false, false, false);
}
}
sheet2.CreateRow(sheet2.LastRowNum + 1);
sheet2.CopyRow(sheet2.LastRowNum - 1, sheet2.LastRowNum + 1);
sheet2Last = sheet2.LastRowNum + 1;
for (var i = 0; i < footerRowLen; i++)
{
CopySheetRow(ref sheet, ref sheet2, footerRowStart + i, sheet2Last + i, false, false, true, false);
}
//自動調整欄位寬度，此欄位為數量
sheet2.AutoSizeColumn(4);
//合併儲存格
if (data.Count > 0)
{
RemoveMergeCells(ref sheet2, headerRowLen);
sheet2.AddMergedRegion(new CellRangeAddress(headerRowLen, sheet2.LastRowNum - footerRowLen, 8, 8));
}
string filePath = @"C:\temp\excel";
//建folder
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
//新完整檔名
string newFileName = "export.xlsx";
//檔案路徑 + 新完整檔名
string fullFilePath = Path.Combine(filePath, newFileName);
FileStream file = new FileStream(fullFilePath, FileMode.Create);//產生檔案
templateWorkbook.Write(file);
file.Close();
GC.Collect();
return(fullFilePath);
}
