You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
364 lines
14 KiB
364 lines
14 KiB
///-----------------------------------------------------------------------
|
|
/// <copyright file="ExcelExtansion.cs" company="ChipMOS">
|
|
/// 程式代號: ExcelExtansion
|
|
/// 程式名稱: ExcelExtansion
|
|
/// 程式說明:
|
|
/// 起始作者: Rexxar.eng
|
|
/// 起始日期: 2017/03/13 16:55:08
|
|
/// 最新修改人: Hercules
|
|
/// 最新修日期: 2017/05/18 17:45:54
|
|
/// </copyright>
|
|
///-----------------------------------------------------------------------
|
|
#region 程式異動記錄
|
|
/// xx.YYYY/MM/DD VER AUTHOR COMMENTS(說明修改的內容)
|
|
/// 01.2017/03/13 1.000 Rexxar.eng Excel tools(RV.3120)
|
|
/// 02.2017/03/13 1.001 Rexxar.eng Excel tools(RV.3121)
|
|
/// 03.2017/03/13 1.002 Rexxar.eng Excel tools(RV.3126)
|
|
/// 04.2017/03/14 1.003 Hercules 調整Excel Export(RV.3129)
|
|
/// 05.2017/05/18 1.004 Hercules 補程式Header註解(RV.4375)
|
|
#endregion
|
|
|
|
using CounsellorBL.BLStructure;
|
|
using MonumentDefine;
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.SS.UserModel;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Globalization;
|
|
using System.IO;
|
|
using System.Linq;
|
|
|
|
namespace CounsellorBL.Helper
|
|
{
|
|
/// <summary>
|
|
/// 類別名稱:ExcelHelper
|
|
/// 類別說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
public static partial class ExcelHelper
|
|
{
|
|
/// <summary>
|
|
/// 函式名稱:ExportExcel
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_pInfo">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public static bool ExportExcel(ExcelHelperParameter i_pInfo)
|
|
{
|
|
//暫存於伺服器端提供下載檔案路徑
|
|
return ExportExcelWithMultSheet(new List<ExcelHelperParameter>() { i_pInfo });
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:ExportExcel
|
|
/// 函式說明:
|
|
/// 起始作者:
|
|
/// 起始日期:
|
|
/// 最新修改人:
|
|
/// 最新修改日:
|
|
/// </summary>
|
|
/// <param name="i_pInfo">
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public static bool ExportExcelWithMultSheet(List<ExcelHelperParameter> i_lpInfo)
|
|
{
|
|
bool success = false;
|
|
|
|
if (i_lpInfo != null)
|
|
{
|
|
|
|
|
|
string sPath = i_lpInfo[0].Path;
|
|
string sFileName = i_lpInfo[0].FileName;
|
|
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
|
|
foreach (ExcelHelperParameter i_pInfo in i_lpInfo)
|
|
{
|
|
//Excel創建工作表
|
|
ISheet sheet = workbook.CreateSheet((string.IsNullOrEmpty(i_pInfo.SheetName)) ? i_pInfo.FileName : i_pInfo.SheetName);
|
|
|
|
int iRowIndex = 0;
|
|
|
|
IRow row = sheet.CreateRow(iRowIndex++);
|
|
|
|
|
|
QueryResponse qrRes = new QueryResponse(i_pInfo.QueryData);
|
|
List<Dictionary<string, object>> data = qrRes.Records;
|
|
List<string> lColumnOrder = new List<string>();
|
|
|
|
int iLen = i_pInfo.HeaderMappingData.Count;
|
|
for (int iIdx = 0; iIdx < iLen; iIdx++)
|
|
{
|
|
Dictionary<string, object> dic = i_pInfo.HeaderMappingData[iIdx];
|
|
lColumnOrder.Add(dic[BLWording.NAME].ToString());
|
|
row.CreateCell(iIdx).SetCellValue(dic[BLWording.DISPLAY].ToString());
|
|
}
|
|
|
|
int iColumnLen = lColumnOrder.Count;
|
|
int iDataLen = data.Count;
|
|
for (int iDataIdx = 0; iDataIdx < iDataLen; iDataIdx++)
|
|
{
|
|
row = sheet.CreateRow(iRowIndex++);
|
|
Dictionary<string, object> dic = data[iDataIdx];
|
|
for (int iColumnIdx = 0; iColumnIdx < iColumnLen; iColumnIdx++)
|
|
{
|
|
|
|
object oData = !i_pInfo.TraslateMap.ContainsKey(lColumnOrder[iColumnIdx]) ? dic[lColumnOrder[iColumnIdx]] : i_pInfo.TraslateMap[lColumnOrder[iColumnIdx]](dic);
|
|
|
|
if (oData is bool)
|
|
{
|
|
|
|
row.CreateCell(iColumnIdx).SetCellValue(Convert.ToBoolean(oData, CultureInfo.CurrentCulture));
|
|
}
|
|
else if (oData is DateTime)
|
|
{
|
|
row.CreateCell(iColumnIdx).SetCellValue(Convert.ToDateTime(oData, CultureInfo.CurrentCulture));
|
|
}
|
|
else if (TypeCheckerHelper.IsNumeric(oData))
|
|
{
|
|
row.CreateCell(iColumnIdx).SetCellValue(Convert.ToDouble(oData, CultureInfo.CurrentCulture));
|
|
}
|
|
else
|
|
{
|
|
if (oData != null)
|
|
{
|
|
row.CreateCell(iColumnIdx).SetCellValue(oData.ToString());
|
|
}
|
|
else
|
|
{
|
|
row.CreateCell(iColumnIdx);
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
int count = iDataLen + 1;
|
|
for (int j = 0; j < count; j++)
|
|
{
|
|
sheet.AutoSizeColumn(j);
|
|
}
|
|
}
|
|
|
|
|
|
var path = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, sPath);
|
|
var fileName = System.IO.Path.Combine(path, sFileName);
|
|
|
|
|
|
if (!Directory.Exists(path))
|
|
{
|
|
//如果匯出暫存路徑不存在，建立它。
|
|
Directory.CreateDirectory(path);
|
|
}
|
|
|
|
|
|
using (MemoryStream ms = new MemoryStream())
|
|
{
|
|
using FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
|
|
workbook.Write(fs);
|
|
}
|
|
|
|
success = true;
|
|
}
|
|
|
|
//暫存於伺服器端提供下載檔案路徑
|
|
return success;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 函式名稱:GetDataTableFromExcelFile
|
|
/// 函式說明:匯入
|
|
/// 起始作者:Rexxar
|
|
/// 起始日期:2017/01/05
|
|
/// 最新修改人: Justin
|
|
/// 最新修改日: 2017/01/05
|
|
/// </summary>
|
|
/// <param name="fileName"></param>
|
|
/// 參數說明
|
|
/// </param>
|
|
/// <returns>
|
|
/// 回傳
|
|
/// </returns>
|
|
public static DataTable GetDataTableFromExcelFile(string fileName, string i_sSheetName = null)
|
|
{
|
|
FileStream fs = null;
|
|
DataTable dt = new DataTable();
|
|
try
|
|
{
|
|
IWorkbook wb = null;
|
|
fs = File.Open(fileName, FileMode.Open, FileAccess.Read);
|
|
switch (Path.GetExtension(fileName).ToUpper(CultureInfo.CurrentCulture))
|
|
{
|
|
case ".XLS":
|
|
{
|
|
wb = new HSSFWorkbook(fs);
|
|
}
|
|
break;
|
|
case ".XLSX":
|
|
{
|
|
wb = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
|
|
}
|
|
break;
|
|
}
|
|
if (wb?.NumberOfSheets > 0)
|
|
{
|
|
ISheet sheet = (string.IsNullOrEmpty(i_sSheetName)) ? wb.GetSheetAt(0) : wb.GetSheet(i_sSheetName);
|
|
if (sheet == null)
|
|
{
|
|
sheet = wb.GetSheetAt(0);
|
|
}
|
|
IRow headerRow = sheet.GetRow(0);
|
|
|
|
//處理標題列
|
|
for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
|
|
{
|
|
dt.Columns.Add(headerRow.GetCell(i).StringCellValue.Trim());
|
|
}
|
|
IRow row = null;
|
|
DataRow dr = null;
|
|
CellType ct;
|
|
//標題列之後的資料
|
|
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
|
|
{
|
|
dr = dt.NewRow();
|
|
row = sheet.GetRow(i);
|
|
if (row == null) continue;
|
|
for (int j = row.FirstCellNum; j < row.LastCellNum && j < headerRow.LastCellNum; j++)
|
|
{
|
|
ICell ic = row.GetCell(j);
|
|
|
|
if (ic == null)
|
|
{
|
|
dr[j] = DBNull.Value;
|
|
}
|
|
else
|
|
{
|
|
|
|
ct = ic.CellType;
|
|
//如果此欄位格式為公式 則去取得CachedFormulaResultType
|
|
if (ct == CellType.Formula)
|
|
{
|
|
|
|
CellType ctFormulaResulat = row.GetCell(j).CachedFormulaResultType;
|
|
dr[j] = ctFormulaResulat switch
|
|
{
|
|
CellType.String => ic.StringCellValue,
|
|
_ => throw new NotImplementedException($"No handle row.GetCell(j).CachedFormulaResultType = {ctFormulaResulat}"),
|
|
};
|
|
}
|
|
else if (ct == CellType.Numeric)
|
|
{
|
|
|
|
if (DateUtil.IsCellInternalDateFormatted(row.GetCell(j)) || DateUtil.IsCellDateFormatted(row.GetCell(j)))
|
|
{
|
|
dr[j] = row.GetCell(j).DateCellValue;
|
|
}
|
|
else
|
|
{
|
|
dr[j] = row.GetCell(j).NumericCellValue;
|
|
}
|
|
|
|
}
|
|
else if (ct == CellType.String)
|
|
{
|
|
dr[j] = row.GetCell(j).ToString();
|
|
}
|
|
else
|
|
{
|
|
dr[j] = row.GetCell(j).ToString().Replace("$", "", StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
}
|
|
}
|
|
dt.Rows.Add(dr);
|
|
}
|
|
}
|
|
fs.Close();
|
|
}
|
|
finally
|
|
{
|
|
if (fs != null) fs.Dispose();
|
|
}
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Adds the new datarow.
|
|
/// </summary>
|
|
/// <param name="dt1">The DT1.</param>
|
|
/// <param name="dt2">The DT2.</param>
|
|
/// <param name="i_iec">The i iec.</param>
|
|
/// <returns></returns>
|
|
public static IEnumerable<DataRow> AddNewRow(DataTable dt1, DataTable dt2, IEqualityComparer<DataRow> i_iec)
|
|
{
|
|
return dt1.AsEnumerable().Except(dt2.AsEnumerable(), i_iec);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Adds or modify datarow.
|
|
/// </summary>
|
|
/// <param name="i_dtOld">The i dt old.</param>
|
|
/// <param name="i_dtNew">The i dt new.</param>
|
|
/// <param name="o_ldrAdd">The o LDR add.</param>
|
|
/// <param name="o_ldrModify">The o LDR modify.</param>
|
|
public static void AddOrModifyRow(DataTable i_dtOld, DataTable i_dtNew, out List<DataRow> o_ldrAdd, out List<DataRow> o_ldrModify)
|
|
{
|
|
List<DataRow> ldrResModify = new List<DataRow>();
|
|
List<DataRow> ldrResAdd = new List<DataRow>();
|
|
IEnumerable<DataRow> ieOld = i_dtOld.AsEnumerable();
|
|
IEnumerable<DataRow> ieNew = i_dtNew.AsEnumerable();
|
|
|
|
foreach (DataRow drNew in ieNew)
|
|
{
|
|
DataRow drFind = ieOld.FirstOrDefault(f => f[BLWording.UID] != null
|
|
&& drNew[BLWording.UID] != null
|
|
&& f[BLWording.UID].ToString() == drNew[BLWording.UID].ToString());
|
|
if (drFind != null) // Modify
|
|
{
|
|
foreach (DataColumn dc in drNew.Table.Columns)
|
|
{
|
|
if (dc.ColumnName == BLWording.UID)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (drNew[dc.ColumnName] == null && drFind[dc.ColumnName] != null)
|
|
{
|
|
ldrResModify.Add(drFind);
|
|
break;
|
|
}
|
|
else if (drNew[dc.ColumnName].ToString() != drFind[dc.ColumnName].ToString())
|
|
{
|
|
ldrResModify.Add(drNew);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
ldrResAdd.Add(drNew);
|
|
}
|
|
}
|
|
|
|
o_ldrAdd = ldrResAdd;
|
|
o_ldrModify = ldrResModify;
|
|
}
|
|
|
|
}
|
|
}
|