Example usage for org.apache.poi.ss.usermodel Workbook getNumberOfSheets

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getNumberOfSheets.

Prototype

int getNumberOfSheets();

Source Link

Document

Get the number of spreadsheets in the workbook

Usage

From source file:com.runwaysdk.dataaccess.io.ExcelImporter.java

License:Open Source License

/**
 * Opens the stream, parses the types from the sheets and set up context objects for them
 * // www .  ja v a  2 s .  co  m
 * @param stream
 * @return
 * @throws IOException
 */
private void openStream(InputStream stream) {
    try {
        Workbook workbook = ExcelUtil.getWorkbook(stream);

        this.errorWorkbook = ExcelUtil.createWorkbook(workbook);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            String sheetName = workbook.getSheetName(i);

            // Skip the error sheet
            if (this.isValidSheet(sheet, sheetName)) {
                Row row = sheet.getRow(0);
                Cell cell = row.getCell(0);
                String type = ExcelUtil.getString(cell);

                contexts.add(builder.createContext(sheet, sheetName, errorWorkbook, type));
            }
        }

        errorWorkbook.createSheet(ERROR_SHEET);
    } catch (IOException e) {
        throw new SystemException(e);
    }
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelImporterTest.java

License:Open Source License

public void testError() throws IOException {
    BusinessDAO business = BusinessDAO.newInstance(mdBusiness.definesType());
    business.setValue("testDouble", "10");
    business.setValue("testInteger", "-1");

    ExcelExporter exporter = new ExcelExporter();

    ExcelExportSheet excelSheet = exporter.addTemplate(mdBusiness.definesType());
    excelSheet.addRow(business);//from  w  ww.  j av  a 2s  .  com

    byte[] bytes = exporter.write();

    ExcelImporter importer = new ExcelImporter(new ByteArrayInputStream(bytes));
    byte[] results = importer.read();

    assertFalse(results.length == 0);

    ExcelExporterTest.writeFile(results);

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(results));

    assertEquals(2, workbook.getNumberOfSheets());

    Sheet importSheet = workbook.getSheetAt(0);

    Row typeRow = importSheet.getRow(0);
    Row attributeRow = importSheet.getRow(1);
    Row labelRow = importSheet.getRow(2);
    Row row = importSheet.getRow(3);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();
        String value = ExcelUtil.getString(row.getCell(i));

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
        assertEquals(business.getValue(mdAttribute.definesAttribute()), value);
    }

    Sheet errorSheet = workbook.getSheetAt(1);

    Row errorRow = errorSheet.getRow(1);

    assertEquals(4, ExcelUtil.getInteger(errorRow.getCell(0)).intValue());
    assertEquals(mdBusiness.getTypeName(), ExcelUtil.getString(errorRow.getCell(1)));
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelImporterTest.java

License:Open Source License

public void testSuccessAndError() throws IOException {
    BusinessDAO valid = BusinessDAO.newInstance(mdBusiness.definesType());
    valid.setValue(TestFixConst.ATTRIBUTE_CHARACTER, "Test Character Value");
    valid.setValue("testDouble", "10.0000");
    valid.setValue("testInteger", "-1");

    BusinessDAO invalid = BusinessDAO.newInstance(mdBusiness.definesType());
    invalid.setValue("testDouble", "10");
    invalid.setValue("testInteger", "-1");

    ExcelExporter exporter = new ExcelExporter();

    ExcelExportSheet excelSheet = exporter.addTemplate(mdBusiness.definesType());
    excelSheet.addRow(valid);/*w ww  . j av  a2s  . c  o m*/
    excelSheet.addRow(invalid);

    byte[] bytes = exporter.write();

    ExcelImporter importer = new ExcelImporter(new ByteArrayInputStream(bytes));
    byte[] results = importer.read();

    assertFalse(results.length == 0);

    ExcelExporterTest.writeFile(results);

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(results));

    assertEquals(2, workbook.getNumberOfSheets());

    Sheet importSheet = workbook.getSheetAt(0);

    Row typeRow = importSheet.getRow(0);
    Row attributeRow = importSheet.getRow(1);
    Row labelRow = importSheet.getRow(2);
    Row row = importSheet.getRow(3);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();
        String value = ExcelUtil.getString(row.getCell(i));

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
        assertEquals(invalid.getValue(mdAttribute.definesAttribute()), value);
    }

    Sheet errorSheet = workbook.getSheetAt(1);

    Row errorRow = errorSheet.getRow(1);

    assertEquals(4, ExcelUtil.getInteger(errorRow.getCell(0)).intValue());
    assertEquals(mdBusiness.getTypeName(), ExcelUtil.getString(errorRow.getCell(1)));

    List<String> ids = BusinessDAO.getEntityIdsFromDB(mdBusiness);

    assertEquals(1, ids.size());

    BusinessDAOIF test = BusinessDAO.get(ids.get(0));

    try {
        assertEquals(valid.getValue(TestFixConst.ATTRIBUTE_CHARACTER),
                test.getValue(TestFixConst.ATTRIBUTE_CHARACTER));
        assertEquals(valid.getValue("testDouble"), test.getValue("testDouble"));
        assertEquals(valid.getValue("testInteger"), test.getValue("testInteger"));
    } finally {
        TestFixtureFactory.delete(test);
    }
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelImporterTest.java

License:Open Source License

public void testMultipleSheetsWithErrors() throws IOException {
    BusinessDAO business = BusinessDAO.newInstance(mdBusiness.definesType());
    business.setValue("testDouble", "10");
    business.setValue("testInteger", "-1");

    BusinessDAO business2 = BusinessDAO.newInstance(mdBusiness2.definesType());
    business2.setValue(TestFixConst.ATTRIBUTE_BOOLEAN, "true");

    ExcelExporter exporter = new ExcelExporter();

    ExcelExportSheet mdBusinessSheet = exporter.addTemplate(mdBusiness.definesType());
    mdBusinessSheet.addRow(business);// w  ww  .  j  ava2  s .  co  m

    ExcelExportSheet mdBusinessSheet2 = exporter.addTemplate(mdBusiness2.definesType());
    mdBusinessSheet2.addRow(business2);

    byte[] bytes = exporter.write();

    ExcelImporter importer = new ExcelImporter(new ByteArrayInputStream(bytes));
    byte[] results = importer.read();

    assertFalse(results.length == 0);

    ExcelExporterTest.writeFile(results);

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(results));

    assertEquals(3, workbook.getNumberOfSheets());

    Sheet importSheet = workbook.getSheetAt(0);

    Row typeRow = importSheet.getRow(0);
    Row attributeRow = importSheet.getRow(1);
    Row labelRow = importSheet.getRow(2);
    Row row = importSheet.getRow(3);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();
        String value = ExcelUtil.getString(row.getCell(i));

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
        assertEquals(business.getValue(mdAttribute.definesAttribute()), value);
    }

    Sheet errorSheet = workbook.getSheetAt(2);

    Row errorRow = errorSheet.getRow(1);

    assertEquals(4, ExcelUtil.getInteger(errorRow.getCell(0)).intValue());
    assertEquals(mdBusiness.getTypeName(), ExcelUtil.getString(errorRow.getCell(1)));
}

---

From source file:com.salahatwa.randomme.ReadXLS.java

/**
 * @param filePath/*from  w w w. j  a  va 2  s .  c o m*/
 * @return  list of Readed cells from xlsx
 */
public List<ReadedBean> readXLSFromFile(String filePath) {
    List<ReadedBean> data = new ArrayList();
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(filePath);
        // Using XSSF for xlsx format, for xls use HSSF

        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        //looping over each workbook sheet
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            //iterating over each row
            while (rowIterator.hasNext()) {

                ReadedBean readedBean = new ReadedBean();
                Row row = (Row) rowIterator.next();
                Iterator cellIterator = row.cellIterator();
                //Iterating over each cell (column wise)  in a particular row.
                while (cellIterator.hasNext()) {
                    Cell cell = (Cell) cellIterator.next();

                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(cell.getStringCellValue());
                        }

                    } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(String.valueOf((int) cell.getNumericCellValue()));
                        }
                        //                           
                    }
                }
                System.out.println(readedBean.getCell());
                data.add(readedBean);
            }
        }
        fis.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return data;
}

---

From source file:com.smanempat.view.ExcelReading.java

/**
 * @param args the command line arguments
 *///from w ww  . j a  v a  2  s. co  m
public static void main(String[] args) {
    InputStream inp = null;
    try {
        inp = new FileInputStream("C:\\Users\\Zakaria\\Documents\\Test\\2009_15112015_1615.xlsx\\");
        Workbook wb = WorkbookFactory.create(inp);

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            System.out.println(wb.getSheetAt(i).getSheetName());
            echoAsCSV(wb.getSheetAt(i));
        }
    } catch (InvalidFormatException ex) {
        Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            inp.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

---

From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java

License:Open Source License

public List<String> listSheets(InputStream is) throws IOException {
    ArrayList<String> sheets = new ArrayList<>();
    Workbook wb = createWorkbook(is);
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        sheets.add(wb.getSheetName(i));//  www  .  j a va2s  . co m
    }
    return sheets;
}

---

From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java

License:Open Source License

public HashMap<String, String> listColumnNames(InputStream is)
        throws IOException, UnsupportedOperationException {
    HashMap<String, String> hm = new LinkedHashMap<>();

    Workbook wb = createWorkbook(is);
    if (wb.getNumberOfSheets() == 0)
        throw new UnsupportedOperationException("At least one sheet is required");
    Sheet sh = getSheet(wb);/*w  ww .j  a va 2 s .  c  o m*/
    if (sh == null)
        throw new UnsupportedOperationException("Unable to find desired sheet");

    String name = null;
    Row hrow = sh.getRow(headersRow - 1);
    for (Cell cell : hrow) {
        if (headersRow == firstDataRow) {
            name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        } else {
            name = fmt.formatCellValue(cell);
            if (StringUtils.isBlank(name))
                name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        }
        hm.put(name.toLowerCase(), name);
    }

    return hm;
}

---

From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java

License:Open Source License

public HashMap<String, Integer> listColumnIndexes(InputStream is)
        throws IOException, UnsupportedOperationException {
    HashMap<String, Integer> hm = new LinkedHashMap<>();

    Workbook wb = createWorkbook(is);
    if (wb.getNumberOfSheets() == 0)
        throw new UnsupportedOperationException("At least one sheet is required");
    Sheet sh = getSheet(wb);//w ww  . j  a  v a  2s  .c  om
    if (sh == null)
        throw new UnsupportedOperationException("Unable to find desired sheet");

    String name = null;
    Row hrow = sh.getRow(headersRow - 1);
    for (Cell cell : hrow) {
        if (headersRow == firstDataRow) {
            name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        } else {
            name = fmt.formatCellValue(cell);
            if (StringUtils.isBlank(name))
                name = "col_" + CellReference.convertNumToColString(cell.getColumnIndex());
        }
        hm.put(name.toLowerCase(), cell.getColumnIndex());
    }

    return hm;
}

---

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId)
        throws DataParserException {
    this.settings = requireNonNull(settings);
    this.context = requireNonNull(context);
    this.workbook = requireNonNull(workbook);
    this.rowIterator = iterate(this.workbook);
    this.offset = requireNonNull(offsetId);
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.currentSheet = null; // default to blank.   Used to figure out when sheet changes and get new field names from header row

    if (!rowIterator.hasNext()) {
        throw new DataParserException(Errors.EXCEL_PARSER_04);
    }/*w ww .  j  a  v  a 2  s . com*/

    headers = new HashMap<>();

    // If Headers are expected, go through and get them from each sheet
    if (settings.getHeader() == ExcelHeader.WITH_HEADER) {
        Sheet sheet;
        String sheetName;
        Row hdrRow;
        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            sheet = workbook.getSheetAt(s);
            sheetName = sheet.getSheetName();
            hdrRow = sheet.rowIterator().next();
            List<Field> sheetHeaders = new ArrayList<>();
            // if the table happens to have blank columns in front of it, loop through and artificially add those as headers
            // This helps in the matching of headers to data later as the indexes will line up properly.
            for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) {
                sheetHeaders.add(Field.create(""));
            }
            for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) {
                Cell cell = hdrRow.getCell(columnNum);
                try {
                    sheetHeaders.add(Cells.parseCell(cell, this.evaluator));
                } catch (ExcelUnsupportedCellTypeException e) {
                    throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum());
                }
            }
            headers.put(sheetName, sheetHeaders);
        }
    }

    Offsets.parse(offsetId).ifPresent(offset -> {
        String startSheetName = offset.getSheetName();
        int startRowNum = offset.getRowNum();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNum = row.getRowNum();
            String sheetName = row.getSheet().getSheetName();
            // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare
            if (startSheetName.equals(sheetName) && rowNum >= startRowNum) {
                if (rowIterator.hasPrevious()) {
                    row = rowIterator.previous();
                    this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null
                            : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet
                } else {
                    this.currentSheet = null;
                }
                break;
            }
        }
    });
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• »