0
\$\begingroup\$

I am using apache-poi to copy excel files while also altering a few things on them. While copying, the styles, values and formulas should be retained. Yet, some formulas need to be replaced with their result value if its available.

Formulas need to be replaced with their result value, if they either contain a specific formula or they reference any external source (another excel sheet).

This is the code I currently use:

public void copyCell(XSSFCell oldCell, SXSSFCell newCell, Map<Integer, CellStyle> styleMap) {
    int styleHashCode = oldCell.getCellStyle().hashCode();
    XSSFCellStyle newCellStyle = (XSSFCellStyle) styleMap.get(styleHashCode);
    if (newCellStyle == null) {
        newCellStyle = (XSSFCellStyle) newCell.getSheet().getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());

        CTColor fgColor = oldCell.getSheet().getWorkbook().getStylesSource()
                .getFillAt((int) oldCell.getCellStyle().getCoreXf().getFillId()).getCTFill().getPatternFill()
                .getFgColor();
        if (fgColor != null) {
            newCellStyle.setFillForegroundColor(
                    new XSSFColor(convertCTColor(fgColor, oldCell.getSheet().getWorkbook().getStylesSource()),
                            new DefaultIndexedColorMap()));
        }
        styleMap.put(styleHashCode, newCellStyle);
    }

    newCell.setCellStyle(newCellStyle);

    switch (oldCell.getCellType()) {
        case STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            if (oldCell.getStringCellValue().matches(".*\\.xls[xm]?.*")) {
                fileNameExceptionMap.put(workbookName,
                        "Cell: " + oldCell.getAddress().formatAsString() + " possibly contains excel path.");
            }
            break;
        case NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case BLANK:
            newCell.setCellType(CellType.BLANK);
            break;
        case BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case FORMULA:
            int externalSheetIndex = -1;
            try {
                EvaluationCell evaluationCell = evalSheet.getCell(oldCell.getRowIndex(), oldCell.getColumnIndex());
                Ptg[] formulaTokens = evalWorkbook.getFormulaTokens(evaluationCell);
                for (Ptg formulaToken : formulaTokens) {
                    if (formulaToken instanceof Ref3DPxg) {
                        Ref3DPxg refToken = (Ref3DPxg) formulaToken;
                        externalSheetIndex = refToken.getExternalWorkbookNumber();
                    } else if (formulaToken instanceof Area3DPxg) {
                        Area3DPxg refToken = (Area3DPxg) formulaToken;
                        externalSheetIndex = refToken.getExternalWorkbookNumber();
                    }
                    if (externalSheetIndex >= 0) {
                        break;
                    }
                }
            } catch (FormulaParseException e) {
                LOG.error("Parsing error: {}", e.getLocalizedMessage());
            }
            if (externalSheetIndex >= 0 || oldCell.getCellFormula().matches(formulaRegex)) {
                switch (oldCell.getCachedFormulaResultType()) {
                    case NUMERIC:
                        newCell.setCellValue(oldCell.getNumericCellValue());
                        break;
                    case STRING:
                        newCell.setCellValue(oldCell.getStringCellValue());
                        break;
                    case BOOLEAN:
                        newCell.setCellValue(oldCell.getBooleanCellValue());
                        break;
                    case ERROR:
                        newCell.setCellErrorValue(oldCell.getErrorCellValue());
                        break;
                    default:
                        break;
                }
            } else {
                if (oldCell.getCachedFormulaResultType().equals(CellType.ERROR)) {
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
                } else {
                    newCell.setCellFormula(oldCell.getCellFormula());
                }
            }
            break;
        default:
            break;
    }
}

private Color convertCTColor(CTColor fgColor, StylesTable styleSource) {
    if (fgColor.xgetRgb() != null) {
        String stringValue = fgColor.xgetRgb().getStringValue();
        int red = Integer.valueOf(stringValue.substring(2, 4), 16).intValue();
        int green = Integer.valueOf(stringValue.substring(4, 6), 16).intValue();
        int blue = Integer.valueOf(stringValue.substring(6, 8), 16).intValue();
        return new Color(red, green, blue);
    }
    byte[] rgb = styleSource.getTheme().getThemeColor((int) fgColor.getTheme()).getRGB();

    int red = (rgb[0] < 0) ? (rgb[0] + 256) : rgb[0];
    int green = (rgb[1] < 0) ? (rgb[1] + 256) : rgb[1];
    int blue = (rgb[2] < 0) ? (rgb[2] + 256) : rgb[2];
    return new Color(red, green, blue);

}

The code is working fine in its funtionality, but the evaluation of formula cells is not really performant. Copying around 360 cells takes roughly 1 second if all cells contain formulas (SUMIF). Comparing that to a values only row with 16k cells which takes on average 200ms, that is very slow for formulas.

Therefore the question if there is any way to upgrade the performance of this method?


If you have any questions, feel free to ask.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.