/**
* Creates a map (i.e. two-dimensional array) filled with ranges. Allow fast
* retrieving {@link CellRangeAddress} of any cell, if cell is contained in
* range.
*
* @see #getMergedRange(CellRangeAddress[][], int, int)
*/
public static CellRangeAddress[][] BuildMergedRangesMap(HSSFSheet sheet)
{
CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][];
for ( int m = 0; m < sheet.NumMergedRegions; m++ )
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion( m );
int requiredHeight = cellRangeAddress.LastRow + 1;
if ( mergedRanges.Length < requiredHeight )
{
CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][];
Array.Copy( mergedRanges, 0, newArray, 0, mergedRanges.Length );
mergedRanges = newArray;
}
for ( int r = cellRangeAddress.FirstRow; r <= cellRangeAddress.LastRow; r++ )
{
int requiredWidth = cellRangeAddress.LastColumn + 1;
CellRangeAddress[] rowMerged = mergedRanges[r];
if ( rowMerged == null )
{
rowMerged = new CellRangeAddress[requiredWidth];
mergedRanges[r] = rowMerged;
}
else
{
int rowMergedLength = rowMerged.Length;
if ( rowMergedLength < requiredWidth )
{
CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth];
Array.Copy(rowMerged, 0, newRow, 0,rowMergedLength );
mergedRanges[r] = newRow;
rowMerged = newRow;
}
}
//Arrays.Fill( rowMerged, cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn + 1, cellRangeAddress );
for (int i = cellRangeAddress.FirstColumn; i < cellRangeAddress.LastColumn + 1; i++)
{
rowMerged[i] = cellRangeAddress;
}
}
}
return mergedRanges;
}
public static CellRangeAddress GetMergedRegion(HSSFSheet sheet, int rowNum, short cellNum)
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
if (rowNum >= merged.FirstRow && rowNum <= merged.LastRow)
{
if (cellNum >= merged.FirstColumn && cellNum <= merged.LastColumn)
{
return merged;
}
}
}
return null;
}
/// <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;
}
