[Bug] There is a problem with formula reading

[gphwxhq]

I noticed that there is still an issue related to easyExcel that hasn't been fixed yet alibaba/easyexcel#2469

[delei]

Thank you for your feedback. We will try to reproduce this issue later.

BTW, as the content in the original issue may be somewhat outdated, could you please use the latest issue template, and provide a sample runnable code using the latest fastexcel version? This would be most helpful.

[delei]

I'm trying to reproduce this issue in my local enviroment.

FastExcel version

1.3.0

JDK version

1.8

Test Case

For a complete example, please refer to my github repository.

• delei-fastexcel-issue-571

ReadListener

@Slf4j
public class Issue571ReadListener extends AnalysisEventListener<DemoData> {
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("Parsed a data row:{}", JSON.toJSONString(data));
        Map<Integer, Cell> cellMap = context.readRowHolder().getCellMap();
        for (Map.Entry<Integer, Cell> entry : cellMap.entrySet()) {
            CellData cellData = (CellData) entry.getValue();
            if (cellData.getFormulaData() != null) {
                log.info("Cell:{} has a formula:{}", entry.getKey(), cellData.getFormulaData().getFormulaValue());
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("All data analysed");
    }
}

Test

public class Issue571Test {

    @Test
    void readTest() {
        File testFile = TestFileUtil.readFile("issue/common/simple_formula.xlsx");
        FastExcel.read(testFile, DemoData.class, new Issue571ReadListener())
                .excelType(ExcelTypeEnum.XLSX)
                .sheet()
                .doReadSync();
    }
}

Data File

The formula in column C was filled manually by dragging.

Result

The filled formula cannot be read.

[delei]

I have raised a pull request(Draft) to fix this issue. In my local environment, I have successfully read the formulae within the current test case file. This is only applicable to xlsx files.

BTW, I still need more test cases and complete the unit testing ASAP. Please feel free to comment or provide further test case files.

[delei]

Analysis

Use a decompression tool to extract the xlsx file, the contents of the sheet1.xml file within are as follows:

After comparing the contents of cells C2, C3, and C4, it was discovered that the formula generated by the fill function cannot be directly obtained within the current XML attribute.

Proposal

We employ the SAX parsing method to read xlsx files, so I extended the CellFormulaTagHandler class to handle shared formulas during file parsing.

A Map has been created to store the shared formulas within the current sheet. Subsequent cells will utilise the shared formula corresponding to the si label as the formula value for that cell.

Result

The formula value has been successfully read.

[ongdisheng]

Hi @delei, I'd like to suggest an approach for reading shared formulas in XLSX files.

Proposed Approach

My idea is to handle shared formulas in CellFormulaTagHandler. When Excel saves shared formulas, only the first cell has the formula text. The other cells just reference it with an empty tag and a shared index. I'm thinking of storing the master formula when we encounter text, then converting it for the empty cells based on their row and column offset. I'd use Apache POI's SharedFormula class for the conversion since it already handles all the cell reference types correctly.

What I'd Like to Change

1. XlsxReadSheetHolder.java

private Map<Integer, SharedFormulaInfo> sharedFormulaMap;

public static class SharedFormulaInfo {
    private String formulaText;
    private int firstRow;
    private int firstCol;
}

2. CellFormulaTagHandler.java

private String handleSharedFormula(XlsxReadSheetHolder holder, String formulaText, int sharedIndex) {
    Integer currentRow = holder.getRowIndex();
    Integer currentCol = holder.getColumnIndex();

    if (!StringUtils.isEmpty(formulaText)) {
        // Store the master formula with its position
        holder.getSharedFormulaMap().put(sharedIndex, 
            new SharedFormulaInfo(formulaText, currentRow, currentCol));
        return formulaText;
    } else {
        // Get master and convert it
        SharedFormulaInfo masterInfo = holder.getSharedFormulaMap().get(sharedIndex);
        return convertSharedFormula(masterInfo, currentRow, currentCol);
    }
}

private String convertSharedFormula(SharedFormulaInfo masterInfo, int currentRow, int currentCol) {
    try {
        // Parse master formula into tokens
        Ptg[] masterPtgs = FormulaParser.parse(masterInfo.getFormulaText(), null, FormulaType.CELL, 0);
        
        // Calculate offset from master position
        int rowOffset = currentRow - masterInfo.getFirstRow();
        int colOffset = currentCol - masterInfo.getFirstCol();
        
        // Use POI to convert with offset
        SharedFormula sharedFormula = new SharedFormula(SpreadsheetVersion.EXCEL2007);
        Ptg[] convertedPtgs = sharedFormula.convertSharedFormulas(masterPtgs, rowOffset, colOffset);
        
        return FormulaRenderer.toFormulaString(null, convertedPtgs);
    } catch (Exception e) {
        // Return master formula if conversion fails
        return masterInfo.getFormulaText();
    }
}

Feel free to share your thoughts or suggestions on this. I would be more than happy to discuss or make changes as needed.
Thanks a lot!

[ongdisheng]

I've tested my proposed approach and below is the result.

Test code

@Slf4j
public class Issue571Test {
    @Test
    public void testReadSharedFormulas() {
        String fileName = TestFileUtil.getPath() + "temp" + File.separator 
                        + "issue571" + File.separator + "shared_formula.xlsx";

        List<String> formulas = new ArrayList<>();

        FastExcel.read(fileName, DemoData.class, new AnalysisEventListener<DemoData>() {
            @Override
            public void invoke(DemoData data, AnalysisContext context) {
                log.info("Parsed a data row:{}", JSON.toJSONString(data));

                Map<Integer, Cell> cellMap = context.readRowHolder().getCellMap();
                for (Map.Entry<Integer, Cell> entry : cellMap.entrySet()) {
                    CellData cellData = (CellData) entry.getValue();
                    if (cellData.getFormulaData() != null) {
                        String formula = cellData.getFormulaData().getFormulaValue();
                        log.info("Cell {} has a formula: {}", entry.getKey(), formula);
                        formulas.add(formula);
                    }
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("All data analysed. Found {} formulas", formulas.size());
            }
        }).sheet().doRead();
    }
}

Test result

PS C:\Users\disheng\Desktop\OSS\fesod\fesod-examples> mvn "-Dmaven.test.skip=false" test "-Dtest=Issue571Test"
[INFO] -------------------------------------------------------
[INFO]  T E S T S
[INFO] -------------------------------------------------------
[INFO] Running org.apache.fesod.sheet.temp.issue571.Issue571Test
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 3 has a formula: RANDBETWEEN(100,999)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 4 has a formula: SUM(B2:C2)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 5 has a formula: SUM($B2:$C2)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 6 has a formula: SUM($B$2:$C2)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 7 has a formula: SUM(B2+C2)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 3 has a formula: RANDBETWEEN(100,999)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 4 has a formula: SUM(B3:C3)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 5 has a formula: SUM($B3:$C3)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 6 has a formula: SUM($B$2:$C3)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 7 has a formula: SUM(B3+C3)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 3 has a formula: RANDBETWEEN(100,999)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 4 has a formula: SUM(B4:C4)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 5 has a formula: SUM($B4:$C4)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 6 has a formula: SUM($B$2:$C4)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 7 has a formula: SUM(B4+C4)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 3 has a formula: RANDBETWEEN(100,999)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 4 has a formula: SUM(B5:C5)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 5 has a formula: SUM($B5:$C5)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 6 has a formula: SUM($B$2:$C5)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 7 has a formula: SUM(B5+C5)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 3 has a formula: RANDBETWEEN(100,999)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 4 has a formula: SUM(B6:C6)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 5 has a formula: SUM($B6:$C6)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 6 has a formula: SUM($B$2:$C6)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 7 has a formula: SUM(B6+C6)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 3 has a formula: RANDBETWEEN(100,999)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 4 has a formula: SUM(B7:C7)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 5 has a formula: SUM($B7:$C7)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 6 has a formula: SUM($B$2:$C7)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 7 has a formula: SUM(B7+C7)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Parsed a data row:{}
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 3 has a formula: RANDBETWEEN(100,999)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 4 has a formula: SUM(B8:C8)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 5 has a formula: SUM($B8:$C8)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 6 has a formula: SUM($B$2:$C8)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Cell 7 has a formula: SUM(B8+C8)
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - All data analysed. Found 35 formulas
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Formulas: [RANDBETWEEN(100,999), SUM(B2:C2), SUM($B2:$C2), SUM($B$2:$C2), SUM(B2+C2), RANDBETWEEN(100,999), SUM(B3:C3), SUM($B3:$C3), SUM($B$2:$C3), SUM(B3+C3), RANDBETWEEN(100,999), SUM(B4:C4), SUM($B4:$C4), SUM($B$2:$C4), SUM(B4+C4), RANDBETWEEN(100,999), SUM(B5:C5), SUM($B5:$C5), SUM($B$2:$C5), SUM(B5+C5), RANDBETWEEN(100,999), SUM(B6:C6), SUM($B6:$C6), SUM($B$2:$C6), SUM(B6+C6), RANDBETWEEN(100,999), SUM(B7:C7), SUM($B7:$C7), SUM($B$2:$C7), SUM(B7+C7), RANDBETWEEN(100,999), SUM(B8:C8), SUM($B8:$C8), SUM($B$2:$C8), SUM(B8+C8)]
[main] INFO org.apache.fesod.sheet.temp.issue571.Issue571Test - Test completed. Total formulas found: 35
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.921 s -- in org.apache.fesod.sheet.temp.issue571.Issue571Test
[INFO]
[INFO] Results:
[INFO]
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0
[INFO]
[INFO]
[INFO] --- jacoco:0.8.14:report (generate-report) @ fesod-examples ---
[INFO] Skipping JaCoCo execution due to missing execution data file.
[INFO]
[INFO] --- jacoco:0.8.14:report-aggregate (generate-aggregate-report) @ fesod-examples ---
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  5.396 s
[INFO] Finished at: 2025-11-16T13:49:51Z
[INFO] ------------------------------------------------------------------------

Test file

The test file shared_formula.xlsx includes different types of shared formulas:

• RANDBETWEEN(100,999): Function with no cell references
• SUM(B2:C2): Relative references
• SUM($B2:$C2): Column absolute references
• SUM($B$2:$C2): Mixed references
• SUM(B2+C2): Addition formulas

[delei]

I've tested my proposed approach and below is the result.

Hi, @ongdisheng
It seems quite good. Please feel free to submit your pull request.

FYI, please refer to the comments on this issue and the case attachments provided by other contributors in the old PR #581 to see if they prove useful for your testing.

BTW, This issue may require a considerable amount of time for review. If all current test cases have passed, you can provide your test case files in the PR.