GroupBy/04-程式設計/Trunk/GroupBuy/CounsellorBL/Helper/ExcelHelper.cs

///-----------------------------------------------------------------------
/// <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;
        }

    }
}