Project_PrincipleOverview_G_YunNan_2025/ModeDisplay/Assets/Scripts/Tool/ExcelHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using UnityEngine;
using System.IO;
using ExcelDataReader;
using System.Reflection;
using OfficeOpenXml;

/// <summary>
/// Excel帮助类
/// </summary>
public class ExcelHelper
{
    /// <summary>
    /// 从Excel文件读取信息
    /// </summary>
    /// <param name="tableName">文件路径名（需要有文件后缀）</param>
    /// <param name="headCount">表头</param>
    /// <returns></returns>
    public static List<T> ReadInfoFromExcel<T>(string filedPath, int headCount)
    {
        List<T> tmpTs = new List<T>();

        using (FileStream fileStream = File.Open(filedPath, FileMode.Open, FileAccess.Read))
        {
            using (IExcelDataReader dataReader = ExcelReaderFactory.CreateReader(fileStream))
            {
                //拿Excel第一张表
                DataTable tmpDataTable = dataReader.AsDataSet().Tables[0];
                //类中变量
                FieldInfo[] tmpFileInfos = typeof(T).GetFields();

                

                //匹配数据
                if (tmpFileInfos.Length != tmpDataTable.Columns.Count)
                {
                    Debug.LogError("tmpFileInfos.Length:"+ tmpFileInfos.Length);
                    Debug.LogError("tmpDataTable.Columns.Count:"+ tmpDataTable.Columns.Count);
                    Debug.LogError($"转换数据与{filedPath}的元素数量不相同，请检查！！！");

                    return tmpTs;
                }

                for (int i = headCount; i < tmpDataTable.Rows.Count; i++)
                {
                    T tmpT = Activator.CreateInstance<T>();

                    for (int j = 0; j < tmpFileInfos.Length; j++)
                    {
                        FieldInfo tmpFieldInfo = tmpFileInfos[j];

                        tmpFieldInfo.SetValue(tmpT, tmpDataTable.Rows[i][j].ToString());
                    }

                    tmpTs.Add(tmpT);
                }
            }
        }

        return tmpTs;
    }

    /// <summary>
    /// 将数据信息写入到Excel文件中
    /// </summary>
    /// <typeparam name="T">类型</typeparam>
    /// <param name="ts">信息</param>
    /// <param name="filePath">全路径+后缀</param>
    public static bool WriteInfoToExcel<T>(string filePath, List<T> ts)
    {
        using (ExcelPackage package = new ExcelPackage())
        {
            if (package.Workbook.Worksheets.Count == 0) package.Workbook.Worksheets.Add("Sheet1");

            ExcelWorksheet sheet = package.Workbook.Worksheets["Sheet1"];

            FieldInfo[] tmpFieldInfos = typeof(T).GetFields();

            //写入标题
            for (int i = 1; i < tmpFieldInfos.Length + 1; i++) sheet.Cells[1, i].Value = tmpFieldInfos[i - 1].Name;

            //写入数据
            for (int i = 2; i < ts.Count + 2; i++)
            {
                for (int j = 1; j < tmpFieldInfos.Length + 1; j++)
                {
                    sheet.Cells[i, j].Value = tmpFieldInfos[j - 1].GetValue(ts[i - 2]);
                }
            }

            FileInfo tmpFileInfo = new FileInfo(filePath);

            //如果不存在表格就创建表格
            if (!tmpFileInfo.Exists) tmpFileInfo.Create().Dispose();

            package.SaveAs(tmpFileInfo);
        }
        return false;
    }

    public static bool WriteInfoToExcel<T>(string filePath, T ts)
    {
        FileInfo tmpFileInfo = new FileInfo(filePath);
        ExcelPackage package;
        if (!tmpFileInfo.Exists || tmpFileInfo.Length == 0)
            package = new ExcelPackage();
        else
            package = new ExcelPackage(tmpFileInfo);

        using (package)
        {
            ExcelWorksheet sheet;
            if (package.Workbook.Worksheets.Count == 0) sheet = package.Workbook.Worksheets.Add("Sheet1");
                else sheet = package.Workbook.Worksheets["Sheet1"];

            FieldInfo[] tmpFieldInfos = typeof(T).GetFields();

            // 确定起始行（如果是新文件从第1行开始，追加模式从最后一行+1开始）
            int startRow = sheet.Dimension?.End.Row + 1 ?? 1;

            // 写入数据
            for (int i = 1; i < tmpFieldInfos.Length + 1; i++)
            {
                sheet.Cells[startRow, i].Value = tmpFieldInfos[i - 1].GetValue(ts);
            }

            package.SaveAs(tmpFileInfo);
        }
        return false;
    }
}