Example usage for org.apache.poi.hssf.usermodel HSSFFormulaEvaluator evaluateAllFormulaCells

List of usage examples for org.apache.poi.hssf.usermodel HSSFFormulaEvaluator evaluateAllFormulaCells

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFFormulaEvaluator
  6. evaluateAllFormulaCells

Introduction

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

Prototype

public static void evaluateAllFormulaCells(Workbook wb)

Source Link

Document

Loops over all cells in all sheets of the supplied workbook.

Usage

From source file:bad.robot.excel.workbook.PoiWorkbook.java

License:Apache License

@Override
public PoiWorkbook refreshFormulas() {
    HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
    return this;
}

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

License:Apache License

public ExcelDataStream(DataSourceInfo dsi, HSSFWorkbook wb, HSSFSheet sheet, Metadata md) {
    super(true);/*from  w  w w.  j  a va2 s.co m*/
    setMetadata(md);
    this.dsi = dsi;
    this.wb = wb;
    this.sheet = sheet;
    evaluator = new HSSFFormulaEvaluator(sheet, wb);
    dfmts = new DateFormat[metadata.getColumnCount()];
    // FIXME: are we sure we want to do this?  Might be heavy-handed...
    HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
    /*
    p("before first infer startrow=" + dsi.getDataStartRow() + " metadata=" + this.metadata);
    this.inferMetadata(-1);
    p("before 2nd infer startrow=" + dsi.getDataStartRow() + " metadata=" + this.metadata);
    this.inferMetadata(-1);
    if (true) throw new RuntimeException("BLAH");
    */
    //p("constructing stream with dsi=" + dsi);
    reset();
}

From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java

License:Apache License

public List<String[]> getRawStrings(String sheetName) {
    //        if (rawStrings == null) { 
    rawStrings = new ArrayList<String[]>();
    //        }//from w  w w . j  ava 2 s .  c o  m

    sheet = ExcelDataSource.getSheet(wbk, sheetName);
    eval = new HSSFFormulaEvaluator(sheet, wbk);
    // FIXME: decide if we want this...
    HSSFFormulaEvaluator.evaluateAllFormulaCells(wbk);
    loadStrings();
    inferMetadata();

    return rawStrings;
}

From source file:com.yanglb.utilitys.codegen.core.reader.BaseReader.java

License:Apache License

/**
 * ?/*from   w w  w  .j a  va  2  s .  c  om*/
 * @throws CodeGenException 
 */
private void doReader() throws CodeGenException {
    // 
    XSSFWorkbook wb = null;
    try {
        // jar??new File
        if (this.excelFile.startsWith("jar:")) {
            String path = this.excelFile.substring(4);
            InputStream is = this.getClass().getResourceAsStream(path);
            wb = new XSSFWorkbook(is);
        } else {
            File file = new File(this.excelFile);
            BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
            wb = new XSSFWorkbook(in);
        }

        // ?
        HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

        // ??
        if (this.sheets == null || this.sheets.length == 0) {
            // ?
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                XSSFSheet sheet = wb.getSheetAt(i);

                // ???Sheet
                if (!this.isReadable(sheet.getSheetName())) {
                    continue;
                }
                this.results.add(this.onReader(sheet));
            }
        } else {
            // ?Sheet
            for (String sheetName : this.sheets) {
                XSSFSheet sheet = wb.getSheet(sheetName);
                if (sheet == null) {
                    throw new CodeGenException(String.format(MsgUtility.getString("E_004"), sheetName));
                }
                this.results.add(this.onReader(sheet));
            }
        }
    } catch (FileNotFoundException e) {
        // ???
        throw new CodeGenException(e.getMessage());
    } catch (UnImplementException e) {
        this.results.clear();
        e.printStackTrace();
    } catch (IOException e) {
        throw new CodeGenException(MsgUtility.getString("E_005"));
    } finally {
        try {
            if (wb != null)
                wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

From source file:fr.amapj.service.engine.generator.excel.ExcelGeneratorTool.java

License:Open Source License

public Workbook getWb() {
    // Recalcul des formules
    /*if (wb instanceof XSSFWorkbook)
    {//from   ww  w.j  av a 2  s .  c om
       XSSFFormulaEvaluator.evaluateAllFormulaCells( (XSSFWorkbook) wb);
    }
    else
    {
       HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
    }
    */
    HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

    return wb;
}

From source file:org.whitley.object.handler.TutorialHandler.java

/**
 * makeScheduleList method./*  w  w w. j  a  va 2 s.c o  m*/
 * Write excel spreadsheet containing information on tutorial schedule
 * @param workbook: Target excel workbook
 * @param timetable: Filled out Timetable
 * @param sheetName: Name of the Sheet
 * @since 1.0
 */
private void makeScheduleList(HSSFWorkbook workbook, ArrayList<TuteTable> timetable, String sheetName) {
    int row_counter = 0;
    HSSFSheet stream_Sheet = workbook.createSheet(sheetName);
    HSSFRow stream_row;
    HSSFCell stream_cell;
    ArrayList<String> data;
    HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
    for (TuteTable stream : timetable) {
        data = new ArrayList<>();
        String stream_day = stream.getStream().getStream_day().toString();
        data.add(stream_day);
        String stream_start = stream.getStream().getStream_start();
        data.add(stream_start);
        String stream_end = stream.getStream().getStream_end();
        data.add(stream_end);
        String stream_code = stream.getSubject().getCode();
        data.add(stream_code);
        String stream_name = stream.getSubject().getName();
        data.add(stream_name);
        String stream_room = stream.getRoom().getRoomName();
        data.add(stream_room);

        stream_row = stream_Sheet.createRow(row_counter);
        row_counter++;
        for (int i = 0; i < data.size() - 1; i++) {
            stream_cell = stream_row.createCell(i);
            stream_cell.setCellValue(data.get(i));
        }
    }
    for (int i = 0; i < 6; i++) {
        stream_Sheet.autoSizeColumn(i);
    }

}

From source file:org.whitley.object.handler.TutorialHandler.java

/**
/**/*ww w. j  a v  a  2s. c om*/
 * makeTutorialDetailList method.
 * Write excel spreadsheet containing information on tutorial details.
 * @param workbook: Target excel workbook
 * @param timetable: Filled out Timetable
 * @param sheetName: Name of the Sheet
 * @since 1.0
 */
private void makeTutorialDetailList(HSSFWorkbook workbook, ArrayList<TuteTable> timetable, String sheetName) {
    int row_counter = 0;
    HSSFSheet stream_Sheet = workbook.createSheet(sheetName);
    HSSFRow stream_row;
    HSSFCell stream_cell;
    ArrayList<String> data;
    HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
    for (TuteTable stream : timetable) {
        stream_row = stream_Sheet.createRow(row_counter);
        stream_cell = stream_row.createCell(0);
        stream_cell.setCellValue("Subject Code");
        stream_cell = stream_row.createCell(1);
        stream_cell.setCellValue(stream.getSubject().getCode());
        row_counter++;
        stream_row = stream_Sheet.createRow(row_counter);
        stream_cell = stream_row.createCell(0);
        stream_cell.setCellValue("Subject Name");
        stream_cell = stream_row.createCell(1);
        stream_cell.setCellValue(stream.getSubject().getName());
        row_counter++;
        stream_row = stream_Sheet.createRow(row_counter);
        stream_cell = stream_row.createCell(0);
        stream_cell.setCellValue("Subject Tutor");
        stream_cell = stream_row.createCell(1);
        stream_cell.setCellValue(stream.getSubject().getTutor().getName());
        row_counter++;
        stream_row = stream_Sheet.createRow(row_counter);
        stream_cell = stream_row.createCell(0);
        stream_cell.setCellValue("Stream Time");
        stream_cell = stream_row.createCell(1);
        stream_cell.setCellValue(stream.getStream().toFormattedString());
        row_counter++;
        stream_row = stream_Sheet.createRow(row_counter);
        stream_cell = stream_row.createCell(0);
        stream_cell.setCellValue("Tutorial Room");
        stream_cell = stream_row.createCell(1);
        stream_cell.setCellValue(stream.getRoom().getRoomName());
        row_counter++;
        for (TuteStudent student : stream.getSubject().getStudents()) {
            stream_row = stream_Sheet.createRow(row_counter);
            stream_cell = stream_row.createCell(1);
            stream_cell.setCellValue(student.toString());
            row_counter++;
        }
        row_counter++;

    }
    stream_Sheet.autoSizeColumn(0);
    stream_Sheet.autoSizeColumn(1);
}

From source file:swift.selenium.WebHelper.java

License:Open Source License

/**
 * This method reads and returns data from each cell of a provided worksheet
 * //from   w w  w.  jav  a  2  s .c o  m
 * @param reqValue
 * @param reqSheet
 * @param rowIndex
 * @param inputHashTable
 * @return
 * @throws IOException
 */
@SuppressWarnings("null")
public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex,
        HashMap<String, Object> inputHashTable) throws IOException {
    HSSFCell reqCell = null;
    Object actualvalue = null;
    String req = "";
    DataFormatter fmt = new DataFormatter();

    if (inputHashTable.isEmpty() == true) {
        inputHashTable = getValueFromHashMap(reqSheet);
    }
    HSSFRow rowActual = reqSheet.getRow(rowIndex);
    if (inputHashTable.get(reqValue) == null) {

        TransactionMapping.report.setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet");
        TransactionMapping.pauseFun("Column " + reqValue + " not Found. Please Check input Sheet");
    } else {
        actualvalue = inputHashTable.get(reqValue);//rowHeader.getCell(colIndex).toString();         
        if (actualvalue != null) {
            int colIndex = Integer.parseInt(actualvalue.toString());
            reqCell = rowActual.getCell(colIndex);
            //TM 27-04-2015: Updated the code for formula in cells
            if (reqCell == null) {
                System.out.println(reqValue + " is Null");
            } else {
                HSSFWorkbook wb = reqCell.getSheet().getWorkbook(); //TM-05/05/2015: Get workbook instance from the worksheet
                HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); //TM-05/05/2015: To refresh all the formulas in the worksheet
                FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

                CellValue cellValue = evaluator.evaluate(reqCell);
                int type = 0;
                if (cellValue != null) {
                    type = cellValue.getCellType();
                } else {
                    type = reqCell.getCellType();
                }

                switch (type) {
                case HSSFCell.CELL_TYPE_BLANK:
                    req = "";
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(reqCell)) {
                        SimpleDateFormat form = new SimpleDateFormat(
                                Automation.configHashMap.get("DATEFORMAT").toString());
                        req = form.format(reqCell.getDateCellValue());
                    } else
                        req = fmt.formatCellValue(reqCell, evaluator);
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    req = reqCell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    req = Boolean.toString(reqCell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    req = "error";
                    break;
                }
            }
        }

        else {
            req = reqCell.getStringCellValue();
            System.out.println("null");
        }
    }
    return req;
}

From source file:workbench.db.importer.ExcelReader.java

License:Apache License

@Override
public void load() throws IOException {
    if (dataFile != null) {
        // do not load the file twice.
        return;/* w ww  .ja  va 2 s .co m*/
    }

    InputStream in = null;
    try {
        in = new FileInputStream(inputFile);
        if (useXLSX) {
            dataFile = new XSSFWorkbook(in);
        } else {
            dataFile = new HSSFWorkbook(in);
        }
    } finally {
        FileUtil.closeQuietely(in);
    }

    initActiveSheet();

    // TODO: find references to external files and update those as well
    // see: https://poi.apache.org/spreadsheet/eval.html
    try {
        if (useXLSX) {
            XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) dataFile);
        } else {
            HSSFFormulaEvaluator.evaluateAllFormulaCells((HSSFWorkbook) dataFile);
        }
    } catch (Exception ex) {
        LogMgr.logError("ExcelReader.load()", "Could not refresh formulas!", ex);
    }
}