/// <summary> /// 跨sheet复制多行,连合并单元格 /// </summary> /// <param name="worksheet">源sheet</param> /// <param name="targetsheet">目标</param> /// <param name="sourceRowNum">源行值</param> /// <param name="copyRowsCount">复制行数</param> /// <param name="destinationRowNum">目标行值</param> /// <param name="cover">是否以覆盖原来内容,true为覆盖,false为插入</param> public static void CopyRowsOverSheet(ISheet worksheet, ISheet targetsheet, int sourceRowNum, int copyRowsCount, int destinationRowNum, bool cover) { int[,] sm = getAllMergedRegions(worksheet); List <int[]> copyAreaMergedRegions = new List <int[]>(); //遍历一次源sheet的全部合并单元格 for (int i = 0; i < sm.GetLength(0); i++) { if (sm[i, 0] >= sourceRowNum && sm[i, 0] < (sourceRowNum + copyRowsCount))//18年8月30日发现bug:小于等于改为小于 { copyAreaMergedRegions.Add(new int[4] { sm[i, 0] - sourceRowNum, sm[i, 1], sm[i, 2] - sourceRowNum, sm[i, 3] }); } } //int addpoint = destinationRowNum; for (int row = 0; row < copyRowsCount; row++) { //跨sheet复制行 NpoiHelperV1.CopyRowOverSheet(worksheet, targetsheet, sourceRowNum + row, destinationRowNum + row, false, cover); } //将新复制的行进行合并 foreach (int[] cell in copyAreaMergedRegions) { NPOI.SS.Util.CellRangeAddress newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress( cell[0] + destinationRowNum, //顶 cell[2] + destinationRowNum, //底 cell[1], //左 cell[3] //右 ); targetsheet.AddMergedRegion(newCellRangeAddress); } }
/// <summary> /// 复制多行并合拼其中的单元格 /// </summary> /// <param name="workbook"></param> /// <param name="worksheet">目标sheet</param> /// <param name="sourceRowNum">起始行号</param> /// <param name="copyRowsCount">复制行数</param> /// <param name="destinationRowNum">目标行号</param> public static void CopyRows(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int copyRowsCount, int destinationRowNum) { int[,] sm = getAllMergedRegions(worksheet); List <int[]> copyAreaMergedRegions = new List <int[]>(); for (int i = 0; i < sm.GetLength(0); i++) { if (sm[i, 0] >= sourceRowNum && sm[i, 0] < (sourceRowNum + copyRowsCount)) { copyAreaMergedRegions.Add(new int[4] { sm[i, 0] - sourceRowNum, sm[i, 1], sm[i, 2] - sourceRowNum, sm[i, 3] }); } } int addpoint = destinationRowNum; for (int row = 0; row < copyRowsCount; row++) { //复制行 NpoiHelperV1.CopyRowWithoutMergedRegion(workbook, worksheet, sourceRowNum + row, addpoint); addpoint++; } foreach (int[] cell in copyAreaMergedRegions) { NPOI.SS.Util.CellRangeAddress newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress( cell[0] + destinationRowNum, //顶 cell[2] + destinationRowNum, //底 cell[1], //左 cell[3] //右 ); worksheet.AddMergedRegion(newCellRangeAddress); } }