List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getMergedRegion
@Override public CellRangeAddress getMergedRegion(int index)
From source file:com.cms.utils.ExcelReader.java
public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) { for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress merged = (CellRangeAddress) sheet.getMergedRegion(i); if (merged.isInRange(rowNum, cellNum)) { return merged; }/*from w w w . ja v a2 s . c om*/ } return null; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return;/* w ww . ja v a 2s .c o m*/ dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixLeftBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) { if (columnIndex > 1) { fixLeftCell(sheet, resultCell.getRowIndex(), columnIndex - 1, cellStyle); // fix merged left border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); for (int leftIndex = firstRow; leftIndex <= lastRow; leftIndex++) { fixLeftCell(sheet, leftIndex, columnIndex - 1, cellStyle); }/*from ww w. j a va2 s .co m*/ break; } } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixRightBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) { fixRightCell(sheet, resultCell.getRowIndex(), columnIndex + 1, cellStyle); // fix merged right border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int regionWidth = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1; for (int rightIndex = firstRow; rightIndex <= lastRow; rightIndex++) { fixRightCell(sheet, rightIndex, columnIndex + regionWidth, cellStyle); }/*from w w w. java 2 s. com*/ break; } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixUpBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) {/*from w w w . j a v a2 s . co m*/ if (rowIndex > 0) { // fix simple up border fixUpCell(sheet, rowIndex - 1, columnIndex, cellStyle); // fix merged up border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstColumn = mergedRegion.getFirstColumn(); int lastColumn = mergedRegion.getLastColumn(); for (int upIndex = firstColumn; upIndex <= lastColumn; upIndex++) { fixUpCell(sheet, rowIndex - 1, upIndex, cellStyle); } break; } } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixDownBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) {/* w ww . j a v a 2 s . co m*/ // fix simple down border fixDownCell(sheet, rowIndex + 1, columnIndex, cellStyle); // fix merged down border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstColumn = mergedRegion.getFirstColumn(); int lastColumn = mergedRegion.getLastColumn(); int regionHeight = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1; for (int downIndex = firstColumn; downIndex <= lastColumn; downIndex++) { fixDownCell(sheet, rowIndex + regionHeight, downIndex, cellStyle); } break; } } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method creates mapping [rangeName : List<CellRangeAddress>]. * List contains all merge regions for this named range. * Attention: if merged regions writes wrong - look on methods isMergeRegionInsideNamedRange or isNamedRangeInsideMergeRegion * todo: how to recognize if merge region must be copied with named range * * @param currentSheet Sheet which contains merge regions *///from w ww . j a v a2 s.c o m protected void initMergeRegions(HSSFSheet currentSheet) { int rangeNumber = templateWorkbook.getNumberOfNames(); for (int i = 0; i < rangeNumber; i++) { HSSFName aNamedRange = templateWorkbook.getNameAt(i); String refersToFormula = aNamedRange.getRefersToFormula(); if (!AreaReference.isContiguous(refersToFormula)) { continue; } AreaReference aref = new AreaReference(refersToFormula); Integer rangeFirstRow = aref.getFirstCell().getRow(); Integer rangeFirstColumn = (int) aref.getFirstCell().getCol(); Integer rangeLastRow = aref.getLastCell().getRow(); Integer rangeLastColumn = (int) aref.getLastCell().getCol(); for (int j = 0; j < currentSheet.getNumMergedRegions(); j++) { CellRangeAddress mergedRegion = currentSheet.getMergedRegion(j); if (mergedRegion != null) { Integer regionFirstRow = mergedRegion.getFirstRow(); Integer regionFirstColumn = mergedRegion.getFirstColumn(); Integer regionLastRow = mergedRegion.getLastRow(); Integer regionLastColumn = mergedRegion.getLastColumn(); boolean mergedInsideNamed = isMergeRegionInsideNamedRange(rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn); boolean namedInsideMerged = isNamedRangeInsideMergeRegion(rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn); if (mergedInsideNamed || namedInsideMerged) { String name = aNamedRange.getNameName(); SheetRange sheetRange = new SheetRange(mergedRegion, currentSheet.getSheetName()); if (mergeRegionsForRangeNames.get(name) == null) { ArrayList<SheetRange> list = new ArrayList<SheetRange>(); list.add(sheetRange); mergeRegionsForRangeNames.put(name, list); } else { mergeRegionsForRangeNames.get(name).add(sheetRange); } } } } } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Create new merge regions in result sheet identically to range's merge regions from template. * Not support copy of frames and rules/* w w w.j a va2 s . co m*/ * * @param resultSheet - result sheet * @param rangeName - range name * @param firstTargetRangeRow - first column of target range * @param firstTargetRangeColumn - first column of target range */ protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName, int firstTargetRangeRow, int firstTargetRangeColumn) { int rangeNameIdx = templateWorkbook.getNameIndex(rangeName); if (rangeNameIdx == -1) return; HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx); AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula()); int column = aref.getFirstCell().getCol(); int row = aref.getFirstCell().getRow(); List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName); if (regionsList != null) for (SheetRange sheetRange : regionsList) { if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) { CellRangeAddress cra = sheetRange.getCellRangeAddress(); if (cra != null) { int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1; int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1; int regionVOffset = cra.getFirstRow() - row; int regionHOffset = cra.getFirstColumn() - column; CellRangeAddress newRegion = cra.copy(); newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn); newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn); newRegion.setFirstRow(regionVOffset + firstTargetRangeRow); newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow); boolean skipRegion = false; for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) { CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex); if (!intersects(newRegion, mergedRegion)) { continue; } skipRegion = true; } if (!skipRegion) { resultSheet.addMergedRegion(newRegion); } } } } }
From source file:com.report.excel.ExcelToHtmlUtils.java
License:Apache License
/** * 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./* www . j a v a 2s . c om*/ * * @see #getMergedRange(CellRangeAddress[][], int, int) */ public static CellRangeAddress[][] buildMergedRangesMap(HSSFSheet sheet) { CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][]; for (int m = 0; m < sheet.getNumMergedRegions(); m++) { final CellRangeAddress cellRangeAddress = sheet.getMergedRegion(m); final int requiredHeight = cellRangeAddress.getLastRow() + 1; if (mergedRanges.length < requiredHeight) { CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][]; System.arraycopy(mergedRanges, 0, newArray, 0, mergedRanges.length); mergedRanges = newArray; } for (int r = cellRangeAddress.getFirstRow(); r <= cellRangeAddress.getLastRow(); r++) { final int requiredWidth = cellRangeAddress.getLastColumn() + 1; CellRangeAddress[] rowMerged = mergedRanges[r]; if (rowMerged == null) { rowMerged = new CellRangeAddress[requiredWidth]; mergedRanges[r] = rowMerged; } else { final int rowMergedLength = rowMerged.length; if (rowMergedLength < requiredWidth) { final CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth]; System.arraycopy(rowMerged, 0, newRow, 0, rowMergedLength); mergedRanges[r] = newRow; rowMerged = newRow; } } Arrays.fill(rowMerged, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn() + 1, cellRangeAddress); } } return mergedRanges; }
From source file:Import.Utils.XSSFConvert.java
/** * Rcupre les informations de fusion des cellules dans la sheet source * pour les appliquer la sheet destination... Rcupre toutes les zones * merged dans la sheet source et regarde pour chacune d'elle si elle se * trouve dans la current row que nous traitons. Si oui, retourne l'objet * CellRangeAddress./*from w ww .j ava 2 s . c o m*/ * * @param sheet the sheet containing the data. * @param rowNum the num of the row to copy. * @param cellNum the num of the cell to copy. * @return the CellRangeAddress created. */ public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) { for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress merged = sheet.getMergedRegion(i); if (merged.isInRange(rowNum, cellNum)) { return merged; } } return null; }