List of usage examples for org.apache.poi.ss.usermodel Sheet getNumMergedRegions
int getNumMergedRegions();
From source file:ExampleClass.java
public static int getNbOfMergedRegions(Sheet sheet, int row) { int count = 0; for (int i = 0; i < sheet.getNumMergedRegions(); ++i) { CellRangeAddress range = sheet.getMergedRegion(i); if (range.getFirstRow() <= row && range.getLastRow() >= row) { ++count;//from www . jav a 2 s. c o m } } return count; }
From source file:bad.robot.excel.row.CopyRow.java
License:Apache License
private static void copyAnyMergedRegions(Sheet worksheet, Row sourceRow, Row newRow) { for (int i = 0; i < worksheet.getNumMergedRegions(); i++) copyMergeRegion(worksheet, sourceRow, newRow, worksheet.getMergedRegion(i)); }
From source file:com.compassplus.gui.MainForm.java
private void removeRow(Sheet sheet, int rowIndex, Workbook wb, boolean debug) { ArrayList<CellRangeAddress> cras = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { cras.add(sheet.getMergedRegion(i)); }// w w w.j a v a2 s.c o m while (sheet.getNumMergedRegions() > 0) { sheet.removeMergedRegion(0); } int lastRowNum = sheet.getLastRowNum(); if (debug) { //System.out.println("lastRowNum = " + lastRowNum); //System.out.println("rowIndex = " + rowIndex); } if (rowIndex >= 0 && rowIndex < lastRowNum) { Row removingRow = sheet.getRow(rowIndex); sheet.removeRow(removingRow); sheet.shiftRows(rowIndex + 1, lastRowNum, -1); } if (rowIndex == lastRowNum) { Row removingRow = sheet.getRow(rowIndex); if (removingRow != null) { sheet.removeRow(removingRow); } } for (CellRangeAddress cra : cras) { if (rowIndex >= cra.getFirstRow() && rowIndex <= cra.getLastRow() && cra.getFirstRow() != cra.getLastRow()) { cra.setLastRow(cra.getLastRow() - 1); sheet.addMergedRegion(cra); } else if (rowIndex < cra.getFirstRow()) { cra.setFirstRow(cra.getFirstRow() - 1); cra.setLastRow(cra.getLastRow() - 1); sheet.addMergedRegion(cra); } else if (rowIndex > cra.getLastRow()) { sheet.addMergedRegion(cra); } } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) { ColumnGroup cg = table.getColumnGroup(facetType); List<UIComponent> headerComponentList = null; if (cg != null) { headerComponentList = cg.getChildren(); }//from ww w. j a v a 2 s. c o m if (headerComponentList != null) { for (UIComponent component : headerComponentList) { if (component instanceof org.primefaces.component.row.Row) { org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component; int sheetRowIndex = sheet.getLastRowNum() + 1; Row xlRow = sheet.createRow(sheetRowIndex); int i = 0; for (UIComponent rowComponent : row.getChildren()) { UIColumn column = (UIColumn) rowComponent; String value = null; if (facetType.equalsIgnoreCase("header")) { value = column.getHeaderText(); } else { value = column.getFooterText(); } int rowSpan = column.getRowspan(); int colSpan = column.getColspan(); Cell cell = xlRow.getCell(i); if (rowSpan > 1 || colSpan > 1) { if (rowSpan > 1) { cell = xlRow.createCell((short) i); Boolean rowSpanFlag = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { rowSpanFlag = true; } } if (!rowSpanFlag) { cell.setCellValue(value); cell.setCellStyle(facetStyle); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex + (rowSpan - 1), //last row (0-based) i, //first column (0-based) i //last column (0-based) )); } } if (colSpan > 1) { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellValue(value); cell.setCellStyle(facetStyle); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) i, //first column (0-based) i + (colSpan - 1) //last column (0-based) )); i = i + colSpan - 1; } } else { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellValue(value); cell.setCellStyle(facetStyle); } i++; } } } } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) { ColumnGroup cg = table.getColumnGroup(facetType); List<UIComponent> headerComponentList = null; if (cg != null) { headerComponentList = cg.getChildren(); }//from w ww.j a v a 2 s . co m if (headerComponentList != null) { for (UIComponent component : headerComponentList) { if (component instanceof org.primefaces.component.row.Row) { org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component; int sheetRowIndex = sheet.getLastRowNum() + 1; Row xlRow = sheet.createRow(sheetRowIndex); int i = 0; for (UIComponent rowComponent : row.getChildren()) { UIColumn column = (UIColumn) rowComponent; String value = null; if (facetType.equalsIgnoreCase("header")) { value = column.getHeaderText(); } else { value = column.getFooterText(); } int rowSpan = column.getRowspan(); int colSpan = column.getColspan(); Cell cell = xlRow.getCell(i); if (rowSpan > 1 || colSpan > 1) { if (rowSpan > 1) { cell = xlRow.createCell((short) i); Boolean rowSpanFlag = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { rowSpanFlag = true; } } if (!rowSpanFlag) { cell.setCellStyle(cellStyle); cell.setCellValue(value); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex + rowSpan - 1, //last row (0-based) i, //first column (0-based) i //last column (0-based) )); } } if (colSpan > 1) { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellStyle(cellStyle); cell.setCellValue(value); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) i, //first column (0-based) i + colSpan - 1 //last column (0-based) )); i = i + colSpan - 1; } } else { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellValue(value); cell.setCellStyle(facetStyle); } i++; } } } } }
From source file:com.dituiba.excel.ExcelUtility.java
License:Apache License
public static void copyRows(Sheet st, int startRow, int endRow, int pPosition) { int pStartRow = startRow; int pEndRow = endRow; int targetRowFrom; int targetRowTo; int columnCount; CellRangeAddress region = null;/*from ww w .j a va 2 s . co m*/ int i; int j; for (i = 0; i < st.getNumMergedRegions(); i++) { region = st.getMergedRegion(i); if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) { targetRowFrom = region.getFirstRow() - pStartRow + pPosition; targetRowTo = region.getLastRow() - pStartRow + pPosition; CellRangeAddress newRegion = region.copy(); newRegion.setFirstRow(targetRowFrom); newRegion.setFirstColumn(region.getFirstColumn()); newRegion.setLastRow(targetRowTo); newRegion.setLastColumn(region.getLastColumn()); st.addMergedRegion(newRegion); } } //set the column height and value for (i = pStartRow; i <= pEndRow; i++) { Row sourceRow = st.getRow(i); columnCount = sourceRow.getLastCellNum(); if (sourceRow != null) { Row newRow = st.createRow(pPosition + i); newRow.setHeight(sourceRow.getHeight()); for (j = 0; j < columnCount; j++) { Cell templateCell = sourceRow.getCell(j); if (templateCell != null) { Cell newCell = newRow.createCell(j); copyCell(templateCell, newCell); } } } } }
From source file:com.eyeq.pivot4j.export.poi.ExcelExporterIT.java
License:Common Public License
/** * @param format//from www .j a v a 2 s .c o m * @param showParentMember * @param showDimensionTitle * @param hideSpans * @param rows * @param mergedRegions * @throws IOException * @throws InvalidFormatException */ protected void testExport(Format format, boolean showParentMember, boolean showDimensionTitle, boolean hideSpans, int rows, int mergedRegions) throws IOException, InvalidFormatException { OutputStream out = null; File file = File.createTempFile("pivot4j-", "." + format.getExtension()); if (deleteTestFile) { file.deleteOnExit(); } try { out = new FileOutputStream(file); ExcelExporter exporter = new ExcelExporter(out); exporter.setFormat(format); exporter.setShowParentMembers(showParentMember); exporter.setShowDimensionTitle(showDimensionTitle); exporter.setHideSpans(hideSpans); exporter.render(getPivotModel()); } finally { out.flush(); IOUtils.closeQuietly(out); } Workbook workbook = WorkbookFactory.create(file); assertThat("Workbook cannot be null.", workbook, is(notNullValue())); Sheet sheet = workbook.getSheetAt(0); assertThat("Worksheet cannot be null.", sheet, is(notNullValue())); assertThat("Invalid worksheet name.", sheet.getSheetName(), is(equalTo("Sales"))); assertThat("Wrong number of rows.", sheet.getLastRowNum(), is(equalTo(rows))); assertThat("Wrong number of merged regions.", sheet.getNumMergedRegions(), is(equalTo(mergedRegions))); }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell templateCell = dataObject.templateCell; HSSFCell resultCell = dataObject.resultCell; BandData bandData = dataObject.bandData; HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook(); HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook(); String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); String styleName = (String) bandData.getParameterValue(paramName); if (styleName == null) continue; HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName); if (cellStyle == null) continue; HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle); if (resultStyle == null) { HSSFCellStyle newStyle = resultWorkbook.createCellStyle(); // color newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor()); newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor()); newStyle.setFillPattern(cellStyle.getFillPattern()); // borders newStyle.setBorderLeft(cellStyle.getBorderLeft()); newStyle.setBorderRight(cellStyle.getBorderRight()); newStyle.setBorderTop(cellStyle.getBorderTop()); newStyle.setBorderBottom(cellStyle.getBorderBottom()); // border colors newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor()); newStyle.setRightBorderColor(cellStyle.getRightBorderColor()); newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor()); newStyle.setTopBorderColor(cellStyle.getTopBorderColor()); // alignment newStyle.setAlignment(cellStyle.getAlignment()); newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment()); // misc DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat(); newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString())); newStyle.setHidden(cellStyle.getHidden()); newStyle.setLocked(cellStyle.getLocked()); newStyle.setIndention(cellStyle.getIndention()); newStyle.setRotation(cellStyle.getRotation()); newStyle.setWrapText(cellStyle.getWrapText()); // font HSSFFont cellFont = cellStyle.getFont(templateWorkbook); HSSFFont newFont = fontCache.getFontByTemplate(cellFont); if (newFont == null) { newFont = resultWorkbook.createFont(); newFont.setFontName(cellFont.getFontName()); newFont.setItalic(cellFont.getItalic()); newFont.setStrikeout(cellFont.getStrikeout()); newFont.setTypeOffset(cellFont.getTypeOffset()); newFont.setBoldweight(cellFont.getBoldweight()); newFont.setCharSet(cellFont.getCharSet()); newFont.setColor(cellFont.getColor()); newFont.setUnderline(cellFont.getUnderline()); newFont.setFontHeight(cellFont.getFontHeight()); newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints()); fontCache.addCachedFont(cellFont, newFont); }/*w w w . j a v a 2s. c o m*/ newStyle.setFont(newFont); resultStyle = newStyle; styleCache.addCachedNamedStyle(cellStyle, resultStyle); } fixNeighbourCellBorders(cellStyle, resultCell); resultCell.setCellStyle(resultStyle); Sheet sheet = resultCell.getSheet(); 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 firstCol = mergedRegion.getFirstColumn(); int lastCol = mergedRegion.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) for (int col = firstCol; col <= lastCol; col++) sheet.getRow(row).getCell(col).setCellStyle(resultStyle); // cell includes only in one merged region break; } } } } }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void unmergeCells(int sheetIndex, String reference) { Sheet sheet = getSheet(sheetIndex); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress cra = sheet.getMergedRegion(i); if (cra.formatAsString().equals(reference)) { sheet.removeMergedRegion(i); break; }/*www .ja va 2 s. c o m*/ } }
From source file:com.ncc.excel.test.ExcelUtil.java
License:Apache License
/** * ??? //w w w. ja v a2 s. c om * * @param sheet * @param row * @param column * @return */ public void setMergedRegion(Sheet sheet) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { // ???? CellRangeAddress ca = sheet.getMergedRegion(i); int firstRow = ca.getFirstRow(); if (startReadPos - 1 > firstRow) {// ?????? continue; } int lastRow = ca.getLastRow(); int mergeRows = lastRow - firstRow;// ? int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); // ??????? for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) { // ?? sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn)); j = j + mergeRows;// ? } } }