Extract Excel 2007 tables with Jakarta POI 3.7

[Donato]

Hello,
I'm fighting with this problem: I have an existing excel file (xlsx or xls, but
created with Excel 2007), containing a table (not a pivot table, just a table
to have cell styling and autofilter, this is a specific 2007 feature I think).

I saw that if you save the Excel file as xlsx, the table definition is put in
the table1.xml file under xl/tables.

My goal is to update the table definition as I create new rows in the sheet.
I've already tried to access table info with getMapInfo, getAllEmbedded,
getNameName methods applied to workbook object, but none of them worked.

So, my question, it is possible to handle tables with POI? If not, are there
any low lewel methods to open and update the xml (or the binary) file?

Many thanks!
Donato

[Nick Burch]

On Tue, 22 Feb 2011, Donato wrote:
> I saw that if you save the Excel file as xlsx, the table definition is
> put in the table1.xml file under xl/tables.

There's some support for this in XSSF. The trick to spotting support for
these things is to first look in XSSFRelation, where you'll see that
handily we know about it as XSSFRelation.TABLE. Next, ask your IDE for
references to that, and in this case it's in XSSFMap via the
getRelatedTables() method. That'll return you a Table object, which is a
wrapper around the various low-level xmlbeans objects. You get at the maps
via getCustomXMLMappings() on a xssfworkbook.

Hopefully that's enough to get you going?

Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Tue, 22 Feb 2011, Donato wrote:
> > I saw that if you save the Excel file as xlsx, the table definition is
> > put in the table1.xml file under xl/tables.
>
> There's some support for this in XSSF. The trick to spotting support for
> these things is to first look in XSSFRelation, where you'll see that
> handily we know about it as XSSFRelation.TABLE. Next, ask your IDE for
> references to that, and in this case it's in XSSFMap via the
> getRelatedTables() method. That'll return you a Table object, which is a
> wrapper around the various low-level xmlbeans objects. You get at the maps
> via getCustomXMLMappings() on a xssfworkbook.
>
> Hopefully that's enough to get you going?
>
> Nick
>


Hello Nick,
thanks for your answer. Could you provide some code to better understand how to
proceed? I've tried to use getMapInfo and also getCustomXMLMappings on a
XSSFWorkbook object but they seem both null.

Thanks!

[Nick Burch]

On Wed, 23 Feb 2011, Donato wrote:
> thanks for your answer. Could you provide some code to better understand
> how to proceed? I've tried to use getMapInfo and also
> getCustomXMLMappings on a XSSFWorkbook object but they seem both null.

Do you have a /xl/xmlMaps.xml part in your file?

And what are the table parts related to? (i.e. which _rels file has them
as a target)

Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Wed, 23 Feb 2011, Donato wrote:
> > thanks for your answer. Could you provide some code to better understand
> > how to proceed? I've tried to use getMapInfo and also
> > getCustomXMLMappings on a XSSFWorkbook object but they seem both null.
>
> Do you have a /xl/xmlMaps.xml part in your file?
>
> And what are the table parts related to? (i.e. which _rels file has them
> as a target)
>
> Nick
>

No, I do not have the xmlMaps.xml. I saved the excel file (created with Office
2007) as .xlsx, then unzipped it and looked at the file system structure.

Under xl, I have among other dirs, xl\tables, containing an XML file for each
table inserted in the excel (I also noticed that if I change the table range
directly in these files, then I zip back the xlsx I can resize the tables, that
is my goal).

How can I create the xmlMaps.xml? Is it created by Excel or by POI?

Thanks!

[Nick Burch]

On Wed, 23 Feb 2011, Donato wrote:
> No, I do not have the xmlMaps.xml. I saved the excel file (created with
> Office 2007) as .xlsx, then unzipped it and looked at the file system
> structure.

Hmm, looks like the user contributed patch for xml mapping and tables
assumed something that wasn't always the case then

> Under xl, I have among other dirs, xl\tables, containing an XML file for
> each table inserted in the excel (I also noticed that if I change the
> table range directly in these files, then I zip back the xlsx I can
> resize the tables, that is my goal).

Can you create a new bug in bugzilla, and upload a simple file with a
table but no xml mappings to it? We can then use that as the basis for
tweaking the support

In the mean time, you'll probably just want to grab the appropriate
DocumentPart yourself, and then manipulate it using XSSFTable once
constructed. It's a bit fiddly though...

Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Wed, 23 Feb 2011, Donato wrote:
> > No, I do not have the xmlMaps.xml. I saved the excel file (created with
> > Office 2007) as .xlsx, then unzipped it and looked at the file system
> > structure.
>
> Hmm, looks like the user contributed patch for xml mapping and tables
> assumed something that wasn't always the case then
>
> > Under xl, I have among other dirs, xl\tables, containing an XML file for
> > each table inserted in the excel (I also noticed that if I change the
> > table range directly in these files, then I zip back the xlsx I can
> > resize the tables, that is my goal).
>
> Can you create a new bug in bugzilla, and upload a simple file with a
> table but no xml mappings to it? We can then use that as the basis for
> tweaking the support
>
> In the mean time, you'll probably just want to grab the appropriate
> DocumentPart yourself, and then manipulate it using XSSFTable once
> constructed. It's a bit fiddly though...
>
> Nick
>

Hello Nick,

I'll open the bug. Could you provide some complete examples in order to use the
XSSFTable object? (I had a look both at the javadoc and at the source files,
but it seems a bit tricky.., it is not clear to me how to move from the
workbook object already created to the document parts).

Thanks!
Donato

[Nick Burch]

On Wed, 23 Feb 2011, Donato wrote:
> I'll open the bug. Could you provide some complete examples in order to
> use the XSSFTable object? (I had a look both at the javadoc and at the
> source files, but it seems a bit tricky.., it is not clear to me how to
> move from the workbook object already created to the document parts).

I've personally no idea what an xssf table is, or why you'd want one... So
I've no specific code to hand!

You'll want to get the document part of the workbook. Then, ask the
openxml4j package code to give you the part for your table.
(XSSFRelation.TABLE has most of the things you need to do it, and look in
POIXMLDocument for the code to do it). When you have the table package
part, create a xssf.model.Table object with it, and you're away

Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Wed, 23 Feb 2011, Donato wrote:
> > I'll open the bug. Could you provide some complete examples in order to
> > use the XSSFTable object? (I had a look both at the javadoc and at the
> > source files, but it seems a bit tricky.., it is not clear to me how to
> > move from the workbook object already created to the document parts).
>
> I've personally no idea what an xssf table is, or why you'd want one... So
> I've no specific code to hand!
>
> You'll want to get the document part of the workbook. Then, ask the
> openxml4j package code to give you the part for your table.
> (XSSFRelation.TABLE has most of the things you need to do it, and look in
> POIXMLDocument for the code to do it). When you have the table package
> part, create a xssf.model.Table object with it, and you're away
>
> Nick
>

Ok, I'll try to use the POIXMLDocument class (which is the superclass of a
XSSFWorkbook, if I'm right). A question: I'm creating workbook objects as

Workbook wb = new HSSFWorkbook(myfile.xls)
or
Workbook wb = new XSSFWorkbook(myfile.xlsx)

Are there any difference, especially for the XSSFTable I'm interested in,
between these declarations and HSSFWorkbook wb = ... or XSSFWorkbook wb = ... ?
(Anyway, I also tried both, and it always seems that I cannot get the map info
neither from the xml nor the binary format). I think that your solution (using
the POIXMLDocument) will work only for xlsx files, won't it?

Thanks, Donato

[Nick Burch]

On Wed, 23 Feb 2011, Donato wrote:
> Ok, I'll try to use the POIXMLDocument class (which is the superclass of a
> XSSFWorkbook, if I'm right). A question: I'm creating workbook objects as
>
> Workbook wb = new HSSFWorkbook(myfile.xls)
> or
> Workbook wb = new XSSFWorkbook(myfile.xlsx)
>
> Are there any difference, especially for the XSSFTable I'm interested
> in, between these declarations and HSSFWorkbook wb = ... or XSSFWorkbook
> wb = ... ?

It'll only work for XSSFWorkbook, as it's a XSSF specific feature

Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Wed, 23 Feb 2011, Donato wrote:
> > Ok, I'll try to use the POIXMLDocument class (which is the superclass of a
> > XSSFWorkbook, if I'm right). A question: I'm creating workbook objects as
> >
> > Workbook wb = new HSSFWorkbook(myfile.xls)
> > or
> > Workbook wb = new XSSFWorkbook(myfile.xlsx)
> >
> > Are there any difference, especially for the XSSFTable I'm interested
> > in, between these declarations and HSSFWorkbook wb = ... or XSSFWorkbook
> > wb = ... ?
>
> It'll only work for XSSFWorkbook, as it's a XSSF specific feature
>
> Nick
>

Hello Nick,

I opened the bug 50829 in Bugzilla.
I was not able to get table relations even trying to use POIXMLDocument.

Thanks!
Donato

[Donato]

Donato <ct003006 <at> mediaset.it> writes:

>
> Nick Burch <nick.burch <at> alfresco.com> writes:
>
> >
> > On Wed, 23 Feb 2011, Donato wrote:
> > > Ok, I'll try to use the POIXMLDocument class (which is the superclass of a
> > > XSSFWorkbook, if I'm right). A question: I'm creating workbook objects as
> > >
> > > Workbook wb = new HSSFWorkbook(myfile.xls)
> > > or
> > > Workbook wb = new XSSFWorkbook(myfile.xlsx)
> > >
> > > Are there any difference, especially for the XSSFTable I'm interested
> > > in, between these declarations and HSSFWorkbook wb = ... or XSSFWorkbook
> > > wb = ... ?
> >
> > It'll only work for XSSFWorkbook, as it's a XSSF specific feature
> >
> > Nick
> >
>
> Hello Nick,
>
> I opened the bug 50829 in Bugzilla.
> I was not able to get table relations even trying to use POIXMLDocument.
>
> Thanks!
> Donato
>

Hello Nick, many many thanks for your very fast solution of the bug 50829.
I had a look a the new getTables() method you've added and I copied the code in
my page. With that I was able to get all table info and also to update tables.

The only minor problem is that if a conditional formatting is applied to the
table, its range is not updated when the table range is (but I think this is
not a topic of tables but it is related to a lack of support for conditional
formatting in XSSFSheet, isn't it?).

This is the code I used to update a table (once extraced):

String cell_start = table.getStartCellReference
( ).formatAsString( );

CellReference cell_end = table.getEndCellReference( );
int new_row = cell_end.getRow( ) + 10; //just to add 10 rows
int new_col = cell_end.getCol( );
String cell_new = (new CellReference(new_row,
new_col)).formatAsString( );

table.getCTTable( ).setRef(cell_start + ":" + cell_new);

Using setRef, the table1.xml file is updated, but the xml for the sheet
(containing a conditional formatting) is not. Do you know any workaround for
this, or a better way to update a table range?

My goal is to populate an empty table (with only columns and styles defined)
with data I retrieve from a db, so I add an unknown number of rows and I'd like
to extend the table accordingly.

Thanks again,
bye
Donato

[Nick Burch]

On Wed, 2 Mar 2011, Donato wrote:
> String cell_new = (new CellReference(new_row,
> new_col)).formatAsString( );
>
> table.getCTTable( ).setRef(cell_start + ":" + cell_new);
>
> Using setRef, the table1.xml file is updated, but the xml for the sheet
> (containing a conditional formatting) is not.

When you call getCT.... you're getting the low level xml beans. At that
point, you need to do everything yourself. So, you'll need to find any
other appropriate beans and set them to match.

Where a high level usermodel call exists, POI will be doing any fiddly
stuff for you (like keeping different bits of the file in sync when you
make a change).

I think in this case though, there isn't a usermodel call for it. However,
we'd love patches that would add this!

Thanks
Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Wed, 2 Mar 2011, Donato wrote:
> > String cell_new = (new CellReference(new_row,
> > new_col)).formatAsString( );
> >
> > table.getCTTable( ).setRef(cell_start + ":" + cell_new);
> >
> > Using setRef, the table1.xml file is updated, but the xml for the sheet
> > (containing a conditional formatting) is not.
>
> When you call getCT.... you're getting the low level xml beans. At that
> point, you need to do everything yourself. So, you'll need to find any
> other appropriate beans and set them to match.
>
> Where a high level usermodel call exists, POI will be doing any fiddly
> stuff for you (like keeping different bits of the file in sync when you
> make a change).
>
> I think in this case though, there isn't a usermodel call for it. However,
> we'd love patches that would add this!
>
> Thanks
> Nick
>

I'll try. In the meantime, is it possible to create a kind of feature request
or to open a bug in bugzilla or somewhere else? I think the following things
should be supported:
- possibility to handle conditional formatting using XSSF objects too
- auto-extend ranges for conditional formatting when new rows are inserted
inside a range
- auto-extend ranges when a table is extented using setRef

Many thanks!
Donato

[Nick Burch]

On Wed, 2 Mar 2011, Donato wrote:
> I'll try. In the meantime, is it possible to create a kind of feature
> request or to open a bug in bugzilla or somewhere else?

Yup, just set the severity to "enhancement" in bugzilla

> I think the following things should be supported:
> - possibility to handle conditional formatting using XSSF objects too
> - auto-extend ranges for conditional formatting when new rows are inserted
> inside a range
> - auto-extend ranges when a table is extented using setRef

One thing to remember is that as POI is a volunteer project, the quickest
way to see new features added is to send in the patches yourself :)

Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Wed, 2 Mar 2011, Donato wrote:
> > I'll try. In the meantime, is it possible to create a kind of feature
> > request or to open a bug in bugzilla or somewhere else?
>
> Yup, just set the severity to "enhancement" in bugzilla
>
> > I think the following things should be supported:
> > - possibility to handle conditional formatting using XSSF objects too
> > - auto-extend ranges for conditional formatting when new rows are inserted
> > inside a range
> > - auto-extend ranges when a table is extented using setRef
>
> One thing to remember is that as POI is a volunteer project, the quickest
> way to see new features added is to send in the patches yourself :)
>
> Nick
>


Hi Nick,
another topic related to tables. I noticed the following problem: I have an
Excel (XLSX) with a table in the sheet1. I add a new sheet with
workBook.createSheet("mysheet") and then I set it as the first sheet with
workBook.setSheetOrder(0). After that, the table is in some way corrupted (i.e.
it is displayed but cells cannot be edited any more).
This happens with xlsx files and not with xls (i.e. it happens with
XSSFWorkbook only). Maybe relations between tables and sheets are not updated
correctly.
Can you check?

Note that this is not related to the problem of reading / updating tables from
POI, but it's just to preserving existing tables in case of new sheets.

Many thanks!
Donato

[Nick Burch]

On Thu, 3 Mar 2011, Donato wrote:
> another topic related to tables. I noticed the following problem: I have
> an Excel (XLSX) with a table in the sheet1. I add a new sheet with
> workBook.createSheet("mysheet") and then I set it as the first sheet
> with workBook.setSheetOrder(0). After that, the table is in some way
> corrupted (i.e. it is displayed but cells cannot be edited any more).
> This happens with xlsx files and not with xls (i.e. it happens with
> XSSFWorkbook only). Maybe relations between tables and sheets are not
> updated correctly. Can you check?

Probably easiest if you do - unzip the .xlsx and take a look at the _rels.
You're looking at what you had before for linking and referencing your
table, and what you have after

Nick

[Donato]

Nick Burch <nick.burch <at> alfresco.com> writes:

>
> On Thu, 3 Mar 2011, Donato wrote:
> > another topic related to tables. I noticed the following problem: I have
> > an Excel (XLSX) with a table in the sheet1. I add a new sheet with
> > workBook.createSheet("mysheet") and then I set it as the first sheet
> > with workBook.setSheetOrder(0). After that, the table is in some way
> > corrupted (i.e. it is displayed but cells cannot be edited any more).
> > This happens with xlsx files and not with xls (i.e. it happens with
> > XSSFWorkbook only). Maybe relations between tables and sheets are not
> > updated correctly. Can you check?
>
> Probably easiest if you do - unzip the .xlsx and take a look at the _rels.
> You're looking at what you had before for linking and referencing your
> table, and what you have after
>
> Nick
>

I had a look at the resulting xlsx file, the relationships seems ok, but the
excel file seems corrupted in some way (major problem: if you delete the sheet
0 also the sheet with the table is removed!). I opened the bug 50867 in
bugzilla to move there this discussion.

Many thanks! Donato

[AhmedHitec]

By examing the "xl\tables\*.xml" files and comparing between the orignal
file and generated one I can figure out where is the problem exactly.

setRef method on the table object should be in the form "A1:D12" for example

Most examples I saw in the net is setting it to somethinf like "$A$1:$D$12"
or "SheetName!$A$1:$D$12"

this confusion comes because this is the standard format we get from the
method formatAsStrin on a CellReference object.





--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org