之前有發現外國寫的複製列方法，發現很多方法是沒效果的，因此自己改寫程式，可有效的複製並覆蓋一列資料。
之前有發現外國寫的複製列方法，發現很多方法是沒效果的，因此自己改寫程式，可有效的複製並覆蓋一列資料。
///
/// 複製一整列
///
private void CopyRow(ref HSSFSheet worksheet, int sourceRowNum, int destinationRowNum
, bool IsCoverRow = false, bool IsRemoveSrcRow = false
, bool copyRowHeight = true, bool resetOriginalRowHeight = true)
{
HSSFRow newRow = worksheet.GetRow(destinationRowNum) as HSSFRow;
HSSFRow sourceRow = worksheet.GetRow(sourceRowNum) as HSSFRow;
HSSFCell oldCell, newCell;
int i;
if (newRow == null)
newRow = worksheet.CreateRow(destinationRowNum) as HSSFRow;
else
{
if (!IsCoverRow)
ShiftRows(ref worksheet, destinationRowNum, worksheet.LastRowNum, 1);
}
// Loop through source columns to add to new row
for (i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
oldCell = sourceRow.GetCell(i) as HSSFCell;
newCell = newRow.GetCell(i) as HSSFCell;
if (newCell == null)
newCell = newRow.CreateCell(i) as HSSFCell;
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// 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 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
CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
for (i = 0; i < worksheet.NumMergedRegions; i++)
{
cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow -
cellRangeAddress.FirstRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
}
//複製行高到新列
if (copyRowHeight)
newRow.Height = sourceRow.Height;
//重製原始列行高
if (resetOriginalRowHeight)
sourceRow.Height = worksheet.DefaultRowHeight;
//清掉原列
if (IsRemoveSrcRow == true)
worksheet.RemoveRow(sourceRow);
}原本的資料列移動也可以靠複製列功能達到更好的移動效果。
///
/// 客製ShiftRows，以保留原本格式
///
private void ShiftRows(ref HSSFSheet sheet, int startrow, int endrow, int n
, bool copyRowHeight = true, bool resetOriginalRowHeight = true)
{
if (n == 0) return;
int i;
if (n > 0)
{
for (i = endrow; i >= startrow; i--)
if (sheet.GetRow(i) != null)
CopyRow(ref sheet, i, i + n, true, true, copyRowHeight, resetOriginalRowHeight);
}
else
{
for (i = startrow; i <= endrow; i++)
if (sheet.GetRow(i) != null)
CopyRow(ref sheet, i, i + n, true, true, copyRowHeight, resetOriginalRowHeight);
}
}