C#使用EPPlus读写Excel

依赖EPPlus

获取依赖可以阅读:Nuget For Unity插件介绍_nugetforunity-CSDN博客

可以参阅该篇快速入门:在Unity中使用Epplus写Excel_unity epplus-CSDN博客

下面是我封装的几个方法:

要在合适的时机配置许可证,比如你的工具类的静态函数.建议使用版本7.7.1

  #region Excel封装,使用Eppluspublic class ExcelRowData{/// <summary>/// 来自那张表/// </summary>public string BelongSheetName { get; set; }/// <summary>/// 来自哪一行(从2开始,第一行为表头)/// </summary>public int RowNumber { get; set; }/// <summary>/// key=列名 value=数据/// </summary>public Dictionary<string, string> Data { get; set; }}#region 读/// <summary>/// 读取 Excel 文件，返回所有工作表的数据（含行号）/// </summary>public static Dictionary<string, List<ExcelRowData>> ReadExcelAllSheets(string filePath){if (!File.Exists(filePath))throw new FileNotFoundException("Excel 文件不存在", filePath);var result = new Dictionary<string, List<ExcelRowData>>();using (var package = new ExcelPackage(new FileInfo(filePath))){foreach (var worksheet in package.Workbook.Worksheets){var sheetData = ReadWorksheet(worksheet);result[worksheet.Name] = sheetData;}}return result;}/// <summary>/// 读取指定工作表的数据（含行号）/// </summary>public static List<ExcelRowData> ReadExcelSheet(string filePath, string sheetName){if (!File.Exists(filePath))throw new FileNotFoundException("Excel 文件不存在", filePath);using (var package = new ExcelPackage(new FileInfo(filePath))){var worksheet = package.Workbook.Worksheets[sheetName];if (worksheet == null)throw new ArgumentException($"未找到工作表：{sheetName}");return ReadWorksheet(worksheet);}}/// <summary>/// 读取单个工作表的数据（第一行为表头，返回包含行号的结构）/// </summary>private static List<ExcelRowData> ReadWorksheet(ExcelWorksheet worksheet){var result = new List<ExcelRowData>();if (worksheet.Dimension == null)return result;int rowCount = worksheet.Dimension.End.Row;int colCount = worksheet.Dimension.End.Column;var sheetName = worksheet.Name;// 读取表头var headers = new List<string>();for (int col = 1; col <= colCount; col++){var header = worksheet.Cells[1, col].Text.Trim();if (string.IsNullOrEmpty(header))throw new Exception($"表头第 {col} 列为空，请检查Excel文件格式。");headers.Add(header);}// 读取数据行for (int row = 2; row <= rowCount; row++){var rowDict = new Dictionary<string, string>();for (int col = 1; col <= colCount; col++){string cellValue = worksheet.Cells[row, col].Text.Trim();if (string.IsNullOrEmpty(cellValue))throw new Exception($"工作表 '{worksheet.Name}' 第 {row} 行，第 {col} 列（{headers[col - 1]}）单元格为空，请检查数据完整性。");string header = headers[col - 1];rowDict[header] = cellValue;}result.Add(new ExcelRowData { BelongSheetName = sheetName, RowNumber = row, Data = rowDict });}return result;}#endregion#region 写/// <summary>/// 将单张表的数据写入 Excel 文件（如果文件不存在会自动创建）/// </summary>/// <param name="sheetName">工作表名称，不能为空或空白</param>/// <param name="rows">该表的数据行列表，不能为空且至少包含一行数据</param>/// <param name="filePath">可选，指定输出文件路径。如果为 null 或空，则默认保存到桌面“Excel”目录下，并以当前时间戳+GUID命名文件</param>/// <exception cref="ArgumentException">当 sheetName 为空或 rows 为空时抛出异常</exception>public static void WriteExcelSingleSheet(string sheetName, List<ExcelRowData> rows, string filePath = null){if (string.IsNullOrWhiteSpace(sheetName))throw new ArgumentException("工作表名称不能为空", nameof(sheetName));if (rows == null || rows.Count == 0)throw new ArgumentException("数据不能为空", nameof(rows));string desktopExcelPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Excel");Directory.CreateDirectory(desktopExcelPath);if (string.IsNullOrWhiteSpace(filePath)){filePath = Path.Combine(desktopExcelPath, $"Excel_{DateTime.Now:yyyyMMdd_HHmmss}_{Guid.NewGuid()}.xlsx");}using (var package = new ExcelPackage(filePath)){var ws = package.Workbook.Worksheets.Add(sheetName);// 写表头var headers = rows[0].Data.Keys.ToList();for (int col = 0; col < headers.Count; col++){ws.Cells[1, col + 1].Value = headers[col];}// 写数据行for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++){var rowData = rows[rowIndex].Data;int excelRow = rowIndex + 2;int colIndex = 1;foreach (var header in headers){rowData.TryGetValue(header, out string cellValue);ws.Cells[excelRow, colIndex].Value = cellValue;colIndex++;}}package.Save();}}/// <summary>/// 将多个表的数据写入同一个 Excel 文件（如果文件不存在会自动创建）/// </summary>/// <param name="excelData">字典，key 为工作表名称，value 为对应的该表数据行列表，字典不能为空且至少含有一个表数据</param>/// <param name="filePath">可选，指定输出文件路径。如果为 null 或空，则默认保存到桌面“Excel”目录下，并以当前时间戳+GUID命名文件</param>/// <exception cref="ArgumentNullException">当 excelData 为 null 或为空时抛出异常</exception>public static void WriteExcelAllSheets(Dictionary<string, List<ExcelRowData>> excelData, string filePath = null){if (excelData == null || excelData.Count == 0)throw new ArgumentNullException(nameof(excelData));string desktopExcelPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Excel");Directory.CreateDirectory(desktopExcelPath);if (string.IsNullOrWhiteSpace(filePath)){filePath = Path.Combine(desktopExcelPath, $"Excel_{DateTime.Now:yyyyMMdd_HHmmss}_{Guid.NewGuid()}.xlsx");}using (var package = new ExcelPackage(filePath)){foreach (var sheetData in excelData){WriteSheetToPackage(sheetData.Key, sheetData.Value, package);}package.Save();}}private static void WriteSheetToPackage(string sheetName, List<ExcelRowData> rows, ExcelPackage package){if (string.IsNullOrWhiteSpace(sheetName))throw new ArgumentException("工作表名称不能为空", nameof(sheetName));if (rows == null || rows.Count == 0)throw new ArgumentException("数据不能为空", nameof(rows));var ws = package.Workbook.Worksheets.Add(sheetName);// 写表头var headers = rows[0].Data.Keys.ToList();for (int col = 0; col < headers.Count; col++){ws.Cells[1, col + 1].Value = headers[col];}// 写数据行for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++){var rowData = rows[rowIndex].Data;int excelRow = rowIndex + 2;int colIndex = 1;foreach (var header in headers){rowData.TryGetValue(header, out string cellValue);ws.Cells[excelRow, colIndex].Value = cellValue;colIndex++;}}}#endregion#endregion