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