npol读取excel
添加引用
找到项目的Nuget包 添加npol的引用(开始以为npol的dll必须要从别的地方单独下载才可以,后来直接从vs里面的Nuget包引用也是可以的)
下面是读取excel方法:
/// <summary>
/// 读取excel
/// </summary>
/// <param name="filePath">文件绝对路径</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="isFirstColumnName">是否创建excel中的第一行</param>
/// <returns></returns>
public static DataTable ReadExcel(string filePath, string sheetName, bool isFirstColumnName = true)
{
if (!File.Exists(filePath))
return null;
DataTable data = new DataTable();
try
{
using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null; //新建IWorkbook对象
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fileStream);
}
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fileStream);
}
else return null;
ISheet sheet = workbook.GetSheet(sheetName); //获取工作表
if (sheet.LastRowNum <= 0) return null;
IRow firstRowClounms = sheet.GetRow(0);
int startRow = 0;
if (isFirstColumnName)//是否构建excel的第一行
{
startRow = 1;
for (int i = firstRowClounms.FirstCellNum; i < firstRowClounms.LastCellNum; ++i)
{
DataColumn column = new DataColumn(firstRowClounms.GetCell(i).StringCellValue);
data.Columns.Add(column);
}
}
else
{
for (int i = firstRowClounms.FirstCellNum; i < firstRowClounms.LastCellNum; ++i)
{
var column = new DataColumn("column" + (i + 1));
data.Columns.Add(column);
}
}
for (int i = startRow; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i); //row读入第i行数据
if (row == null) continue;
DataRow dr = data.NewRow();
for (int j = 0; j < row.LastCellNum; j++) //获取工作表每一列
{
if (!string.IsNullOrEmpty(row.GetCell(j).ToString()))
dr[j] = GetConvertValueType(row.GetCell(j));
}
data.Rows.Add(dr);
}
}
return data;
}
catch (Exception ex)
{
throw ex;
return null;
}
}
private static object GetConvertValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank:
return null;
case CellType.Boolean:
return cell.BooleanCellValue;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
if (format != 0) { return Convert.ToDateTime(cell.DateCellValue).ToString("yyyy-MM-dd HH:mm:ss"); } else { return cell.NumericCellValue; }
case CellType.String:
return cell.StringCellValue;
case CellType.Error:
return cell.ErrorCellValue;
case CellType.Formula:
default:
return "=" + cell.CellFormula;
}
}
读取后的结果