• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

Apache POI get named range (also from structured table reference)

 
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Guys,

this is a cross-post from here:

webpage

I am trying to get 2 named ranges ("Test") and ("TestVariable") from Excel (you can download example from link above).

MY code is:



I have code from here:

https://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges but this is not working.

When there is "Test" variable the Cell c = {E,F} and it is ok.

But when i am trying to read TestVariable (reference to structure table) there is an error:


Please help,
can i refer to structured table named range reference?

Best Wishes,
Jacek




 
Rancher
Posts: 508
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

... But when i am trying to read TestVariable (reference to structure table) there is an error:




For working with Excel tables one has to use this API: org.apache.poi.ss.usermodel.XSSFTable

For example:

From Excel create a table in a workbook's sheet ("MyTableSheet"): Insert -> Table.
Name the table: Formulas -> Name Manager (here one can lookup the created table name and edit it as "MyTable")

Access the table from the Java program using Apache POI:


 
Jacek jaryszek
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi thank you!

so answer is: you can using only table object but you can not access to named range using reference to strcuture table...

Best,
Jacek
 
Prasad Saya
Rancher
Posts: 508
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Luke jaryszek wrote:Hi thank you!
so answer is: you can using only table object but you can not access to named range using reference to strcuture table...
Best,
Jacek



You are welcome. Actually, I don't know; ii looks like a table is different from a named range. To access a range of named cells the example you had referred at the Apache POI web site is the correct one. You can try the example (named range) by creating a named range in an Excel sheet: name a range of cells using the "Name Manager" and then try to access them in Java. Here is the link again:

https://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges
 
Jacek jaryszek
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried this my friend and tihs is not working.

Structure table NOT for APACHE POI named range references.

Best,
Jacek
 
Prasad Saya
Rancher
Posts: 508
15
Notepad Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Luke jaryszek wrote:I tried this my friend and tihs is not working.
Structure table NOT for APACHE POI named range references.
Best, Jacek



Which one is not working: tables or named references?

If you have tried, please explain and post the code you had tried.
 
Jacek jaryszek
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
From internet from my friend:

This isn't a problem with formula function coverage, it is a core problem with XSSF formula SYNTAX evaluation.  POI just doesn't understand Excel Table "Structured References" in formulas.  These are XSSF format objects only, and have no equivalent in HSSF.

https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

This means POI formula support is limited to pre-2007 syntax only, which is an increasing problem.

The fix should not be too hard, but would be deep into XSSFEvaluationWorkbook.  There is no function to register.  It would need to extend getName(String, int) to also check all sheets for tables matching the given name (tables have globally scoped names but are tied to specific sheets), and return something similar to EvaluationName.  

Since table references are just convenience syntax, this could just map directly to a range specification and parse the formula using the range expression from that point on, I think.

I plan to dig a bit, but any fix will involve some extensive refactoring, so I'm not sure anyone outside the core committers can write a patch acceptable to the repository owners.



When in Excel you have Reference within named range like:
=Table1[Column1] isntead of "=Sheet1!$A$1:$B$1" you can't read this with APACHE POI.

Only addresses. You have workaround for this using evaluation function.

Best,
Jacek

 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic