NPOI用Excel模板导出Excel文件（三）几个插入、删除以及复制方法
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 }
第二种插入方法：
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 }
第三种插入方法：
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 }
前两种方法都使用了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 }
