依赖EPPlus
获取依赖可以阅读:Nuget For Unity插件介绍_nugetforunity-CSDN博客
可以参阅该篇快速入门:在Unity中使用Epplus写Excel_unity epplus-CSDN博客
下面是我封装的几个方法:
要在合适的时机配置许可证,比如你的工具类的静态函数.建议使用版本7.7.1
#region Excel封装,使用Epplus
public 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