/// <summary>
/// 复制行
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="startRowIndex">起始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <returns></returns>
public static int CopyRows(this ISheet sheet, int startRowIndex, int endRowIndex)
{
int span = endRowIndex - startRowIndex + 1;
int newStartRowIndex = startRowIndex + span;
// 插入空行
sheet.InsertRows(newStartRowIndex, span);
// 复制行
for (var i = startRowIndex; i <= endRowIndex; i++)
{
IRow sourceRow = sheet.GetRow(i);
IRow targetRow = sheet.GetRow(i + span);
targetRow.Height = sourceRow.Height;
targetRow.ZeroHeight = sourceRow.ZeroHeight;
// 复制单元格
foreach (ICell sourceCell in sourceRow.Cells)
{
ICell targetCell = targetRow.GetCell(sourceCell.ColumnIndex);
if (null == targetCell)
{
targetCell = targetRow.CreateCell(sourceCell.ColumnIndex);
}
if (null != sourceCell.CellStyle)
{
targetCell.CellStyle = sourceCell.CellStyle;
}
if (null != sourceCell.CellComment)
{
targetCell.CellComment = sourceCell.CellComment;
}
if (null != sourceCell.Hyperlink)
{
targetCell.Hyperlink = sourceCell.Hyperlink;
}
var cfrs = sourceCell.GetConditionalFormattingRules();// 复制条件样式
if (null != cfrs && cfrs.Length > 0)
{
targetCell.AddConditionalFormattingRules(cfrs);
}
targetCell.SetCellType(sourceCell.CellType);
// 复制值
switch (sourceCell.CellType)
{
case CellType.Numeric:
targetCell.SetCellValue(sourceCell.NumericCellValue);
break;
case CellType.String:
targetCell.SetCellValue(sourceCell.StringCellValue);
break;
case CellType.Formula:
targetCell.SetCellValue(sourceCell.CellFormula);
break;
case CellType.Blank:
targetCell.SetCellValue(sourceCell.StringCellValue);
break;
case CellType.Boolean:
targetCell.SetCellValue(sourceCell.BooleanCellValue);
break;
case CellType.Error:
targetCell.SetCellValue(sourceCell.ErrorCellValue);
break;
}
}
}
// 获取模板行内的合并区域
var regions = sheet.GetMergedRegionInfos(startRowIndex, endRowIndex, null, null);
// 复制合并区域
foreach (var regionInfo in regions)
{
regionInfo.FirstRow += span;
regionInfo.LastRow += span;
sheet.AddMergedRegion(regionInfo);
}
// 获取模板行内的图片
var pictures = sheet.GetAllPictureInfos(startRowIndex, endRowIndex, null, null);
// 复制图片
foreach (var pictureInfo in pictures)
{
pictureInfo.FirstRow += span;
pictureInfo.LastRow += span;
sheet.AddPicture(pictureInfo);
}
return(span);
}
/// <summary>
/// 复制源行到目标行(忽略合并单元格等信息)
/// </summary>
/// <param name="sourceRow"></param>
/// <param name="targetRowIndex"></param>
/// <returns></returns>
public static IRow CopyTo(this IRow sourceRow, int targetRowIndex)
{
if (targetRowIndex == sourceRow.RowNum)
{
throw new ArgumentException("目标行标不能等于源行标。");
}
ISheet currentSheet = sourceRow.Sheet;
IRow targetRow = currentSheet.GetRow(targetRowIndex);
if (targetRow != null)
{
currentSheet.ShiftRows(targetRowIndex, currentSheet.LastRowNum, 1);
}
else
{
targetRow = currentSheet.CreateRow(targetRowIndex);
}
targetRow.Height = sourceRow.Height;
targetRow.ZeroHeight = sourceRow.ZeroHeight;
#region 制单元格
foreach (var sourceCell in sourceRow.Cells)
{
ICell targetCell = targetRow.GetCell(sourceCell.ColumnIndex);
if (null == targetCell)
{
targetCell = targetRow.CreateCell(sourceCell.ColumnIndex);
}
if (null != sourceCell.CellStyle)
{
targetCell.CellStyle = sourceCell.CellStyle;
}
if (null != sourceCell.CellComment)
{
targetCell.CellComment = sourceCell.CellComment;
}
if (null != sourceCell.Hyperlink)
{
targetCell.Hyperlink = sourceCell.Hyperlink;
}
var cfrs = sourceCell.GetConditionalFormattingRules(); //复制条件样式
if (null != cfrs && cfrs.Length > 0)
{
targetCell.AddConditionalFormattingRules(cfrs);
}
targetCell.SetCellType(sourceCell.CellType);
#region 制值
switch (sourceCell.CellType)
{
case CellType.Numeric:
targetCell.SetCellValue(sourceCell.NumericCellValue);
break;
case CellType.String:
targetCell.SetCellValue(sourceCell.RichStringCellValue);
break;
case CellType.Formula:
targetCell.SetCellFormula(sourceCell.CellFormula);
break;
case CellType.Blank:
targetCell.SetCellValue(sourceCell.StringCellValue);
break;
case CellType.Boolean:
targetCell.SetCellValue(sourceCell.BooleanCellValue);
break;
case CellType.Error:
targetCell.SetCellErrorValue(sourceCell.ErrorCellValue);
break;
}
#endregion
}
#endregion
return(targetRow);
}