Example usage for org.apache.poi.ss.util CellReference CellReference

List of usage examples for org.apache.poi.ss.util CellReference CellReference

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellReference
  6. CellReference

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellReference CellReference.

Prototype

public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)

Source Link

Usage

From source file:com.canoo.webtest.plugins.exceltest.ExcelCellUtils.java

License:Open Source License

public static CellReference getCellReference(final Step step, final String cell, final String rowStr,
        final String colStr) {
    if (cell != null) {
        return getCellReference(step, cell);
    } else {//w  w w.  j a  v  a2s  .  c o  m
        try {
            final int row = Integer.parseInt(rowStr);
            if (row > 0) {
                try {
                    final int col = Short.parseShort(colStr);
                    if (col > 0) {
                        return new CellReference(row - 1, col - 1, true, true);
                    }
                } catch (NumberFormatException e) {
                    if (colStr.matches("[A-Z]+")) {
                        return new CellReference(colStr + rowStr);
                    }
                }
                throw new StepExecutionException(
                        "Can't parse '" + colStr + "' as a column reference (eg. 'A' or '1')", step);

            }
        } catch (NumberFormatException e) {
            // fallthrough
        }
        throw new StepExecutionException("Can't parse '" + rowStr + "' as a integer row reference.", step);
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetNamedCellInput.java

License:Apache License

public String getCellExcelReference() {
    if (currentNamedCell != null) {
        CellReference reference = new CellReference(currentNamedCell.getRowIndex(),
                currentNamedCell.getColumnIndex(), true, true);
        return reference.formatAsString();
    } else {/*w  w  w. ja  va2s.c  o  m*/
        return null;
    }
}

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 {/*from  w w  w  .ja  va  2  s.  c om*/
            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 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();
        }//w  w w .j a  va  2  s.c o 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);
    }
}