Example usage for org.apache.poi.hssf.usermodel HSSFName setRefersToFormula

List of usage examples for org.apache.poi.hssf.usermodel HSSFName setRefersToFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFName
  6. setRefersToFormula

Introduction

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

Prototype

public void setRefersToFormula(String formulaText)

Source Link

Usage

From source file:com.wetts.base.utils.poi.excel.ExcelDao.java

License:Open Source License

/**
 * ??//from  w  w w .j a v  a 2 s.  c  o  m
 *
 * @param wb
 * @param name
 * @param expression
 * @return
 */
public static HSSFName createName(HSSFWorkbook wb, String name, String expression) {
    HSSFName refer = wb.getName(name);
    //?????
    if (refer == null) {
        refer = wb.createName();
        refer.setNameName(name);
    }
    //???
    refer.setRefersToFormula(expression);
    return refer;
}

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

License:Open Source License

protected void closeWorkbook(OutputStream os) throws JRException {
    try {//from   w  w  w  .  ja  v  a 2s .co m
        for (Object anchorName : anchorNames.keySet()) // the anchorNames map contains no entries for reports with ignore anchors == true;
        {
            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()) { // the pageLinks map contains no entries for reports with ignore hyperlinks == true 
            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++) {
            workbook.getSheetAt(i).setForceFormulaRecalculation(true);
        }

        workbook.write(os);
    } catch (IOException e) {
        throw new JRException("Error generating XLS report : " + jasperPrint.getName(), e);
    }
}

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

License:Open Source License

protected void createTextCell(final JRPrintText textElement, final JRExporterGridCell gridCell,
        final int colIndex, final int rowIndex, final JRStyledText styledText, final StyleInfo baseStyle,
        final short forecolor) throws JRException {
    String formula = getFormula(textElement);
    String textStr = styledText.getText();

    if (formula != null) {
        try {//  ww w  .j  a  v a2s.c o  m
            TextValue value = getTextValue(textElement, textStr);

            if (value instanceof NumberTextValue) {
                String convertedPattern = getConvertedPattern(textElement,
                        ((NumberTextValue) value).getPattern());
                if (convertedPattern != null) {
                    baseStyle.setDataFormat(dataFormat.getFormat(convertedPattern));
                }
            } else if (value instanceof DateTextValue) {
                String convertedPattern = getConvertedPattern(textElement,
                        ((DateTextValue) value).getPattern());
                if (convertedPattern != null) {
                    baseStyle.setDataFormat(dataFormat.getFormat(convertedPattern));
                }
            }

            HSSFCellStyle cellStyle = initCreateCell(gridCell, colIndex, rowIndex, baseStyle);
            cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula(formula);
            endCreateCell(cellStyle);
            return;
        } catch (Exception e)//FIXMENOW what exceptions could we get here?
        {
            if (log.isWarnEnabled()) {
                log.warn(e.getMessage());
            }
        }
    }

    XlsReportConfiguration configuration = getCurrentItemConfiguration();

    if (configuration.isDetectCellType()) {
        TextValue value = getTextValue(textElement, textStr);
        value.handle(new TextValueHandler() {
            public void handle(StringTextValue textValue) {
                HSSFCellStyle cellStyle = initCreateCell(gridCell, colIndex, rowIndex, baseStyle);
                if (JRCommonText.MARKUP_NONE.equals(textElement.getMarkup())) {
                    setStringCellValue(textValue.getText());
                } else {
                    setRichTextStringCellValue(styledText, forecolor, textElement, getTextLocale(textElement));
                }
                endCreateCell(cellStyle);
            }

            public void handle(NumberTextValue textValue) {
                String convertedPattern = getConvertedPattern(textElement, textValue.getPattern());
                if (convertedPattern != null) {
                    baseStyle.setDataFormat(dataFormat.getFormat(convertedPattern));
                }

                HSSFCellStyle cellStyle = initCreateCell(gridCell, colIndex, rowIndex, baseStyle);
                if (textValue.getValue() == null) {
                    cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                } else {
                    cell.setCellValue(textValue.getValue().doubleValue());
                }
                endCreateCell(cellStyle);
            }

            public void handle(DateTextValue textValue) {
                baseStyle.setDataFormat(
                        dataFormat.getFormat(getConvertedPattern(textElement, textValue.getPattern())//FIXMEFORMAT why no null test like in numeric above?
                ));
                HSSFCellStyle cellStyle = initCreateCell(gridCell, colIndex, rowIndex, baseStyle);
                Date date = textValue.getValue();
                if (date == null) {
                    cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                } else {
                    date = translateDateValue(textElement, date);
                    cell.setCellValue(date);
                }
                endCreateCell(cellStyle);
            }

            public void handle(BooleanTextValue textValue) {
                HSSFCellStyle cellStyle = initCreateCell(gridCell, colIndex, rowIndex, baseStyle);
                if (textValue.getValue() == null) {
                    cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                } else {
                    cell.setCellValue(textValue.getValue().booleanValue());
                }
                endCreateCell(cellStyle);
            }

        });
    } else {
        HSSFCellStyle cellStyle = initCreateCell(gridCell, colIndex, rowIndex, baseStyle);
        if (JRCommonText.MARKUP_NONE.equals(textElement.getMarkup())) {
            setStringCellValue(textStr);
        } else {
            setRichTextStringCellValue(styledText, forecolor, textElement, getTextLocale(textElement));
        }
        endCreateCell(cellStyle);
    }

    if (!configuration.isIgnoreAnchors()) {
        String anchorName = textElement.getAnchorName();
        if (anchorName != null) {
            HSSFName aName = workbook.createName();
            aName.setNameName(JRStringUtil.getJavaIdentifier(anchorName));
            aName.setSheetIndex(workbook.getSheetIndex(sheet));
            CellReference cRef = new CellReference(rowIndex, colIndex, true, true);
            aName.setRefersToFormula(cRef.formatAsString());
            anchorNames.put(anchorName, aName);
        }
    }

    setHyperlinkCell(textElement);
}

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

License:Open Source License

protected void closeWorkbook(OutputStream os) throws JRException {
    try {/*from  w  ww .ja  v  a 2 s . c  om*/
        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:net.sf.jasperreports.engine.export.JRXlsMetadataExporter.java

License:Open Source License

protected void exportText(final JRPrintText textElement) throws JRException {
    String currentColumnName = textElement.getPropertiesMap()
            .getProperty(JRXlsAbstractMetadataExporter.PROPERTY_COLUMN_NAME);
    if (currentColumnName != null && currentColumnName.length() > 0) {
        String currentColumnData = textElement.getPropertiesMap()
                .getProperty(JRXlsAbstractMetadataExporter.PROPERTY_DATA);
        boolean repeatValue = getPropertiesUtil().getBooleanProperty(textElement,
                JRXlsAbstractMetadataExporter.PROPERTY_REPEAT_VALUE, false);

        setColumnName(currentColumnName);
        adjustColumnWidth(currentColumnName, textElement.getWidth(),
                ((JRXlsExporterNature) nature).getColumnAutoFit(textElement));
        adjustRowHeight(textElement.getHeight(),
                isWrapText(textElement) || ((JRXlsExporterNature) nature).getRowAutoFit(textElement));

        final short forecolor = getWorkbookColor(textElement.getForecolor()).getIndex();

        TextAlignHolder textAlignHolder = getTextAlignHolder(textElement);
        short horizontalAlignment = getHorizontalAlignment(textAlignHolder);
        short verticalAlignment = getVerticalAlignment(textAlignHolder);
        short rotation = getRotation(textAlignHolder);

        XlsReportConfiguration configuration = getCurrentItemConfiguration();

        short mode = backgroundMode;
        short backcolor = whiteIndex;
        if (!configuration.isIgnoreCellBackground() && textElement.getBackcolor() != null) {
            mode = HSSFCellStyle.SOLID_FOREGROUND;
            backcolor = getWorkbookColor(textElement.getBackcolor()).getIndex();
        }/*from   w  ww. j av  a2s.co m*/

        final StyleInfo baseStyle = new StyleInfo(mode, backcolor, horizontalAlignment, verticalAlignment,
                rotation, getLoadedFont(textElement, forecolor, null, getTextLocale(textElement)),
                new BoxStyle(textElement),
                isWrapText(textElement) || ((JRXlsExporterNature) nature).getColumnAutoFit(textElement),
                isCellLocked(textElement), isCellHidden(textElement));

        final JRStyledText styledText;
        final String textStr;
        final String formula;
        final CellSettings cellSettings = new CellSettings();
        if (currentColumnData != null) {
            styledText = new JRStyledText();
            styledText.append(currentColumnData);
            textStr = currentColumnData;
            formula = null;
        } else {
            styledText = getStyledText(textElement);
            if (styledText != null) {
                textStr = styledText.getText();
            } else {
                textStr = null;
            }
            formula = getFormula(textElement);
        }

        if (formula != null) {
            TextValue value = getTextValue(textElement, textStr);

            if (value instanceof NumberTextValue) {
                String convertedPattern = getConvertedPattern(textElement,
                        ((NumberTextValue) value).getPattern());
                if (convertedPattern != null) {
                    baseStyle.setDataFormat(dataFormat.getFormat(convertedPattern));
                }
            } else if (value instanceof DateTextValue) {
                String convertedPattern = getConvertedPattern(textElement,
                        ((DateTextValue) value).getPattern());
                if (convertedPattern != null) {
                    baseStyle.setDataFormat(dataFormat.getFormat(convertedPattern));
                }
            }

            cellSettings.importValues(HSSFCell.CELL_TYPE_FORMULA, getLoadedCellStyle(baseStyle), null, formula);

        } else if (getCurrentItemConfiguration().isDetectCellType()) {
            TextValue value = getTextValue(textElement, textStr);
            value.handle(new TextValueHandler() {
                public void handle(StringTextValue textValue) {
                    if (JRCommonText.MARKUP_NONE.equals(textElement.getMarkup())) {
                        cellSettings.importValues(HSSFCell.CELL_TYPE_STRING, getLoadedCellStyle(baseStyle),
                                new HSSFRichTextString(textValue.getText()));
                    } else {
                        cellSettings.importValues(HSSFCell.CELL_TYPE_STRING, getLoadedCellStyle(baseStyle),
                                getRichTextString(styledText, forecolor, textElement,
                                        getTextLocale(textElement)));
                    }
                }

                public void handle(NumberTextValue textValue) {
                    String convertedPattern = getConvertedPattern(textElement, textValue.getPattern());
                    if (convertedPattern != null) {
                        baseStyle.setDataFormat(dataFormat.getFormat(convertedPattern));
                    }
                    cellSettings.importValues(HSSFCell.CELL_TYPE_NUMERIC, getLoadedCellStyle(baseStyle),
                            textValue.getValue());
                }

                public void handle(DateTextValue textValue) {
                    String convertedPattern = getConvertedPattern(textElement, textValue.getPattern());
                    if (convertedPattern != null) {
                        baseStyle.setDataFormat(dataFormat.getFormat(convertedPattern));
                    }
                    Date value = textValue.getValue() == null ? null
                            : translateDateValue(textElement, textValue.getValue());
                    cellSettings.importValues(HSSFCell.CELL_TYPE_NUMERIC, getLoadedCellStyle(baseStyle), value);
                }

                public void handle(BooleanTextValue textValue) {
                    cellSettings.importValues(HSSFCell.CELL_TYPE_BOOLEAN, getLoadedCellStyle(baseStyle),
                            textValue.getValue());
                }

            });
        } else {
            if (JRCommonText.MARKUP_NONE.equals(textElement.getMarkup())) {
                cellSettings.importValues(HSSFCell.CELL_TYPE_STRING, getLoadedCellStyle(baseStyle),
                        new HSSFRichTextString(textStr));
            } else {
                cellSettings.importValues(HSSFCell.CELL_TYPE_STRING, getLoadedCellStyle(baseStyle),
                        getRichTextString(styledText, forecolor, textElement, getTextLocale(textElement)));
            }
        }

        if (!configuration.isIgnoreAnchors()) {
            String anchorName = textElement.getAnchorName();
            if (anchorName != null) {
                HSSFName aName = workbook.createName();
                aName.setNameName(JRStringUtil.getJavaIdentifier(anchorName));
                aName.setSheetIndex(workbook.getSheetIndex(sheet));
                CellReference cRef = new CellReference(rowIndex, columnNamesMap.get(currentColumnName), true,
                        true);
                aName.setRefersToFormula(cRef.formatAsString());
                anchorNames.put(anchorName, aName);
            }
        }

        setHyperlinkCell(textElement, cellSettings);
        addTextElement(cellSettings, textStr, repeatValue, currentColumnName);
    }
}

From source file:org.jxstar.report.studio.ExportStatBO.java

/**
 * excle???????//from w w  w . j a  v a2 s . c o  m
 * s1y=OFFSET(Sheet1!$C$3,0,0,COUNTA(Sheet1!C:C)-1,1) -- 
 * s2y=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!B:B)-1,1) -- 
 * @param titles -- 
 * @param chars -- 
 * @param nums -- 
 * @param wb -- 
 * @return
 */
private boolean updateChartName(String[] titles, String chars, String nums, HSSFWorkbook wb) {
    //?1???1
    char x_no = 'B', y_no = 'C';
    for (int i = 0, n = titles.length; i < n; i++) {
        if (chars.equals(titles[i])) {
            x_no = (char) (65 + i + 1);
        } else if (nums.equals(titles[i])) {
            y_no = (char) (65 + i + 1);
        }
    }

    //?????
    HSSFName s1y = wb.getName("s1y");
    HSSFName s2y = wb.getName("s2y");
    if (s1y == null || s2y == null) {
        _log.showWarn("chart tpl HSSFName s1y is null or s2y is null!!");
        return false;
    }

    _log.showDebug(".............x_no=" + x_no + ";y_no=" + y_no);
    s1y.setRefersToFormula("OFFSET(Sheet1!$" + y_no + "$3,0,0,COUNTA(Sheet1!" + y_no + ":" + y_no + ")-1,1)");
    s2y.setRefersToFormula("OFFSET(Sheet1!$" + x_no + "$3,0,0,COUNTA(Sheet1!" + x_no + ":" + x_no + ")-1,1)");

    return true;
}

From source file:uk.ac.manchester.cs.owl.semspreadsheets.model.hssf.impl.WorkbookHSSFImpl.java

License:BSD License

public void addName(String name, Range rng) {
    if (workbook.getName(name) != null) {
        workbook.removeName(name);// w  w w  .ja  v a 2 s . c  o  m
    }
    HSSFName hssfName = workbook.createName();
    hssfName.setNameName(name);
    hssfName.setRefersToFormula(rng.toFixedAddress());
}