NPOI操作Excel(三)--解析Excel

通过前面两篇的基础学习，我们对NPOI有了一定了了解，下面就开始进入实战，解析下面格式的Excel（下面只是列举了几个例子），并保存入库

首先我们先分析一下，要解析这样的Excel，需要把指标【橘色背景和蓝色背景】（作为指标入库）、科目【棕色背景和黄色背景】（作为X轴入库）、数据【乳白色背景和白色背景】（作为Y轴入库）的数据分开入库。

第一张图我们得到的指标毫无疑问应该是第三行从第二列开始到最后一列的数据，而第二张图我们得到的指标应该是非金融企业部门-使用、非金融企业部门-来源、金融机构部门-使用、金融机构部门-来源，以此类推，我们要想取到这样的数据，首先需要把合并行的单元格填充、然后把合并列的数据合并，我们可以通过二维数组来实实现。

由于每个Excel的格式不一样，指标数据的行数，列数也不一样，所以我们要想把数据区分开只能通过背景颜色，把三部分是数据分开并放到三个二维数组里，然后解析入库，由于Excel的背景颜色存在不一样，所以不能写死，通过观察我们可以发现，每个Excel都是从指标行开始有背景颜色到数据行开始变背景颜色，这样我们就可以区分开来，到这里相信聪明的你已经知道怎么做了，下面我们就开始实现吧

1、获取Excel的扩展名并创建工作簿，如果是xls创建HSSFWorkbook工作簿，如果是xlxs创建XSSFWorkbook工作簿

 1     public static void ReadFromExcelFile(string filePath)
 2     {
 3         IWorkbook wk = null;
 4         string extension = System.IO.Path.GetExtension(filePath);//GetExtension获取Excel的扩展名
 5         try
 6         {
 7            FileStream fs = File.OpenRead(filePath);
 8            if (extension.Equals(".xls"))
 9            {                   
10                wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
11            }
12            else
13            {                    
14                wk = new XSSFWorkbook(fs);//把xlsx文件中的数据写入wk中
15            }
16            fs.Close();                
17            sheet = wk.GetSheetAt(0);//读取当前表数据   20            GetIndexRow();//获取【指标、科目、数据】的行数列数
21            ReadData();//读数据并保存到数组中
22            SaveData();//解析数组数据并保存入库
23         }
24         catch (Exception e)
25         {               
26            Console.WriteLine(e.Message); //只在Debug模式下才输出
27         }
28     }

2、获取指标从哪行开始

 1  for (int i = 0; i < sheet.LastRowNum; i++)//sheet.LastRowNum当前表的行数
 2  {
 3     IRow row = sheet.GetRow(i);  //读取当前行数据
 4     if (row != null)
 5     {
 6        if (row.GetCell(0) != null)  //读取该行的第1列数据
 7        {
 8          ICellStyle style = row.GetCell(0).CellStyle;//当前行第一列的样式
 9          row.GetCell(0).SetCellType(CellType.String);//把第一行第一列的值类型转换成string类型
10          short GroundColor = style.FillForegroundColor;//获取当前行第一列的背景色
11          if (i == 0)//若或i=0说明是第一行，没有背景色的
12          {
13             Title = row.GetCell(0).StringCellValue;//获取第一行第一列的值即标题的值
14             TitleColor = GroundColor;//第一行第一列背景色的值付给TitleColor
15             continue;
16          }
17          else//如果不是第一行
18          {
19             if (GroundColor == TitleColor)
20             {
21                if (row.GetCell(0).StringCellValue.Contains("单位"))
22                {
23                   IndexUnit = row.GetCell(0).StringCellValue.Replace("单位：", "").Replace("单位:", "");
24                   continue;
25                 }
26             }
27             else if (GroundColor != TitleColor && IndexColor == 0)//如果GroundColor不等于TitleColor说明改行是指标行
28             {
29                 IndexColor = GroundColor;// 把GroundColor的值赋值给IndexColor
30                 IndexStart = i;//记录改行，改行是指标行的起始行
31                 break;
32             }
33         }
34     }
35   }
36 }

 3、获取指标从哪行结束

 1 for (int i = IndexStart + 1; i < sheet.LastRowNum; i++)
 2 {
 3     IRow row = sheet.GetRow(i);  //读取当前行数据
 4      if (row != null)
 5      {
 6           if (row.GetCell(0) != null)  //读取该行的第1列数据
 7           {
 8                ICellStyle style = row.GetCell(0).CellStyle;
 9                short GroundColor = style.FillForegroundColor;
10                if (IndexColor != GroundColor)
11                {
12                      LeftDataColor = GroundColor;
13                      IndexEnd = i - 1;
14                      break;
15                }
16            }
17       }
18 }

4、获取数据从哪行开始到哪行结束

 1 for (int i = IndexEnd + 1; i < sheet.LastRowNum; i++)
 2 {
 3      DataRowStart = IndexEnd + 1;//数据开始行
 4      IRow row = sheet.GetRow(i);  //读取当前行数据
 5      if (row != null)
 6      {
 7           if (row.GetCell(0) != null)  //读取该行的第1列数据
 8           {
 9                 ICellStyle style = row.GetCell(0).CellStyle;
10                 short GroundColor = style.FillForegroundColor;
11                 if (LeftDataColor != GroundColor)
12                 {
13                       DataRowEnd = i - 1;//数据结束行
14                       break;
15                  }
16            }
17      }
18 }

5、获取科目【左侧】的列数

 1 if (sheet.GetRow(IndexEnd + 1) != null)
 2 {
 3        for (int i = 0; i < sheet.GetRow(IndexEnd + 1).LastCellNum; i++)
 4         {
 5               if (sheet.GetRow(IndexEnd + 1).GetCell(i) != null)
 6                {
 7                      ICellStyle style = sheet.GetRow(IndexEnd + 1).GetCell(i).CellStyle;
 8                       short GroundColor = style.FillForegroundColor;
 9                       sheet.GetRow(IndexEnd + 1).GetCell(i).SetCellType(CellType.String);
10                        if (GroundColor != LeftDataColor)
11                         {
12                             DataLeftCell = i;//科目的列数
13                             break;
14                         }
15                  } 
17            }
18 }

6、把数据保存到数组中【指标数组】

 1 string[,] IndexArray = new string[IndexEnd-IndexStart+1, sheet.GetRow(0).LastCellNum - DataLeftCell];//指标
 2 
 3  4  //循环指标行
 5 for (int r = IndexStart; r <= IndexEnd; r++)
 6 {
 7    IRow row = sheet.GetRow(r);  //读取当前行数据
 8    if (row != null)
 9    {
10       for (int c = DataLeftCell; c <= row.LastCellNum - DataLeftCell; c++)
11       {
12           if (row.GetCell(c) != null)
13           {
14               row.GetCell(c).SetCellType(CellType.String);
15               #region 判断是否是合并单元格
16               if (string.IsNullOrEmpty(row.GetCell(c).StringCellValue))
17               {
18                    ICell cell = row.GetCell(c);
19                    Dimension dimension = new Dimension();
20                    if (IsMergedRegions.IsMergeCell(cell, out dimension))//如果是空判断是否是合并单元格
21                    {
22                         IndexArray[r - IndexStart, c- DataLeftCell] = dimension.DataCell.StringCellValue;//如果是取合并单元格的值
23                    }
24                    else
25                    {
26                         IndexArray[r - IndexStart, c- DataLeftCell] = row.GetCell(c).StringCellValue;//否则取改单元格本身的值
27                    }
28               }
29               else
30               {
31                    IndexArray[r - IndexStart, c- DataLeftCell] = row.GetCell(c).StringCellValue;
32               }
33               #endregion
34           }
35       }
36    }
37 }

7、把数据保存到数组中【科目数组】

 1 string[,]  LeftDataArray = new string[DataRowEnd-DataRowStart+1, DataLeftCell];//科目
 2  for (int r = DataRowStart; r <= DataRowEnd; r++)
 3             {
 4                 IRow row = sheet.GetRow(r);  //读取当前行数据
 5                 if (row != null)
 6                 {
 7                     for (int c = 0; c < DataLeftCell; c++)
 8                     {
 9                         if (row.GetCell(c) != null)
10                         {
11                             row.GetCell(c).SetCellType(CellType.String);
12 
13                             #region 判断是否是合并单元格
14                             if (string.IsNullOrEmpty(row.GetCell(c).StringCellValue))
15                             {
16                                 ICell cell = row.GetCell(c);
17                                 Dimension dimension = new Dimension();
18                                 if (IsMergedRegions.IsMergeCell(cell, out dimension))
19                                 {
20                                     LeftDataArray[r - DataRowStart, c] = dimension.DataCell.StringCellValue;
21                                 }
22                                 else
23                                 {
24                                     LeftDataArray[r - DataRowStart, c] = row.GetCell(c).StringCellValue;
25                                 }
26                             }
27                             else
28                             {
29                                 LeftDataArray[r - DataRowStart, c] = row.GetCell(c).StringCellValue;
30                             }
31                             #endregion
32                         }
33                     }
34                 }
35             }

8、把数据保存到数组中【数据数组】

 1 string[,]  RightDataArray= new string[DataRowEnd - DataRowStart + 1, sheet.GetRow(0).LastCellNum - DataLeftCell];//数据
 2  for (int r = DataRowStart; r <= DataRowEnd; r++)
 3             {
 4                 IRow row = sheet.GetRow(r);  //读取当前行数据
 5                 if (row != null)
 6                 {
 7                     for (int c = DataLeftCell; c < row.LastCellNum; c++)
 8                     {
 9                         if (row.GetCell(c) != null)
10                         {
11                             row.GetCell(c).SetCellType(CellType.String);
12                             RightDataArray[r - DataRowStart, c- DataLeftCell] = row.GetCell(c).StringCellValue;
13                         }
14                     }
15                 }
16             }

9、解析数组保存数据

 1  private static void SaveData()
 2  {
 3        //IndexModel im = new IndexModel();
 4        DataModel dm = new DataModel();
 5        for (int ic = 0; ic < sheet.GetRow(0).LastCellNum - DataLeftCell ; ic++)//循环指标列
 6        {
 7           dm.IndexName = null;
 8           dm.IndexCode = IndexCode++.ToString().PadLeft(4, '0');
 9           #region 获取指标名称
10           for (int ir = 0; ir < IndexEnd - IndexStart + 1; ir++)
11           {
12               if (IndexArray[ir, ic] != null)
13               {
14                    if (dm.IndexName == null)
15                    {
16                        dm.IndexName = IndexArray[ir, ic];                        
17                    }
18                    else
19                    {
20                        if (!dm.IndexName.Contains(IndexArray[ir, ic]))
21                        {
22                           dm.IndexName = dm.IndexName + "_" + IndexArray[ir, ic];//同一列字符串拼接
23                        }
24                    }
25                 }
26            }
27           #endregion
28           //循环得右侧数据
29           for (int rr = 0; rr < DataRowEnd - DataRowStart + 1; rr++)//循环右侧数据的行
30           {
31                 #region 右侧数据
32                 if (RightDataArray[rr, ic] != null)
33                 {
34                     dm.IndexYValue = RightDataArray[rr, ic];
35                 }
36                 #endregion
37                 dm.IndexXValue = null;
38                 //循环得左侧数据
39                 for (int lc = 0; lc < DataLeftCell; lc++)
40                 {
41                     if (LeftDataArray[rr, lc] !=null)
42                     {
43                          if (dm.IndexXValue == null)
44                          {
45                               dm.IndexXValue = LeftDataArray[rr, lc];
46                          }
47                          else
48                          {
49                               if (!dm.IndexXValue.Contains(LeftDataArray[rr, lc]))
50                               {
51                                   dm.IndexXValue = dm.IndexXValue + "_" + LeftDataArray[rr, lc];
52                               }
53                           }                              
54                       }
55                   }
56             Console.WriteLine($"指标名称:{dm.IndexName} 指标编码:{dm.IndexCode} IndexXValue:{dm.IndexXValue} IndexYValue:{dm.IndexYValue}");
57           }
58       }
59  }

 10、上面用到的方法IsMergeCell判断是否是合并单元格

 1 /// <summary>
 2 /// 判断指定单元格是否为合并单元格，并且输出该单元格的维度
 3 /// </summary>
 4 /// <param name="cell">单元格</param>
 5 /// <param name="dimension">单元格维度</param>
 6 /// <returns>返回是否为合并单元格的布尔(Boolean)值</returns>
 7 public static bool IsMergeCell(this ICell cell, out Dimension dimension)
 8 {
 9     return cell.Sheet.IsMergeCell(cell.RowIndex, cell.ColumnIndex, out dimension);
10 }

 

posted @ 2017-09-04 14:14  念宇儿  阅读(5329)  评论(0)  编辑  收藏  举报