List of usage examples for org.apache.poi.ss.usermodel Sheet getNumMergedRegions
int getNumMergedRegions();
From source file:org.seasar.fisshplate.core.element.AbstractCell.java
License:Apache License
/** * ??//from w w w . j a va2 s . co m * * @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.//from w w w. j a v a2 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./* w w w .ja v a2 s . co 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./* w w w . j ava 2 s .c om*/ * * @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./* ww w. j a va 2s.c o 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:org.wso2.security.tool.adapter.ExcelInputAdapter.java
License:Open Source License
/** * Converts the data in the files with .xlsx extension to the JSON format. * A workbook is created from the the excel file (.xlsx) and while iterating through the sheets in the workbook; * the data is read and set in to a JSONObject. The JSONObject returned by the method contains an array of * row objects corresponding to each row in the workbook. A row object contains values of each cell in a given row, * with key values starting from letter 'A'. * * @param dataFilePath The path where the data file uploaded is saved. * @return returns the JSON object that contains all the data in the .xlsx file. * @throws FeedbackToolException If the .xlsx file is not found in the given path or due to an error in * parsing the data in the data file. *///from w w w . java 2s . c o m @Override public JSONObject convert(String dataFilePath) throws FeedbackToolException { // JSONObject to hold the array of row objects JSONObject dataJSONObject = new JSONObject(); try { Workbook workbook = WorkbookFactory.create(new File(dataFilePath)); logInfo = "Workbook has " + workbook.getNumberOfSheets() + " sheets"; log.info(logInfo); Iterator<Sheet> sheetIterator = workbook.sheetIterator(); // JSONArray to hold all the row objects JSONArray rowsJSONArray = new JSONArray(); while (sheetIterator.hasNext()) { Sheet sheet = sheetIterator.next(); logInfo = "Sheet: " + sheet.getSheetName() + " has " + sheet.getNumMergedRegions() + " merged regions"; log.info(logInfo); DataFormatter dataFormatter = new DataFormatter(); logInfo = "Iterating over Rows and Columns using for-each loop"; log.info(logInfo); for (Row row : sheet) { // JSONObject to hold the data in the cells of a given row JSONObject rowJSONObject = new JSONObject(); char keyLetter = 'A'; for (Cell cell : row) { String cellValue = dataFormatter.formatCellValue(cell); rowJSONObject.put(keyLetter, cellValue); ++keyLetter; } rowsJSONArray.add(rowJSONObject); } } dataJSONObject.put(Constants.JSON_DATA_OBJECT, rowsJSONArray); } catch (InvalidFormatException e) { throw new FeedbackToolException("Error in parsing the data file uploaded", e); } catch (IOException e) { throw new FeedbackToolException("Data file was not found in the specified location", e); } return dataJSONObject; }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//from w ww . j a v a2s .c om 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"); }
From source file:uk.co.spudsoft.birt.emitters.excel.handlers.PageHandler.java
License:Open Source License
private CellRangeAddress getMergedRegionBegunBy(Sheet sheet, int row, int col) { for (int i = 0; i < sheet.getNumMergedRegions(); ++i) { CellRangeAddress range = sheet.getMergedRegion(i); if ((range.getFirstColumn() == col) && (range.getFirstRow() == row)) { return range; }//w ww .j a va 2 s. c o m } return null; }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.Issue50MultiRowCrosstabHeaderGrids.java
License:Open Source License
@Test public void testHeader() throws Exception { debug = false;/*from ww w . j a v a 2s. c om*/ InputStream inputStream = runAndRenderReport("Issue50MultiRowCrosstabHeaderGrids.rptdesign", "xlsx"); assertNotNull(inputStream); try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); Sheet sheet = workbook.getSheetAt(0); assertEquals("Atelier graphique", sheet.getRow(2).getCell(1).getStringCellValue()); assertTrue(mergedRegion(sheet, 0, 0, 1, 0)); assertTrue(mergedRegion(sheet, 0, 1, 1, 1)); assertEquals(34, sheet.getNumMergedRegions()); assertEquals(100, this.firstNullRow(workbook.getSheetAt(0))); } finally { inputStream.close(); } }