Example usage for org.apache.poi.hssf.usermodel HSSFSheet setForceFormulaRecalculation

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setForceFormulaRecalculation

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. setForceFormulaRecalculation

Introduction

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

Prototype

@Override
public void setForceFormulaRecalculation(boolean value)

Source Link

Document

Control if Excel should be asked to recalculate all formulas on this sheet when the workbook is opened.

Calculating the formula values with org.apache.poi.ss.usermodel.FormulaEvaluator is the recommended solution, but this may be used for certain cases where evaluation in POI is not possible.

It is recommended to force recalcuation of formulas on workbook level using org.apache.poi.ss.usermodel.Workbook#setForceFormulaRecalculation(boolean) to ensure that all cross-worksheet formuals and external dependencies are updated.

Usage

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to modify data from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* @param colIndex - Index of col 0,1,2 etc.
* @param value - value to be modified/*from   w  ww .  j a  v a 2s.com*/
*/
public void modifyExcelCol(int sheetIndex, int rowIndex, int colIndex, String value) {
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        sheet.setForceFormulaRecalculation(true);
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        cell.setCellValue(value);
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workBook.write(fileOut);
        fileOut.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to modify data from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.   * 
* @param rowColFilter - [filtercol=value]@[modifyCol1=value,modifyCol2=value,...] 
* filterCol= filter column to identify the row[s] to be modify with a value
* modifyCol[n]= column to be modified found by filter col with a value
*//*w  w w  .j  a  v  a 2  s  .c o m*/
public void modifyMultiRowExcel(int sheetIndex, String rowColFilterText) {
    try {

        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        sheet.setForceFormulaRecalculation(true);
        String[] rowColFilters = rowColFilterText.split(";");
        for (String rowColFilter : rowColFilters) {
            String filter = "";
            String modcol = "";
            try {
                filter = rowColFilter.split("@")[0].replace("[", "").replace("]", "").trim();
                modcol = rowColFilter.split("@")[1].replace("[", "").replace("]", "").trim();
                if (filter != null && !filter.isEmpty()) {
                    String[] filters = filter.split("=");
                    int colIndex = this.getColumnIndex(filters[0]);
                    int rowIndex = this.getRowIndexByColumnValue(colIndex, filters[1]);
                    if (rowIndex >= 0) {
                        Row row = sheet.getRow(rowIndex);
                        String[] colModList = modcol.split(",");
                        for (String eachCol : colModList) {
                            String[] eachList = eachCol.split("=");
                            if (eachList.length > 0) {
                                int modColIndex = this.getColumnIndex(eachList[0]);
                                Cell cell = row.getCell(modColIndex);
                                cell.setCellValue(eachList[1]);
                            }
                        }
                    }
                }
            } catch (Exception e) {
                WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + " Excel Sheet Index="
                        + sheetIndex + "  Col Filter=" + filter + " Mod Col=" + modcol);
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workBook.write(fileOut);
        fileOut.close();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + "Excel Sheet Index=" + sheetIndex
                + " with Excel Row Col Filter=" + rowColFilterText);
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (Exception e) {
        WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + "Excel Sheet Index=" + sheetIndex
                + " with Excel Row Col Filter=" + rowColFilterText);
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:net.sf.jasperreports.engine.export.JRXlsMetadataExporter.java

License:Open Source License

protected void closeWorkbook(OutputStream os) throws JRException {
    try {//from  w w w .j  av a  2s . c o  m
        for (Object anchorName : anchorNames.keySet()) {
            HSSFName anchor = anchorNames.get(anchorName);
            List<Hyperlink> linkList = anchorLinks.get(anchorName);
            anchor.setRefersToFormula(
                    "'" + workbook.getSheetName(anchor.getSheetIndex()) + "'!" + anchor.getRefersToFormula());

            if (linkList != null && !linkList.isEmpty()) {
                for (Hyperlink link : linkList) {
                    link.setAddress(anchor.getRefersToFormula());
                }
            }
        }

        int index = 0;
        for (Integer linkPage : pageLinks.keySet()) {
            List<Hyperlink> linkList = pageLinks.get(linkPage);
            if (linkList != null && !linkList.isEmpty()) {
                for (Hyperlink link : linkList) {
                    index = onePagePerSheetMap.get(linkPage - 1) != null
                            ? (onePagePerSheetMap.get(linkPage - 1) ? Math.max(0, linkPage - 1)
                                    : Math.max(0, sheetsBeforeCurrentReportMap.get(linkPage)))
                            : 0;
                    link.setAddress("'" + workbook.getSheetName(index) + "'!$A$1");
                }
            }
        }

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet currentSheet = workbook.getSheetAt(i);
            currentSheet.setForceFormulaRecalculation(true);
            for (String columnName : columnNames) {
                Integer columnWidth = columnWidths.get(columnName);
                if (columnWidth != null && columnWidth < Integer.MAX_VALUE) {
                    currentSheet.setColumnWidth(columnNamesMap.get(columnName),
                            Math.min(43 * columnWidth, 256 * 255));
                } else {
                    currentSheet.autoSizeColumn(columnNamesMap.get(columnName), false);
                }
            }
        }
        workbook.write(os);
    } catch (IOException e) {
        throw new JRException("Error generating XLS metadata report : " + jasperPrint.getName(), e);
    }
}

From source file:org.jxstar.report.xls.ReportXlsForm.java

public Object output() throws ReportException {
    _log.showDebug("excel form report output ...");
    //?ID?????//from   ww w . java 2s .  c o  m
    String funId = _mpReptInfo.get("fun_id");
    //ID
    String areaId = _mpMainArea.get("area_id");

    //?
    HSSFWorkbook tmpwb = ReportXlsUtil.readWorkBook(_xlsFile);
    HSSFSheet tmpsheet = tmpwb.getSheetAt(0);

    HSSFSheet sheet = _hssfWB.getSheetAt(0);

    Map<String, String> mpValue = null;
    for (int i = 0; i < _lsMainRecord.size(); i++) {
        mpValue = _lsMainRecord.get(i);

        //?
        if (i == 0) {
            sheet = ReportXlsUtil.fillForm(funId, sheet, mpValue, _lsMainCol, _mpUser, i + 1,
                    _lsMainRecord.size());
            sheet = ReportXlsUtil.fillCheckInfo(funId, areaId, sheet, mpValue);
            sheet = ReportXlsUtil.fillHead(sheet, _lsHeadInfo, _mpUser);
        } else {
            tmpsheet = ReportXlsUtil.fillForm(funId, tmpsheet, mpValue, _lsMainCol, _mpUser, i + 1,
                    _lsMainRecord.size());
            tmpsheet = ReportXlsUtil.fillCheckInfo(funId, areaId, tmpsheet, mpValue);
            tmpsheet = ReportXlsUtil.fillHead(tmpsheet, _lsHeadInfo, _mpUser);

            sheet = ReportXlsUtil.appendSheet(sheet, tmpsheet);
            tmpwb = ReportXlsUtil.readWorkBook(_xlsFile);
            tmpsheet = tmpwb.getSheetAt(0);
        }
        //SHEET???
        if (i == 0)
            sheet.setForceFormulaRecalculation(true);

        //??
        if (!ReportXlsUtil.isAllowOut(sheet))
            break;
    }

    return _hssfWB;
}

From source file:org.jxstar.report.xls.ReportXlsFormGrid.java

/**
 * /*  w  ww.jav  a 2 s  . c  om*/
 */
public Object output() throws ReportException {
    _log.showDebug("excel form-grid report output ...");
    //?ID?????
    String funId = _mpReptInfo.get("fun_id");
    //?
    HSSFWorkbook tmpwb = ReportXlsUtil.readWorkBook(_xlsFile);
    HSSFSheet tmpsheet = tmpwb.getSheetAt(0);

    HSSFSheet sheet = _hssfWB.getSheetAt(0);

    int tempRow = -1;
    if (_lsSubArea.size() > 0) {
        String not_page = _lsSubArea.get(_lsSubArea.size() - 1).get("not_page");
        if (not_page.equals("1")) {
            tempRow = getAreaTempRow();
            _log.showDebug(".............sub area is not page, template clone row:" + tempRow);
        }
    }

    Map<String, String> mpValue = null;
    for (int i = 0; i < _lsMainRecord.size(); i++) {
        mpValue = _lsMainRecord.get(i);

        //?
        if (i == 0) {
            sheet = ReportXlsUtil.fillForm(funId, sheet, mpValue, _lsMainCol, _mpUser, i + 1,
                    _lsMainRecord.size());
            sheet = ReportXlsUtil.fillHead(sheet, _lsHeadInfo, _mpUser);

            //?
            sheet = fillSubArea(sheet, mpValue);
        } else {
            tmpsheet = ReportXlsUtil.fillForm(funId, tmpsheet, mpValue, _lsMainCol, _mpUser, i + 1,
                    _lsMainRecord.size());
            tmpsheet = ReportXlsUtil.fillHead(tmpsheet, _lsHeadInfo, _mpUser);

            //?????tmpsheet???
            tmpsheet = fillSubArea(tmpsheet, mpValue);
            sheet = ReportXlsUtil.appendSheet(sheet, tmpsheet, tempRow);
            tmpwb = ReportXlsUtil.readWorkBook(_xlsFile);
            tmpsheet = tmpwb.getSheetAt(0);
        }
        //SHEET???
        if (i == 0)
            sheet.setForceFormulaRecalculation(true);

        //??
        if (!ReportXlsUtil.isAllowOut(sheet))
            break;
    }

    return _hssfWB;
}

From source file:org.jxstar.report.xls.ReportXlsGrid.java

public Object output() throws ReportException {
    _log.showDebug("excel grid report output ...");

    String area_num = MapUtil.getValue(_mpMainArea, "page_size", "0");
    if (area_num.length() == 0 || area_num.equals("0")) {//"???"
        throw new ReportException(JsMessage.getValue("reportxlsgrid.hint01"));
    }/*from   w  w  w .ja  va  2 s.co  m*/

    //
    int pos = 0;
    //?
    int pageSize = Integer.parseInt(area_num);
    //
    int pageNum = ReportXlsUtil.calPageNum(_lsMainRecord.size(), pageSize);

    //?
    HSSFWorkbook tmpwb = ReportXlsUtil.readWorkBook(_xlsFile);
    HSSFSheet tmpsheet = tmpwb.getSheetAt(0);

    HSSFSheet sheet = _hssfWB.getSheetAt(0);

    //===================Grid??=========================
    String not_page = _mpMainArea.get("not_page");
    if (not_page.equals("1")) {
        if (insertSheetRow(sheet, pageSize)) {
            //?1?? 
            pageNum = 1;
            pageSize = _lsMainRecord.size();
        }
    }
    //==========================================================

    //
    for (int i = 0; i < pageNum; i++) {
        pos = i * pageSize;

        if (i == 0) {
            sheet = ReportXlsUtil.fillGrid(sheet, _lsMainRecord, _lsMainCol, _mpUser, pageSize, pos, i + 1,
                    pageNum);
            sheet = ReportXlsUtil.fillHead(sheet, _lsHeadInfo, _mpUser);
        } else {
            tmpsheet = ReportXlsUtil.fillGrid(tmpsheet, _lsMainRecord, _lsMainCol, _mpUser, pageSize, pos,
                    i + 1, pageNum);
            tmpsheet = ReportXlsUtil.fillHead(tmpsheet, _lsHeadInfo, _mpUser);

            //?
            sheet = ReportXlsUtil.appendSheet(sheet, tmpsheet);
            tmpwb = ReportXlsUtil.readWorkBook(_xlsFile);
            tmpsheet = tmpwb.getSheetAt(0);
        }
        //SHEET???
        if (i == 0)
            sheet.setForceFormulaRecalculation(true);

        //??
        if (!ReportXlsUtil.isAllowOut(sheet))
            break;
    }

    return _hssfWB;
}

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) {
    int i, columnIndex;
    ArrayList<DictionaryDO> tempQcColumns;
    DictionaryDO dict;//from   w  w  w . ja  va2  s . c  om
    HashSet<Integer> emptyColumns;
    Name rangeName;
    Row row;
    String rangeFormula;

    if (qcColumns != null && !qcColumns.isEmpty())
        row = sheet.getRow(32);
    else
        row = sheet.getRow(3);
    emptyColumns = new HashSet<Integer>();
    for (i = 0; i < row.getLastCellNum(); i++) {
        if (i >= maxChars.size() || maxChars.get(i) == 0)
            emptyColumns.add(i);
    }

    setHeaderCells(sheet, qcName, qcType, sheetName);

    if (qcColumns != null && !qcColumns.isEmpty()) {
        tempQcColumns = new ArrayList<DictionaryDO>();
        tempQcColumns.addAll(qcColumns);
        for (i = tempQcColumns.size() - 1; i > -1; i--) {
            if (emptyColumns.contains(i + 5)) {
                tempQcColumns.remove(i);
                removeColumn(sheet, i + 5);
                maxChars.remove(i + 5);
            }
        }

        rangeName = getName(wb, sheet, "RowNumber");
        if (rangeName == null) {
            rangeName = wb.createName();
            rangeName.setSheetIndex(wb.getSheetIndex(sheet));
            rangeName.setNameName("RowNumber");
        }
        rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$"
                + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1);
        rangeName.setRefersToFormula(rangeFormula);

        /*
         * Create named ranges for the graph to be able to locate the appropriate
         * data
         */
        columnIndex = 5;
        for (i = 0; i < tempQcColumns.size(); i++) {
            dict = tempQcColumns.get(i);
            if (!DataBaseUtil.isEmpty(dict.getCode())) {
                rangeName = getName(wb, sheet, dict.getCode());
                if (rangeName == null) {
                    rangeName = wb.createName();
                    rangeName.setSheetIndex(wb.getSheetIndex(sheet));
                    rangeName.setNameName(dict.getCode());
                }
                rangeFormula = rangeName.getRefersToFormula();
                if (rangeFormula != null && rangeFormula.length() > 0
                        && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1)))
                    rangeFormula += ",";
                else
                    rangeFormula = "";
                rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex)
                        + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$"
                        + (sheet.getLastRowNum() + 1);
                rangeName.setRefersToFormula(rangeFormula);
            }
            columnIndex++;
        }
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 5; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) {
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 0; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    }

    wb.setSheetName(wb.getSheetIndex(sheet), sheetName);
    sheet.setForceFormulaRecalculation(true);
    maxChars.clear();
}