/// <summary>
/// 插入Excel行
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
/// <param name="row"></param>
private static void MyInsertRow(HSSFSheet sheet, int rowIndex, int count, HSSFRow row)
{
#region 批量移动行
sheet.ShiftRows(
rowIndex, //--开始行
sheet
.LastRowNum, //--结束行
count, //--移动大小(行数)--往下移动
true, //是否复制行高
false, //是否重置行高
true //是否移动批注
);
#endregion
#region 对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
for (int i = rowIndex; i < rowIndex + count - 1; i++)
{
HSSFRow targetRow;
HSSFCell sourceCell;
HSSFCell targetCell;
targetRow = sheet.CreateRow(i + 1);
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
sourceCell = row.GetCell(m);
if (sourceCell == null)
continue;
targetCell = targetRow.CreateCell(m);
targetCell.Encoding = sourceCell.Encoding;
targetCell.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
}
//CopyRow(sourceRow, targetRow);
//Util.CopyRow(sheet, sourceRow, targetRow);
}
HSSFRow firstTargetRow = sheet.GetRow(rowIndex);
HSSFCell firstSourceCell;
HSSFCell firstTargetCell = null;
for (int m = row.FirstCellNum; m < row.LastCellNum; m++)
{
firstSourceCell = row.GetCell(m);
if (firstSourceCell == null)
continue;
firstTargetCell = firstTargetRow.CreateCell(m);
firstTargetCell.Encoding = firstSourceCell.Encoding;
firstTargetCell.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
sheet.AddMergedRegion(new Region(firstTargetRow.RowNum, 3, firstTargetRow.RowNum, 4));
#endregion
}
private void InsertRows(HSSFSheet sheet1, int fromRowIndex, int rowCount)
{
sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false, true);
for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
{
IRow rowSource = sheet1.GetRow(rowIndex + rowCount);
IRow rowInsert = sheet1.CreateRow(rowIndex);
rowInsert.Height = rowSource.Height;
for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
{
ICell cellSource = rowSource.GetCell(colIndex);
ICell cellInsert = rowInsert.CreateCell(colIndex);
if (cellSource != null)
{
cellInsert.CellStyle = cellSource.CellStyle;
}
}
}
}
/// <summary>
/// HSSFRow Copy Command
///
/// Description: Inserts a existing row into a new row, will automatically push down
/// any existing rows. Copy is done cell by cell and supports, and the
/// command tries to copy all properties available (style, merged cells, values, etc...)
/// </summary>
/// <param name="workbook">Workbook containing the worksheet that will be changed</param>
/// <param name="worksheet">WorkSheet containing rows to be copied</param>
/// <param name="sourceRowNum">Source Row Number</param>
/// <param name="destinationRowNum">Destination Row Number</param>
private HSSFRow CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
{
// Get the source / new row
var newRow = (HSSFRow)worksheet.GetRow(destinationRowNum);
var sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
}
else
{
newRow = (HSSFRow)worksheet.CreateRow(destinationRowNum);
}
// Loop through source columns to add to new row
for (var i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
var oldCell = (HSSFCell)sourceRow.GetCell(i);
var newCell = (HSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null) continue;
// Copy style from old cell and apply to new cell
var newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
newCell.CellStyle = newCellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.BLANK:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.BOOLEAN:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.ERROR:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.FORMULA:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.NUMERIC:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.STRING:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (var i = 0; i < worksheet.NumMergedRegions; i++)
{
var cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow != sourceRow.RowNum) continue;
var newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.FirstRow -
cellRangeAddress.LastRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
return newRow;
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="sheet"></param>
/// <param name="row"></param>
static void DeleteRow(HSSFSheet sheet,HSSFRow row)
{
if (row == null) return;
row.RemoveAllCells();
sheet.RemoveRow(row);
sheet.ShiftRows(row.RowNum + 1, sheet.LastRowNum, -1);
}
