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

List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. 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.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * Purpose of this method to read an Microsoft Workbook in DataCollection dc
 * supplied along with workbook. Each sheet will be a grid in dc with the
 * same name as sheet name.//  w  w  w  .ja  v a  2  s . c  om
 * 
 * @param wb
 *            This is an instance of MS excel workbook(i.e .xls or .xlsx)
 *            created by POI WorkbookFactory.
 * @param dc
 */
public void readAWorkbook(Workbook wb, DataCollection dc) {
    if (wb == null || dc == null) {
        throw new IllegalArgumentException(XLSReader.ILLEGAL_ARGUMENT);
    }

    int nbrSheets = wb.getNumberOfSheets();
    String sheetName = null;
    String gridName = dc.getTextValue("gridName", null);
    Sheet sheet = null;

    int nbrColumns = -1;
    int nbrPhysicalRows = 0;

    for (int k = 0; k < nbrSheets; k++) {

        sheet = wb.getSheetAt(k);
        sheetName = sheet.getSheetName();
        nbrPhysicalRows = sheet.getPhysicalNumberOfRows();
        if (nbrPhysicalRows < 2) {
            Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS);
            // dc.addMessage(XLSReader.INSUFFICIENT_ROWS, sheetName +
            // XLSReader.INSUFFICIENT_DATA_ROWS);
            continue;
        }

        try {
            nbrColumns = this.readASheet(sheet);
            /**
             * swallow all the exceptions during excel sheet reading and put
             * appropriate message. While reading excel following exceptions
             * can come: 1. IllegalStateExcetion if column data type
             * mismatch in excel sheet. 2. ExilityException etc.
             */
        } catch (ExilityException e) {
            String msg = this.replaceMessageParams(XLSReader.EXCEPTION_MSG,
                    new String[] { sheetName, e.getMessage() });
            dc.addError(msg);
            Spit.out(e);
        }

        if (nbrColumns == -1) {
            continue;
        }

        /**
         * This is for little more flexibility to user if they have only one
         * sheet to be read and has supplied a gridName along with service
         * then let set first sheet one as given gridName(In case of simple
         * file upload and read content as grid)
         */
        if (gridName != null) {
            sheetName = gridName;
            gridName = null;
        }

        dc.addGrid(sheetName, this.getGrid());
        Spit.out(sheetName + " added to dc with " + this.rows.size() + " row(s)");
        this.columnsData.clear();
        this.rows.clear();

        // this.printXlSRec(dc.getGrid(sheetName).getRawData());

    }
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/**
 * /*w  ww .j  a va 2s  .  c  o  m*/
 * @param inputStream
 * @param dc
 * @return
 */
private List<Sheet> getSheets(InputStream inputStream, DataCollection dc) {
    List<Sheet> sheets = new ArrayList<Sheet>();
    Workbook workbook = null;
    boolean valuesSheetFound = false;
    try {
        workbook = WorkbookFactory.create(inputStream);
        int n = workbook.getNumberOfSheets();
        for (int i = 0; i < n; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int nbrRows = sheet.getPhysicalNumberOfRows();
            String sheetName = sheet.getSheetName();
            if (nbrRows > 0) {
                sheets.add(sheet);

                if (!valuesSheetFound && sheetName.equals(CommonFieldNames.VALUES_TABLE_NAME)) {
                    /*
                     * this is supposed to be the first one. swap it if
                     * required
                     */
                    if (i != 0) {
                        sheets.add(i, sheets.get(0));
                        sheets.add(0, sheet);
                    }
                    valuesSheetFound = true;
                }
            }

        }
    } catch (Exception e) {
        String msg = "Error while reading spread sheet. " + e.getMessage();
        Spit.out(msg);
        if (dc != null) {
            dc.addError(msg);
        }
    }
    return sheets;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

public List<Xtmpinddl> analizeXlsIndi(UploadedFile file, final DcsUsuario usuario, List<DcsCatPais> paises,
        List<DcsCatIndicadores> indicadores) throws DCSException {
    Workbook excelXLS = null;
    List<Xtmpinddl> listaCarga = null;
    try {//w ww  .java 2 s. co  m
        String extension = getExtension(file.getFileName());
        Iterator<Row> rowIterator;
        if (extension.equalsIgnoreCase("xlsx")) {
            excelXLS = new XSSFWorkbook(file.getInputstream());
        } else if (extension.equalsIgnoreCase("xls")) {
            excelXLS = new HSSFWorkbook(file.getInputstream());
        }
        int numberOfSheets = excelXLS != null ? excelXLS.getNumberOfSheets() : 0;
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = excelXLS != null ? excelXLS.getSheetAt(i) : null;
            rowIterator = sheet != null ? sheet.iterator() : null;
            if (sheet != null && i == 0) {
                listaCarga = this.analizeSheetIndi(rowIterator, usuario, sheet.getSheetName(), paises,
                        indicadores);
                if (!listaCarga.isEmpty()) {
                    loadedSheets.add(sheet.getSheetName().trim().toUpperCase());
                } else {
                    omittedSheets.add(sheet.getSheetName().trim().toUpperCase() + ", Empty");
                }
            } else {
                String mensaje = sheet != null ? sheet.getSheetName().trim().toUpperCase() + ", not valid."
                        : "Not valid.";
                omittedSheets.add(mensaje);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
        throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
    } finally {
        try {
            file.getInputstream().close();
        } catch (IOException ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
            throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
        }
    }
    return listaCarga;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

public List<XtmpinddlFlota> analizeXlsFlota(UploadedFile file, final DcsUsuario usuario,
        List<DcsCatPais> paises) throws DCSException {
    Workbook excelXLS = null;
    List<XtmpinddlFlota> listaCarga = null;
    try {/*w  w  w  .j av  a2s.co m*/
        String extension = getExtension(file.getFileName());
        Iterator<Row> rowIterator;
        if (extension.equalsIgnoreCase("xlsx")) {
            excelXLS = new XSSFWorkbook(file.getInputstream());
        } else if (extension.equalsIgnoreCase("xls")) {
            excelXLS = new HSSFWorkbook(file.getInputstream());
        }
        int numberOfSheets = excelXLS != null ? excelXLS.getNumberOfSheets() : 0;
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = excelXLS != null ? excelXLS.getSheetAt(i) : null;
            rowIterator = sheet != null ? sheet.iterator() : null;
            if (sheet != null && i == 0) {
                listaCarga = this.analizeSheetFlota(rowIterator, usuario, sheet.getSheetName(), paises);
                if (!listaCarga.isEmpty()) {
                    loadedSheets.add(sheet.getSheetName().trim().toUpperCase());
                } else {
                    omittedSheets.add(sheet.getSheetName().trim().toUpperCase() + ", Empty");
                }
            } else {
                String mensaje = sheet != null ? sheet.getSheetName().trim().toUpperCase() + ", not valid."
                        : "Not valid.";
                omittedSheets.add(mensaje);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
        throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
    } finally {
        try {
            file.getInputstream().close();
        } catch (IOException ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, "Error IO", ex);
            throw new DCSException("An error ocurred while analizing the file: " + ex.getMessage());
        }
    }
    return listaCarga;
}

From source file:com.github.wnameless.workbookaccessor.WorkbookReader.java

License:Apache License

/**
 * Creates a {@link WorkbookReader} by given {@link Workbook}. Assumes there
 * is a header included in the spreadsheet.
 * //from   www  .j a  va  2  s  .  c  o m
 * @param workbook
 *          a {@link Workbook}
 */
public WorkbookReader(Workbook workbook) {
    this.workbook = workbook;
    if (workbook.getNumberOfSheets() == 0)
        workbook.createSheet();
    sheet = workbook.getSheetAt(0);
    setHeader();
}

From source file:com.github.wnameless.workbookaccessor.WorkbookWriter.java

License:Apache License

/**
 * Creates a {@link WorkbookWriter} by given {@link Workbook}.
 * //w  w w .  java  2  s . co m
 * @param workbook
 *          a {@link Workbook}
 */
public WorkbookWriter(Workbook workbook) {
    this.workbook = workbook;
    if (workbook.getNumberOfSheets() == 0)
        workbook.createSheet();
    sheet = workbook.getSheetAt(0);
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.CommentsAnalysisReportGenerator.java

License:Apache License

/**
 * Create Report File./*from   w ww .j  a v a2 s  . com*/
 */
protected File getFile(String p_reportType, Job p_job, Workbook p_workBook) {
    String langInfo = null;
    // If the Workbook has only one sheet, the report name should contain language pair info, such as en_US_de_DE.
    if (p_workBook != null && p_workBook.getNumberOfSheets() == 1) {
        Sheet sheet = p_workBook.getSheetAt(0);
        String srcLang = null, trgLang = null;
        if (p_job != null) {
            srcLang = p_job.getSourceLocale().toString();
        }
        if (srcLang == null) {
            Row languageInfoRow = sheet.getRow(LANGUAGE_INFO_ROW);
            if (languageInfoRow != null) {
                srcLang = languageInfoRow.getCell(0).getStringCellValue();
                srcLang = srcLang.substring(srcLang.indexOf("[") + 1, srcLang.indexOf("]"));
                trgLang = languageInfoRow.getCell(1).getStringCellValue();
                trgLang = trgLang.substring(trgLang.indexOf("[") + 1, trgLang.indexOf("]"));
            } else {
                Row dataRow = sheet.getRow(sheet.getLastRowNum());
                if (dataRow != null) {
                    try {
                        long jobId = (long) dataRow.getCell(0).getNumericCellValue();
                        Job job = ServerProxy.getJobHandler().getJobById(jobId);
                        srcLang = job.getSourceLocale().toString();
                    } catch (Exception e) {
                    }

                }
            }
        }
        if (trgLang == null) {
            trgLang = sheet.getSheetName();
        }
        if (srcLang != null && trgLang != null) {
            langInfo = srcLang + "_" + trgLang;
        }
    }

    return ReportHelper.getReportFile(p_reportType, p_job, ReportConstants.EXTENSION_XLSX, langInfo);
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

/**
 * Create workbook name areas for category failure drop down list, it is
 * from "AA8" to "AAn"./*from  w w w  .j a  v a  2 s  .c  om*/
 * <P>
 * Only write the data of drop down list into the first sheet as it can be
 * referenced from all sheets.
 * </P>
 * <P>
 * The formula is like
 * "[sheetName]!$AA$[startRow]:$AA$[endRow]",i.e."TER!$AA$8:$AA$32".
 * </P>
 */
private void createCategoryFailureNameArea(Workbook p_workbook) {
    try {
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> categories = getFailureCategoriesList();
            // Set the categories in "AA" column, starts with row 8.
            int col = 26;
            for (int i = 0; i < categories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(categories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AA$" + (SEGMENT_START_ROW + 1) + ":$AA$"
                    + (SEGMENT_START_ROW + categories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(CATEGORY_FAILURE_DROP_DOWN_LIST);

            // Hide "AA" column
            firstSheet.setColumnHidden(26, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void createQualityAssessmentNameArea(Workbook p_workbook) {
    try {//from  w  w  w .j a v  a2 s  .  c o  m
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> qualityCategories = getQualityAssessmentList();
            // Set the categories in "AA" column, starts with row 8.
            int col = 27;
            for (int i = 0; i < qualityCategories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(qualityCategories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AB$" + (SEGMENT_START_ROW + 1) + ":$AB$"
                    + (SEGMENT_START_ROW + qualityCategories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(QUALITY_ASSESSMENT_LIST);

            // Hide "AB" column
            firstSheet.setColumnHidden(27, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void createMarketSuitabilityNameArea(Workbook p_workbook) {
    try {/*  w ww  .  java2 s  .  co  m*/
        // Ensure the name area is written only one time,otherwise it has
        // problem when open generated excel file.
        if (p_workbook.getNumberOfSheets() == 1) {
            Sheet firstSheet = getSheet(p_workbook, 0);
            List<String> marketCategories = getMarketSuitabilityList();
            // Set the categories in "AC" column, starts with row 11.
            int col = 28;
            for (int i = 0; i < marketCategories.size(); i++) {
                Row row = getRow(firstSheet, SEGMENT_START_ROW + i);
                Cell cell = getCell(row, col);
                cell.setCellValue(marketCategories.get(i));
            }

            String formula = firstSheet.getSheetName() + "!$AC$" + (SEGMENT_START_ROW + 1) + ":$AC$"
                    + (SEGMENT_START_ROW + marketCategories.size());
            Name name = p_workbook.createName();
            name.setRefersToFormula(formula);
            name.setNameName(MARKET_SUITABILITY_LIST);

            // Hide "AC" column
            firstSheet.setColumnHidden(28, true);
        }
    } catch (Exception e) {
        logger.error("Error when create hidden area for category failures.", e);
    }
}