DeviceManager/TsSFCDevice.Client.Launch/Plan/xucPlanImport.cs

using DeviceRepair.Models;
using DeviceRepair.Models.Enum;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Reflection;

namespace TsSFCDevice.Client.Launch.Plan
{
    public partial class xucPlanImport : DevExpress.XtraEditors.XtraUserControl
    {
        public EnumDeviceBelong Belong = EnumDeviceBelong.PM;

        public delegate void ShowLogEventHandler();
        public event ShowLogEventHandler ShowLogs;

        public DevExpress.XtraLayout.Utils.LayoutVisibility LogIsVisibility
        {
            set { layoutControlItem4.Visibility = value; }
        }

        public DateTime? PlanYear { get { return Convert.ToDateTime(deMaintenanceYear.EditValue); } }

        public string FilePath { get { return btnSelectFilePath.EditValue + ""; } }

        public xucPlanImport()
        {
            InitializeComponent();
        }

        private void xucPlanImport_Load(object sender, EventArgs e)
        {
            deMaintenanceYear.EditValue = DateTime.Today;
            this.layoutControl1.AllowCustomization = false;
        }

        /// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSelectFilePath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {
            using (DevExpress.XtraEditors.XtraOpenFileDialog xofd = new DevExpress.XtraEditors.XtraOpenFileDialog())
            {
                xofd.Filter = "(*.xlsx)|*.xlsx";
                if (xofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    string targetPath = xofd.FileName;
                    btnSelectFilePath.Text = targetPath;
                }
            }
        }

        /// <summary>
        /// 模板下载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void hyperlinkLabelControl1_Click(object sender, EventArgs e)
        {
            try
            {
                using (DevExpress.XtraEditors.XtraSaveFileDialog xsfd = new DevExpress.XtraEditors.XtraSaveFileDialog())
                {
                    xsfd.Filter = "(*.xlsx)|*.xlsx";
                    if (xsfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    {
                        string targetPath = xsfd.FileName;
                        OutExcel(targetPath);
                        XtraMessageBoxHelper.Info("导出成功！");
                    }
                }
            }
            catch (Exception ex)
            {
                XtraMessageBoxHelper.Error(ex.Message);
            }
        }

        public void ErrorLogs()
        {
            ShowLogs?.Invoke();
        }

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

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="savePath"></param>
        void OutExcel(string savePath)
        {
            // 创建Excel工作簿和工作表
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet($"OEM设备AM计划");
            ISheet sheet2 = workbook.CreateSheet($"选项列表");

            //读取Model属性生成键值对
            KeyValuePairsBind();

            // 写入表头
            IRow headerRow = sheet.CreateRow(0);
            int index = 0;

            // 创建一个下拉框选项列表
            string[] options = new string[] { };
            if (Belong == EnumDeviceBelong.AM)
            {
                options = new string[] { "", "Daily" };
            }
            else if (Belong == EnumDeviceBelong.PM)
            {
                options = new string[] { "", "Monthly", "Annual", "Quarterly", "Semi-an" };
            }

            // 设置下拉框选项为字符串类型
            IRow row = sheet2.CreateRow(0);
            for (int i = 0; i < options.Length; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(options[i]);
            }

            // 创建下拉框选项列表
            var validationHelper = sheet.GetDataValidationHelper();
            IDataValidationConstraint constraint = validationHelper.CreateExplicitListConstraint(options);

            int StartCellIndex = 0, EndCellIndex = 0;
            foreach (KeyValuePair<string, string> item in keyValuePairs)
            {
                if (item.Key == "Jan")
                    StartCellIndex = index;
                else if (item.Key == "Dec")
                    EndCellIndex = index;

                headerRow.CreateCell(index).SetCellValue(item.Value);
                index++;
            }

            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 1048575, StartCellIndex, EndCellIndex); // 从第二行开始到最后一行，第一列
            IDataValidation dataValidation = validationHelper.CreateValidation(constraint, cellRangeAddressList);
            dataValidation.ShowErrorBox = true;
            // 将下拉框数据验证对象应用到整列的每个单元格
            sheet.AddValidationData(dataValidation);

            // 删除选项sheet
            workbook.RemoveSheetAt(workbook.GetSheetIndex(sheet2));

            // 保存Excel文件
            using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }

        private void hyperlinkLabelControl2_Click(object sender, EventArgs e)
        {
            ShowLogs?.Invoke();
        }
    }
}