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.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

public static ODLDatastoreAlterable<ODLTableAlterable> importExcel(InputStream stream, ExecutionReport report) {
    //tmpFileBugFix();

    ODLDatastoreAlterable<ODLTableAlterable> ds = ODLFactory.createAlterable();

    Workbook wb = null;
    try {/*from w  ww.  j  a  va2s.c o  m*/
        wb = WorkbookFactory.create(stream);

        String author = getAuthor(wb);
        if (author != null && Strings.equalsStd(author, AppConstants.ORG_NAME)) {
            ds.setFlags(ds.getFlags() | ODLDatastore.FLAG_FILE_CREATED_BY_ODL);
        }

    } catch (Throwable e) {
        throw new RuntimeException(e);
    }

    // look for the schema; remove it from the workbook to simplify the later workbook updating code
    // (the schema gets held by the datastore structure anyway)
    SchemaSheetInformation info = null;
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        if (Strings.equalsStd(sheet.getSheetName(), SCHEMA_SHEET_NAME)) {
            info = importSchemaTables(sheet, report);
            wb.removeSheetAt(i);
            break;
        }
    }

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        ODLTableAlterable table = ds.createTable(sheet.getSheetName(), -1);
        importSheet(table, sheet, info != null ? info.schema : null, false);
    }

    return ds;
}

---

From source file:com.opendoorlogistics.studio.LoadedDatastore.java

License:Open Source License

private void updateWorkbookWithModifications(Workbook wb, ExecutionReport report) {
    // parse the original tables; these will be held in the datastore with the same index as the sheet
    int nbOriginal = originalLoadedDs.getTableCount();
    if (nbOriginal != wb.getNumberOfSheets()) {
        throw new RuntimeException();
    }/*from  ww  w.  ja v  a  2  s . com*/

    ArrayList<ODLTableReadOnly> oldOnesToReadd = new ArrayList<>();
    for (int i = nbOriginal - 1; i >= 0; i--) {
        ODLTableReadOnly originalTable = originalLoadedDs.getTableAt(i);
        ODLTableReadOnly newTable = ds.getTableByImmutableId(originalTable.getImmutableId());

        if (newTable == null) {
            // table was deleted
            wb.removeSheetAt(i);
        } else if (DatastoreComparer.isSame(originalTable, newTable, DatastoreComparer.CHECK_ALL) == false) {
            Sheet sheet = wb.getSheetAt(i);

            boolean sameStructure = DatastoreComparer.isSameStructure(originalTable, newTable,
                    DatastoreComparer.CHECK_ALL);
            if (sameStructure) {
                // re-write all values but skip the header row
                int nbOversized = 0;
                for (int iRow = 0; iRow < newTable.getRowCount(); iRow++) {
                    int iTargetRow = iRow + 1;
                    Row row = sheet.getRow(iTargetRow);
                    if (row == null) {
                        row = sheet.createRow(iTargetRow);
                    }

                    int nc = newTable.getColumnCount();
                    for (int col = 0; col < nc; col++) {
                        Cell cell = row.getCell(col);
                        if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            // don't set the value of formula cells...
                            continue;
                        }
                        if (cell == null) {
                            cell = row.createCell(col);
                        }

                        String sval = TableUtils.getValueAsString(newTable, iRow, col);
                        if (sval != null && sval.length() > PoiIO.MAX_CHAR_COUNT_IN_EXCEL_CELL) {
                            nbOversized++;
                        }
                        cell.setCellValue(sval);
                    }
                }

                // delete any rows after the last row (including 1 for the header)
                int lastOKRow = newTable.getRowCount();
                while (sheet.getLastRowNum() > lastOKRow) {
                    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
                }

                if (nbOversized > 0 && report != null) {
                    report.log(PoiIO.getOversizedWarningMessage(nbOversized, newTable.getName()));
                    ;
                }

            } else {
                // delete and replace. replace after parsing all original tables as we can get table name conflicts
                wb.removeSheetAt(i);
                oldOnesToReadd.add(newTable);
            }

        }

    }

    // re-add any totally replaced tables
    for (ODLTableReadOnly table : oldOnesToReadd) {
        Sheet sheet = wb.createSheet(table.getName());
        if (sheet != null) {
            PoiIO.exportTable(sheet, table, report);
        }
    }

    // add new tables at the end
    for (int i = 0; i < ds.getTableCount(); i++) {
        ODLTableReadOnly newTable = ds.getTableAt(i);
        if (originalLoadedDs.getTableByImmutableId(newTable.getImmutableId()) == null) {
            // new table...
            Sheet sheet = wb.createSheet(newTable.getName());
            if (sheet != null) {
                PoiIO.exportTable(sheet, newTable, report);
            }
        }

    }
}

---

From source file:com.ostrichemulators.semtool.poi.main.POIReader.java

License:Open Source License

public static ImportData readNonloadingSheet(Workbook workbook) {
    ImportData id = new ImportData();

    int sheets = workbook.getNumberOfSheets();
    for (int sheetnum = 0; sheetnum < sheets; sheetnum++) {
        Sheet sheet = workbook.getSheetAt(sheetnum);
        String sheetname = workbook.getSheetName(sheetnum);

        // we need to shoehorn the arbitrary data from a spreadsheet into our
        // ImportData class, which has restrictions on the data...we're going
        // to do it by figuring out the row with the most columns, and then
        // naming all the columns with A, B, C...AA, AB...
        // then load everything as if it was plain data
        // first, figure out our max number of columns
        int rows = sheet.getLastRowNum();
        int maxcols = Integer.MIN_VALUE;
        for (int r = 0; r <= rows; r++) {
            Row row = sheet.getRow(r);//w  ww  . j  a  v a 2  s .  c o m
            if (null != row) {
                int cols = (int) row.getLastCellNum();
                if (cols > maxcols) {
                    maxcols = cols;
                }
            }
        }

        // second, make "properties" for each column
        LoadingSheetData nlsd = new LoadingSheetData(sheetname, "A");
        for (int c = 1; c < maxcols; c++) {
            nlsd.addProperty(Integer.toString(c));
        }

        // lastly, fill the sheets
        for (int r = 0; r <= rows; r++) {
            Row row = sheet.getRow(r);
            if (null != row) {
                Map<String, Value> propmap = new HashMap<>();

                int lastpropcol = row.getLastCellNum();
                for (int c = 1; c <= lastpropcol; c++) {
                    String val = getString(row.getCell(c));
                    if (!val.isEmpty()) {
                        propmap.put(Integer.toString(c), VF.createLiteral(val));
                    }
                }

                nlsd.add(getString(row.getCell(0)), propmap);
            }
        }

        if (!nlsd.isEmpty()) {
            id.add(nlsd);
        }
    }

    return id;
}

---

From source file:com.projectswg.tools.controllers.MainController.java

License:Open Source License

private void handlePopulateList(File source) throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(source);
    outputSelectionList.clear();/*from   w w w  .  jav a  2 s .c o  m*/
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        outputSelectionList.add(getFileItem(source.getAbsolutePath(), workbook.getSheetAt(i)));
    }
    workbook.close();
}

---

From source file:com.projectswg.tools.ExcelToIff.java

License:Open Source License

private static void convertWorkbook(String path) {
    File file = new File(path);
    if (!file.exists()) {
        System.err.println(String.format("Could not convert %s as it doesn't exist!", path));
        return;//w  ww.  j a va2  s . com
    }

    try {
        Workbook workbook = WorkbookFactory.create(file);
        System.out.println("Converting sheets from workbook " + file.getAbsolutePath());
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            path = file.getAbsolutePath().split("\\.")[0] + "_" + sheet.getSheetName() + ".iff";
            convertSheet(new File(path), sheet);
        }
        System.out.println("Conversion for workbook " + file.getAbsolutePath() + " completed.");
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
}

---

From source file:com.qihang.winter.poi.cache.ExcelCache.java

License:Apache License

public static Workbook getWorkbook(String url, Integer[] sheetNums, boolean needAll) {
    InputStream is = null;//w w  w  .jav  a 2s  .  com
    List<Integer> sheetList = Arrays.asList(sheetNums);
    try {
        is = com.qihang.winter.poi.cache.manager.POICacheManager.getFile(url);
        Workbook wb = WorkbookFactory.create(is);
        // sheet
        if (!needAll) {
            for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
                if (!sheetList.contains(i)) {
                    wb.removeSheetAt(i);
                }
            }
        }
        return wb;
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage(), e);
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        try {
            is.close();
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
    }
    return null;
}

---

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet,
        Map<String, Object> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }//from   w  w w. j  av a  2s.com
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map);
        }
        if (dataSet != null) {
            // step 4. ?
            dataHanlder = params.getDataHanlder();
            if (dataHanlder != null) {
                needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
            }
            addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

@Override
public String[] getSheetNames(File f) throws IOException {
    Workbook wb = open(f);

    String[] names = new String[wb.getNumberOfSheets()];
    for (int i = 0; i < names.length; i++) {
        names[i] = wb.getSheetName(i);//from  www .  ja  v a  2 s. com
    }
    return names;
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

protected List<Sheet> identifySheets(int[] sheetNums, Workbook workbook) {
    int maxSheetNumber = workbook.getNumberOfSheets() - 1;

    List<Sheet> sheets = new ArrayList<Sheet>(sheetNums.length);
    for (int sn : sheetNums) {
        if (sn > maxSheetNumber)
            throw new IllegalArgumentException("Sheet not found with index '" + sn + "'");

        sheets.add(workbook.getSheetAt(sn));
    }/*from w  w  w  .j  a v a 2s .  co m*/
    return sheets;
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private void excerpt(Workbook wb, List<Sheet> sheetsToKeep, OutputStream output) throws IOException {
    // Make the requested sheets be read only
    Set<String> keepNames = new HashSet<String>();
    for (Sheet s : sheetsToKeep) {
        keepNames.add(s.getSheetName());
        for (Row r : s) {
            for (Cell c : r) {
                if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    switch (c.getCachedFormulaResultType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        double vd = c.getNumericCellValue();
                        c.setCellType(Cell.CELL_TYPE_NUMERIC);
                        c.setCellValue(vd);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        RichTextString vs = c.getRichStringCellValue();
                        c.setCellType(Cell.CELL_TYPE_STRING);
                        c.setCellValue(vs);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean vb = c.getBooleanCellValue();
                        c.setCellType(Cell.CELL_TYPE_BOOLEAN);
                        c.setCellValue(vb);
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        c.setCellType(Cell.CELL_TYPE_BLANK);
                        break;
                    }/*from ww w  .  j  a  va2  s  .c  o  m*/
                }
            }
        }
    }

    // Remove all the other sheets
    // Note - work backwards! Avoids order changing under us
    for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
        String name = wb.getSheetName(i);
        if (!keepNames.contains(name)) {
            wb.removeSheetAt(i);
        }
    }

    // Save
    wb.write(output);
}

---

• «
• 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
• »