WCS/Cowain.Base/Helpers/ExcelHelper.cs

using Cowain.Base.Models;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Cowain.Base.Helpers;

public class ExcelHelper<T> where T : class
{
    public static ResultModel ExportExcel(List<T> data, string filePath)
    {
        if (data == null || data.Count == 0)
            return ResultModel.Error("数据不能为空");

        if (string.IsNullOrWhiteSpace(filePath))
            return ResultModel.Error("路径不能为空");
        try
        {
            // 创建Excel工作簿
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            // 获取对象属性作为表头
            var properties = typeof(T).GetProperties();

            // 创建表头行
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < properties.Length; i++)
            {
                headerRow.CreateCell(i).SetCellValue(properties[i].Name);
            }

            // 填充数据
            for (int i = 0; i < data.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                var item = data[i];

                for (int j = 0; j < properties.Length; j++)
                {
                    var value = properties[j].GetValue(item)?.ToString() ?? "";
                    row.CreateCell(j).SetCellValue(value);
                }
            }

            // 保存文件
            using (FileStream fs = new FileStream(filePath, FileMode.Create))
            {
                workbook.Write(fs);
            }
            return ResultModel.Success("导出成功");
        }
        catch (Exception ex)
        {
            return ResultModel.Error($"错误：{ex.Message}");
        }

    }

    public static async Task<ResultModel> ExportExcelAsync(List<T> data, string filePath)
    {
        if (data == null || data.Count == 0)
            return ResultModel.Error("数据不能为空");

        if (string.IsNullOrWhiteSpace(filePath))
            return ResultModel.Error("路径不能为空");
        try
        {
            // 创建Excel工作簿
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            // 获取对象属性作为表头
            var properties = typeof(T).GetProperties();

            // 创建表头行
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < properties.Length; i++)
            {
                headerRow.CreateCell(i).SetCellValue(properties[i].Name);
            }

            // 填充数据
            for (int i = 0; i < data.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                var item = data[i];

                for (int j = 0; j < properties.Length; j++)
                {
                    var value = properties[j].GetValue(item)?.ToString() ?? "";
                    row.CreateCell(j).SetCellValue(value);
                }
            }

            // 保存文件
            using (FileStream fs = new FileStream(filePath, FileMode.Create))
            {
                await Task.Run(() => workbook.Write(fs));
            }
            return ResultModel.Success("导出成功");
        }
        catch (Exception ex)
        {
            return ResultModel.Error($"错误：{ex.Message}");
        }
    }

    public static ResultModel<List<T>> ImportExcel(string filePath)
    {
        if (string.IsNullOrWhiteSpace(filePath))
            return ResultModel<List<T>>.Error("路径不能为空");

        try
        {
            List<T> data = new List<T>();
            IWorkbook workbook;

            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(fs);
            }

            ISheet sheet = workbook.GetSheetAt(0);
            if (sheet == null)
                return ResultModel<List<T>>.Error("未找到工作表");

            var properties = typeof(T).GetProperties();
            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue;

                T item = Activator.CreateInstance<T>();
                for (int j = 0; j < properties.Length; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell != null)
                    {
                        var value = cell.ToString();
                        properties[j].SetValue(item, Convert.ChangeType(value, properties[j].PropertyType));
                    }
                }
                data.Add(item);
            }

            return ResultModel<List<T>>.Success(data);
        }
        catch (Exception ex)
        {
            return ResultModel<List<T>>.Error($"错误：{ex.Message}");
        }
    }


}