Display_D_HP/TeamAAS-VM/Models/PLC/PlcExcelLoad.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.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace TeamAAS_VP.Models.PLC
{
    public class PlcExcelLoad
    {
        /// <summary>
        /// 真正的 bool 类型地址结构（无字符串类型）
        /// </summary>
        public class PLCAddrs
        {
            public string OpcAddress { get; set; }   // PLC地址（变量名）
            public string Name { get; set; }         // 报警文本
            public bool Value { get; set; }          // 真实 BOOL 类型值
        }

        // 默认PLC地址表路径
        private string _filePath = Path.Combine(Path.GetDirectoryName(Application.StartupPath), @"Configuration Files\PLC地址表.xlsx");

        // 所有集合统一使用 PLCAddrs
        public List<PLCAddrs> IOInputList = new List<PLCAddrs>();
        public List<PLCAddrs> IOOutputList = new List<PLCAddrs>();
        public List<PLCAddrs> JoggingList = new List<PLCAddrs>();
        public List<PLCAddrs> AlarmMaskList = new List<PLCAddrs>();
        public List<PLCAddrs> InputParamList = new List<PLCAddrs>();
        public List<PLCAddrs> OutputParamList = new List<PLCAddrs>();
        public List<PLCAddrs> AlaemList = new List<PLCAddrs>();      // 报警列表
        public List<PLCAddrs> SysStateList = new List<PLCAddrs>();   // 系统状态

        public string FilePath
        {
            get => _filePath;
            set => _filePath = value;
        }

        public PlcExcelLoad(string filePathStr = null)
        {
            if (!string.IsNullOrEmpty(filePathStr))
                _filePath = filePathStr;
        }

        // Excel工作表转DataTable
        public DataTable GetSheetData(int sheetIndex)
        {
            DataTable dt = new DataTable();
            try
            {
                using (var fs = new FileStream(_filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = new XSSFWorkbook(fs);
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    if (sheet == null || sheet.GetRow(0) == null) return null;

                    IRow headerRow = sheet.GetRow(0);
                    for (int i = 0; i < headerRow.LastCellNum; i++)
                        dt.Columns.Add(headerRow.GetCell(i)?.ToString()?.Trim() ?? $"Column{i}");

                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue;

                        DataRow dataRow = dt.NewRow();
                        for (int j = 0; j < dt.Columns.Count; j++)
                            dataRow[j] = row.GetCell(j)?.ToString()?.Trim() ?? "";

                        dt.Rows.Add(dataRow);
                    }
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 加载Excel数据（全部是真实 bool 变量，无字符串类型）
        /// </summary>
        public void LoadData(int sheetIndex)
        {
            try
            {
                if (!File.Exists(_filePath)) return;
                DataTable dt = GetSheetData(sheetIndex);
                if (dt == null || dt.Rows.Count == 0) return;

                RemoveEmptyRow(dt);

                foreach (DataRow row in dt.Rows)
                {
                    PLCAddrs temp = new PLCAddrs();

                    switch (sheetIndex)
                    {
                        case 0: // IO输入
                            temp.OpcAddress = row[0].ToString().Trim();
                            temp.Name = row[1].ToString().Trim();
                            IOInputList.Add(temp);
                            break;

                        case 1: // IO输出
                            temp.OpcAddress = row[0].ToString().Trim();
                            temp.Name = row[1].ToString().Trim();
                            IOOutputList.Add(temp);
                            break;

                        case 2: // 点动
                            temp.OpcAddress = row[0].ToString().Trim();
                            temp.Name = row[1].ToString().Trim();
                            JoggingList.Add(temp);
                            break;

                        case 3: // 参数输入
                            temp.OpcAddress = row[0].ToString().Trim();
                            temp.Name = row[1].ToString().Trim();
                            InputParamList.Add(temp);
                            break;

                        case 4: // 参数输出
                            temp.OpcAddress = row[0].ToString().Trim();
                            temp.Name = row[1].ToString().Trim();
                            OutputParamList.Add(temp);
                            break;

                        case 5: // 报警屏蔽
                            temp.OpcAddress = row[0].ToString().Trim();
                            temp.Name = row[1].ToString().Trim();
                            AlarmMaskList.Add(temp);
                            break;

                        case 6: // 报警信息（A列名称，B列地址）
                            temp.Name = row[0].ToString().Trim();       // 报警文本
                            temp.OpcAddress = row[1].ToString().Trim(); // PLC地址
                            AlaemList.Add(temp);
                            break;

                        case 7: // 系统状态
                            temp.OpcAddress = row[0].ToString().Trim();
                            temp.Name = row[1].ToString().Trim();
                            SysStateList.Add(temp);
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"加载PLC地址表失败：{ex.Message}");
            }
        }

        // 清除空行
        public void RemoveEmptyRow(DataTable dt)
        {
            List<DataRow> delRows = new List<DataRow>();
            foreach (DataRow row in dt.Rows)
            {
                bool isEmpty = true;
                foreach (var item in row.ItemArray)
                {
                    if (!string.IsNullOrWhiteSpace(item?.ToString()))
                    {
                        isEmpty = false;
                        break;
                    }
                }
                if (isEmpty) delRows.Add(row);
            }
            foreach (var row in delRows) dt.Rows.Remove(row);
        }

        // 安全写入Excel
        public void SaveCellValue(string sheetName, int rowIndex, int colIndex, object value)
        {
            if (!File.Exists(_filePath))
            {
                MessageBox.Show("PLC地址表不存在！");
                return;
            }

            try
            {
                using (FileStream fs = new FileStream(_filePath, FileMode.Open, FileAccess.ReadWrite))
                {
                    IWorkbook workbook = new XSSFWorkbook(fs);
                    ISheet sheet = workbook.GetSheet(sheetName) ?? workbook.CreateSheet(sheetName);
                    IRow row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                    ICell cell = row.GetCell(colIndex) ?? row.CreateCell(colIndex);
                    cell.SetCellValue(value?.ToString() ?? "");

                    fs.SetLength(0);
                    workbook.Write(fs);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Excel写入失败：{ex.Message}");
            }
        }
    }


}