1440WGJ/JY.Utility/ExcelImporter.cs

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;

namespace JY.Utility
{
    public static class ExcelImporter
    {
        public static List<T> Import<T>(string filePath, string sheetName = "Sheet1") where T : new()
        {
            // 设置 LicenseContext（非商业用途）
            ExcelPackage.License.SetNonCommercialPersonal("EVE");

            if (!File.Exists(filePath))
            {
                throw new FileNotFoundException("Excel文件不存在", filePath);
            }

            var result = new List<T>();
            var fileInfo = new FileInfo(filePath);

            using (var package = new ExcelPackage(fileInfo))
            {
                var worksheet = package.Workbook.Worksheets.FirstOrDefault(w => w.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
                if (worksheet == null)
                {
                    throw new ArgumentException($"指定的工作表 '{sheetName}' 不存在");
                }

                if (worksheet.Dimension == null)
                {
                    return result;
                }

                var dimension = worksheet.Dimension;
                var headers = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);

                for (int col = dimension.Start.Column; col <= dimension.End.Column; col++)
                {
                    var headerValue = worksheet.Cells[1, col].Value?.ToString().Trim();
                    if (!string.IsNullOrEmpty(headerValue))
                    {
                        headers[headerValue] = col;
                    }
                }

                var properties = TypeDescriptor.GetProperties(typeof(T));

                for (int row = dimension.Start.Row + 1; row <= dimension.End.Row; row++)
                {
                    var item = new T();
                    bool hasData = false;

                    foreach (PropertyDescriptor property in properties)
                    {
                        if (headers.TryGetValue(property.Description, out int col))
                        {
                            var cellValue = worksheet.Cells[row, col].Value;

                            if (cellValue != null)
                            {
                                hasData = true;
                                var stringValue = cellValue.ToString().Trim();

                                try
                                {
                                    var targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                    var convertedValue = Convert.ChangeType(stringValue, targetType);
                                    property.SetValue(item, convertedValue);
                                }
                                catch
                                {
                                    property.SetValue(item, null);
                                }
                            }
                        }
                    }

                    if (hasData)
                    {
                        result.Add(item);
                    }
                }
            }

            return result;
        }
    }
}