NPOI对Excel的内容读取转成DataTable

 

NPOI版本：2.0.6.0

public void ExcelDt()
{
DataTable dt = new DataTable();
IWorkbook hssfworkbook;
using(FileStream fileStream = new FileStream(ImageEPath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = WorkbookFactory.Create(fileStream);
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
dt = ReadTable(sheet, 0, true, 2);
}
/// <summary>
/// 读取Excel数据-支持xls,xlsx
/// </summary>
/// <param name="sheet">sheet页码</param>
/// <param name="HeaderRowIndex">列头所在行，-1代表没有列头</param>
/// <param name="needHeader"></param>
/// <param name="cCount">2列填1</param>
/// <returns></returns>
public static DataTable ReadTable(ISheet sheet, int HeaderRowIndex, bool needHeader, int cCount)
{
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
try
{
//判断是否有列头
if(HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for(int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = cCount;
for(int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
if(headerRow.GetCell(i) == null)
{
if(table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if(table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
for(int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
IRow row;
if(sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for(int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if(row.GetCell(j) != null)
{
switch(row.GetCell(j).CellType)
{
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if(str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
if(DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch(row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if(strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch(Exception exception)
{}
}
table.Rows.Add(dataRow);
}
catch(Exception exception)
{}
}
}
catch(Exception exception)
{}
return table;
}