How can I ignore external workbook references in formula

[abygladiator]

I'm using v3.1.0 and my worksheet has an external workbook reference in the formula for range of cells. When I'm trying to use Poiji.fromExcel and pass the sheet and options, I'm getting the exception "could not resolve external workbook name ... Workbook environment has not been setup".

Will you please suggest the best way to resolve this issue?

Thanks

[github-actions]

Thank you for contributing to Poiji! Feel free to create a PR If you want to contribute directly :)

[ozlerhakan]

Hi @abygladiator , we could apply this solution for your use case. Could you share the file your have with me, so we can add a test for this as well for the next release of Poiji?

[abygladiator]

I tried this:

for (int a = 0; sheet.getPhysicalNumberOfRows(); ++) {
for (Cell cell sheet.getRow(c)) {
if (cell.getCellType() == CellType.FORMULA) {
var bcd: FormulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); bcd.setIgnoreMissingWorkbooks (true);
Double tempCellValue = cell.getNumericCellValue();
cell.setCellValue(tempCellValue);
}
}
}
List data = Poiji.fromExcel(sheet, mapper, options);

But, it still the cell values contain the formula not the actual value and throws the same error.

What did I miss here?

Can you please provide a code snippet?

[ozlerhakan]

Sorry but I need a sample like the one you have to try it out.

[abygladiator]

See the two files Test-01 refers to the values in Test-02. Assume Test-02 is somewhere in the cloud and you have the URL for the physical document referenced in the Test-01 file
Test-01.xlsx
Test-02.xlsx

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[ozlerhakan]

I haven't checked it yet. Ping it for the stale bot

[abygladiator]

Can you look at it please? Let me know if you need more input

[ozlerhakan]

Hi @abygladiator ,

I couldn't reproduce this using 4.1.2.

[abygladiator]

Are you saying version 4.1.2 supports reading the worksheet values with vlookup and other formula types?

[ozlerhakan]

I was able to read the cells in the excel files with no exception. What do you exactly aim when you correctly read your files using Poiji?

[abygladiator]

Yes, I want to read the cell values and convert the sheet data to the mapper class. However, this line
List data = Poiji.fromExcel(sheet, mapper, options);
throws "could not resolve external workbook name ... Workbook environment has not been setup" exception

[ozlerhakan]

Either https://github.com/ozlerhakan/poiji?tab=readme-ov-file#poi-sheet-support or https://github.com/ozlerhakan/poiji?tab=readme-ov-file#annotations works without the exception you point out. Make sure you're using the latest version of Poiji

[ozlerhakan]

Did you figure it out @abygladiator ?

[abygladiator]

I tried to use version 4.2.1and run it iwith gradle 6.5 but I'm getting the following error:

"Could not resolve all files for configuration ':compileClasspath'.
Could not find com.github.ozlerhakan:Poiji:4.2.1."

Is it possible to run the new version with gradle 6.5?

[ozlerhakan]

Interesting.. this shouldn't be related to gradle version. Could you clean your project and add poiji from scratch?

[abygladiator]

Still same issue

[ozlerhakan]

let me know if you encounter this again since it's not related to Poiji, Thanks!