StaffPerformance/wispro.sp.utility/ExcelHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

namespace wispro.sp.utility
{
    public class ExcelHelper
    {

        //string[] temList = new string[] {"PACN1917256",
        //                                    "PAUS2010223",
        //                                    "PAUS1914936",
        //                                    "PACN1815861",
        //                                    "PACN1913445",
        //                                    "PACN1816576",
        //                                    "PACN2113860",
        //                                    "PACN1812755-结案",
        //                                    "PACN2015701",
        //                                    "PCTCN1815005",
        //                                    "PACN1913883-加快",
        //                                    "PACN1915086-加快",
        //                                    "PACN1810141",
        //                                    "PACN1815247",
        //                                    "PACN1812737",
        //                                    "PACN1813923",
        //                                    "PACN1912513",
        //                                    "PACN1912504",
        //                                    "PAEPO2112337",
        //                                    "PAUS2111949",
        //                                    "PATW2111951",
        //                                    "PAEPO2111950",
        //                                    "PAEPO2111788",
        //                                    "PAEPO2013035",
        //                                    "PAEPO1918035",
        //                                    "PAEPO1813034",
        //                                    "PAGB1711461" };

        public DataTable MerageExcel(string desFileName,string[] excelFiles)
        {
            DataTable retDt = null;
            foreach(string strFileName in excelFiles)
            {
                if (System.IO.File.Exists(strFileName))
                {
                    if (retDt == null)
                    {
                        retDt = NPOIExcel.ExcelToDataTable(strFileName, true,true);

                        DataTable temdt = new DataTable();
                        foreach(DataColumn col in retDt.Columns)
                        {
                            
                            DataColumn temCol = new DataColumn();
                            temCol.ColumnName = col.ColumnName;
                            temCol.DataType = col.DataType;
                            temCol.Caption = col.Caption;
                            
                            temdt.Columns.Add(temCol);
                        }

                        MerageDataTable(temdt, retDt);

                        retDt = temdt;
                    }
                    else
                    {
                        DataTable dt = NPOIExcel.ExcelToDataTable(strFileName, true, true);
                        MerageDataTable(retDt, dt);

                    }
                }
                else
                {
                    throw new ApplicationException($"文件不存在：{strFileName}");
                }
            }

            string strCalculatedFile = ConfigurationSettings.AppSettings["CalculatedFile"];// "21.01-21.06 工程师绩效报表-总表.xlsx";
            DataTable CalculatedTable = NPOIExcel.ExcelToDataTable(strCalculatedFile, true);

            #region  删除空行
            List<DataRow> delRows = new List<DataRow>();
            System.Collections.Hashtable temHash = new System.Collections.Hashtable();
            foreach (DataRow row in retDt.Rows)
            {
                if ((row[0] == null || row[0].ToString() == "") && (row[1] == null || row[1].ToString() == "") && (row[2] == null || row[2].ToString() == ""))
                {
                    delRows.Add(row);
                }
                else
                {
                    #region 添加我方案号和处理事项相同的项目到hashTable
                    string strKey = $"{row["我方文号"].ToString()}-{row["处理事项"].ToString()}";
                    if(temHash.Contains(strKey))
                    {
                        List<DataRow> List = (List<DataRow>)temHash[strKey];
                        List.Add(row);
                    }
                    else
                    {
                        List<DataRow> List = new List<DataRow>();
                        List.Add(row);
                        temHash.Add(strKey, List);
                    }
                    #endregion
                    #region 处理事项为技术确认直接删除
                    try
                    {
                        if (row["处理事项"].ToString().Trim() == "技术确认")
                        {
                            delRows.Add(row);
                        }
                    }
                    catch { }
                    #endregion
                    #region 已核算绩效标记
                    string strTem = isCalculatedData(row, CalculatedTable);

                    if(strTem != "")
                    {
                        if (!retDt.Columns.Contains("核查数据反馈"))
                        {
                            retDt.Columns.Add("核查数据反馈");
                        }

                        row["核查数据反馈"] = strTem;
                    }
                    #endregion
                    #region 处理君龙处理人
                    row["处理人"] = FormatStaffName(row["处理人"].ToString());
                    #endregion
                    #region 处理人和核稿人相同时，删除核稿人
                    if (row["处理人"].ToString() == row["核稿人"].ToString())
                    {
                        row["核稿人"] = "";
                    }
                    #endregion
                    #region 严重超期降系数处理,如果代理人没有填写客观原因才降系数，如果没有客户期限，期限为内部期限，如果没有内部期限，期限为委托日期+30天
                    try
                    {
                        if (row["处理事项"].ToString() == "新申请")
                        {
                            DateTime dt1 = DateTime.MinValue;
                            if (!string.IsNullOrEmpty(row["返稿日"].ToString()))
                            {
                                try
                                {
                                    dt1 = DateTime.Parse(row["返稿日"].ToString());
                                }
                                catch
                                {
                                    dt1 = DateTime.Parse("1899-12-30").AddDays(double.Parse(row["返稿日"].ToString()));
                                    row["返稿日"] = dt1;
                                }

                            }

                            DateTime dt2 = DateTime.MinValue;
                            if (!string.IsNullOrEmpty(row["客户期限"].ToString()))
                            {
                                try
                                {
                                    dt2 = DateTime.Parse(row["客户期限"].ToString());
                                }
                                catch
                                {
                                    dt2 = DateTime.Parse("1899-12-30").AddDays(double.Parse(row["客户期限"].ToString()));
                                    row["客户期限"] = dt2;
                                }

                            }
                            else
                            {
                                if (!string.IsNullOrEmpty(row["内部期限"].ToString()))
                                {

                                    try
                                    {
                                        dt2 = DateTime.Parse(row["内部期限"].ToString());
                                    }
                                    catch
                                    {
                                        dt2 = DateTime.Parse("1899-12-30").AddDays(double.Parse(row["内部期限"].ToString()));
                                        row["内部期限"] = dt2;
                                    }
                                }
                                else
                                {
                                    if (!string.IsNullOrEmpty(row["委案日期"].ToString()))
                                    {

                                        try
                                        {
                                            dt2 = DateTime.Parse(row["委案日期"].ToString()).AddDays(30);
                                        }
                                        catch
                                        {
                                            dt2 = DateTime.Parse("1899-12-30").AddDays(double.Parse(row["内部期限"].ToString())).AddDays(30);
                                            row["内部期限"] = dt2.AddDays(-30);
                                        }
                                    }
                                }
                            }

                            string strReason = row["备注（发文严重超期是否属客观原因，若为否，请填写原因）"].ToString();

                            if ((dt1 - dt2).TotalDays > 30 && !string.IsNullOrEmpty(strReason))
                            {
                                if (!retDt.Columns.Contains("是否降系数"))
                                {
                                    retDt.Columns.Add("是否降系数");
                                }

                                switch (row["案件系数"].ToString())
                                {
                                    case "S":
                                        row["案件系数"] = "A";
                                        row["是否降系数"] = $"严重超期降系数【超期：{(dt1 - dt2).TotalDays}】 S-A";
                                        break;
                                    case "A":
                                        row["案件系数"] = "B";
                                        row["是否降系数"] = $"严重超期降系数【超期：{(dt1 - dt2).TotalDays}】 A-B";
                                        break;
                                    case "B":
                                        row["案件系数"] = "C";
                                        row["是否降系数"] = $"严重超期降系数【超期：{(dt1 - dt2).TotalDays}】 B-C";
                                        break;
                                    case "C":
                                        row["案件系数"] = "D";
                                        row["是否降系数"] = $"严重超期降系数【超期：{(dt1 - dt2).TotalDays}】 C-D";
                                        break;
                                }

                            }
                        }
                    }
                    catch { }
                    #endregion 
                }
            }

            #region 删除我方文号和处理事项相同，比较少处理人的记录
            foreach (string strKey in temHash.Keys)
            {
                List<DataRow> rows = (List<DataRow>)temHash[strKey];
                if(rows.Count > 1)
                {
                    DataRow retRow = null;
                    List<string> clrList = null;
                    foreach(DataRow row in rows)
                    {
                        if (retRow == null)
                        {
                            retRow = row;
                            clrList = row["处理人"].ToString().Split(new char[] { ',', '，', '、' }).ToList<string>();
                        }
                        else
                        {
                            List<string> temClrList = row["处理人"].ToString().Split(new char[] { ',', '，', '、' }).ToList<string>();
                            if(temClrList.Count > clrList.Count)
                            {
                                delRows.Add(retRow);
                                retRow = row;
                                clrList = temClrList;
                            }
                            else
                            {
                                delRows.Add(row);
                            }
                        }
                    }
                }
            }
            #endregion

            foreach(DataRow row in delRows)
            {
                retDt.Rows.Remove(row);
            }
            #endregion
            CalculatedTable.Dispose();

            NPOIExcel.DataTableToExcel(retDt ,desFileName);
            return retDt;
        }

        public void MerageDataTable(DataTable retDt, DataTable dt)
        {
            int iRow = 0;
            foreach (DataRow row in dt.Rows)
            {
                iRow++;
                
                //判断是否有相同的记录，我方文号+处理事项+处理人相同
                DataRow oldRow = isExists(row, retDt);
                if (oldRow == null)
                {
                    DataRow newRow = retDt.NewRow();

                    foreach (DataColumn col in dt.Columns)
                    {
                        if (retDt.Columns.Contains(col.ColumnName))
                        {
                            newRow[col.ColumnName] = row[col.ColumnName];
                        }
                    }

                    retDt.Rows.Add(newRow);
                }
                else
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (retDt.Columns.Contains(col.ColumnName))
                        {
                            if (string.IsNullOrEmpty(oldRow[col.ColumnName].ToString())
                                && !string.IsNullOrEmpty(row[col.ColumnName].ToString()))
                            {
                                oldRow[col.ColumnName] = row[col.ColumnName];
                            }

                        }
                    }
                }
            }
        }

        /// <summary>
        /// 处理君龙处理人
        /// </summary>
        /// <param name="strName"></param>
        /// <returns></returns>
        private string FormatStaffName(string strName)
        {
            if (string.IsNullOrEmpty(strName))
            {
                return strName;
            }

            string strReturn = "";
            string[] Names = strName.Trim().Split(new char[] { ',' });

            foreach(string name in Names)
            {
                
                strReturn = string.IsNullOrEmpty(strReturn) ? name.Replace("-君龙", "") : $"{strReturn},{name.Replace("-君龙", "")}";
            }

            return strReturn;
        }

        private DataRow isExists(DataRow row,DataTable dt)
        {

            //DataRow[] temRows = dt.Select($"我方文号='{row["我方文号"].ToString()}' and 处理事项='{row["处理事项"].ToString()}' and 处理人='{row["处理人"].ToString()}'");
            //if (temRows.Length > 0)
            //{
            //    return temRows[0];
            //}
            //else
            //{
            //    return null;
            //}


            foreach (DataRow temRow in dt.Rows)
            {
                if(row["我方文号"].ToString() == temRow["我方文号"].ToString() &&
                    row["处理事项"].ToString() == temRow["处理事项"].ToString() &&
                    row["处理人"].ToString() == temRow["处理人"].ToString())
                {
                    System.Diagnostics.Debug.WriteLine(string.Format("{0} 重复项目", row["我方文号"].ToString()));

                    return temRow;

                    
                }
            }

            return null;
        }

        private string isCalculatedData(DataRow row ,DataTable CalculatedTable)
        {
            foreach (DataRow temRow in CalculatedTable.Rows)
            {
                if (row["我方文号"].ToString() == temRow["我方文号"].ToString() &&
                    row["处理事项"].ToString() == temRow["处理事项"].ToString() )
                {
                    if(row["处理事项"].ToString() == "处理审查意见")
                    {
                        if ( row["案件阶段"].ToString() == temRow["案件阶段"].ToString())
                            return temRow["绩效核算月份"].ToString() + "已核算绩效！";
                    }
                    else
                    {
                        return temRow["绩效核算月份"].ToString() + "已核算绩效！";
                    }
                    
                }
            }

            return "";
        }
        
        /// <summary>
        /// 填充点数
        /// </summary>
        /// <param name="strPath">总表文件路径</param>
        /// <param name="dianshuFilePath">点数规则文件路径</param>
        public void FillDianShu(string strPath,string dianshuFilePath)
        {
            DataTable dsTable = NPOIExcel.ExcelToDataTable(dianshuFilePath,true);

            //DataRow[] Rows = dsTable.Select("1=1", "转换条件");

            DataTable dt = NPOIExcel.ExcelToDataTable(strPath, true);

            for(int iRow = dsTable.Rows.Count-1;iRow>=0;iRow--)
            //foreach(DataRow row in dsTable.Rows)
            {
                
                DataRow row = dsTable.Rows[iRow];


                if (!string.IsNullOrEmpty(row["转换条件"].ToString().Trim()))
                {
                    try
                    {
                        DataRow[] temRows = dt.Select(row["转换条件"].ToString().Trim());

                        foreach (DataRow dataRow in temRows)
                        {
                            dataRow["基本点数"] = double.Parse(row["点数"].ToString());
                        }
                    }
                    catch(Exception ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex.ToString());
                    }
                }
            }
           

            #region 翻译按字数计算
            DataRow[] fyList = dt.Select("处理事项='翻译'");
            foreach (DataRow dataRow in fyList)
            {
                string strBZ = dataRow["备注（填表注意事项）"].ToString();
                System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex("(\\d{1,})字");
                System.Text.RegularExpressions.Match m = r.Match(strBZ);
                

                if (m.Success)
                {
                    double iChar = double.Parse(m.Groups[1].Value);

                    if (strBZ.Contains("中-英"))
                    {
                        double ds = (iChar*0.16)/1000.00;
                        dataRow["基本点数"] = ds.ToString();
                    }
                    else
                    {
                        if (strBZ.Contains("中-德") || strBZ.Contains("德-中"))
                        {
                            double ds = (iChar * 0.18) / 1000.00;
                            dataRow["基本点数"] = ds.ToString();
                        }
                        else
                        {
                            if (strBZ.Contains("英-中"))
                            {
                                double ds = (iChar * 0.1) / 1000.00;
                                dataRow["基本点数"] = ds.ToString();
                            }
                        }
                    }
                }
                else
                {

                }
            }
            #endregion

            #region 已算绩效，基本点数为0
            DataRow[] ysjxList = dt.Select("备注（填表注意事项）='已算绩效' or 备注（填表注意事项）='绩效已算' or 备注（填表注意事项）='已计算' or 备注（填表注意事项）='绩效已核算'  or 核查数据反馈 like '已核算绩效'");
            foreach (DataRow dataRow in ysjxList)
            {
                dataRow["基本点数"] = "0";
            }
            #endregion

            NPOIExcel.DataTableToExcel(dt, strPath);
        }

    }
}