/// <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);
}
}
