C# 使用 ExcelDataReader 实现 Excel 转换为 DataTable
程序员文章站
2022-03-21 07:55:02
在 github 上下载了 ExcelDataReader 的源码,摘取Demo的核心代码进行小改。该代码,包含了取第几页的数据,还有是否将Excel的第一行作为DataTable的列名。 public class ExcelHelper { public static DataSet ExcelToDataSet(string relativeFilePath, bool useHeaderRow = true) { var fil...
在 github 上下载了 ExcelDataReader 的源码,摘取Demo的核心代码进行小改。
该代码,包含了取第几页的数据,还有是否将Excel的第一行作为DataTable的列名
。
public class ExcelHelper
{
public static DataSet ExcelToDataSet(string relativeFilePath, bool useHeaderRow = true)
{
var filePath = Path.GetFullPath(relativeFilePath);
if (!File.Exists(filePath))
{
throw new FileNotFoundException("找不到文件!");
}
var extension = Path.GetExtension(filePath).ToLower();
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
var sw = new Stopwatch();
sw.Start();
IExcelDataReader reader = null;
if (extension == ".xls")
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (extension == ".xlsx")
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (extension == ".csv")
{
reader = ExcelReaderFactory.CreateCsvReader(stream);
}
if (reader == null)
return null;
var openTiming = sw.ElapsedMilliseconds;
DataSet ds;
using (reader)
{
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = useHeaderRow // 第一行包含列名
}
});
}
return ds;
}
}
public static DataTable ExcelToDataTable(string relativeFilePath, int sheet = 0, bool useHeaderRow = true)
{
var ds = ExcelToDataSet(relativeFilePath, useHeaderRow);
if (ds == null)
return null;
return ds.Tables[sheet];
}
}
本文地址:https://blog.csdn.net/Upgrader/article/details/107476116
推荐阅读