0

I'm having a problem evaluating excel sheet cell which uses named cell in its formula. I'm using apache-poi version 3.17, java 1.8 and the file I'm working with is .xlsm. Here is an example code:

//Getting the excel file from the database
Document doc = documentRepository.findByDocumentType(DocumentType.EXAMPLE);
InputStream fis = new ByteArrayInputStream(doc.getFileData());
Workbook wb = new XSSFWorkbook(fis);
Sheet mappingSheet = workbook.getSheet("Quote services");

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

// This cell contains a formula which uses named cell
CellReference cellReference = new CellReference("A109");
Row row = mappingSheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol());
CellValue cellValue = evaluator.evaluate(cell);

This gives me the following error:

org.apache.poi.ss.formula.FormulaParseException: Specified named range 'Discount' does not exist in the current workbook.

The excel formula looks something like that:

=Discount*A7

Where Discount is A5 and if I change the formula to look like this:

=A5*A7

it works fine.

This is kind of strange cause if I try to get the named field Discount it finds it, but if I use evaluate on a cell which uses the field it doesn't.

The document is predefined so I can't change anything in it. If I name the given discount cell with poi and with the same name there is no problem, but I don't want to do this cause there are more named fields in the document and if something change I have to change the code. Any help will be appreciated!

I found the problem! It was that the Discount field was referenced from a hidden sheet in the document and it was referenced like this: Discount instead of 'Quote services'!Discount.

antonionikolov
  • 97
  • 1
  • 1
  • 8
  • Please show the output of `Name name = workbook.getName("Discount"); System.out.println(name.getNameName() + " refers to " + name.getRefersToFormula());` – Axel Richter Feb 23 '18 at 14:25
  • This is the output: `Discount refers to 'Quote services'!$AO$10` The above were example cells this is the real one (not that it matters). – antonionikolov Feb 23 '18 at 14:38
  • Then I cannot reproducing this behavior. The formula `=IF(A1="NO",A13*(1-Discount),A13*(1-M13))` results in a value in the `Excel` sheet if `A1="NO"`? Or does it shows `#Name?` then? – Axel Richter Feb 23 '18 at 14:45
  • It has to show the discount in given currency (double number). Depending on `A1` it chooses the right way to calculate. – antonionikolov Feb 23 '18 at 14:57

1 Answers1

0

Are you sure that Discount is indeed within the Workbook that you are evaluating? The message is pretty clear.

Can you show exactly how the formula looks like - is it something like this:

=SUM('C:\Data\[Finances.xlsx]Discount'!D10:D25)

Try setIgnoreMissingWorkbooks(true), and if it works, then the Discount is set in another workbook. setIgnoreMissingWorkbooks.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Yes, I'm sure. It is in the same Workbook. If I try to get it like this: `Name name = workbook.getName("Discount")` it finds it. The formula looks something like this `=IF(A1="NO",A13*(1-Discount),A13*(1-M13))` – antonionikolov Feb 23 '18 at 13:02
  • In the calculations, there are some cells which are in a different sheet, but in the same workbook. The field `Discount` is in the same sheet thought. – antonionikolov Feb 23 '18 at 15:06
  • If you delete the formula and make it refer only the discount, like this `=Discount`, then run the Java code would it work? – Vityata Feb 23 '18 at 15:12
  • Unfortunately, the sheet is private and I can't edit it but if I set the formula with `apache-poi` it is working, but it is not exactly the same thing. – antonionikolov Feb 23 '18 at 15:26
  • 1
    @antonionikolov - you can always create an empty excel workbook, write 2 namespaces and try it there. – Vityata Feb 23 '18 at 15:27
  • In a new document, it is working. But why it is it giving this error. Strange... It is a pretty complex document, but good that now I know this is not the problem. Thanks! – antonionikolov Feb 23 '18 at 15:39
  • @antonionikolov - anything is possible. From writing the `o` in `Discount` with Cyrillic/Greek to whatever else... – Vityata Feb 23 '18 at 15:41
  • I think I found the problem. There is a hidden sheet in this workbook where The `Discount` cell is referred, but it looks like this `Discount` when it should be `'Quote services'!Discount`. – antonionikolov Feb 23 '18 at 16:24