ongdisheng commented on issue #571:
URL: https://github.com/apache/fesod/issues/571#issuecomment-3537088953
Hi @delei, I'd like to suggest a solution for reading shared formulas in
XLSX files.
## Proposed Solution
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**
```java
private Map<Integer, SharedFormulaInfo> sharedFormulaMap;
public static class SharedFormulaInfo {
private String formulaText;
private int firstRow;
private int firstCol;
}
```
2. **CellFormulaTagHandler.java**
```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!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
