List of usage examples for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows
int getPhysicalNumberOfRows();
From source file:org.eclipse.birt.report.data.oda.excel.impl.util.ExcelFileReader.java
License:Open Source License
private void initialise() throws IOException, OdaException { try {/* w ww . ja v a 2 s . com*/ if (isXlsxFile(fileExtension)) { xlsxread = new XlsxFileReader(fis); callback = new XlsxRowCallBack(); xlsxSheetRidNameMap = xlsxread.getSheetNames(); for (String sheetName : workSheetList) { String rid = xlsxSheetRidNameMap.get(sheetName); if (rid == null) throw new OdaException(Messages.getString("invalid_sheet_name")); //$NON-NLS-1$ xlsxread.processSheet(rid, callback, this.xlsxRowsToRead); maxRowsInAllSheet = callback.getMaxRowsInSheet(); maxRowsInThisSheet = callback.getMaxRowsInSheet(); } } else if (isXlsFile(fileExtension)) { if (workBook == null) { workBook = new HSSFWorkbook(fis); } formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator(); workBook.setMissingCellPolicy(Row.RETURN_NULL_AND_BLANK); sheet = workBook.getSheet(workSheetList.get(currentSheetIndex)); if (sheet == null) throw new OdaException(Messages.getString("invalid_sheet_name")); maxRowsInThisSheet = sheet.getPhysicalNumberOfRows(); for (String sheetName : workSheetList) { Sheet localSheet = workBook.getSheet(sheetName); maxRowsInAllSheet += localSheet.getPhysicalNumberOfRows(); } } isInitialised = true; } catch (NullPointerException e) { throw new OdaException(e); } catch (OpenXML4JException e) { throw new OdaException(e); } catch (SAXException e) { if (e.getMessage().equalsIgnoreCase(XlsxFileReader.ROW_LIMIT_REACHED_EX_MSG)) { maxRowsInThisSheet = callback.getMaxRowsInSheet(); isInitialised = true; } else { throw new OdaException(e); } } }
From source file:org.geoserver.wfs.response.ExcelOutputFormatTest.java
License:Open Source License
private void testExcelOutputFormat(Workbook wb) throws IOException { Sheet sheet = wb.getSheet("PrimitiveGeoFeature"); assertNotNull(sheet);/*w ww .jav a2s.c o m*/ FeatureSource fs = getFeatureSource(MockData.PRIMITIVEGEOFEATURE); // check the number of rows in the output final int feautureRows = fs.getCount(Query.ALL); assertEquals(feautureRows + 1, sheet.getPhysicalNumberOfRows()); // check the header is what we expect final SimpleFeatureType schema = (SimpleFeatureType) fs.getSchema(); final Row header = sheet.getRow(0); assertEquals("FID", header.getCell(0).getRichStringCellValue().toString()); for (int i = 0; i < schema.getAttributeCount(); i++) { assertEquals(schema.getDescriptor(i).getLocalName(), header.getCell(i + 1).getRichStringCellValue().toString()); } // check some selected values to see if the content and data type is the one // we expect FeatureIterator fi = fs.getFeatures().features(); SimpleFeature sf = (SimpleFeature) fi.next(); fi.close(); // ... a string cell Cell cell = sheet.getRow(1).getCell(1); assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); assertEquals(sf.getAttribute(0), cell.getRichStringCellValue().toString()); // ... a geom cell cell = sheet.getRow(1).getCell(4); assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); assertEquals(sf.getAttribute(3).toString(), cell.getRichStringCellValue().toString()); // ... a number cell cell = sheet.getRow(1).getCell(6); assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); assertEquals(((Number) sf.getAttribute(5)).doubleValue(), cell.getNumericCellValue()); // ... a date cell (they are mapped as numeric in xms?) cell = sheet.getRow(1).getCell(10); assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); assertEquals(sf.getAttribute(9), cell.getDateCellValue()); // ... a boolean cell (they are mapped as numeric in xms?) cell = sheet.getRow(1).getCell(12); assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); assertEquals(sf.getAttribute(11), cell.getBooleanCellValue()); // ... an empty cell (original value is null -> no cell) cell = sheet.getRow(1).getCell(3); assertNull(cell); }
From source file:org.geoserver.wfs.response.ExcelOutputFormatTest.java
License:Open Source License
private void testMultipleFeatureTypes(Workbook wb) throws IOException { // check we have the expected sheets Sheet sheet = wb.getSheet("PrimitiveGeoFeature"); assertNotNull(sheet);/* w ww . j a va2 s . c om*/ // check the number of rows in the output FeatureSource fs = getFeatureSource(MockData.PRIMITIVEGEOFEATURE); assertEquals(fs.getCount(Query.ALL) + 1, sheet.getPhysicalNumberOfRows()); sheet = wb.getSheet("GenericEntity"); assertNotNull(sheet); // check the number of rows in the output fs = getFeatureSource(MockData.GENERICENTITY); assertEquals(fs.getCount(Query.ALL) + 1, sheet.getPhysicalNumberOfRows()); }
From source file:org.grible.excel.ExcelFile.java
License:Open Source License
public String[][] getValues() { Sheet sheet = workbook.getSheetAt(0); int keysCount = generalKeys.size(); int rowCount = sheet.getPhysicalNumberOfRows() - 1; String[][] result = new String[rowCount][keysCount]; for (int i = 0; i < rowCount; i++) { Row row = sheet.getRow(i + 1);//from w w w . j av a 2s . co m for (int j = 0; j < keysCount; j++) { Cell cell = row.getCell(j); result[i][j] = getStringCellValue(cell); } } return result; }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
private static <T> void read(ExcelProcessControllerImpl controller, ExcelReadContext<T> context, Sheet sheet, int startRow, Integer pageSize, Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMapping, Class<T> targetClass, ExcelReadRowProcessor<T> processor, boolean isTrimSpace) { Assert.isTrue(sheet != null, "sheet can't be null"); Assert.isTrue(startRow >= 0, "startRow must greater than or equal to 0"); Assert.isTrue(pageSize == null || pageSize >= 1, "pageSize == null || pageSize >= 1"); Assert.isTrue(fieldMapping != null, "fieldMapping can't be null"); // Assert.isTrue(targetClass != null, "clazz can't be null"); List<T> list = context.getDataList(); if (sheet.getPhysicalNumberOfRows() == 0) { return;//from w w w. j a v a2s.c o m } // int endRow = sheet.getLastRowNum(); if (pageSize != null) { endRow = startRow + pageSize - 1; } for (int i = startRow; i <= endRow; i++) { Row row = sheet.getRow(i); // proc row context.setCurRow(row); context.setCurRowIndex(i); context.setCurCell(null); context.setCurColIndex(null); T t = null; if (!fieldMapping.isEmpty()) { t = readRow(context, row, fieldMapping, targetClass, processor, isTrimSpace); } if (processor != null) { try { controller.reset(); t = processor.process(controller, context, row, t); } catch (RuntimeException re) { if (re instanceof ExcelReadException) { ExcelReadException ere = (ExcelReadException) re; ere.setRowIndex(row.getRowNum()); // ere.setColIndex(); throw ere; } else { ExcelReadException e = new ExcelReadException(re); e.setRowIndex(row.getRowNum()); e.setColIndex(null); e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION); throw e; } } } if (!controller.isDoSkip()) { list.add(t); } if (controller.isDoBreak()) { break; } } }
From source file:org.logic2j.contrib.excel.ExcelReader.java
License:Open Source License
public TabularData read() throws IOException { if (this.file.getName().endsWith(".xls")) { final InputStream myxls = new FileInputStream(this.file); final HSSFWorkbook workBook = new HSSFWorkbook(myxls); final Sheet sheet = workBook.getSheetAt(0); final int excelPhysicalRows = sheet.getPhysicalNumberOfRows(); List<String> columnNames; if (this.firstRowIsHeaders) { columnNames = readRow(sheet, 0, String.class); } else {//from www .j av a2 s. c om final int nbColunms = ((HSSFSheet) sheet).getRow(0).getPhysicalNumberOfCells(); final List<String> colNames = new ArrayList<String>(); for (int i = 0; i < nbColunms; i++) { colNames.add(createSequenceElement(i)); } columnNames = colNames; } final List<List<Serializable>> listData = new ArrayList<List<Serializable>>(); for (int r = this.firstRowIsHeaders ? 1 : 0; r < excelPhysicalRows; r++) { final List<Serializable> listRow = readRow(sheet, r, Serializable.class); if (listRow != null) { // Sometimes listData.add(listRow); } } String dataSetName = this.file.getName(); if (dataSetName.lastIndexOf('.') >= 0) { dataSetName = dataSetName.substring(0, dataSetName.lastIndexOf('.')); } final TabularData tbl = new TabularData(dataSetName, columnNames, listData); tbl.setPrimaryKeyColumn(this.primaryKeyColumn); return tbl; } throw new IOException("According to extension file may not be of Excel format: " + this.file); /* OOXML else if (this.fileName.endsWith(".xlsx")) { if (this.sheet == null) { final XSSFWorkbook workBook = new XSSFWorkbook(this.fileName); this.sheet = workBook.getSheetAt(0); } final int totalRows = this.sheet.getPhysicalNumberOfRows(); for (int i = this.firstRowIsHeaders ? 0 : 1; i < totalRows; i++) { final XSSFRow row = ((XSSFSheet) this.sheet).getRow(i); if (row != null) { final int cells = row.getPhysicalNumberOfCells(); final Term[] args = new Term[cells]; for (int c = 0; c < cells; c++) { final XSSFCell cell = row.getCell(c); String value = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); // If it is a formula, then it must be a numeric value. args[c] = this.termAdapter.term(value, FactoryMode.ANY_TERM); break; case Cell.CELL_TYPE_NUMERIC: value = Double.toString(cell.getNumericCellValue()); args[c] = this.termAdapter.term(value, FactoryMode.ANY_TERM); break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); args[c] = this.termAdapter.term("\"" + value.replace("\"", "").replaceAll("\\r|\\n", "") + "\"", FactoryMode.LITERAL); break; default: args[c] = this.termAdapter.term("\"" + value + "\"", FactoryMode.LITERAL); } // Note: There is a problem if the content of the cell is too long // args[c] = prolog.getTermFactory().create("\""+value.replace("\"", "").replaceAll("\\r|\\n", "")+"\"", // FactoryMode.ANY_TERM); } final Clause cl = new Clause(this.prolog, new Struct(dataSetName, args)); clauses.add(cl); } } } */ }
From source file:org.meveo.commons.utils.ExcelToCsv.java
License:Apache License
/** * Called to convert the contents of the currently opened workbook into * a CSV file.//from w ww. j av a 2s. c o m */ private void convertToCSV() { Sheet sheet = null; Row row = null; int lastRowNum = 0; this.csvData = new ArrayList<ArrayList<String>>(); log.debug("Converting files contents to CSV format."); // and then iterate through them. for (int i = 0; i < sheetsIdsToConvert.length; i++) { // Get a reference to a sheet and check to see if it contains // any rows. sheet = this.workbook.getSheetAt(sheetsIdsToConvert[i]); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines // for inclusion in the resylting CSV file. lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToCSV(row); } } } }
From source file:org.ohdsi.whiteRabbit.scan.SourceDataScan.java
License:Apache License
private void addRow(Sheet sheet, Object... values) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); for (Object value : values) { Cell cell = row.createCell(row.getPhysicalNumberOfCells()); if (value instanceof Integer || value instanceof Long || value instanceof Double) cell.setCellValue(Double.parseDouble(value.toString())); else/*from ww w . ja va 2s . co m*/ cell.setCellValue(value.toString()); } }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * Clone the sheet at the passed index and replace values as needed */// w w w.j av a 2 s.c o m public Sheet addSheet(Workbook wb, SheetToAdd sheetToAdd, Set<String> usedSheetNames, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); Sheet sheet = sheetToAdd.getSheet(); sheet.setForceFormulaRecalculation(true); int sheetIndex = wb.getSheetIndex(sheet); // Configure the sheet name, replacing any values as needed, and ensuring it is unique for the workbook String sheetName = EvaluationUtil.evaluateExpression(sheetToAdd.getOriginalSheetName(), sheetToAdd.getReplacementData(), prefix, suffix).toString(); sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames); wb.setSheetName(sheetIndex, sheetName); usedSheetNames.add(sheetName); log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex); // Iterate across all of the rows in the sheet, and configure all those that need to be added/cloned List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>(); int totalRows = sheet.getPhysicalNumberOfRows(); int rowsFound = 0; for (int rowNum = 0; rowsFound < totalRows && rowNum < 50000; rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases Row currentRow = sheet.getRow(rowNum); if (log.isDebugEnabled()) { log.debug("Handling row: " + ExcelUtil.formatRow(currentRow)); } if (currentRow != null) { rowsFound++; } // If we find that the row that we are on is a repeating row, then add the appropriate number of rows to clone String repeatingRowProperty = getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum, repeatSections); if (repeatingRowProperty != null) { String[] dataSetSpanSplit = repeatingRowProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData()); int numRowsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug("Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numRowsToRepeat; i++) { Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i)); if (repeatNum == 1 && row != null && row != currentRow) { rowsFound++; } Map<String, Object> newReplacements = getReplacementData(sheetToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); rowsToAdd.add(new RowToAdd(row, newReplacements)); if (log.isDebugEnabled()) { log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow); } } } if (numRowsToRepeat > 1) { rowNum += numRowsToRepeat - 1; } } else { rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData())); if (log.isDebugEnabled()) { log.debug("Adding row: " + ExcelUtil.formatRow(currentRow)); } } } // Now, go through all of the collected rows, and add them back in for (int i = 0; i < rowsToAdd.size(); i++) { RowToAdd rowToAdd = rowsToAdd.get(i); if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) { Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections); if (log.isDebugEnabled()) { log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow)); } } } return sheet; }
From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268IT.java
License:Open Source License
private void assertSheetNotEmpty(final Sheet sheet) { Assert.assertTrue(sheet.getPhysicalNumberOfRows() > 0); for (int r = 0; r < 10; r += 1) { Row row = sheet.getRow(r);/* w ww . j av a2 s . com*/ Assert.assertNotNull(row); Assert.assertNotNull(row.getCell(0)); Assert.assertNotNull(row.getCell(0).getStringCellValue()); } }