/// <summary>
/// NPOI根据路径获取文件转换成DataTable
/// </summary>
/// <param name="FilePath"></param>
/// <returns></returns>
private DataTable NpoiReader(string FilePath)
{
FileStream stream = System.IO.File.OpenRead(FilePath);
IWorkbook workbook = new HSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(0);//获取第一行列数据
DataTable table = new DataTable();
#region 循环遍历获取表的列名
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
table.Columns.Add(sheet.GetRow(0).GetCell(i).ToString());//逐个添加列名
}
for (var i = sheet.LastRowNum; i > 0; i--)
{
IRow row = sheet.GetRow(i);
List<string> dataList = new List<string>();
for (var j = 0; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
int rowIndex = i;
while (cell == null)
{
if (i > 0)
{
cell = sheet.GetRow(--rowIndex).GetCell(j);
}
else
{
continue;
}
}
dataList.Add(cell.ToString());
}
table.Rows.Add(dataList.ToArray());
}
#endregion
return table;
}
用以上方法将xls文件路径处理后转换成DataTable。
欢迎分享讨论!