DeviceManager/DeviceRepairAndOptimization/Pages/AM/Plan/pagePlanImport.cs

using DevExpress.XtraBars;
using DevExpress.XtraBars.ToolbarForm;
using DeviceRepair.Models;
using DeviceRepairAndOptimization.Biz.AM;
using DeviceRepairAndOptimization.Common;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;

namespace DeviceRepairAndOptimization.Pages.AM.Plan
{
    public partial class pagePlanImport : ToolbarForm
    {
        StringBuilder logs = new StringBuilder();

        public pagePlanImport()
        {
            InitializeComponent();
        }

        private void pagePlanImport_Load(object sender, EventArgs e)
        {
            xucPlanImport1.ShowLogs += XucPlanImport1_ShowLogs;
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void itemBtnSubmit_ItemClick(object sender, ItemClickEventArgs e)
        {
            string filePath = xucPlanImport1.FilePath;
            DataTable dataTable = new DataTable();
            if (string.IsNullOrEmpty(filePath))
            {
                XtraMessageBoxHelper.Error("请选择待导入的文件");
                return;
            }

            try
            {
                logs.Clear();
                splashScreenManager1.ShowWaitForm();

                int CurrentMaintenanceYear = xucPlanImport1.PlanYear.HasValue ? xucPlanImport1.PlanYear.Value.Year : DateTime.Now.Year;

                APIResponseData apiResponseData = Biz.DeviceManager.Instance.GetQuery("");
                if (!apiResponseData.IsSuccess)
                    throw new Exception(apiResponseData.Message);

                List<DeviceInformationInfo> driveLst = apiResponseData.ToDeserializeObject<List<DeviceInformationInfo>>();

                logs.AppendLine("正在打开Excel文件");
                using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook;
                    if (Path.GetExtension(filePath) == ".xls")
                    {
                        logs.AppendLine("处理.xls文件");
                        workbook = new HSSFWorkbook(fs); // 处理.xls文件
                    }
                    else if (Path.GetExtension(filePath) == ".xlsx")
                    {
                        logs.AppendLine("处理.xlsx文件");
                        workbook = new XSSFWorkbook(fs); // 处理.xlsx文件
                    }
                    else
                    {

                        throw new Exception("不支持的文件格式");
                    }

                    logs.AppendLine("正在读取文件内容");

                    ISheet sheet = workbook.GetSheetAt(0); // 获取第一个工作表
                    if (sheet == null)
                    {
                        throw new Exception("不支持的文件格式");
                    }

                    logs.AppendLine("获取第一个工作表");

                    logs.AppendLine("读取表头（第一行）并将其添加到 DataTable 中");

                    // 读取表头（第一行）并将其添加到 DataTable 中
                    if (sheet.LastRowNum == 0)
                    {
                        throw new Exception("导入数据不能为空");
                    }

                    IRow headerRow = sheet.GetRow(0);
                    if (headerRow == null)
                    {
                        throw new Exception("首行数据获取失败,导入的模板不正确!");
                    }

                    for (int i = 0; i < headerRow.LastCellNum; i++)
                    {
                        ICell headerCell = headerRow.GetCell(i);
                        if (headerCell != null)
                        {
                            dataTable.Columns.Add(headerCell.ToString());
                        }
                    }

                    bool hasError = false;
                    logs.AppendLine("读取数据行并将其添加到 DataTable 中");
                    // 读取数据行并将其添加到 DataTable 中
                    for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                    {
                        try
                        {
                            IRow dataRow = sheet.GetRow(rowIndex);
                            if (dataRow != null)
                            {
                                DataRow newRow = dataTable.NewRow();

                                if (dataRow.GetCell(0) == null)
                                    continue;

                                for (int colIndex = 0; colIndex < dataRow.LastCellNum; colIndex++)
                                {
                                    ICell cell = dataRow.GetCell(colIndex);
                                    if (cell != null)
                                    {
                                        if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                                        {
                                            try
                                            {
                                                // 将单元格值转换为日期
                                                DateTime dateValue = cell.DateCellValue;
                                                newRow[colIndex] = dateValue;
                                            }
                                            catch
                                            {
                                                logs.AppendLine($"行{rowIndex}出现错误输入的【新PM起始月份】非日期格式！");
                                                hasError = true;
                                            }
                                        }
                                        else
                                        {
                                            newRow[colIndex] = cell.ToString();
                                        }
                                    }
                                }

                                #region 校验设备信息

                                DeviceInformationInfo di = driveLst.Where(x => x.EquipmentID.Equals((newRow[0] + ""), StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault();
                                if (di == null)
                                {
                                    hasError = true;
                                    throw new Exception($"不存在设备编号为：{(newRow[0] + "")}的设备！");
                                }
                                newRow[0] = di.EquipmentID;
                                #endregion

                                #region 校验计划是否冲突

                                apiResponseData = PlanManager.Instance.GetPlanRecordProgress(di.AutoID, CurrentMaintenanceYear);
                                if (apiResponseData.IsSuccess)
                                {
                                    List<PlanProgress> progress = apiResponseData.ToDeserializeObject<List<PlanProgress>>();
                                    if (progress != null && progress.Count > 0)
                                    {
                                        foreach (PlanProgress item in progress)
                                        {
                                            if (newRow[Enum.Parse(typeof(Models.Enum.enumMonth), item.PlanMonth + "") + ""] + "" != item.PlanType)
                                            {
                                                hasError = true;
                                                throw new Exception($"设备编号为：{(newRow[0] + "")}的设备中，存在已保养{Enum.Parse(typeof(Models.Enum.enumMonth), item.PlanMonth + "")}月份的年度数据，无法修改！");
                                            }
                                        }
                                    }
                                }

                                #endregion


                                dataTable.Rows.Add(newRow);
                            }
                        }
                        catch (Exception ex)
                        {
                            logs.AppendLine($"行{rowIndex}出现错误:{ex.Message}");
                        }
                    }

                    if (hasError)
                    {
                        throw new Exception("导入的数据存在错误！");
                    }

                    //读取键值对
                    KeyValuePairsBind();

                    List<DriveMaintencePlanInfo> lst = new List<DriveMaintencePlanInfo>();
                    logs.AppendLine("数据对应关系映射开始...");

                    int NotFoundCount = 0;
                    foreach (DataRow item in dataTable.Rows)
                    {
                        string EquipmentID = item[keyValuePairs["EquipmentID"]] + "";
                        DeviceInformationInfo di = driveLst.Where(x => x.EquipmentID == EquipmentID).FirstOrDefault();
                        if (lst.Any(x => x.EquipmentID == di.AutoID))
                        {
                            throw new Exception($"设备编号为:{EquipmentID}的设备数据,在表格中存在多条！");
                        }

                        if (di != null)
                        {
                            foreach (KeyValuePair<string, string> kvp in keyValuePairs)
                            {
                                Models.Enum.enumMonth em;
                                if (Enum.TryParse(kvp.Value, out em))
                                {
                                    DriveMaintencePlanInfo itemModel = new DriveMaintencePlanInfo
                                    {
                                        EquipmentID = di.AutoID,
                                        CompleteDate = null,
                                        MaintenanceYear = CurrentMaintenanceYear,
                                        MaintenanceMonth = (int)em,
                                        MaintenanceType = item[kvp.Value] + "",
                                        Remarks = item[keyValuePairs["Comment"]] + "",
                                        PMStartMonth = null,
                                        CreatDate = DateTime.Today,
                                        CreatUser = GlobalInfo.CurrentUser.AutoID,
                                        ChangeDate = DateTime.Today,
                                        ChangeUser = GlobalInfo.CurrentUser.AutoID
                                    };

                                    DateTime pMStartMonth = DateTime.Now;
                                    if (DateTime.TryParse((item[keyValuePairs["StartMonth"]] + ""), out pMStartMonth))
                                    {
                                        itemModel.PMStartMonth = pMStartMonth;
                                    }

                                    lst.Add(itemModel);
                                }
                            }
                        }
                        else
                        {
                            NotFoundCount++;
                            logs.AppendLine($"程序中不存在设备编号为:{EquipmentID}的设备数据.");
                        }
                    }


                    if (lst.Count == 0 && NotFoundCount > 0)
                        throw new Exception("导入的设备编号不存在！");
                    else if (lst.Count == 0)
                        throw new Exception("导入的数据为空！");

                    apiResponseData = PlanManager.Instance.InsertDatas(lst);

                    if (apiResponseData.IsSuccess)
                    {
                        splashScreenManager1.TryCloseWait();
                        DialogResult = DialogResult.OK;
                        this.Close();
                    }
                    else
                    {
                        throw new Exception(apiResponseData.Message);
                    }
                }
            }
            catch (Exception ex)
            {
                logs.AppendLine(ex.Message);
                splashScreenManager1.TryCloseWait();
                xucPlanImport1.LogIsVisibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Always;
                XtraMessageBoxHelper.Error("操作出错！");
            }
        }

        private void XucPlanImport1_ShowLogs()
        {
            new page_XtraFormLog("日志信息", logs).ShowDialog(this);
        }

        Dictionary<string, string> keyValuePairs;

        /// <summary>
        /// 反射model类键值对
        /// </summary>
        void KeyValuePairsBind()
        {
            if (keyValuePairs == null || keyValuePairs.Count == 0)
            {
                if (keyValuePairs == null)
                    keyValuePairs = new Dictionary<string, string>();

                PropertyInfo[] properties = typeof(DriveMaintenancePlanExcelModel).GetProperties();
                foreach (PropertyInfo item in properties)
                {
                    DisplayNameAttribute[] attrs =
                        (DisplayNameAttribute[])item.GetCustomAttributes(typeof(DisplayNameAttribute), true);
                    keyValuePairs.Add(item.Name, attrs[0].DisplayName);
                }
            }
        }
    }
}