Bug 63934 - The column doesn't exist in lookup table
Summary: The column doesn't exist in lookup table
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.1.1-FINAL
Hardware: PC All
: P2 normal with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 57721
Blocks:
  Show dependency tree
 
Reported: 2019-11-19 09:39 UTC by niketan mishra
Modified: 2023-09-12 10:58 UTC (History)
1 user (show)


Attachments
Attached hereinwith is a sample excel file to reproduce the issue . Created a simple excel table in two sheets and called XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook) on workbook . (13.42 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-11-19 09:39 UTC, niketan mishra 		Details
Attached hereinwith is the screenshot for the Exception. (69.94 KB, image/png)
2019-11-19 09:43 UTC, niketan mishra 		Details
Attached hereinwith is the java code to reproduce the issue (1.20 KB, text/plain)
2019-11-19 09:45 UTC, niketan mishra 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description niketan mishra 2019-11-19 09:39:21 UTC 
Created attachment 36886 [details]
Attached hereinwith is a sample excel file to reproduce the issue . Created a simple excel table in two sheets and called XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook) on workbook .

duplicate bug id - 57721
i have an excel file with named excel tables for vlookup in a sheet.
im trying to get the vlookup result in another sheet by referencing through Gender_lookup table. I am using formula =VLOOKUP(TRIM(CLEAN([@[Gender ]])),Gender_lookup,2,0) which takes Gender value from current row in my table and checks for corresponding Gender value in Gender_lookup table in another sheet.
I tried evaluating with XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
which gives exception as org.apache.poi.ss.formula.FormulaParseException: The column  doesn't exist in table Gender_lookup at org.apache.poi.ss.formula.FormulaParser.parseStructuredReference(FormulaParser.java:821)
According to me the issue is related to named Table range inside Gender lookup table.
I have checked the table name and its working perfectly in excel.
Please help me with the issue without changing excel table formulaes.
Im attaching a sample excel to reproduce the issue with screenshot.
Comment 1 niketan mishra 2019-11-19 09:43:01 UTC 
Created attachment 36887 [details]
Attached hereinwith is the screenshot for the Exception.

Please find attached screenshot for the bug
Comment 2 niketan mishra 2019-11-19 09:45:59 UTC 
Created attachment 36888 [details]
Attached hereinwith is the java code to reproduce the issue

Please find attached java file to reproduce issue. Please replace the excel path location in java file with your saved excel location.
Comment 3 maxence.cramet 2023-08-10 06:00:37 UTC 
Same issue with 5.2.3
Comment 4 Matthias Raschhofer 2023-09-01 11:44:30 UTC 
Btw, there's a workaround for the issue: 
If you specify the range in the lookup table explicitly, both excel and the apache poi library can evaluate the formula just fine. E.g.: 
=VLOOKUP([@[Gender ]];Gender_lookup[[Gender input]:[Gender mod]];2;0)
Comment 5 Matthias Raschhofer 2023-09-01 12:27:07 UTC 
According to Microsoft the structure-reference Gender_lookup[] is equivalent to Gender_lookup[#Data], which poi is able to parse correctly.

See https://learn.microsoft.com/en-us/openspecs/office_standards/ms-oe376/bcd72180-31a3-423b-8f83-d224b2286da3.
Comment 6 Matthias Raschhofer 2023-09-01 14:06:59 UTC 
Since there's a workaround, imho this is not a very critical issue, however it would probably still be good to support this case.

I opened a pull request to offer a fix for this issue: https://github.com/apache/poi/pull/514
Comment 7 PJ Fanning 2023-09-12 10:58:57 UTC 
https://github.com/apache/poi/pull/514 is now merged