Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getNumberOfSheets

Introduction

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

Prototype

@Override
public int getNumberOfSheets()

Source Link

Document

get the number of spreadsheets in the workbook (this will be three after serialization)

Usage

From source file:it.smartcommunitylab.riciclo.app.importer.converter.DataImporter.java

License:Apache License

private Rifiuti readExcel(InputStream inp) throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
    ExcelExtractor extractor = new ExcelExtractor(wb);

    extractor.setFormulasNotResults(true);
    extractor.setIncludeSheetNames(false);

    Rifiuti rifiuti = new Rifiuti();

    Set<String> sheetNames = Sets.newHashSet();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        sheetNames.add(wb.getSheetAt(i).getSheetName());
    }/*from   www  .j  ava2  s .  c o  m*/

    Set missingExpected = Sets.newHashSet(expectedSheets);
    missingExpected.removeAll(sheetNames);

    Set additionalFound = Sets.newHashSet(sheetNames);
    additionalFound.removeAll(expectedSheets);

    if (!missingExpected.isEmpty() || !additionalFound.isEmpty()) {
        throw new ImportError(Lists.newArrayList("Missing sheet(s) expected: " + missingExpected,
                "Additional sheet(s) found: " + additionalFound));
    }

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        Thread.sleep(500);
        //         System.out.println(sheet.getSheetName());
        //         if (sheet.getRow(0).getLastCellNum() == 1 && !oneColumnAsMany.contains(sheet.getSheetName())) {
        //         } else {
        {
            System.err.println(">" + sheet.getSheetName());
            List<Map<String, String>> result = getSheetMap(sheet);
            mapMap(rifiuti, sheet.getSheetName(), result);
        }
    }

    completePuntiRaccolta(rifiuti);

    return rifiuti;
}

From source file:it.smartcommunitylab.ungiorno.importer.Importer.java

License:Apache License

/**
 * @param appId/*from   w  w w.  j a  v a 2  s  .  c  om*/
 * @param schoolId
 * @param wb
 * @throws ImportError 
 */
private void mapChildrenData(String appId, String schoolId, HSSFWorkbook wb) throws ImportError {
    Map<String, KidProfile> kidMap = null;
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        List<Map<String, String>> result = getSheetMap(sheet);
        if (SHEET_PROFILO.equals(sheet.getSheetName())) {
            kidMap = parseKidProfiles(appId, schoolId, result);
        }
        if (SHEET_UTENTI.equals(sheet.getSheetName())) {
            parseUsers(appId, schoolId, result, kidMap);
        }
        if (SHEET_DELEGHE.equals(sheet.getSheetName())) {
            parseDeleghe(appId, schoolId, result, kidMap);
        }
        if (SHEET_ALLERGIE.equals(sheet.getSheetName())) {
            parseAllergie(appId, schoolId, result, kidMap);
        }
        if (SHEET_BUS.equals(sheet.getSheetName())) {
            parseKidBus(appId, schoolId, result, kidMap);
        }
        if (SHEET_INSEGNANTI.equals(sheet.getSheetName())) {
            parseInsegnanti(appId, schoolId, result, kidMap);
        }

    }
}

From source file:it.smartcommunitylab.ungiorno.importer.Importer.java

License:Apache License

private HSSFWorkbook readFile(InputStream inp, Set<String> expected) throws IOException, ImportError {

    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
    ExcelExtractor extractor = new ExcelExtractor(wb);

    extractor.setFormulasNotResults(true);
    extractor.setIncludeSheetNames(false);

    Set<String> sheetNames = Sets.newHashSet();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        sheetNames.add(wb.getSheetAt(i).getSheetName());
    }/* ww w. ja  va  2s .  com*/

    Set<String> missingExpected = new HashSet<String>(expected);
    missingExpected.removeAll(sheetNames);

    Set<String> additionalFound = Sets.newHashSet(sheetNames);
    additionalFound.removeAll(expected);

    if (!missingExpected.isEmpty() || !additionalFound.isEmpty()) {
        System.err.println("Missing sheet(s) expected: " + missingExpected + " - Additional sheet(s) found: "
                + additionalFound);
        throw new ImportError(Lists.newArrayList("Missing sheet(s) expected: " + missingExpected,
                "Additional sheet(s) found: " + additionalFound));
    }
    return wb;
}

From source file:it.smartcommunitylab.ungiorno.importer.Importer.java

License:Apache License

/**
 * @param wb/*  ww  w  .j av  a2s  .c o m*/
 * @param schoolProfile
 * @param teachers
 */
private void mapSchoolData(String appId, String schoolId, HSSFWorkbook wb) {
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        {
            List<Map<String, String>> result = getSheetMap(sheet);
            if (SHEET_PROFILO.equals(sheet.getSheetName())) {
                parseProfile(schoolProfile, result);
            }
            if (SHEET_ASSENZE.equals(sheet.getSheetName())) {
                List<TypeDef> types = parseTypes(result);
                schoolProfile.setAbsenceTypes(types);
            }
            if (SHEET_MALATTIE.equals(sheet.getSheetName())) {
                List<TypeDef> types = parseTypes(result);
                schoolProfile.setFrequentIllnesses(types);
            }
            if (SHEET_TIPOLOGIE_NOTE.equals(sheet.getSheetName())) {
                List<TypeDef> types = parseTypes(result);
                schoolProfile.setTeacherNoteTypes(types);
            }
            if (SHEET_CIBI.equals(sheet.getSheetName())) {
                List<TypeDef> types = parseTypes(result);
                schoolProfile.setFoodTypes(types);
            }
            if (SHEET_BUS.equals(sheet.getSheetName())) {
                schoolProfile.setBuses(parseBuses(result));
            }
            if (SHEET_SEZIONI.equals(sheet.getSheetName())) {
                schoolProfile.setSections(parseSections(result));
            }
            if (SHEET_INSEGNANTI.equals(sheet.getSheetName())) {
                parseTeachers(appId, schoolId, result);
            }
        }
    }
}

From source file:javaapplication2.Frame1.java

void fillData(File file) {
    int index = -1;
    HSSFWorkbook workbook = null;
    try {/*from www  .  j  a  va2 s. co  m*/
        try {
            FileInputStream inputStream = new FileInputStream(file);
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException ex) {
            Logger.getLogger(Frame1.class.getName()).log(Level.SEVERE, null, ex);
        }

        String[] strs = new String[workbook.getNumberOfSheets()];
        //get all sheet names from selected workbook
        for (int i = 0; i < strs.length; i++) {
            strs[i] = workbook.getSheetName(i);
        }
        JFrame frame = new JFrame("Input Dialog");

        String selectedsheet = (String) JOptionPane.showInputDialog(frame,
                "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null,
                strs, strs[0]);

        if (selectedsheet != null) {
            for (int i = 0; i < strs.length; i++) {
                if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
                    index = i;
            }
            HSSFSheet sheet = workbook.getSheetAt(index);
            HSSFRow row = sheet.getRow(0);

            headers.clear();
            //int value=row.getLastCellNum();
            for (int i = 0; i < row.getLastCellNum(); i++) {
                HSSFCell cell1 = row.getCell(i);
                headers.add(cell1.toString());

            }

            data.clear();
            for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                Vector d = new Vector();
                row = sheet.getRow(j);
                int noofrows = row.getLastCellNum();
                for (int i = 0; i < noofrows; i++) { //To handle empty excel cells 
                    HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
                    d.add(cell.toString());
                }
                d.add("\n");
                data.add(d);
            }
        } else {
            return;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java

License:Open Source License

/**
 * Generates the actual workbook of data.
 *
 * @param timelordData the data to generate a workbook for
 * @return the workbook/*from w ww .  j a  v  a  2s.c  o  m*/
 */
protected HSSFWorkbook generateWorkbook(TimelordData timelordData) {
    HSSFWorkbook wb = new HSSFWorkbook();

    // Build the Map of the Styles that will be applied to cells
    // in the workbook
    Map<String, HSSFCellStyle> styleMap = buildStyleMap(wb);
    Map<String, List<String>> sheetToNotes = new TreeMap<String, List<String>>(new DateComparator());

    // Since there is an issue re-ordering sheets after they
    // have been created.  First create the book with all needed
    // sheets
    preCreateAllSheets(wb, timelordData, sheetToNotes, styleMap);

    // After all the sheets have been pre-created, iterate through all
    // the tasks to add them into the sheets.
    int rowNum = addAllTasks(wb, timelordData, sheetToNotes, styleMap);

    // This section applies all the styles, creates the footers and adds
    // the notes onto the sheet.
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = wb.getSheetAt(i);
        String sheetName = wb.getSheetName(i);
        createFooterRows(sheet, rowNum, styleMap);

        // This will apply styles to the rows that had no task associated
        // for a given week.
        for (int j = 1; j < rowNum - 1; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row == null) {
                row = sheet.createRow(j);
                row.setHeight((short) 0);
                HSSFCell cell = row.createCell((short) 0);
                cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameStyle"));
                cell.setCellValue("");

                cell = row.createCell(MAX_COLUMN);
                cell.setCellStyle((HSSFCellStyle) styleMap.get("totalColumnStyle"));
                cell.setCellFormula("SUM(B" + (j + 1) + ":H" + (j + 1) + ")");
            }
        }

        List<String> noteList = sheetToNotes.get(sheetName);
        createNotesRows(sheet, noteList);

        HSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true);
    }

    // Finally order the sheets properly
    if (logger.isDebugEnabled()) {
        logger.debug("Re-ordering sheets under final order.");
    }

    return wb;
}

From source file:net.sourceforge.fenixedu.presentationTier.Action.residenceManagement.ResidenceManagementDispatchAction.java

License:Open Source License

private String[] getAllSpreadsheets(HSSFWorkbook wb) {
    String[] spreadsheets = new String[wb.getNumberOfSheets()];
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        spreadsheets[i] = wb.getSheetName(i);
    }//from   w  w w  . jav a 2 s. c o  m
    return spreadsheets;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei.//from ww  w .  j  av  a 2 s. c o  m
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(File excelSheet) throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    int numberOfSheets = wb.getNumberOfSheets();
    List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();

        int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows][columns];
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    return sheetList;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und
 * Listen von den Zeilen der sheets von der excel-Datei.
 *
 * @param excelSheet/*from w  ww.  j  a va  2  s  .c  o  m*/
 *            Die excel-Datei.
 * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit
 *         den sheets beinhalten weitere Listen mit String die jeweils eine Zeile
 *         reprsentieren.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<List<List<String>>> exportWorkbookAsStringList(File excelSheet)
        throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            reihe.add(Boolean.toString(cell.getBooleanCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            reihe.add(cell.getCellFormula());
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            reihe.add(Double.toString(cell.getNumericCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            reihe.add(cell.getRichStringCellValue().getString());
                        }
                    }
                }
                excelSheetList.add(reihe);
            }
        }
        sl.add(excelSheetList);
    }
    return sl;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Replace null cells into empty cells.//from w w w  . ja va2 s .c  o  m
 *
 * @param excelSheet
 *            the excel sheet
 * @return the HSSF workbook
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws FileNotFoundException
 *             the file not found exception
 */
public static HSSFWorkbook replaceNullCellsIntoEmptyCells(File excelSheet)
        throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j, Cell.CELL_TYPE_BLANK);
                    }
                }
            }
        }
    }
    return wb;
}