Unity 使用 ExcelDataReader 读取Excel表
/*
* FileName: ReadExcelTest
* Author: ming
* CreateTime: 2024/11/15 10:52:36
* Description: 读取Excel数据（程序运行期间只读取一次）
*
*/
using ExcelDataReader;
using System.Data;
using System.IO;
using System.Threading.Tasks;
using System;
public class ExcelHelper
{
private string _filePath;
private DataSet _cacheDataSet; // 缓存读取的DataSet
private readonly object _lock = new object(); // 线程锁
public ExcelHelper(string filePath)
{
_filePath = filePath;
}
// 异步读取Excel数据
public async Task<DataSet> ReadExcelAsync()
{
if (_cacheDataSet != null)
return _cacheDataSet;
return await Task.Run<DataSet>(() =>
{
lock (_lock)
{
if (_cacheDataSet == null) // 双重检查锁定
{
using (var stream = File.Open(_filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
_cacheDataSet = reader.AsDataSet();
}
}
}
}
return _cacheDataSet;
});
}
// 根据 sheet 名获取 DataTable
public async Task<DataTable> GetSheetByNameAsync(string sheetName)
{
await ReadExcelAsync();
return _cacheDataSet.Tables[sheetName];
}
// 根据 sheet 索引获取 DataTable
public async Task<DataTable> GetSheetByIndexAsync(int sheetIndex)
{
await ReadExcelAsync();
if (sheetIndex >= 0 && sheetIndex < _cacheDataSet.Tables.Count)
return _cacheDataSet.Tables[sheetIndex];
throw new ArgumentOutOfRangeException("Sheet index out of range");
}
// 获取指定行的内容（从 0 开始的行索引）
public async Task<object[]> GetRowAsync(string sheetName, int rowIndex)
{
var table = await GetSheetByNameAsync(sheetName);
if (rowIndex >= 0 && rowIndex < table.Rows.Count)
{
return table.Rows[rowIndex].ItemArray;
}
throw new ArgumentOutOfRangeException("Row index out of range");
}
// 获取指定列的内容（从 0 开始的列索引）
public async Task<object[]> GetColumnAsync(string sheetName, int columnIndex)
{
var table = await GetSheetByNameAsync(sheetName);
if (columnIndex >= 0 && columnIndex < table.Rows.Count)
{
var column = new object[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
column[i] = table.Rows[i][columnIndex];
}
return column;
}
throw new ArgumentOutOfRangeException("Column index out of range");
}
// 获取指定单元格内容
public async Task<object> GetCellAsync(string sheetName, int rowIndex, int columnIndex)
{
var table = await GetSheetByNameAsync(sheetName);
if ( rowIndex >= 0 && rowIndex < table.Rows.Count &&
columnIndex >= 0 && columnIndex < table.Columns.Count )
{
return table.Rows[rowIndex][columnIndex];
}
throw new ArgumentOutOfRangeException("Row or column index out of range");
}
// 获取指定 Sheet 行数
public async Task<int> GetRowCountAsync(string sheetName)
{
var table = await GetSheetByNameAsync(sheetName);
return table.Rows.Count;
}
// 获取指定 Sheet 列数
public async Task<int> GetColumnCountAsync(string sheetName)
{
var table = await GetSheetByNameAsync(sheetName);
return table.Columns.Count;
}
}