NPOI解决由于excel删除数据导致空行读取问题

时间:2022-07-17 17:20:45

1。解决问题思路一:申明判断是否空行变量用于判断是否空行,声明变量数组用于临时非空行数据,最后存于datatable中。

 /// <summary>读取excel,
/// 默认第一行为表头
/// </summary>
/// <param name="strFileName">excel文档绝对路径</param>
/// <param name="rowIndex">内容行偏移量,第一行为表头,内容行从第二行开始则为1</param>
/// <returns></returns>
public static DataTable Import(string strFileName, int rowIndex)
{
DataTable dt = new DataTable(); IWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = WorkbookFactory.Create(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(); IRow headRow = sheet.GetRow();
if (headRow != null)
{
int colCount = headRow.LastCellNum;
for (int i = ; i < colCount; i++)
{
dt.Columns.Add("COL_" + i);
}
} for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
bool emptyRow = true;
object[] itemArray = null; if (row != null)
{
itemArray = new object[row.LastCellNum]; for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{ if (row.GetCell(j) != null)
{ switch (row.GetCell(j).CellType)
{
case CellType.NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
{
itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else//其他数字类型
{
itemArray[j] = row.GetCell(j).NumericCellValue;
}
break;
case CellType.BLANK:
itemArray[j] = string.Empty;
break;
case CellType.FORMULA:
if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx")
{
XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook);
if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
{
itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else//其他数字类型
{
itemArray[j] = row.GetCell(j).NumericCellValue;
}
}
else
{
itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
}
}
else
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook);
if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
{
itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else//其他数字类型
{
itemArray[j] = row.GetCell(j).NumericCellValue;
}
}
else
{
itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
}
}
break;
default:
itemArray[j] = row.GetCell(j).StringCellValue;
break; } if (itemArray[j] != null && !string.IsNullOrEmpty(itemArray[j].ToString().Trim()))
{
emptyRow = false;
}
}
}
} //非空数据行数据添加到DataTable
if (!emptyRow)
{
dt.Rows.Add(itemArray);
}
}
return dt;
}

2.思路二:对excel存于datatable中的空行进行遍历处理

 protected void RemoveEmpty(DataTable dt)
{
List<DataRow> removelist = new List<DataRow>();
for (int i = ; i < dt.Rows.Count; i++)
{
bool IsNull = true;
for (int j = ; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
IsNull = false;
}
}
if (IsNull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = ; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
}