不懂事的小男孩

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

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

  1 /// <summary>读取excel，
  2          /// 默认第一行为表头
  3          /// </summary>
  4          /// <param name="strFileName">excel文档绝对路径</param>
  5          /// <param name="rowIndex">内容行偏移量，第一行为表头，内容行从第二行开始则为1</param>
  6          /// <returns></returns>
  7          public static DataTable Import(string strFileName, int rowIndex)
  8          {
  9              DataTable dt = new DataTable();
 10  
 11              IWorkbook hssfworkbook;
 12              using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
 13              {
 14                  hssfworkbook = WorkbookFactory.Create(file);
 15              }
 16              ISheet sheet = hssfworkbook.GetSheetAt(0);
 17  
 18              IRow headRow = sheet.GetRow(0);
 19              if (headRow != null)
 20              {
 21                  int colCount = headRow.LastCellNum;
 22                  for (int i = 0; i < colCount; i++)
 23                  {
 24                      dt.Columns.Add("COL_" + i);
 25                  }
 26              }
 27  
 28              for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++)
 29              {
 30                  IRow row = sheet.GetRow(i);
 31                  bool emptyRow = true;
 32                  object[] itemArray = null;
 33  
 34                  if (row != null)
 35                  {
 36                      itemArray = new object[row.LastCellNum];
 37  
 38                      for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
 39                      {
 40  
 41                          if (row.GetCell(j) != null)
 42                          {
 43  
 44                              switch (row.GetCell(j).CellType)
 45                              {
 46                                  case CellType.NUMERIC:
 47                                      if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
 48                                      {
 49                                          itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
 50                                      }
 51                                      else//其他数字类型
 52                                      {
 53                                          itemArray[j] = row.GetCell(j).NumericCellValue;
 54                                      }
 55                                      break;
 56                                  case CellType.BLANK:
 57                                      itemArray[j] = string.Empty;
 58                                      break;
 59                                  case CellType.FORMULA:
 60                                      if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx")
 61                                      {
 62                                          XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook);
 63                                          if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
 64                                          {
 65                                               if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
 66                                               {
 67                                                   itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
 68                                               }
 69                                               else//其他数字类型
 70                                               {
 71                                                   itemArray[j] = row.GetCell(j).NumericCellValue;
 72                                               }
 73                                          }
 74                                          else
 75                                          {
 76                                              itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
 77                                          }
 78                                      }
 79                                      else
 80                                      {
 81                                          HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook);
 82                                          if (eva.Evaluate(row.GetCell(j)).CellType == CellType.NUMERIC)
 83                                          {
 84                                               if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型
 85                                               {
 86                                                   itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
 87                                               }
 88                                               else//其他数字类型
 89                                               {
 90                                                   itemArray[j] = row.GetCell(j).NumericCellValue;
 91                                               }
 92                                          }
 93                                          else
 94                                          {
 95                                              itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
 96                                          }
 97                                      }
 98                                      break;
 99                                  default:
100                                      itemArray[j] = row.GetCell(j).StringCellValue;
101                                      break;
102  
103                              }
104  
105                              if (itemArray[j] != null && !string.IsNullOrEmpty(itemArray[j].ToString().Trim()))
106                              {
107                                  emptyRow = false;
108                              }
109                          }
110                      }
111                  }
112  
113                  //非空数据行数据添加到DataTable
114                  if (!emptyRow)
115                  {
116                      dt.Rows.Add(itemArray);
117                  }
118              }
119              return dt;
120          }

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

 1 protected void RemoveEmpty(DataTable dt)
 2         {
 3             List<DataRow> removelist = new List<DataRow>();
 4             for (int i = 0; i < dt.Rows.Count; i++)
 5             {
 6                 bool IsNull = true;
 7                 for (int j = 0; j < dt.Columns.Count; j++)
 8                 {
 9                     if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
10                     {
11                         IsNull = false;
12                     }
13                 }
14                 if (IsNull)
15                 {
16                     removelist.Add(dt.Rows[i]);
17                 }
18             }
19             for (int i = 0; i < removelist.Count; i++)
20             {
21                 dt.Rows.Remove(removelist[i]);
22             }
23         }
View Code

 

posted on 2017-05-08 09:39  不懂事的小男孩  阅读(7152)  评论(0)  编辑  收藏  举报