List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268IT.java
License:Open Source License
@Test public void testSheetNames() throws Exception { MasterReport report = createReport(); Assert.assertFalse(new ReportStructureValidator().isValidForFastProcessing(report)); ByteArrayOutputStream boutFast = new ByteArrayOutputStream(); FastExcelReportUtil.processXlsx(report, boutFast); Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray())); Assert.assertEquals(3, workbook.getNumberOfSheets()); Assert.assertEquals("FIRST REPORT", workbook.getSheetName(0)); Assert.assertEquals("SECOND REPORT", workbook.getSheetName(1)); Assert.assertEquals("SECOND REPORT 2", workbook.getSheetName(2)); }
From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268IT.java
License:Open Source License
@Test public void testSheetContent() throws Exception { MasterReport report = createReport(); Assert.assertFalse(new ReportStructureValidator().isValidForFastProcessing(report)); ByteArrayOutputStream boutFast = new ByteArrayOutputStream(); FastExcelReportUtil.processXlsx(report, boutFast); Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray())); Assert.assertEquals(3, workbook.getNumberOfSheets()); assertSheetNotEmpty(workbook.getSheetAt(0)); assertSheetNotEmpty(workbook.getSheetAt(1)); assertSheetNotEmpty(workbook.getSheetAt(2)); }
From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268Test.java
License:Open Source License
@Test public void testSheetNamesInFastMode() throws Exception { MasterReport report = createReport(); Assert.assertTrue(new ReportStructureValidator().isValidForFastProcessing(report)); ByteArrayOutputStream boutFast = new ByteArrayOutputStream(); FastExcelReportUtil.processXlsx(report, boutFast); Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray())); Assert.assertEquals(3, workbook.getNumberOfSheets()); Assert.assertEquals("FIRST REPORT", workbook.getSheetName(0)); Assert.assertEquals("SECOND REPORT", workbook.getSheetName(1)); Assert.assertEquals("SECOND REPORT 2", workbook.getSheetName(2)); }
From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268Test.java
License:Open Source License
@Test public void testSheetContent() throws Exception { MasterReport report = createReport(); Assert.assertTrue(new ReportStructureValidator().isValidForFastProcessing(report)); ByteArrayOutputStream boutFast = new ByteArrayOutputStream(); FastExcelReportUtil.processXlsx(report, boutFast); Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray())); Assert.assertEquals(3, workbook.getNumberOfSheets()); assertSheetNotEmpty(workbook.getSheetAt(0)); assertSheetNotEmpty(workbook.getSheetAt(1)); assertSheetNotEmpty(workbook.getSheetAt(2)); }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinterBase.java
License:Open Source License
protected Workbook createWorkbook() { // Not opened yet. Lets do this now. if (templateInputStream != null) { // do some preprocessing .. try {/*from www.ja va 2 s. co m*/ final Workbook workbook = WorkbookFactory.create(templateInputStream); // OK, we have a workbook, but we can't stop here.. final int sheetCount = workbook.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { final String sheetName = workbook.getSheetName(i); // make sure that that name is marked as used .. makeUnique(sheetName); } return workbook; } catch (IOException e) { logger.warn("Unable to read predefined xls-data.", e); } catch (InvalidFormatException e) { logger.warn("Unable to read predefined xls-data.", e); } } if (isUseXlsxFormat()) { return new XSSFWorkbook(); } else { return new HSSFWorkbook(); } }
From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java
License:Open Source License
private void importFromFile(final File file, final boolean firstRowIsHeader) { final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length())); try {/*from w w w.j a v a 2s.c om*/ final InputStream fin = new FileInputStream(file); try { IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout); } finally { fin.close(); } if (Thread.currentThread().isInterrupted()) { return; } final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray())); int sheetIndex = 0; if (workbook.getNumberOfSheets() > 1) { final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent); if (selectorDialog.performSelection()) { sheetIndex = selectorDialog.getSelectedIndex(); } else { return; } } final TypedTableModel tableModel = new TypedTableModel(); final Sheet sheet = workbook.getSheetAt(sheetIndex); final Iterator rowIterator = sheet.rowIterator(); if (firstRowIsHeader) { if (rowIterator.hasNext()) { final Row headerRow = (Row) rowIterator.next(); final short cellCount = headerRow.getLastCellNum(); for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = headerRow.getCell(colIdx); if (cell != null) { while (colIdx > tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } final RichTextString string = cell.getRichStringCellValue(); if (string != null) { tableModel.addColumn(string.getString(), Object.class); } else { tableModel.addColumn( Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)), Object.class); } } } } } Object[] rowData = null; while (rowIterator.hasNext()) { final Row row = (Row) rowIterator.next(); final short cellCount = row.getLastCellNum(); if (cellCount == -1) { continue; } if (rowData == null || rowData.length != cellCount) { rowData = new Object[cellCount]; } for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = row.getCell(colIdx); final Object value; if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { final RichTextString string = cell.getRichStringCellValue(); if (string != null) { value = string.getString(); } else { value = null; } } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { final CellStyle hssfCellStyle = cell.getCellStyle(); final short dataFormat = hssfCellStyle.getDataFormat(); final String dataFormatString = hssfCellStyle.getDataFormatString(); if (isDateFormat(dataFormat, dataFormatString)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { value = cell.getBooleanCellValue(); } else { value = cell.getStringCellValue(); } } else { value = null; } if (value != null && "".equals(value) == false) { while (colIdx >= tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } } rowData[colIdx] = value; } if (Thread.currentThread().isInterrupted()) { return; } tableModel.addRow(rowData); } final int colCount = tableModel.getColumnCount(); final int rowCount = tableModel.getRowCount(); for (int col = 0; col < colCount; col++) { Class type = null; for (int row = 0; row < rowCount; row += 1) { final Object value = tableModel.getValueAt(row, col); if (value == null) { continue; } if (type == null) { type = value.getClass(); } else if (type != Object.class) { if (type.isInstance(value) == false) { type = Object.class; } } } if (Thread.currentThread().isInterrupted()) { return; } if (type != null) { tableModel.setColumnType(col, type); } } parent.importComplete(tableModel); } catch (Exception e) { parent.importFailed(e); logger.error("Failed to import spreadsheet", e); // NON-NLS } }
From source file:org.project.utilities.ExcelRead.java
public ArrayList readexl(File exfile, String exfilename) { ArrayList storvalues = new ArrayList(); try {// w ww . ja v a 2 s. co m // File file2 = new File("/home/asl/Desktop/html work/ProgramFile/test_template.xls"); FileInputStream file = new FileInputStream(exfile); //Workbook workbook = null; // String name = file2.getName(); String name = exfilename; Workbook workbook = null; if (name.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(file); } else if (name.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(file); } // workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL); DataFormatter fmt = new DataFormatter(); for (int sn = 0; sn < workbook.getNumberOfSheets(); sn++) { Sheet sheet = workbook.getSheetAt(sn); for (int rn = sheet.getFirstRowNum() + 1; rn <= sheet.getLastRowNum(); rn++) { Row row = sheet.getRow(rn); if (row == null) { // There is no data in this row, handle as needed } else { // Row "rn" has data ArrayList storeval = new ArrayList(); // System.out.println("size " + row.getLastCellNum()); for (int cn = 0; cn < 17; cn++) { // for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn); // cell.setCellType(Cell.CELL_TYPE_STRING); String val = ""; // String strCellValue = ""; if (cell == null) { // This cell is empty/blank/un-used, handle as needed } else { String cellStr = fmt.formatCellValue(cell); val = cellStr; // Do something with the value } storeval.add(val); } storvalues.add(storeval); } } } } catch (Exception e) { e.printStackTrace(); } return storvalues; }
From source file:org.ramadda.data.docs.TabularOutputHandler.java
License:Apache License
/** * _more_//from w ww. ja v a 2 s . c o m * * @param request _more_ * @param entry _more_ * @param suffix _more_ * @param inputStream _more_ * @param visitInfo _more_ * @param visitor _more_ * * @throws Exception _more_ */ private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream, TextReader visitInfo, TabularVisitor visitor) throws Exception { // System.err.println("visitXls: making workbook"); Workbook wb = makeWorkbook(suffix, inputStream); // System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets()); int maxRows = visitInfo.getMaxRows(); for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) { if (!visitInfo.okToShowSheet(sheetIdx)) { continue; } Sheet sheet = wb.getSheetAt(sheetIdx); // System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum()); List<List<Object>> rows = new ArrayList<List<Object>>(); int sheetSkip = visitInfo.getSkip(); for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows) && (rowIdx <= sheet.getLastRowNum()); rowIdx++) { if (sheetSkip-- > 0) { continue; } Row row = sheet.getRow(rowIdx); if (row == null) { continue; } List<Object> cols = new ArrayList<Object>(); short firstCol = row.getFirstCellNum(); for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) { Cell cell = row.getCell(col); if (cell == null) { break; } Object value = null; int type = cell.getCellType(); if (type == cell.CELL_TYPE_NUMERIC) { value = new Double(cell.getNumericCellValue()); } else if (type == cell.CELL_TYPE_BOOLEAN) { value = new Boolean(cell.getBooleanCellValue()); } else if (type == cell.CELL_TYPE_ERROR) { value = "" + cell.getErrorCellValue(); } else if (type == cell.CELL_TYPE_BLANK) { value = ""; } else if (type == cell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } else { value = cell.getStringCellValue(); } cols.add(value); } /** * ** TODO * org.ramadda.util.text.Row row = new Row(cols); * * if ( !visitInfo.rowOk(row)) { * if (rows.size() == 0) { * //todo: check for the header line * } else { * continue; * } * } */ rows.add(cols); } if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) { break; } } }
From source file:org.ramadda.data.docs.TabularOutputHandler.java
License:Apache License
/** * _more_//from w w w. j a v a2 s . c om * * @param args _more_ * * @throws Exception _more_ */ public static void main(String[] args) throws Exception { Workbook wb = makeWorkbook(IOUtil.getFileExtension(args[0]), new FileInputStream(args[0])); for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) { Sheet sheet = wb.getSheetAt(sheetIdx); System.err.println(sheet.getSheetName()); for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); if (row == null) { continue; } short firstCol = row.getFirstCellNum(); int colCnt = 0; for (short col = firstCol; col < row.getLastCellNum(); col++) { Cell cell = row.getCell(col); if (cell == null) { break; } Object value = null; int type = cell.getCellType(); if (type == cell.CELL_TYPE_NUMERIC) { value = new Double(cell.getNumericCellValue()); } else if (type == cell.CELL_TYPE_BOOLEAN) { value = new Boolean(cell.getBooleanCellValue()); } else if (type == cell.CELL_TYPE_ERROR) { value = "" + cell.getErrorCellValue(); } else if (type == cell.CELL_TYPE_BLANK) { value = ""; } else if (type == cell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } else { value = cell.getStringCellValue(); } if (colCnt++ > 0) System.out.print(","); System.out.print(value); } System.out.println(""); } } }
From source file:org.ramadda.plugins.media.TabularOutputHandler.java
License:Open Source License
/** * _more_/*from w w w . j av a 2 s. c o m*/ * * @param request _more_ * @param entry _more_ * @param suffix _more_ * @param inputStream _more_ * @param visitInfo _more_ * @param visitor _more_ * * @throws Exception _more_ */ private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream, Visitor visitInfo, TabularVisitor visitor) throws Exception { // System.err.println("visitXls: making workbook"); Workbook wb = makeWorkbook(suffix, inputStream); // System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets()); int maxRows = visitInfo.getMaxRows(); for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) { if (!visitInfo.okToShowSheet(sheetIdx)) { continue; } Sheet sheet = wb.getSheetAt(sheetIdx); // System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum()); List<List<Object>> rows = new ArrayList<List<Object>>(); int sheetSkip = visitInfo.getSkip(); for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows) && (rowIdx <= sheet.getLastRowNum()); rowIdx++) { if (sheetSkip-- > 0) { continue; } Row row = sheet.getRow(rowIdx); if (row == null) { continue; } List<Object> cols = new ArrayList<Object>(); short firstCol = row.getFirstCellNum(); for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) { Cell cell = row.getCell(col); if (cell == null) { break; } Object value = null; int type = cell.getCellType(); if (type == cell.CELL_TYPE_NUMERIC) { value = new Double(cell.getNumericCellValue()); } else if (type == cell.CELL_TYPE_BOOLEAN) { value = new Boolean(cell.getBooleanCellValue()); } else if (type == cell.CELL_TYPE_ERROR) { value = "" + cell.getErrorCellValue(); } else if (type == cell.CELL_TYPE_BLANK) { value = ""; } else if (type == cell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } else { value = cell.getStringCellValue(); } cols.add(value); } /** * ** TODO * org.ramadda.util.text.Row row = new Row(cols); * * if ( !visitInfo.rowOk(row)) { * if (rows.size() == 0) { * //todo: check for the header line * } else { * continue; * } * } */ rows.add(cols); } if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) { break; } } }