Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getNumberOfSheets

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getNumberOfSheets

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getNumberOfSheets

Introduction

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

Prototype

@Override
public int getNumberOfSheets()

Source Link

Document

Get the number of worksheets in the this workbook

Usage

From source file:accounts.ExcelUtils.java

License:Apache License

public Map<String, Map<TRId, TR>> processAllSheets(String filename, Map<String, BankAccount> baMap,
        String accountName) throws IOException, DBException {
    Map<String, Map<TRId, TR>> excelTrMap = new TreeMap<>();
    FileInputStream file = new FileInputStream(new File(filename));

    // Get the workbook instance for XLS file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        String sheetName = workbook.getSheetName(i);
        if ("RentalSummary".equalsIgnoreCase(sheetName) || "CompanySummary".equalsIgnoreCase(sheetName)
                || "PersonalSummary".equalsIgnoreCase(sheetName)) {
            continue;
        }//from  w w  w  .  ja v  a2 s  . c  o  m
        if (accountName != null && !accountName.equalsIgnoreCase(sheetName)) {
            continue;
        }
        Map<TRId, TR> mapTr = new HashMap<>();
        excelTrMap.put(sheetName, mapTr);
        System.out.println("Processing sheet: " + sheetName);
        BankAccount ba = baMap.get(sheetName);
        if (ba == null) {
            throw new IOException("Unknown bank account name in excel=" + sheetName);
        }

        for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) {
            Row row = sheet.getRow(rownum);

            // Get iterator to all cells of current row

            TR tr = DBFactory.inst().createCorrespondingTRObj(ba);
            tr.setDate(row.getCell(0).getDateCellValue());
            tr.setDescription(row.getCell(1).getStringCellValue());
            tr.setDebit((float) row.getCell(2).getNumericCellValue());
            tr.setComment(row.getCell(3).getStringCellValue());
            tr.setTrType(row.getCell(4).getStringCellValue());
            tr.setTaxCategory(row.getCell(5).getStringCellValue());
            tr.setProperty(row.getCell(6).getStringCellValue());
            tr.setOtherEntity(row.getCell(7).getStringCellValue());
            String locked = row.getCell(8).getStringCellValue();
            tr.setLocked("YES".equalsIgnoreCase(locked));
            tr.setTrId();
            mapTr.put(tr.getTrId(), tr);

        }
    }
    return excelTrMap;

}

From source file:accounts.ExcelUtils.java

License:Apache License

public Map<String, Map<TRId, TR>> processAllSheets(String filename) throws IOException {
    Map<String, Map<TRId, TR>> excelTrMap = new TreeMap<>();
    FileInputStream file = new FileInputStream(new File(filename));

    // Get the workbook instance for XLS file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        String sheetName = workbook.getSheetName(i);
        Map<TRId, TR> mapTr = new HashMap<>();
        excelTrMap.put(sheetName, mapTr);
        System.out.println("Processing sheet: " + sheetName);

        // Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = sheet.iterator();

        for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) {
            Row row = sheet.getRow(rownum);

            // Get iterator to all cells of current row

            TR tr = new TRNonDB();
            tr.setDate(row.getCell(0).getDateCellValue());
            tr.setDescription(row.getCell(1).getStringCellValue());
            tr.setDebit((float) row.getCell(2).getNumericCellValue());
            tr.setComment(row.getCell(3).getStringCellValue());
            tr.setTrType(row.getCell(4).getStringCellValue());
            tr.setTaxCategory(row.getCell(5).getStringCellValue());
            tr.setProperty(row.getCell(6).getStringCellValue());
            tr.setOtherEntity(row.getCell(7).getStringCellValue());
            String lockedStr = row.getCell(7).getStringCellValue();
            if ("YES".equalsIgnoreCase(lockedStr) || "TRUE".equalsIgnoreCase(lockedStr)) {
                tr.setLocked(true);/*from ww  w . ja  va 2  s  .  c o  m*/
            }
            tr.setTrId();
            mapTr.put(tr.getTrId(), tr);

        }
    }
    return excelTrMap;

}

From source file:at.mukprojects.exclycore.dao.ExclyCoreWriterTest.java

License:Open Source License

/**
 * Tests the XLSXWriter functions.//from w w w.j  a v a 2s .  c  o m
 */
@Test
public void testXLSXWriter() throws Exception {
    XSSFWorkbook workbook = writer.createWorkbook(outputFile, false);
    workbook.createSheet("Sheet New");
    writer.closeWorkbook();

    assertTrue(outputTestFile.exists());

    workbook = writer.createWorkbook(outputFile, true);
    workbook.createSheet("Sheet Add");
    writer.closeWorkbook();

    assertTrue(outputTestFile.exists());

    FileInputStream inputStream = new FileInputStream(outputTestFile);
    workbook = new XSSFWorkbook(inputStream);

    assertTrue(workbook.getNumberOfSheets() == 2);

    workbook.close();
}

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java

License:Apache License

/**
 * Populates the internal maps based on user specified columns This is used
 * to gain TemplateColumn info from existing sheets (non-templates).
 *
 * @param book//  w  ww. j  a v a  2s.  c o  m
 *            the book
 * @param columnNames
 *            the column names
 * @throws Exception
 *             the exception
 */
public void generateMappingsFromList(XSSFWorkbook book, List<String> columnNames) throws Exception {
    for (int i = 0; i < book.getNumberOfSheets(); i++) {
        Sheet sheet = book.getSheetAt(i);
        TemplateSheet templateSheet = new TemplateSheet(sheet.getSheetName());
        populateColumns(sheet, templateSheet, columnNames);
        sheetMap.put(sheet.getSheetName(), templateSheet);
        i++;
    }
}

From source file:com.celtris.exparse.parser.ExcelReader.java

License:Apache License

public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction)
        throws IOException, InstantiationException, IllegalAccessException {

    FileInputStream file = new FileInputStream(new File(absolutePath));

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook);

    Iterator<Sheet> sheetIterator = workbook.iterator();
    List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets());
    int sheetCount = 0;
    while (sheetIterator.hasNext()) {
        sheetCount++;//from  w w w  .  jav a  2 s .  co  m

        ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass);
        Sheet sheet = sheetIterator.next();
        Iterator<Row> rowIterator = sheet.iterator();

        int rowCount = 0;

        // Evaluating header
        if (headerExtraction) {
            if (rowIterator.hasNext()) {

                rowCount++;

                Field[] fields = excelModelClass.getFields();
                List<String> heaaderStr = new ArrayList<String>(fields.length);

                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String cellStrValue = cell.getStringCellValue();

                    heaaderStr.add(cellStrValue);
                }
                excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName());
            }
        }

        while (rowIterator.hasNext()) {
            rowCount++;
            Row row = rowIterator.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            List<String> rowStr = new ArrayList<String>(excelParser.parameterCount());
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellStrValue = "";
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        objFormulaEvaluator.evaluate(cell);
                        cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                    } else {

                        cellStrValue = Double.toString(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellStrValue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case BLANK:

                default:
                    break;
                }
                rowStr.add(cellStrValue);
            }

            excelParser.processRow(rowStr, rowCount, sheet.getSheetName());
        }

        SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(),
                sheetCount);
        sheetDataList.add(sheetData);
    }

    file.close();
    workbook.close();
    return sheetDataList;
}

From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java

License:Apache License

private void readXmlBasedExcel(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data)
        throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        Cell cell = null;//  w ww .j  av  a2 s .c  om

        List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>();

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }

            Map<String, String> columnData = new HashMap<String, String>();

            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK);

                columnData.put("column" + (cellIndex + 1), cell.toString());
            }

            sheetData.add(columnData);
        }

        data.put("sheet" + (sheetIndex + 1), sheetData);
    }

}

From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java

License:Apache License

@SuppressWarnings("rawtypes")
private void readXmlBasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int index = 0; index < sheetCount; index++) {
        XSSFSheet sheet = workbook.getSheetAt(index);

        Sheet s = new Sheet();

        sheets.add(s);// w ww .j a v  a  2s .  c om

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);

            Record record = new Record();

            s.addRecord(record);

            Iterator it = row.cellIterator();

            while (it.hasNext()) {
                record.addCellValue(it.next());
            }
        }
    }

}

From source file:com.consensus.qa.framework.ExcelOperations.java

private XSSFSheet GetSheetFromWorkBook(XSSFWorkbook workbook, String sheetName) {
    int numOfWorkBooks = 0;
    String sheet = null;/*from   w w w.  j a  v a 2s .  c om*/
    numOfWorkBooks = workBook.getNumberOfSheets();
    for (int count = 0; count < numOfWorkBooks; count++) {
        if (sheetName.toString().toLowerCase().contains(workBook.getSheetName(count).toLowerCase())) {
            sheet = workBook.getSheetName(count);
            break;
        }
    }
    if (sheet != null) {
        worksheet = workBook.getSheet(sheet);
    }
    return worksheet;
}

From source file:com.docdoku.server.esindexer.ESTools.java

License:Open Source License

private static String microsoftExcelDocumentToString(InputStream inputStream)
        throws IOException, OpenXML4JException, XmlException {
    StringBuilder sb = new StringBuilder();
    try (InputStream excelStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
            POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
            ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
            sb.append(excelExtractor.getText());
        } else { // New format
            XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
            int numberOfSheets = workBook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workBook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    XSSFRow row = (XSSFRow) rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        sb.append(cell.toString());
                        sb.append(" ");
                    }/* ww w  . j a va2 s  .c  o m*/
                    sb.append("\n");
                }
                sb.append("\n");
            }
        }
    }
    return sb.toString();
}

From source file:com.excel.javafx.frames.MainFrame.java

private void getSourceFileHeaders(File file) {
    try {//  w  ww .  j  a  v a2  s.  c  om
        FileInputStream sourceFile1 = new FileInputStream(file);
        XSSFWorkbook workbook1 = new XSSFWorkbook(sourceFile1);
        sourceSheetSelector.removeAllItems(); //to clear existing headers
        for (int sheetno = 0; sheetno < workbook1.getNumberOfSheets(); sheetno++) {
            sourceSheetSelector.addItem(workbook1.getSheetName(sheetno));
        }
        //sourceColumnSelector();     // to fill columnSelection
    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    }
}