您的位置: 首页  >  IT文章  >  NPOI用Excel模板导出Excel文件（三）几个插入、删除以及复制方法

NPOI用Excel模板导出Excel文件（三）几个插入、删除以及复制方法

分类: IT文章 • 2024-06-10 16:49:00

NPOI的插入行与删除行，从感觉上来说，没有直接使用COM组件里的添加删除好用，在做的过程中，发现图表并不会随着插入的行而位置向下移。也就是说，图中饼图的位置是12行到27行，当我插入40行数据时，饼图的位置，还是在12行到27行。

不过，也能满足我们的大多数需求了，这里简单的写几个方法好，其实这几个方法都是网上其他人的，我也是汇总测试了一下，还是有用的。

第一种插入方法：

 1 private void MyInsertRow(XSSFSheet sheet, int RowIndex, int RowCount, IRow RowStyle)
 2         {
 3             
 4                 #region 批量移动行
 5                 sheet.ShiftRows(
 6                     RowIndex,                                 //--开始行
 7                     sheet.LastRowNum,                      //--结束行
 8                     RowCount,                             //--移动大小(行数)--往下移动
 9                     true,                                  //是否复制行高
10                     false//,                               //是否重置行高
11                          //true                                 //是否移动批注
12                 );
13                 #endregion
14 
15                 #region //对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
16                 for (int i = RowIndex; i < RowIndex + RowCount - 1; i++)
17                 {
18                     IRow targetRow = null;
19                     ICell sourceCell = null;
20                     ICell targetCell = null;
21 
22                     targetRow = sheet.CreateRow(i + 1);
23 
24                     for (int m = RowStyle.FirstCellNum; m < RowStyle.LastCellNum; m++)
25                     {
26                         sourceCell = RowStyle.GetCell(m);
27                         if (sourceCell == null)
28                             continue;
29                         targetCell = targetRow.CreateCell(m);
30                         //targetCell.Encoding = sourceCell.Encoding;
31                         targetCell.CellStyle = sourceCell.CellStyle;
32                         targetCell.SetCellType(sourceCell.CellType);
33                     }
34                     //CopyRow(sourceRow, targetRow);
35                     //Util.CopyRow(sheet, sourceRow, targetRow);
36                 }
37 
38                 //IRow firstTargetRow = sheet.GetRow(RowIndex);
39                 IRow firstTargetRow1 = sheet.GetRow(6);
40                 IRow firstTargetRow2 = sheet.GetRow(5);
41                 IRow firstTargetRow3 = sheet.GetRow(7);
42                 IRow firstTargetRow = RowStyle;
43                 ICell firstSourceCell = null;
44                 ICell firstTargetCell = null;
45 
46                 for (int m = RowStyle.FirstCellNum; m < RowStyle.LastCellNum; m++)
47                 {
48                     firstSourceCell = RowStyle.GetCell(m);
49                     if (firstSourceCell == null)
50                         continue;
51                     firstTargetCell = firstTargetRow.CreateCell(m);
52 
53                     //firstTargetCell.Encoding = firstSourceCell.Encoding;
54                     firstTargetCell.CellStyle = firstSourceCell.CellStyle;
55                     firstTargetCell.SetCellType(firstSourceCell.CellType);
56                     if (firstTargetCell.CellType == CellType.Formula)
57                     firstTargetCell.SetCellFormula(firstSourceCell.CellFormula);
58             }
59                 #endregion
60             
61         }
View Code

第二种插入方法：

 1 private void insertRow(XSSFWorkbook wb, XSSFSheet sheet, int starRow, int rows)
 2         {
 3             /*
 4              * ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool resetOriginalRowHeight); 
 5              * 
 6              * startRow 开始行
 7              * endRow 结束行
 8              * n 移动行数
 9              * copyRowHeight 复制的行是否高度在移
10              * resetOriginalRowHeight 是否设置为默认的原始行的高度
11              * 
12              */
13 
14             sheet.ShiftRows(starRow + 1, sheet.LastRowNum, rows, true, true);
15             
16             starRow = starRow - 1;
17 
18             for (int i = 0; i < rows; i++)
19             {
20 
21                 XSSFRow sourceRow = null;
22                 XSSFRow targetRow = null;
23                 XSSFCell sourceCell = null;
24                 XSSFCell targetCell = null;
25 
26                 short m;
27 
28                 starRow = starRow + 1;
29                 sourceRow = (XSSFRow)sheet.GetRow(starRow);
30                 targetRow = (XSSFRow)sheet.CreateRow(starRow + 1);
31                 targetRow.HeightInPoints = sourceRow.HeightInPoints;
32 
33                 for (m = (short)sourceRow.FirstCellNum; m < 8; m++)
34                 {
35 
36                     sourceCell = (XSSFCell)sourceRow.GetCell(m);
37                     targetCell = (XSSFCell)targetRow.CreateCell(m);
38 
39                     //targetCell.Encoding = sourceCell.Encoding;
40                     targetCell.CellStyle = sourceCell.CellStyle;
41                     targetCell.SetCellType(sourceCell.CellType);
42 
43                 }
44             }
45 
46         }
View Code

第三种插入方法：

 1 public void insertRow(XSSFSheet sheet, int RowIndex, int RowCount, IRow RowStyle)
 2         {
 3             IRow sourceRow = RowStyle;
 4 
 5             for (int i = 0; i < RowCount - 1; i++)
 6             {
 7 
 8                 IRow row = sheet.CreateRow(RowIndex + i);
 9                 row.Height = sourceRow.Height;
10                 for (int m = sourceRow.FirstCellNum; m < 8; m++)
11                 {
12                     ICell cell = row.CreateCell(m);
13                     // cell.SetCellValue("0");
14                     ICellStyle cellStyle = sourceRow.Cells[m].CellStyle;
15                     cellStyle.Alignment = HorizontalAlignment.Center;
16                     cell.CellStyle = cellStyle;
17 
18                     cell.SetCellType(sourceRow.Cells[m].CellType);
19                     if (cell.CellType == CellType.Formula)
20                         cell.SetCellFormula(sourceRow.Cells[m].CellFormula);
21                 }
22 
23             }
24         }
View Code

前两种方法都使用了ShiftRows这个函数，此外，当此函数的第三个值为-1时，即可删除行，如下：

sheet.ShiftRows(i, i + 1, -1);

参数必须为-1，尝试过其他负值，没有发生作用。

复制的方法：

 1 public void CopyRange(ISheet sheet, int fromRowIndex, int fromColIndex, int toRowIndex, int toColIndex, bool onlyData)
 2         {
 3              IRow sourceRow = sheet.GetRow(fromRowIndex); 
 4              if (sourceRow != null )
 5             {
 6                 ICell sourceCell = sourceRow.GetCell(fromColIndex);
 7                 if (sourceCell != null)
 8                 {
 9                     IRow changingRow = null;
10                     ICell changingCell = null;
11                     changingRow = sheet.GetRow(toRowIndex);
12                     if (changingRow == null)
13                         changingRow = sheet.CreateRow(toRowIndex);
14                     changingCell = changingRow.GetCell(toColIndex);
15                     if (changingCell == null)
16                         changingCell = changingRow.CreateCell(toColIndex);
17 
18                     if (onlyData)//仅数据
19                     {
20                         //对单元格的值赋值
21                         changingCell.SetCellValue(sourceCell.StringCellValue);
22                     }
23                     else         //非仅数据
24                     {
25                         //单元格的编码
26                         //changingCell.Encoding = sourceCell.Encoding;
27                         //单元格的格式
28                         changingCell.SetCellType(sourceCell.CellType); ;
29                         changingCell.CellStyle = sourceCell.CellStyle;
30                         //单元格的公式
31                         if (sourceCell.CellType != CellType.Formula)
32                             changingCell.SetCellValue(sourceCell.StringCellValue);
33                         else if (fromRowIndex == 5 || fromRowIndex == 6)
34                             changingCell.SetCellFormula("F" + toRowIndex + "/E" + toRowIndex);
35                         else if (fromRowIndex == 7 && fromColIndex == 4)
36                             changingCell.SetCellFormula("SUM(E" + (toRowIndex - 2) + ":E" + (toRowIndex - 1) + ")");
37                         else if (fromRowIndex == 7 && fromColIndex == 5)
38                             changingCell.SetCellFormula("SUM(F" + (toRowIndex - 2) + ":F" + (toRowIndex - 1) + ")");
39                         else if (fromRowIndex == 7 && fromColIndex == 6)
40                             changingCell.SetCellFormula("F" + toRowIndex + "/E" + toRowIndex);
41                     }
42                 }                 
43              }
44          }
View Code

相关推荐