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

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(0);
 
             IRow headRow = sheet.GetRow(0);
             if (headRow != null)
             {
                 int colCount = headRow.LastCellNum;
                 for (int i = 0; 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 = 0; i < dt.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; 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 = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
        }