Example usage for org.apache.poi.ss.usermodel Sheet getForceFormulaRecalculation

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getForceFormulaRecalculation.

Prototype

boolean getForceFormulaRecalculation();

Source Link

Document

Whether Excel will be asked to recalculate all formulas in this sheet when the workbook is opened.

Usage

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_FormulaRecalculationTest.java

License:Apache License

@Test
public void forcesToRecalculate_Hssf() throws Exception {
    data.wb = new HSSFWorkbook();
    data.wb.createSheet("sheet1");
    data.wb.createSheet("sheet2");

    step.recalculateAllWorkbookFormulas();

    if (!data.wb.getForceFormulaRecalculation()) {
        int sheets = data.wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = data.wb.getSheetAt(i);
            assertTrue("Sheet #" + i + ": " + sheet.getSheetName(), sheet.getForceFormulaRecalculation());
        }//from   w  w w. java  2  s.co m
    }
}

---

From source file:uk.ac.liverpool.spreadsheet.ExcelFeatureAnalysis.java

License:Apache License

private static void analyseSheet(Sheet ss, Element s, Namespace n, ExcelFeatureAnalysis efa) {
    // generic part
    boolean costumFormatting = false;
    boolean formulae = false;
    boolean UDF = false;
    boolean hasComments = false;

    Set<String> udfs = new HashSet<String>();
    FormulaEvaluator evaluator = ss.getWorkbook().getCreationHelper().createFormulaEvaluator();

    s.setAttribute("name", ss.getSheetName());
    s.setAttribute("firstRow", "" + ss.getFirstRowNum());
    s.setAttribute("lastRow", "" + ss.getLastRowNum());
    try {//from  w  w  w.  ja  va2 s  . c o m
        s.setAttribute("forceFormulaRecalc", "" + ss.getForceFormulaRecalculation());
    } catch (Throwable x) {
        //x.printStackTrace();
    }

    // shapes in detail? 
    Footer footer = ss.getFooter();
    if (footer != null) {
        s.setAttribute("footer", "true");
    }
    Header header = ss.getHeader();
    if (header != null) {
        s.setAttribute("header", "true");
    }
    PaneInformation paneInformation = ss.getPaneInformation();
    if (paneInformation != null) {
        s.setAttribute("panels", "true");
    }

    HSSFSheet hs = null;
    XSSFSheet xs = null;
    if (ss instanceof HSSFSheet) {
        hs = (HSSFSheet) ss;
        try {
            if (hs.getDrawingPatriarch() != null) {
                if (hs.getDrawingPatriarch().containsChart())
                    s.addContent(new Element("charts", sn));
                if (hs.getDrawingPatriarch().countOfAllChildren() > 0)
                    s.addContent(new Element("shapes", sn));
            }
        } catch (Exception x) {
            x.printStackTrace();
        }

        if (hs.getSheetConditionalFormatting().getNumConditionalFormattings() > 0) {
            s.setAttribute("conditionalFormatting", "true");
        }
    }
    if (ss instanceof XSSFSheet) {
        xs = (XSSFSheet) ss;

    }
    Iterator<Row> rows = ss.rowIterator();

    int firstColumn = (rows.hasNext() ? Integer.MAX_VALUE : 0);
    int endColumn = 0;
    while (rows.hasNext()) {
        Row row = rows.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    s.setAttribute("firstColumn", "" + firstColumn);
    s.setAttribute("lastColumn", "" + endColumn);
    rows = ss.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        for (Cell cell : row)
            if (cell != null) {
                try {
                    if (!cell.getCellStyle().getDataFormatString().equals("GENERAL"))
                        costumFormatting = true;
                } catch (Throwable t) {
                }

                if (cell.getCellComment() != null)
                    hasComments = true;
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    // System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //                        if (DateUtil.isCellDateFormatted(cell)) {
                    //                            // System.out.println(cell.getDateCellValue());
                    //                        } else {
                    //                            // System.out.println(cell.getNumericCellValue());
                    //                        }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    // System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    // System.out.println(cell.getCellFormula());
                    formulae = true;
                    if (!UDF)
                        try {
                            evaluator.evaluate(cell);
                        } catch (Exception x) {
                            if (x instanceof NotImplementedException) {
                                Throwable e = x;

                                //e.printStackTrace();
                                while (e != null) {
                                    for (StackTraceElement c : e.getStackTrace()) {
                                        if (c.getClassName().contains("UserDefinedFunction")) {
                                            UDF = true;
                                            System.out.println("UDF " + e.getMessage());
                                            udfs.add(e.getMessage());
                                        }
                                    }
                                    e = e.getCause();
                                }

                            }
                        }
                    break;
                default:
                }

            }
    }
    if (costumFormatting) {
        Element cf = new Element("customisedFormatting", sn);
        s.addContent(cf);
    }
    if (formulae) {
        Element cf = new Element("formulae", sn);
        s.addContent(cf);
    }
    if (UDF) {
        Element cf = new Element("userDefinedFunctions", sn);
        for (String sss : udfs)
            cf.addContent(new Element("userDefinedFunction", sn).setAttribute("functionName", sss));
        s.addContent(cf);
    }
    if (hasComments) {
        Element cf = new Element("cellComments", sn);
        s.addContent(cf);
    }
}

---