Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook 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:axiom.util.TextExtractor.java

License:Open Source License

public static String msExcelExtractor(InputStream is) throws Exception {
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    StringBuffer sb = new StringBuffer();

    final int numSheets = wb.getNumberOfSheets();
    for (int k = 0; k < numSheets; k++) {
        HSSFSheet sheet = wb.getSheetAt(k);
        Iterator rIt = sheet.rowIterator();
        while (rIt.hasNext()) {
            HSSFRow row = (HSSFRow) rIt.next();
            Iterator cIt = row.cellIterator();
            while (cIt.hasNext()) {
                HSSFCell cell = (HSSFCell) cIt.next();
                sb.append(cell.toString()).append(" ");
            }//from  www. j  a v a 2  s .  com
        }
    }

    return sb.toString();
}

---

From source file:cdc.impl.datasource.office.ExcelDataSource.java

License:LGPL

private static DataColumnDefinition[] readDataModel(String sourceName, Map params)
        throws IOException, RJException {
    BufferedInputStream is = null;
    try {//  w  w w .  ja v a2 s.com
        is = new BufferedInputStream(new FileInputStream((String) params.get(PARAM_FILE)));
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
        String[] sheets;
        if (params.get(PARAM_SHEET) != null) {
            sheets = new String[] { (String) params.get(PARAM_SHEET) };
        } else {
            sheets = new String[wb.getNumberOfSheets()];
            for (int i = 0; i < sheets.length; i++) {
                sheets[i] = wb.getSheetName(i);
            }
        }
        if (sheets.length == 0) {
            throw new RJException("Excel file " + params.get(PARAM_FILE) + " does not provide any sheets.");
        }
        List cols = new ArrayList();
        HSSFSheet sheet = wb.getSheet(sheets[0]);
        if (sheet == null) {
            //System.out.println("Thorwing: " + "Sheet " + sheets[0] + " is not provided by file " + params.get(PARAM_FILE));
            throw new RJException(
                    "Sheet '" + sheets[0] + "' is not provided by file " + params.get(PARAM_FILE));
        }
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
        //first row should provide data model
        HSSFRow row = sheet.getRow(0);
        evaluator.setCurrentRow(row);
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = row.getCell(i);
            cols.add(new ExcelDataColumnDefinition(decodeValue(cell, evaluator),
                    DataColumnDefinition.TYPE_STRING, sourceName, i));
        }
        for (int i = 1; i < sheets.length; i++) {
            sheet = wb.getSheet(sheets[i]);
            if (sheet == null) {
                throw new RJException("Sheet '" + params.get(PARAM_SHEET) + "' is not provided by file "
                        + params.get(PARAM_FILE));
            }
            evaluator = new HSSFFormulaEvaluator(sheet, wb);
            //first row should provide data model
            row = sheet.getRow(0);
            evaluator.setCurrentRow(row);
            List localCols = new ArrayList();
            for (i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                HSSFCell cell = row.getCell(i);
                DataColumnDefinition col = new ExcelDataColumnDefinition(decodeValue(cell, evaluator),
                        DataColumnDefinition.TYPE_STRING, sourceName, i);
                localCols.add(col);
            }
            List toRemove = new ArrayList();
            for (Iterator iterator = cols.iterator(); iterator.hasNext();) {
                DataColumnDefinition object = (DataColumnDefinition) iterator.next();
                if (!localCols.contains(object)) {
                    toRemove.add(object);
                }
            }
            cols.removeAll(toRemove);
        }

        return (DataColumnDefinition[]) cols.toArray(new DataColumnDefinition[] {});
    } finally {
        if (is != null) {
            is.close();
        }
    }
}

---

From source file:cdc.impl.datasource.office.SheetsIterator.java

License:LGPL

public SheetsIterator(HSSFWorkbook workbook, String[] sheetNames) {
    this.workbook = workbook;
    this.sheetNames = sheetNames;
    if (sheetNames == null) {
        this.sheetNames = new String[workbook.getNumberOfSheets()];
        for (int i = 0; i < this.sheetNames.length; i++) {
            this.sheetNames[i] = workbook.getSheetName(i);
        }/*  www  .j av  a2  s . c  om*/
    }
}

---

From source file:com.b510.excel.client.HSSFReadWrite.java

License:Apache License

/**
 * Method main// ww w. j av a2  s . c o m
 *
 * Given 1 argument takes that as the filename, inputs it and dumps the cell
 * values/types out to sys.out.<br/>
 *
 * given 2 arguments where the second argument is the word "write" and the
 * first is the filename - writes out a sample (test) spreadsheet see
 * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/>
 *
 * given 2 arguments where the first is an input filename and the second an
 * output filename (not write), attempts to fully read in the spreadsheet
 * and fully write it out.<br/>
 *
 * given 3 arguments where the first is an input filename and the second an
 * output filename (not write) and the third is "modify1", attempts to read
 * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col
 * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1".
 * If you take the output from the write test, you'll have a valid scenario.
 */
public static void main(String[] args) {
    String[] name = new String[2];
    name[0] = "HSSFReadWrite.xlsx";
    name[1] = "write";
    if (name.length < 1) {
        System.err.println("At least one argument expected");
        return;
    }
    String fileName = name[0];
    try {
        if (name.length < 2) {

            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);

            System.out.println("Data dump:\n");

            for (int k = 0; k < wb.getNumberOfSheets(); k++) {
                HSSFSheet sheet = wb.getSheetAt(k);
                int rows = sheet.getPhysicalNumberOfRows();
                System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s).");
                for (int r = 0; r < rows; r++) {
                    HSSFRow row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }

                    int cells = row.getPhysicalNumberOfCells();
                    System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");
                    for (int c = 0; c < cells; c++) {
                        HSSFCell cell = row.getCell(c);
                        String value = null;

                        switch (cell.getCellType()) {

                        case HSSFCell.CELL_TYPE_FORMULA:
                            value = "FORMULA value=" + cell.getCellFormula();
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC:
                            value = "NUMERIC value=" + cell.getNumericCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_STRING:
                            value = "STRING value=" + cell.getStringCellValue();
                            break;

                        default:
                        }
                        System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                    }
                }
            }
        } else if (name.length == 2) {
            if (name[1].toLowerCase().equals("write")) {
                System.out.println("Write mode");
                long time = System.currentTimeMillis();
                HSSFReadWrite.testCreateSampleSheet(fileName);

                System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
            } else {
                System.out.println("readwrite test");
                HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
                FileOutputStream stream = new FileOutputStream(name[1]);

                wb.write(stream);
                stream.close();
            }
        } else if (name.length == 3 && name[2].toLowerCase().equals("modify1")) {
            // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to
            // string "MODIFIED CELL!!"

            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
            FileOutputStream stream = new FileOutputStream(name[1]);
            HSSFSheet sheet = wb.getSheetAt(0);

            for (int k = 0; k < 25; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            for (int k = 74; k < 100; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            HSSFRow row = sheet.getRow(39);
            HSSFCell cell = row.getCell(3);
            cell.setCellValue("MODIFIED CELL!!!!!");

            wb.write(stream);
            stream.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:com.bayareasoftware.chartengine.ds.ExcelDataSource.java

License:Apache License

/**
 * work around bug with named cells//from  ww w.j a  v a2  s  .c o  m
 * 
 */
private static void rewriteFormulas(final HSSFWorkbook workbook) {
    //if (true) return;
    // build up a cache of names
    // this is just an easy way of fetching the HSSFName based on the string
    // representation of the name
    final Map<String, HSSFName> nameCache = new HashMap<String, HSSFName>(workbook.getNumberOfNames());
    for (int i = 0; i < workbook.getNumberOfNames(); i++) {
        final HSSFName name = workbook.getNameAt(i);
        nameCache.put(name.getNameName(), name);
    }
    // remove all the sheet names from the name references, having the sheet
    // names around messes up the formulas
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        nameCache.remove(workbook.getSheetName(i));
    }
    //p("Names: " + nameCache.keySet());

    // loop over all the cells and rewrite the formula ones
    for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) {
        final HSSFSheet sheet = workbook.getSheetAt(sheetCount);
        for (final Iterator rowIterator = sheet.rowIterator(); rowIterator.hasNext();) {
            final HSSFRow row = (HSSFRow) rowIterator.next();
            for (final Iterator cellIterator = row.cellIterator(); cellIterator.hasNext();) {
                final HSSFCell cell = (HSSFCell) cellIterator.next();
                if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    String formula = cell.getCellFormula();
                    for (final String name : nameCache.keySet()) {
                        final Pattern pattern = Pattern.compile("(\\W|^)" + name + "(\\W|$)",
                                Pattern.CASE_INSENSITIVE);
                        final HSSFName hssfName = nameCache.get(name);
                        formula = pattern.matcher(formula)
                                .replaceAll("$1" + hssfName.getReference().replace("$", "\\$") + "$2");
                    }
                    //p("Resetting Cell (" + cell.toString()
                    //      + ") Formula:" + formula);
                    cell.setCellFormula(formula);
                } // end if
            } // end for
        } // end for
    } // end for
}

---

From source file:com.bluecubs.xinco.index.filetypes.XincoIndexMicrosoftExcel.java

License:Apache License

public String getFileContentString(File f) {
    int i, j, j2, k;
    short k2;/*from  ww  w  .  j  av  a 2  s. com*/
    HSSFWorkbook wb = null;
    HSSFSheet sheet = null;
    HSSFRow row = null;
    HSSFCell cell = null;
    InputStream is = null;
    String cell_string = "";
    try {
        is = new FileInputStream(f);
        POIFSFileSystem fs = new POIFSFileSystem(is);
        wb = new HSSFWorkbook(fs);
        for (i = 0; i < wb.getNumberOfSheets(); i++) {
            sheet = wb.getSheetAt(i);
            j2 = 0;
            for (j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                while ((row = sheet.getRow(j2)) == null) {
                    j2++;
                }
                j2++;
                k2 = 0;
                for (k = 0; k < row.getPhysicalNumberOfCells(); k++) {
                    while ((cell = row.getCell(k2)) == null) {
                        k2++;
                    }
                    k2++;
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_FORMULA:
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        cell_string = cell_string + cell.getNumericCellValue() + "\t";
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        cell_string = cell_string + cell.getStringCellValue() + "\t";
                        break;
                    default:
                    }

                }
                cell_string = cell_string + "\n";
            }
            cell_string = cell_string + "\n\n\n";
        }
        is.close();
    } catch (Exception fe) {
        cell_string = null;
        if (is != null) {
            try {
                is.close();
            } catch (Exception ise) {
            }
        }
    }
    return cell_string;
}

---

From source file:com.citrix.g2w.webdriver.util.ReadExcelReport.java

License:Open Source License

/**
 * Method to read the file.//from   www.  ja  v a 2 s  .c  o  m
 * 
 * @param filePath
 *            (file to read)
 * @return testReport
 */
public Map<Integer, List> readFile(final String filePath) {
    this.logger.log("Absolute file path:" + filePath);
    List<List> report = null;
    try {
        FileInputStream file = new FileInputStream(new File(filePath));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get the worksheet count
        int workSheetCount = workbook.getNumberOfSheets();

        for (int count = 0; count < workSheetCount; count++) {
            // Get first sheet from the workbook
            HSSFSheet sheet = workbook.getSheetAt(count);

            Row rowObj = null;
            List rowData = new ArrayList();
            report = new ArrayList<List>();
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                rowObj = sheet.getRow(rowNum);
                rowData = new ArrayList();
                if (rowObj != null) {
                    Iterator<Cell> rowCellIterator = rowObj.cellIterator();
                    while (rowCellIterator.hasNext()) {
                        Cell cellObj = rowCellIterator.next();
                        rowData.add(this.getValue(cellObj));
                    }
                    report.add(rowData);
                }
            }
            this.workSheetsContent.put(count, report);
        }

    } catch (Exception e) {
        e.printStackTrace();
        String errorMessage = "Error while reading file : " + filePath;
        this.logger.log(errorMessage);
        this.logger.log(e.getMessage());
        throw new RuntimeException(e.getMessage());
    }
    return this.workSheetsContent;
}

---

From source file:com.cladonia.xngreditor.ImportUtilities.java

License:Open Source License

public static String[] getWorkSheets(File toSplit) throws FileNotFoundException, IOException, Exception {

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(toSplit));
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    String[] workSheets = new String[wb.getNumberOfSheets() + 1];
    //this.tablesCombo.addItem("- Please Select -");
    workSheets[0] = "- Please Select -";
    for (int cnt = 0; cnt < wb.getNumberOfSheets(); ++cnt) {
        workSheets[cnt + 1] = wb.getSheetName(cnt);

    }//from   www .j  a  v a 2  s  .c o m

    return (workSheets);

}

---

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

License:Apache License

private void readOleBasedData(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data) {

    try {//from   www.  j ava  2s.c  om
        HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream);

        int sheetCount = workbook.getNumberOfSheets();

        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
            Cell cell = null;

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

            int lastRowNumber = sheet.getLastRowNum();

            for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) {
                HSSFRow 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);
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

---

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

License:Apache License

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

    int sheetCount = workbook.getNumberOfSheets();

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

        Sheet s = new Sheet();

        sheets.add(s);//from   w ww .java  2  s . c o  m

        int lastRowNumber = sheet.getLastRowNum();

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

            Record record = new Record();

            s.addRecord(record);

            Iterator it = row.cellIterator();

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

}

---

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