List of usage examples for org.apache.poi.ss.usermodel Sheet getMergedRegion
public CellRangeAddress getMergedRegion(int index);
From source file:org.primefaces.extensions.showcase.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(); }// ww w.jav a2s. c om 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:org.primefaces.extensions.showcase.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 w w .j a v a 2 s . c om*/ 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:org.projectforge.excel.ExportSheet.java
License:Open Source License
private static Row copyRow(Sheet worksheet, int rowNum) { Row sourceRow = worksheet.getRow(rowNum); //Save the text of any formula before they are altered by row shifting String[] formulasArray = new String[sourceRow.getLastCellNum()]; for (int i = 0; i < sourceRow.getLastCellNum(); i++) { if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) formulasArray[i] = sourceRow.getCell(i).getCellFormula(); }/*w w w . j a v a2 s .c om*/ worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1); Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1 // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell; // If the old cell is null jump to next cell if (oldCell == null) { continue; } else { newCell = newRow.createCell(i); } // Copy style from old cell and apply to new cell CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(formulasArray[i]); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; default: break; } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } return newRow; }
From source file:org.seasar.fisshplate.core.element.AbstractCell.java
License:Apache License
/** * ??// w w w. j ava 2s .c om * * @param cell */ AbstractCell(CellWrapper cell) { this.cell = cell; Sheet templateSheet = cell.getRow().getSheet().getHSSFSheet(); int rowNum = cell.getRow().getHSSFRow().getRowNum(); //?????????????? for (int i = 0; i < templateSheet.getNumMergedRegions(); i++) { CellRangeAddress reg = templateSheet.getMergedRegion(i); setUpMergedCellInfo(cell.getHSSFCell().getColumnIndex(), rowNum, reg); if (isMergedCell) { break; } } cellValue = cell.getObjectValue(); }
From source file:org.specrunner.source.excel.SourceFactoryExcel.java
License:Open Source License
/** * Load a document from a target./*www. j a v a 2 s. c o m*/ * * @param uri * The target corresponding uri (if any). * @param target * The target. * @param encoding * The encoding. * @return The document, if exists, null, otherwise. * @throws SourceException * On load error. */ @Override protected Document fromTarget(URI uri, String target, String encoding) throws SourceException { Element html = new Element("html"); Document result = new Document(html); OPCPackage pkg = null; InputStream in = null; POIFSFileSystem fsys = null; try { Workbook wb = null; if (isFile(uri, target)) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug("Source from file:" + target); } in = new FileInputStream(new File(target)); } else { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug("Source from URI:" + uri); } in = uri.toURL().openStream(); } if (target.trim().toLowerCase().endsWith(XLSX)) { pkg = OPCPackage.open(in); wb = new XSSFWorkbook(pkg); } else { fsys = new POIFSFileSystem(in); wb = new HSSFWorkbook(fsys); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Map<String, Dimension> spanMap = new HashMap<String, Dimension>(); Set<String> ignoreMap = new HashSet<String>(); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress region = sheet.getMergedRegion(j); for (int x = region.getFirstRow(); x <= region.getLastRow(); x++) { for (int y = region.getFirstColumn(); y <= region.getLastColumn(); y++) { if (x == region.getFirstRow() && y == region.getFirstColumn()) { spanMap.put(x + "," + y, new Dimension(region.getLastRow() - x + 1, region.getLastColumn() - y + 1)); } else { ignoreMap.add(x + "," + y); } } } } Element table = new Element("table"); table.addAttribute(new Attribute("border", "1")); html.appendChild(table); Element caption = readCaption(table, sheet); Iterator<Row> ite = sheet.iterator(); readBody(table, caption, spanMap, ignoreMap, ite, headers(table, caption, spanMap, ignoreMap, ite)); } } catch (Exception e) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug(e.getMessage(), e); } throw new SourceException(e); } finally { if (pkg != null) { try { pkg.close(); } catch (IOException e) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug(e.getMessage(), e); } throw new SourceException(e); } } if (in != null) { try { in.close(); } catch (IOException e) { if (UtilLog.LOG.isDebugEnabled()) { UtilLog.LOG.debug(e.getMessage(), e); } throw new SourceException(e); } } } return result; }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * Copy rows.//from ww w. ja v a 2 s . c o m * * @param srcSheet * the src sheet * @param destSheet * the dest sheet * @param srcRowStart * the src row start * @param srcRowEnd * the src row end * @param destRow * the dest row * @param checkLock * the check lock * @param setHiddenColumn * the set hidden column */ public static void copyRows(final Sheet srcSheet, final Sheet destSheet, final int srcRowStart, final int srcRowEnd, final int destRow, final boolean checkLock, final boolean setHiddenColumn) { int length = srcRowEnd - srcRowStart + 1; if (length <= 0) { return; } destSheet.shiftRows(destRow, destSheet.getLastRowNum(), length, true, false); for (int i = 0; i < length; i++) { copySingleRow(srcSheet, destSheet, srcRowStart + i, destRow + i, checkLock, setHiddenColumn); } // If there are are any merged regions in the source row, copy to new // row for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i); if ((cellRangeAddress.getFirstRow() >= srcRowStart) && (cellRangeAddress.getLastRow() <= srcRowEnd)) { int targetRowFrom = cellRangeAddress.getFirstRow() - srcRowStart + destRow; int targetRowTo = cellRangeAddress.getLastRow() - srcRowStart + destRow; CellRangeAddress newCellRangeAddress = new CellRangeAddress(targetRowFrom, targetRowTo, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); destSheet.addMergedRegion(newCellRangeAddress); } } }
From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java
License:MIT License
/** * Index merged region./*from w ww . j a va 2s. co m*/ * * @param sheet1 * the sheet 1 * @return the map */ public static Map<String, CellRangeAddress> indexMergedRegion(final Sheet sheet1) { int numRegions = sheet1.getNumMergedRegions(); Map<String, CellRangeAddress> cellRangeMap = new HashMap<>(); for (int i = 0; i < numRegions; i++) { CellRangeAddress caddress = sheet1.getMergedRegion(i); if (caddress != null) { cellRangeMap.put( CellUtility.getCellIndexNumberKey(caddress.getFirstColumn(), caddress.getFirstRow()), caddress); } } return cellRangeMap; }
From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java
License:MIT License
/** * Skipped region cells.//www. java 2 s . co m * * @param sheet1 * the sheet 1 * @return the list */ public static List<String> skippedRegionCells(final Sheet sheet1) { int numRegions = sheet1.getNumMergedRegions(); List<String> skipCellList = new ArrayList<>(); for (int i = 0; i < numRegions; i++) { CellRangeAddress caddress = sheet1.getMergedRegion(i); if (caddress != null) { addSkipCellToListInTheRegion(skipCellList, caddress); } } return skipCellList; }
From source file:org.wicketstuff.poi.excel.TableParserTest.java
License:Apache License
public void testTable1() throws IOException, ResourceStreamNotFoundException, ParseException { Sheet sheet = new HSSFWorkbook().createSheet(); TableParser tableParser = new TableParser(sheet, new GeneralPurposeExporter()); tableParser.parse(new Table1()); assertEquals(9, sheet.getLastRowNum()); assertEquals(3, sheet.getNumMergedRegions()); assertEquals(0, sheet.getMergedRegion(0).getFirstColumn()); assertEquals(1, sheet.getMergedRegion(0).getLastColumn()); assertEquals(3, sheet.getMergedRegion(0).getFirstRow()); assertEquals(3, sheet.getMergedRegion(0).getLastRow()); assertEquals(0, sheet.getMergedRegion(1).getFirstColumn()); assertEquals(0, sheet.getMergedRegion(1).getLastColumn()); assertEquals(4, sheet.getMergedRegion(1).getFirstRow()); assertEquals(5, sheet.getMergedRegion(1).getLastRow()); assertEquals(0, sheet.getMergedRegion(2).getFirstColumn()); assertEquals(2, sheet.getMergedRegion(2).getLastColumn()); assertEquals(6, sheet.getMergedRegion(2).getFirstRow()); assertEquals(6, sheet.getMergedRegion(2).getLastRow()); assertEquals("04/01/2000", sheet.getRow(3).getCell(2).getStringCellValue()); assertEquals("05/01/2000", sheet.getRow(4).getCell(2).getStringCellValue()); assertEquals("06/01/2000", sheet.getRow(5).getCell(2).getStringCellValue()); assertNull(sheet.getRow(6).getCell(2)); assertEquals("08/01/2000", sheet.getRow(7).getCell(2).getStringCellValue()); // tester.startResource(new ResourceStreamResource(new // XlsStream(sheet.getWorkbook()))); // PoiTestUtil.openFileInResponse(tester); }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//from w w w.ja v a 2s . c o m out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); int lastNum = -1; while (rows.hasNext()) { Row row = rows.next(); int curNum = row.getRowNum(); if (curNum - lastNum > 1) { for (int i = lastNum + 2; i <= curNum; i++) { out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, i); out.format(" <td colspan=%d style=\"%s\"> </td>%n", (endColumn - firstColumn + 1), styleSimpleContents(null, false)); out.format(" </tr>%n"); } } lastNum = curNum; out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; boolean isNumeric = false; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content != null && !content.equals("") && (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC)) { if (DateUtil.isCellDateFormatted(cell)) { // Date if ("mmm-yy".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("MMM.yy"); content = sdfRus.format(cell.getDateCellValue()); } else if ("h:mm".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("HH:mm"); content = sdfRus.format(cell.getDateCellValue()); } else if (style.getDataFormatString() != null && style.getDataFormatString().contains("mm")) { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); content = sdfRus.format(cell.getDateCellValue()); } else { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy"); content = sdfRus.format(cell.getDateCellValue()); } } else { // Number if ("- 0".equals(content.trim())) content = " "; else content = "<nobr>" + content.replace(",", " ").replace(".", ",") + "</nobr>"; isNumeric = true; } } if (content == null || content.equals("")) content = " "; } } boolean isInRangeNotFirst = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress rangeAddress = sheet.getMergedRegion(j); if (row.getRowNum() == rangeAddress.getFirstRow() && i == rangeAddress.getFirstColumn()) { if (rangeAddress.getLastRow() - row.getRowNum() > 0) attrs += " rowspan=" + (rangeAddress.getLastRow() - row.getRowNum() + 1); if (rangeAddress.getLastColumn() - i > 0) attrs += " colspan=" + (rangeAddress.getLastColumn() - i + 1); break; } else if (row.getRowNum() >= rangeAddress.getFirstRow() && row.getRowNum() <= rangeAddress.getLastRow() && i >= rangeAddress.getFirstColumn() && i <= rangeAddress.getLastColumn()) { isInRangeNotFirst = true; break; } } if (!isInRangeNotFirst) { out.format(" <td style=\"%s\" %s>%s</td>%n", styleSimpleContents(style, isNumeric), attrs, content); } } // columns out.format(" </tr>%n"); } // rows out.format("</tbody>%n"); }