using NPOI.SS.UserModel;
using Performance.DtoModels;
using Performance.EntityModels;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Performance.Services.ExtractExcelService
{
    public class WriteDataHelper
    {
        public enum UnitType
        {
            护理组,
            医生组,
            医技组
        }

        public static void WriteSheetHeader(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<ExcelHeader> headers)
        {
            if (headers == null || !headers.Any()) return;

            var columns = headers.Select(t => t).ToList();

            // 收入支出系数
            var nurseFactor = sheet.GetOrCreate(point.AccountingUnit.FirstOrDefault(t => t.UnitType == UnitType.护理组.ToString())?.FactorRow.Value ?? 0);
            var doctorFactor = sheet.GetOrCreate(point.AccountingUnit.FirstOrDefault(t => t.UnitType == UnitType.医生组.ToString())?.FactorRow.Value ?? 0);
            var technicianFactor = sheet.GetOrCreate(point.AccountingUnit.FirstOrDefault(t => t.UnitType == UnitType.医技组.ToString())?.FactorRow.Value ?? 0);
            // 费用类型
            var columnHeader = sheet.GetOrCreate(point.HeaderFirstRowNum.Value);
            // 工作量系数
            var workloadFactor = sheet.GetOrCreate(point.HeaderFirstRowNum.Value + 1);

            var factorStyle = new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType)
                ? style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.系数, CellFormat.数字)
                : style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.系数, CellFormat.百分比);
            var columnStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.列头);

            // 去除excel中已存在的列
            int headerFirstCellNum = point.DataFirstCellNum.Value;
            if (columnHeader.LastCellNum > point.DataFirstCellNum.Value)
            {
                var rows = new IRow[] { nurseFactor, doctorFactor, technicianFactor, columnHeader };
                for (int index = point.DataFirstCellNum.Value; index < columnHeader.LastCellNum; index++)
                {
                    var category = columnHeader.GetCell(index).GetDecodeEscapes();
                    if (string.IsNullOrEmpty(category)) continue;

                    if (sheetType != SheetType.Income)
                    {
                        if (index > headerFirstCellNum) headerFirstCellNum = index;
                        columns.RemoveAll(t => t.ColumnName.NoBlank() == category);
                    }
                    else
                    {
                        foreach (var item in rows)
                        {
                            var cell = item.GetCell(index);
                            if (cell != null)
                                item.RemoveCell(cell);
                        }
                        var column = columns.FirstOrDefault(w => w.ColumnName.NoBlank() == category);
                        if (column != null) column.Sort = index;
                    }
                }
                if (headerFirstCellNum > point.DataFirstCellNum.Value)
                    headerFirstCellNum += 1;
            }

            if (columns == null || !columns.Any()) return;

            // 补充excel中不存在的列
            foreach (var item in columns.OrderBy(t => t.Sort).ThenBy(t => t.ColumnName))
            {
                var columnCell = columnHeader.GetOrCreate(headerFirstCellNum);
                columnCell.SetCellValue(item.ColumnName);
                columnCell.CellStyle = columnStyle;

                if (new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType))
                {
                    var workloadCell = workloadFactor.GetOrCreate(headerFirstCellNum);
                    workloadCell.SetCellValue<decimal>(item.WorkloadFactor);
                    workloadCell.CellStyle = factorStyle;
                }
                else
                {
                    WriteFactor(doctorFactor, headerFirstCellNum, item?.DoctorFactor, factorStyle);

                    WriteFactor(nurseFactor, headerFirstCellNum, item?.NurseFactor, factorStyle);

                    WriteFactor(technicianFactor, headerFirstCellNum, item?.TechnicianFactor, factorStyle);
                }

                headerFirstCellNum++;
            }
        }

        private static void WriteFactor(IRow row, int cellIndex, decimal? value, ICellStyle style)
        {
            var cell = row.GetCell(cellIndex);
            if (cell != null)
            {
                row.RemoveCell(cell);
            }
            cell = row.CreateCell(cellIndex);

            if (value.HasValue)
            {
                cell.SetCellValue<decimal>(value);
            }
            else
            {
                cell.SetCellValue<string>("#VALUE!");
            }
            cell.CellStyle = style;
        }

        public static void ClearSheetFormula(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<string> headers, List<ExtractTransDto> data)
        {
            headers = headers.Select(t => t.NoBlank()).ToList();
            int dataFirstCellNum = point.DataFirstCellNum.Value;
            var columnHeader = sheet.GetOrCreate(point.HeaderFirstRowNum.Value);

            for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
            {
                for (int rowIndex = point.DataFirstRowNum.Value; rowIndex < sheet.LastRowNum + 1; rowIndex++)
                {
                    var row = sheet.GetRow(rowIndex);
                    if (row == null) continue;

                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    if (headers.Any(w => w == column))
                    {
                        row.GetOrRecreate(cellIndex);
                    }
                }
            }
        }

        #region ExtractData

        public static void WriteSheetData(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<string> headers, List<ExtractTransDto> data)
        {
            var columnHeader = sheet.GetOrCreate(point.HeaderFirstRowNum.Value);
            int dataFirstRowNum = point.DataFirstRowNum.Value;

            List<IncomeRow> rows = new List<IncomeRow>();

            WriteSheetDataExistent(sheet, columnHeader, point, sheetType, style, headers, data, rows, ref dataFirstRowNum);

            if (sheetType == SheetType.Income && rows != null)
            {
                dataFirstRowNum = point.DataFirstRowNum.Value;
                RemoveIncomeRow(sheet, point);
                var endRowNum = WriteSheetIncomeData(sheet, columnHeader, point, style, headers, data, rows, dataFirstRowNum);
                dataFirstRowNum = endRowNum;
            }

            if (data == null || !data.Any()) return;

            WriteSheetDataNonexistent(sheet, columnHeader, point, sheetType, style, headers, data, dataFirstRowNum);
        }

        private static void WriteSheetDataExistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<ExtractTransDto> data, List<IncomeRow> incomes, ref int dataFirstRowNum)
        {
            if (sheet.LastRowNum <= dataFirstRowNum) return;

            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            for (int rowIndex = point.DataFirstRowNum.Value; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                string department = row.GetOrCreate(dataFirstCellNum - 1).GetDecodeEscapes();
                if (string.IsNullOrEmpty(department)) continue;
                if (rowIndex >= dataFirstRowNum) dataFirstRowNum = rowIndex + 1;

                var deptData = data.Where(t => t.Department.NoBlank() == department);
                if (deptData == null || !deptData.Any(t => t.Value.HasValue && t.Value != 0)) continue;

                #region 写入数据

                if (sheetType == SheetType.Income)
                {
                    if (!incomes.Any(t => t.Department == department))
                        incomes.Add(GetIncomeRowMessage(row, dataFirstCellNum, department, rowIndex));
                    continue;
                }

                for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    var cell = row.GetOrCreate(cellIndex);

                    if (string.IsNullOrEmpty(column)) continue;
                    var value = deptData.FirstOrDefault(t => t.Category.NoBlank() == column)?.Value;
                    //数据为空，且单元格值不为空，不写入数据（保留原始值）
                    // 22.3.29 ry 只要是提取的列头全部覆盖数据
                    if (headers != null && headers.Contains(column) && value.HasValue && value != 0)
                    {
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                }

                #endregion

                data.RemoveAll(t => t.Department == department);

                //if (point.DataFirstRowNum.Value < dataFirstRowNum) dataFirstRowNum += 1;
            }
        }

        private static void WriteSheetDataNonexistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<ExtractTransDto> data, int dataFirstRowNum)
        {
            var departments = data.Select(s => s.Department ?? "")/*.Where(w => !string.IsNullOrEmpty(w))*/.Distinct().ToList();

            var filed = fieldOut;
            if (sheetType == SheetType.Income)
            {
                if (sheet.SheetName.Contains("住院") || sheet.SheetName.Contains("门诊"))
                    filed = sheet.SheetName.Contains("住院") ? fieldInpat : fieldOut;

                var ename = data.Where(w => w.SheetName == sheet.SheetName)?.FirstOrDefault(w => !string.IsNullOrEmpty(w.EName))?.EName;
                if (!string.IsNullOrEmpty(ename) && (ename.Contains("住院") || ename.Contains("门诊")))
                    filed = ename.Contains("住院") ? fieldInpatOut : fieldOutInpat;
            }
            else if (sheet.SheetName.Contains("工作量"))
            {
                filed = sheet.SheetName.Contains("医生") ? fieldDoctor : fieldNurse;
            }

            //var deptStyle = style.GetCellStyle();
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            foreach (string department in departments)
            {
                var deptData = data.Where(t => (t.Department ?? "") == department);
                if (deptData != null && deptData.Any())
                {
                    var row = sheet.GetOrCreate(dataFirstRowNum);
                    for (int cellIndex = point.HeaderFirstCellNum.Value; cellIndex < columnHeader.LastCellNum; cellIndex++)

                    {
                        var column = columnHeader.GetCell(cellIndex).GetDecodeEscapes();
                        List<string> uts = new List<string> { "核算单元(医技组)", "核算单元(医生组)", "核算单元(护理组)" };
                        if (uts.Contains(column))
                            column = column?.Replace("(", "（").Replace(")", "）");

                        var cell = row.GetOrCreate(cellIndex);

                        if (filed.ContainsKey(column))
                        {
                            var value = filed[column]?.Invoke(deptData.First());

                            cell.SetCellOValue(value);
                            cell.CellStyle = cellStyle;
                        }
                        else if (sheetType == SheetType.Income || (headers != null && headers.Contains(column)))
                        {
                            var value = deptData.FirstOrDefault(t => t.Category.NoBlank() == column)?.Value;
                            cell.SetCellValue<decimal>(value);
                            cell.CellStyle = cellStyle;
                        }
                    }
                    dataFirstRowNum++;
                }
            }
        }

        private static int WriteSheetIncomeData(ISheet sheet, IRow columnHeader, PerSheetPoint point, ExcelStyle style, List<string> headers,
            List<ExtractTransDto> data, List<IncomeRow> incomes, int dataFirstRowNum)
        {
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);
            //var deptStyle = style.GetCellStyle();

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            foreach (var item in incomes)
            {
                var row = sheet.GetOrCreate(dataFirstRowNum);

                var deptData = data.Where(t => t.Department == item.Department);
                if (deptData != null && deptData.Any())
                {
                    var deptContents = new Dictionary<int, string>
                    {
                        { 1, item.Department },
                        { 2, (sheet.SheetName.Contains("门诊")
                            ? deptData.FirstOrDefault(t => !string.IsNullOrEmpty(t.OutNurseAccounting))?.OutNurseAccounting
                            : deptData.FirstOrDefault(t => !string.IsNullOrEmpty(t.InpatNurseAccounting))?.InpatNurseAccounting) ?? item.NurseAccount },
                        { 3, (sheet.SheetName.Contains("门诊")
                            ? deptData.FirstOrDefault(t => !string.IsNullOrEmpty(t.OutDoctorAccounting))?.OutDoctorAccounting
                            : deptData.FirstOrDefault(t => !string.IsNullOrEmpty(t.InpatDoctorAccounting))?.InpatDoctorAccounting) ?? item.DoctorAccount },
                        { 4, (sheet.SheetName.Contains("门诊")
                            ? deptData.FirstOrDefault(t => !string.IsNullOrEmpty(t.OutTechnicAccounting))?.OutTechnicAccounting
                            : deptData.FirstOrDefault(t => !string.IsNullOrEmpty(t.InpatTechnicAccounting))?.InpatTechnicAccounting) ?? item.TechnicAccounting },
                    };

                    foreach (var content in deptContents)
                    {
                        var cell = row.GetOrCreate(dataFirstCellNum - content.Key);
                        cell.SetCellValue(content.Value);
                        cell.CellStyle = cellStyle;
                    }

                    for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                    {
                        var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                        var cell = row.GetOrCreate(cellIndex);

                        var value = (deptData != null && deptData.Any())
                            ? deptData.FirstOrDefault(t => t.Category.NoBlank() == column)?.Value
                            : 0;
                        //if (cell.CellType != CellType.Formula)
                        //{
                        //    cell.SetCellValue<decimal>(value);
                        //    cell.CellStyle = cellStyle;
                        //}
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                    dataFirstRowNum++;
                    data.RemoveAll(t => t.Department == item.Department);
                }
            }
            return dataFirstRowNum;
        }

        public static string HasValue(params string[] list)
        {
            if (list == null || !list.Any()) return null;

            return list.FirstOrDefault(t => !string.IsNullOrEmpty(t));
        }

        /// <summary> 住院核算单元 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldInpat = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) => dto.InpatDoctorAccounting },
            { "核算单元（护理组）", (dto) => dto.InpatNurseAccounting },
            { "核算单元（医技组）", (dto) => dto.InpatTechnicAccounting },
        };

        /// <summary> 门诊核算单元 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldOut = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) => dto.OutDoctorAccounting },
            { "核算单元（护理组）", (dto) => dto.OutNurseAccounting },
            { "核算单元（医技组）", (dto) => dto.OutTechnicAccounting },
        };

        /// <summary> 住院核算单元 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldInpatOut = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) => new string[]{ dto.InpatDoctorAccounting, dto.OutDoctorAccounting }.FirstOrDefault(w=>!string.IsNullOrEmpty(w)) },
            { "核算单元（护理组）", (dto) => new string[]{ dto.InpatNurseAccounting, dto.OutNurseAccounting }.FirstOrDefault(w=>!string.IsNullOrEmpty(w)) },
            { "核算单元（医技组）", (dto) => new string[]{ dto.InpatTechnicAccounting, dto.OutTechnicAccounting }.FirstOrDefault(w=>!string.IsNullOrEmpty(w)) },
        };

        /// <summary> 门诊核算单元 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldOutInpat = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) => new string[]{ dto.OutDoctorAccounting, dto.InpatDoctorAccounting }.FirstOrDefault(w=>!string.IsNullOrEmpty(w)) },
            { "核算单元（护理组）", (dto) => new string[]{ dto.OutNurseAccounting, dto.InpatNurseAccounting }.FirstOrDefault(w=>!string.IsNullOrEmpty(w)) },
            { "核算单元（医技组）", (dto) => new string[]{ dto.OutTechnicAccounting, dto.InpatTechnicAccounting }.FirstOrDefault(w=>!string.IsNullOrEmpty(w)) },
        };

        /// <summary> 医生工作量 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldDoctor = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元", (dto) =>  new string []{ dto.OutDoctorAccounting, dto.InpatDoctorAccounting, dto.OutTechnicAccounting, dto.InpatTechnicAccounting }.FirstOrDefault(t => !string.IsNullOrEmpty(t))
            },
        };

        /// <summary> 护理工作量 </summary>
        private static readonly Dictionary<string, Func<ExtractTransDto, string>> fieldNurse = new Dictionary<string, Func<ExtractTransDto, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元", (dto) => new string []{ dto.OutNurseAccounting, dto.InpatNurseAccounting }.FirstOrDefault(t => !string.IsNullOrEmpty(t)) },
        };

        #endregion ExtractData

        #region CollectData

        public static void WriteCollectData(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<string> headers, List<collect_data> data)
        {
            var columnHeader = sheet.GetOrCreate(point.HeaderFirstRowNum.Value);
            int dataFirstRowNum = point.DataFirstRowNum.Value;

            List<IncomeRow> rows = new List<IncomeRow>();

            WriteCollectDataExistent(sheet, columnHeader, point, sheetType, style, headers, data, rows, ref dataFirstRowNum);

            if (sheetType == SheetType.Income && rows != null)
            {
                dataFirstRowNum = point.DataFirstRowNum.Value;
                RemoveIncomeRow(sheet, point);
                WriteCollectIncomeData(sheet, columnHeader, point, style, headers, data, rows, dataFirstRowNum);
                dataFirstRowNum = point.DataFirstRowNum.Value + rows.Count;
            }

            if (data == null || !data.Any()) return;

            WriteCollectDataNonexistent(sheet, columnHeader, point, sheetType, style, headers, data, dataFirstRowNum);
        }

        private static void WriteCollectDataExistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<collect_data> data, List<IncomeRow> incomes, ref int dataFirstRowNum)
        {
            if (sheet.LastRowNum <= dataFirstRowNum) return;

            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            for (int rowIndex = point.DataFirstRowNum.Value; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                string department = row.GetOrCreate(dataFirstCellNum - 1).GetDecodeEscapes();
                if (string.IsNullOrEmpty(department)) continue;

                if (rowIndex > dataFirstRowNum) dataFirstRowNum = rowIndex + 1;

                var deptData = data.Where(t => t.Department == department);
                if (deptData == null || !deptData.Any(t => !string.IsNullOrEmpty(t.CellValue))) continue;

                #region 写入数据 

                if (sheetType == SheetType.Income)
                {
                    incomes.Add(GetIncomeRowMessage(row, dataFirstCellNum, department, rowIndex));
                    continue;
                }

                for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    var cell = row.GetOrCreate(cellIndex);

                    var value = deptData.FirstOrDefault(t => t.TypeName.NoBlank() == column)?.CellValue;
                    //数据为空，且单元格值不为空，不写入数据（保留原始值）
                    var notWrite = string.IsNullOrEmpty(value) && !string.IsNullOrEmpty(cell.ToString());
                    if (!notWrite)
                    {
                        cell.SetCellValue<decimal>(value);
                        if (headers != null && headers.Contains(column))
                        {
                            cell.CellStyle = cellStyle;
                        }
                    }
                }

                #endregion

                data.RemoveAll(t => t.Department == department);
            }

            if (point.DataFirstRowNum.Value < dataFirstRowNum) dataFirstRowNum += 1;
        }

        private static void WriteCollectDataNonexistent(ISheet sheet, IRow columnHeader, PerSheetPoint point, SheetType sheetType, ExcelStyle style,
            List<string> headers, List<collect_data> data, int dataFirstRowNum)
        {
            var departments = data.Select(s => s.Department ?? "")/*.Where(w => !string.IsNullOrEmpty(w))*/.Distinct().ToList();

            var filed = new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType)
                ? collectWork
                : new SheetType[] { SheetType.OtherIncome, SheetType.Expend }.Contains(sheetType) ? collectIncome : collectDept;

            //var deptStyle = style.GetCellStyle();
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);

            headers = headers.Select(t => t.NoBlank()).ToList();

            foreach (string department in departments)
            {
                var deptData = data.Where(t => (t.Department ?? "") == department);
                if (deptData == null || !deptData.Any()) continue;

                var row = sheet.GetOrCreate(dataFirstRowNum);
                for (int cellIndex = point.HeaderFirstCellNum.Value; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetCell(cellIndex).GetDecodeEscapes()?.Replace("(", "（").Replace(")", "）");
                    var cell = row.GetOrCreate(cellIndex);

                    if (filed.ContainsKey(column))
                    {
                        cell.SetCellOValue(filed[column]?.Invoke(deptData.First()));
                        cell.CellStyle = cellStyle;
                    }
                    else if (sheetType == SheetType.Income || (headers != null && headers.Contains(column)))
                    {
                        var value = deptData.FirstOrDefault(t => t.TypeName.NoBlank() == column)?.CellValue;
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                }
                dataFirstRowNum++;
            }
        }

        private static void WriteCollectIncomeData(ISheet sheet, IRow columnHeader, PerSheetPoint point, ExcelStyle style, List<string> headers,
            List<collect_data> data, List<IncomeRow> incomes, int dataFirstRowNum)
        {
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);
            //var deptStyle = style.GetCellStyle();

            headers = headers.Select(t => t.NoBlank()).ToList();

            int dataFirstCellNum = point.DataFirstCellNum.Value;

            foreach (var item in incomes)
            {
                var row = sheet.GetOrCreate(dataFirstRowNum);

                var deptData = data.Where(t => t.Department == item.Department);
                if (deptData == null || !deptData.Any()) continue;

                var deptContents = new Dictionary<int, string>
                {
                    { 1, item.Department },
                    { 2, item.NurseAccount },
                    { 3, item.DoctorAccount },
                    { 4, item.TechnicAccounting },
                };

                foreach (var content in deptContents)
                {
                    var cell = row.GetOrCreate(dataFirstCellNum - content.Key);
                    cell.SetCellValue(content.Value);
                    cell.CellStyle = cellStyle;
                }

                for (int cellIndex = dataFirstCellNum; cellIndex < columnHeader.LastCellNum; cellIndex++)
                {
                    var column = columnHeader.GetOrCreate(cellIndex).GetDecodeEscapes();
                    var cell = row.GetOrCreate(cellIndex);

                    var value = deptData.FirstOrDefault(t => t.TypeName.NoBlank() == column)?.CellValue;
                    if (!string.IsNullOrEmpty(value) && cell.CellType != CellType.Formula)
                    {
                        cell.SetCellValue<decimal>(value);
                        cell.CellStyle = cellStyle;
                    }
                }

                dataFirstRowNum++;
                data.RemoveAll(t => t.Department == item.Department);
            }
        }

        /// <summary> 收入固定列 </summary>
        private static readonly Dictionary<string, Func<collect_data, string>> collectIncome = new Dictionary<string, Func<collect_data, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元（医生组）", (dto) => dto.AccountingUnitDoctor },
            { "核算单元（护理组）", (dto) => dto.AccountingUnitNurse },
            { "核算单元（医技组）", (dto) => dto.AccountingUnitTechnician },
        };

        /// <summary> 工作量固定列 </summary>
        private static readonly Dictionary<string, Func<collect_data, string>> collectWork = new Dictionary<string, Func<collect_data, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元", (dto) => dto.AccountingUnitDoctor },
        };

        /// <summary> 科室详情数据固定列 </summary>
        private static readonly Dictionary<string, Func<collect_data, string>> collectDept = new Dictionary<string, Func<collect_data, string>>
        {
            { "科室名称", (dto) => dto.Department },
            { "核算单元类型", (dto) => dto.UnitType },
        };

        #endregion CollectData

        /// <summary>
        /// 抽取优化，获取有数据的科室信息
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dataFirstCellNum"></param>
        /// <param name="department"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private static IncomeRow GetIncomeRowMessage(IRow row, int dataFirstCellNum, string department, int rowIndex)
        {
            var nurse = row.GetOrCreate(dataFirstCellNum - 2).GetDecodeEscapes();
            var doctor = row.GetOrCreate(dataFirstCellNum - 3).GetDecodeEscapes();
            var technic = row.GetOrCreate(dataFirstCellNum - 4).GetDecodeEscapes();

            for (int j = dataFirstCellNum - 4; j < dataFirstCellNum; j++)
            {
                var cell = row.GetCell(j);
                if (cell != null)
                {
                    cell.RemoveCellComment();
                    row.RemoveCell(cell);
                }
            }

            return new IncomeRow(department, doctor, nurse, technic, rowIndex);
        }

        /// <summary>
        /// 抽取优化，删除收入数据的核算科室信息
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dataFirstCellNum"></param>
        /// <param name="department"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private static void RemoveIncomeRow(ISheet sheet, PerSheetPoint point)
        {
            if (sheet == null)
                return;

            for (int i = point.DataFirstRowNum.Value; i < sheet.LastRowNum + 1; i++)
            {
                var row = sheet.GetRow(i);
                if (row != null)
                {
                    int dataFirstCellNum = point.DataFirstCellNum.Value;
                    //跳过核算单元和科室
                    for (int j = dataFirstCellNum - 4; j < dataFirstCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell != null)
                        {
                            cell.RemoveCellComment();
                            row.RemoveCell(cell);
                        }
                    }
                }
            }
        }
    }
}
