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

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

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

Introduction

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

Prototype

@Override
public Iterator<Sheet> iterator()

Source Link

Document

Alias for #sheetIterator() to allow foreach loops Note: remove() is not supported on this iterator.

Usage

From source file:com.axelor.apps.admin.service.AsciiDocExportService.java

License:Open Source License

public File export(File excelFile, File asciiDoc, String lang) {

    if (excelFile == null) {
        return null;
    }//from   w w w .j  a v  a 2 s  .  c  o m

    if (lang != null && lang.equals("fr")) {
        docIndex = 11;
        titleIndex = 6;
        menuIndex = 10;
    }

    try {
        FileInputStream inStream = new FileInputStream(excelFile);

        XSSFWorkbook workbook = new XSSFWorkbook(inStream);

        if (asciiDoc == null) {
            asciiDoc = File.createTempFile(excelFile.getName().replace(".xlsx", ""), ".txt");
        }

        FileWriter fw = new FileWriter(asciiDoc);

        fw.write("= Documentation\n:toc:");

        processSheet(workbook.iterator(), fw);

        fw.close();

        return asciiDoc;

    } catch (IOException e) {
        e.printStackTrace();
    }

    return null;
}

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++;/*  w w w. j a  va  2s  .c  o  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:gov.anl.cue.arcane.engine.matrix.MatrixEngine.java

License:Open Source License

/**
 * Read parameters./*from  ww w. j  av a 2s .c o m*/
 *
 * @param fileName the file name
 * @throws IOException Signals that an I/O exception has occurred.
 * @throws FileNotFoundException the file not found exception
 */
public void readParameters(String fileName) throws IOException, FileNotFoundException {

    // Attempt to open the spreadsheet.
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName)));

    // Scan the sheets.
    Iterator<XSSFSheet> sheets = workbook.iterator();

    // Read the first sheet.
    XSSFSheet sheet = sheets.next();

    // Scan the rows.
    for (Row row : sheet) {

        // Get the next row label.
        String label = row.getCell(0).getStringCellValue();

        // Check the label type.
        if (label.equals("Random Seed")) {

            // Read the value.
            this.randomSeed = (int) row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Population Size")) {

            // Read the value.
            this.populationSize = (int) row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Kill Fraction")) {

            // Read the value.
            this.killFraction = row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Crossover Probability")) {

            // Read the value.
            this.crossoverProbability = row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Mutation Probability for Cells")) {

            // Read the value.
            this.mutationProbabilityForCells = row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Maximum New Term Count")) {

            // Read the value.
            this.maximumNewTermCount = (int) row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Addition Probability")) {

            // Read the value.
            this.additionProbability = row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Subtraction Probability")) {

            // Read the value.
            this.subtractionProbability = row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Multiplication Probability")) {

            // Read the value.
            this.multiplicationProbability = row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Accumulate Add Probability")) {

            // Read the value.
            this.accumulateAddProbability = row.getCell(1).getNumericCellValue();

            // Check the next label type.
        } else if (label.equals("Accumulate Subtract Probability")) {

            // Read the value.
            this.accumulateSubtractProbability = row.getCell(1).getNumericCellValue();

        }

    }

    // Close the workbook.
    workbook.close();

}

From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java

License:Open Source License

/**
 * Import template extract fitness information.
 *
 * @param matrixModel the matrix model/* ww  w  .  j  a  v  a 2s  .  c om*/
 * @param nodeCounts the node counts
 * @param nodeRequests the node requests
 * @param workbook the workbook
 * @return the iterator
 */
public static Iterator<XSSFSheet> importTemplateExtractFitnessInformation(MatrixModel matrixModel,
        HashMap<Integer, Integer> nodeCounts, int nodeRequests, XSSFWorkbook workbook) {

    // Prepare to scan the sheets.
    Iterator<XSSFSheet> sheets = workbook.iterator();

    // Ignore the parameters sheet.
    XSSFSheet sheet = sheets.next();
    sheet = sheets.next();

    // Scan the fitness sheet.
    for (int rowIndex = 0; rowIndex < nodeRequests; rowIndex++) {

        // Get the next equation.
        String equation = Util.getSpreadsheetString(sheet, rowIndex + 1, 0);

        // Store the equation.
        for (int count = 0; count < nodeCounts.get(rowIndex); count++) {
            matrixModel.fitnessEquations.add(equation);
        }

        // Get the next node name.
        String nodeName = Util.getSpreadsheetString(sheet, rowIndex + 1, 1);

        // Store the node names.
        for (int count = 0; count < nodeCounts.get(rowIndex); count++) {
            matrixModel.nodeNames.add(nodeName + (count + 1));
        }

    }

    // Get the next fitness function specifier.
    String specifier = Util.getSpreadsheetString(sheet, nodeRequests + 1, 0);

    // Decode the fitness function specifier.
    if (specifier.equals(MatrixModel.ZERO_FITNESS_STRING)) {

        // Store the fitness function specifier.
        matrixModel.fitnessFunctionType = MatrixModel.FITNESS_FUNCTION_TYPE.ZERO_FITNESS;

    } else if (specifier.equals(MatrixModel.SIMPLE_MAXIMUM_STRING)) {

        // Store the fitness function specifier.
        matrixModel.fitnessFunctionType = MatrixModel.FITNESS_FUNCTION_TYPE.SIMPLE_MAXIMUM;

    } else {

        // Store the fitness function specifier.
        matrixModel.fitnessFunctionType = MatrixModel.FITNESS_FUNCTION_TYPE.USER_EQUATION;

        // Read the number of steps.
        matrixModel.stepCount = (int) Util.getSpreadsheetNumber(sheet, nodeRequests + 2, 1);

        // Read the step size.
        matrixModel.stepSize = Util.getSpreadsheetNumber(sheet, nodeRequests + 3, 1);

        // Read the step size.
        matrixModel.equationEvolution = Util.getSpreadsheetBoolean(sheet, nodeRequests + 4, 1);

    }

    // Return the results.
    return sheets;

}

From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java

License:Open Source License

/**
 * Imports the dimensions for a matrix model from a
 * template spreadsheet./*from w  w w . j  a  v  a  2s. c om*/
 *
 * @param fileName            the file name
 * @return the matrix dimensions
 */
public static HashMap<Integer, Integer> importTemplateDimensions(String fileName) {

    // Create the results holder.
    HashMap<Integer, Integer> nodeCounts = new HashMap<Integer, Integer>();

    // Try to read the spreadsheet.
    try {

        // Attempt to open the template spreadsheet.
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName)));

        // Scan the sheets.
        Iterator<XSSFSheet> sheets = workbook.iterator();

        // Ignore the first sheet.
        XSSFSheet sheet = sheets.next();
        sheet = sheets.next();

        // Prepare to scan the node count column.
        Iterator<Row> rowIterator = sheet.rowIterator();

        // Skip the header row.
        rowIterator.next();

        // Find the total number of nodes requested.
        int rowIndex = 0;
        Double nextCellValue = Util.getSpreadsheetNumber(sheet, rowIterator.next().getRowNum(), 2);
        while (!Double.isNaN(nextCellValue)) {

            // Store the results.
            nodeCounts.put(rowIndex++, nextCellValue.intValue());

            // Get the next node count.
            nextCellValue = Util.getSpreadsheetNumber(sheet, rowIterator.next().getRowNum(), 2);

        }

        // Close the workbook.
        workbook.close();

        // Catch errors.
    } catch (Exception e) {

        // Note an error.
        nodeCounts = null;

    }

    // Return the results.
    return nodeCounts;

}

From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java

License:Open Source License

/**
 * Read extract fitness information./*  ww  w .j a v  a 2 s .  c om*/
 *
 * @param matrixModel the matrix model
 * @param matrixDimensions the matrix dimensions
 * @param workbook the workbook
 * @return the iterator
 */
public static Iterator<XSSFSheet> readExtractFitnessInformation(MatrixModel matrixModel,
        MatrixModel.MatrixDimensions matrixDimensions, XSSFWorkbook workbook) {

    // Scan the sheets.
    Iterator<XSSFSheet> sheets = workbook.iterator();
    XSSFSheet sheet = sheets.next();

    // Scan the fitness sheet.
    for (int rowIndex = 0; rowIndex < matrixDimensions.rows; rowIndex++) {

        // Get the next equation.
        String equation = Util.getSpreadsheetString(sheet, rowIndex + 1, 0);

        // Store the equation.
        matrixModel.fitnessEquations.add(equation);

        // Get the next node name.
        String nodeName = Util.getSpreadsheetString(sheet, rowIndex + 1, 1);

        // Store the node names.
        matrixModel.nodeNames.add(nodeName);

    }

    // Get the next fitness function specifier.
    String specifier = Util.getSpreadsheetString(sheet, matrixDimensions.rows + 1, 0);

    // Decode the fitness function specifier.
    if (specifier.equals(MatrixModel.ZERO_FITNESS_STRING)) {

        // Store the fitness function specifier.
        matrixModel.fitnessFunctionType = MatrixModel.FITNESS_FUNCTION_TYPE.ZERO_FITNESS;

    } else if (specifier.equals(MatrixModel.SIMPLE_MAXIMUM_STRING)) {

        // Store the fitness function specifier.
        matrixModel.fitnessFunctionType = MatrixModel.FITNESS_FUNCTION_TYPE.SIMPLE_MAXIMUM;

    } else {

        // Store the fitness function specifier.
        matrixModel.fitnessFunctionType = MatrixModel.FITNESS_FUNCTION_TYPE.USER_EQUATION;

        // Read the number of steps.
        matrixModel.stepCount = (int) Util.getSpreadsheetNumber(sheet, matrixDimensions.rows + 2, 1);

        // Read the step size.
        matrixModel.stepSize = Util.getSpreadsheetNumber(sheet, matrixDimensions.rows + 3, 1);

    }

    // Return the results.
    return sheets;

}

From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java

License:Open Source License

/**
 * Reads the dimensions for a matrix model from a spreadsheet.
 * This method is necessary because Excel spreadsheets
 * do not reliably store the row and column dimension
 * in the meta-information. The values that are stored
 * there are not guaranteed to be correct in all cases.
 *
 * @param fileName            the file name
 * @return the matrix dimensions//w w w .  j a  v  a2s  .  com
 */
public static MatrixDimensions readDimensions(String fileName) {

    // Create the results holder.
    MatrixDimensions matrixDimensions = new MatrixModel.MatrixDimensions();

    // Try to read the spreadsheet.
    try {

        // Attempt to open the spreadsheet.
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(fileName)));

        // Scan the sheets.
        Iterator<XSSFSheet> sheets = workbook.iterator();

        // Skip the first sheet.
        XSSFSheet sheet = sheets.next();

        // Move to the sheet for the first variable.
        sheet = sheets.next();

        // Find the number of rows.
        matrixDimensions.rows = sheet.getLastRowNum();

        // Prepare to check the first row.
        Iterator<Row> rowIterator = sheet.iterator();

        // Check the header row length
        Row row = rowIterator.next();
        matrixDimensions.columns = row.getLastCellNum() - 2;

        // Close the workbook.
        workbook.close();

        // Catch errors.
    } catch (Exception e) {

        // Note an error.
        matrixDimensions = null;

    }

    // Return the results.
    return matrixDimensions;

}

From source file:gov.anl.cue.arcane.engine.UtilTest.java

License:Open Source License

/**
 * Test get spreadsheet number./*w  w  w  . j av a  2s  . co  m*/
 *
 * @throws Exception the exception
 */
@Test
public void testGetSpreadsheetNumber() throws Exception {

    // Attempt to open the spreadsheet.
    XSSFWorkbook workbook = new XSSFWorkbook(
            new FileInputStream(new File(Util.INPUT_DIR + "//" + Util.TEST_DIR + "//Test.xlsx")));

    // Prepare to access the sheets.
    Iterator<XSSFSheet> sheets = workbook.iterator();

    // Read the first sheet.
    XSSFSheet sheet = sheets.next();

    // Check the results.
    Assert.assertEquals((Double) Util.getSpreadsheetNumber(sheet, 0, 0), (Double) 1.0);
    Assert.assertEquals((Double) Util.getSpreadsheetNumber(sheet, 1, 0), (Double) 1.0);

    // Close the workbook.
    workbook.close();

}

From source file:gov.anl.cue.arcane.engine.UtilTest.java

License:Open Source License

/**
 * Test get spreadsheet string./*from w  ww  .  j  a v a2s .c  o  m*/
 *
 * @throws Exception the exception
 */
@Test
public void testGetSpreadsheetString() throws Exception {

    // Attempt to open the spreadsheet.
    XSSFWorkbook workbook = new XSSFWorkbook(
            new FileInputStream(new File(Util.INPUT_DIR + "//" + Util.TEST_DIR + "//Test.xlsx")));

    // Prepare to access the sheets.
    Iterator<XSSFSheet> sheets = workbook.iterator();

    // Read the first sheet.
    XSSFSheet sheet = sheets.next();

    // Check the results.
    Assert.assertTrue(Util.getSpreadsheetString(sheet, 2, 0).equals("Test text"));
    Assert.assertTrue(Util.getSpreadsheetString(sheet, 3, 0).equals("Another string"));

    // Close the workbook.
    workbook.close();

}

From source file:gov.anl.cue.arcane.engine.UtilTest.java

License:Open Source License

/**
 * Test get spreadsheet boolean./* ww  w  . j a v a2 s.  c o m*/
 *
 * @throws Exception the exception
 */
@Test
public void testGetSpreadsheetBoolean() throws Exception {

    // Attempt to open the spreadsheet.
    XSSFWorkbook workbook = new XSSFWorkbook(
            new FileInputStream(new File(Util.INPUT_DIR + "//" + Util.TEST_DIR + "//Test.xlsx")));

    // Prepare to access the sheets.
    Iterator<XSSFSheet> sheets = workbook.iterator();

    // Read the first sheet.
    XSSFSheet sheet = sheets.next();

    // Check the results.
    Assert.assertTrue(Util.getSpreadsheetBoolean(sheet, 4, 0));
    Assert.assertFalse(Util.getSpreadsheetBoolean(sheet, 5, 0));

    // Close the workbook.
    workbook.close();

}