You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Sony Antony <so...@yahoo.com> on 2010/04/25 15:20:16 UTC
Is it possible to read a table from Excel
Hello :
We have an excel workbook in the new XSSF format, with multiple embedded tables.
When I do a Workbook.getNumberOfNames(), I get zero.
Is it possible to read embedded tables using POI.
Any help will be greatly appreciated.
Thanks
--sony
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Is it possible to read a table from Excel
Posted by Sony Antony <so...@yahoo.com>.
This is useful.
Yes when I renamed the .xlsx file to .zip, I could see the contents.
And sure enough corresponding to each table inside the excel file, I saw a tableN.xml file ( where N is the number of teh table like 1,2,3... )
Eg : D:\tt\Book1.zip\xl\tables directory contains two file named table1.xml and table2.xml for teh excel file that had two embedded tables.
It looked like this
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="2" name="Table2" displayName="Table2" ref="B11:E19" totalsRowShown="0">
<autoFilter ref="B11:E19" />
- <tableColumns count="4">
<tableColumn id="1" name="Column1" />
<tableColumn id="2" name="Column2" />
<tableColumn id="3" name="Column3" />
<tableColumn id="4" name="Column4" />
</tableColumns>
<tableStyleInfo name="TableStyleDark1" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" />
</table>
So yeah, I can use this info to deduce teh table boundaries for each embedded tables.
Thanks a lot.
--sony
--- On Mon, 4/26/10, Nick Burch <ni...@alfresco.com> wrote:
> From: Nick Burch <ni...@alfresco.com>
> Subject: Re: Is it possible to read a table from Excel
> To: "POI Users List" <us...@poi.apache.org>
> Date: Monday, April 26, 2010, 10:22 AM
> On Mon, 26 Apr 2010, Sony Antony
> wrote:
> > these are not embedded files.
> > But tables created by :
> > 1. select a rectangle region of cells
> > 2. From Home -> Format as Table > Medium
>
> I'd suggest you create a very simple file without tables,
> and save it. Then, open a copy, and add a very simple table,
> and save it. Now, unzip both the files (.xlsx files are
> actually a zip file of XML files).
>
> Finally, see what new files were added for the table, and
> see how the normal xml files have been altered. The output
> of this process should inform how much work reading the
> tables will be. It'll also provide two nice and simple files
> for unit testing any future support :)
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Is it possible to read a table from Excel
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 26 Apr 2010, Sony Antony wrote:
> these are not embedded files.
> But tables created by :
> 1. select a rectangle region of cells
> 2. From Home -> Format as Table > Medium
I'd suggest you create a very simple file without tables, and save it.
Then, open a copy, and add a very simple table, and save it. Now, unzip
both the files (.xlsx files are actually a zip file of XML files).
Finally, see what new files were added for the table, and see how the
normal xml files have been altered. The output of this process should
inform how much work reading the tables will be. It'll also provide two
nice and simple files for unit testing any future support :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Is it possible to read a table from Excel
Posted by Sony Antony <so...@yahoo.com>.
Thanks for teh response :
these are not embedded files.
But tables created by :
1. select a rectangle region of cells
2. From Home -> Format as Table > Medium
So now an area of my sheet has a table. This Table has its own Header Row and contents
I can read teh whole sheet without any problem.
But I would like to read the tables within the sheet. ( I would like to recognize that the sheet has multiple tables contained within and be able to read their headers )
Im starting to suspect that POI cannot do this.
--sony
--- On Mon, 4/26/10, MSB <ma...@tiscali.co.uk> wrote:
> From: MSB <ma...@tiscali.co.uk>
> Subject: Re: Is it possible to read a table from Excel
> To: user@poi.apache.org
> Date: Monday, April 26, 2010, 1:35 AM
>
> Can I ask what may seem a stupid question please? What do
> you mean by
> 'retrieve them as individual tables'? Do you want to read
> the contents of
> the embedded files using POI or simply extract them from
> the 'host'
> document?
>
> I am going to assume that you can do the former using POI
> and the latter
> would actually be quite straightforward; once you have the
> embedded object
> as an XSSFWorkbook, simply pass an OutputStream to it's
> write() method.
>
> Yours
>
> Mark B
>
>
> Sony Antony-3 wrote:
> >
> > Thank you very much for the reply Mark :
> > Actually I had already tried getAllEmbedds().
> > ( Nothing gets printed below ) L
> > FileInputStream fis = new FileInputStream(file);
> > XSSFWorkbook wb = new XSSFWorkbook(fis);
> > List<PackagePart> emb = wb.getAllEmbedds() ;
> > for( PackagePart pp : emb ){
> >
> System.out.println("ZZZ"+pp.getContentType());
> > }
> >
> >
> > Does it make any difference how the XSSFWorkbook
> object is instantiated. I
> > mean, am I using the wrong constructor. ?
> >
> > ( The way it is, I can read individual cell's
> contents. I just cant
> > retrieve them as individual tables. )
> >
> > --sony
> >
> >
> >
> >
> > --- On Sun, 4/25/10, MSB <ma...@tiscali.co.uk>
> wrote:
> >
> >> From: MSB <ma...@tiscali.co.uk>
> >> Subject: Re: Is it possible to read a table from
> Excel
> >> To: user@poi.apache.org
> >> Date: Sunday, April 25, 2010, 9:51 AM
> >>
> >> Take a look at this;
> >> http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#getAllEmbedds%28%29
> >> because, as the tables are embedded, this is the
> most
> >> likely way to retrieve
> >> a reference to them.
> >>
> >> That method returns a List containing zero, one or
> more
> >> elements of type
> >> org.apache.poi.openxml4j.opc.PackagePart which you
> can then
> >> process using
> >> POI again - that is assuming they are all Excel
> workbooks.
> >> If you look at
> >> the constructor for the XSSFWorkbook class, you
> will see
> >> that one takes a
> >> reference to an OPCPackage object and it is
> possible to
> >> retrieve just such
> >> an object from the PackagePart by calling it's -
> that is
> >> the PackagePart(s)
> >> - getPackage() method.
> >>
> >> Have never tried it myself but I think that will
> work.
> >>
> >> Yours
> >>
> >> Mark B
> >>
> >>
> >>
> >> Sony Antony-3 wrote:
> >> >
> >> > Hello :
> >> > We have an excel workbook in the new XSSF
> format, with
> >> multiple embedded
> >> > tables.
> >> > When I do a Workbook.getNumberOfNames(), I
> get zero.
> >> >
> >> > Is it possible to read embedded tables using
> POI.
> >> >
> >> > Any help will be greatly appreciated.
> >> >
> >> > Thanks
> >> > --sony
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> ---------------------------------------------------------------------
> >> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> >> > For additional commands, e-mail: user-help@poi.apache.org
> >> >
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >> http://old.nabble.com/Is-it-possible-to-read-a-table-from-Excel-tp28356078p28356524.html
> >> Sent from the POI - User mailing list archive at
> >> Nabble.com.
> >>
> >>
> >>
> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
> >
> >
>
> --
> View this message in context: http://old.nabble.com/Is-it-possible-to-read-a-table-from-Excel-tp28356078p28361330.html
> Sent from the POI - User mailing list archive at
> Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Is it possible to read a table from Excel
Posted by MSB <ma...@tiscali.co.uk>.
Can I ask what may seem a stupid question please? What do you mean by
'retrieve them as individual tables'? Do you want to read the contents of
the embedded files using POI or simply extract them from the 'host'
document?
I am going to assume that you can do the former using POI and the latter
would actually be quite straightforward; once you have the embedded object
as an XSSFWorkbook, simply pass an OutputStream to it's write() method.
Yours
Mark B
Sony Antony-3 wrote:
>
> Thank you very much for the reply Mark :
> Actually I had already tried getAllEmbedds().
> ( Nothing gets printed below ) L
> FileInputStream fis = new FileInputStream(file);
> XSSFWorkbook wb = new XSSFWorkbook(fis);
> List<PackagePart> emb = wb.getAllEmbedds() ;
> for( PackagePart pp : emb ){
> System.out.println("ZZZ"+pp.getContentType());
> }
>
>
> Does it make any difference how the XSSFWorkbook object is instantiated. I
> mean, am I using the wrong constructor. ?
>
> ( The way it is, I can read individual cell's contents. I just cant
> retrieve them as individual tables. )
>
> --sony
>
>
>
>
> --- On Sun, 4/25/10, MSB <ma...@tiscali.co.uk> wrote:
>
>> From: MSB <ma...@tiscali.co.uk>
>> Subject: Re: Is it possible to read a table from Excel
>> To: user@poi.apache.org
>> Date: Sunday, April 25, 2010, 9:51 AM
>>
>> Take a look at this;
>> http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#getAllEmbedds%28%29
>> because, as the tables are embedded, this is the most
>> likely way to retrieve
>> a reference to them.
>>
>> That method returns a List containing zero, one or more
>> elements of type
>> org.apache.poi.openxml4j.opc.PackagePart which you can then
>> process using
>> POI again - that is assuming they are all Excel workbooks.
>> If you look at
>> the constructor for the XSSFWorkbook class, you will see
>> that one takes a
>> reference to an OPCPackage object and it is possible to
>> retrieve just such
>> an object from the PackagePart by calling it's - that is
>> the PackagePart(s)
>> - getPackage() method.
>>
>> Have never tried it myself but I think that will work.
>>
>> Yours
>>
>> Mark B
>>
>>
>>
>> Sony Antony-3 wrote:
>> >
>> > Hello :
>> > We have an excel workbook in the new XSSF format, with
>> multiple embedded
>> > tables.
>> > When I do a Workbook.getNumberOfNames(), I get zero.
>> >
>> > Is it possible to read embedded tables using POI.
>> >
>> > Any help will be greatly appreciated.
>> >
>> > Thanks
>> > --sony
>> >
>> >
>> >
>> >
>> >
>> ---------------------------------------------------------------------
>> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> > For additional commands, e-mail: user-help@poi.apache.org
>> >
>> >
>> >
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Is-it-possible-to-read-a-table-from-Excel-tp28356078p28356524.html
>> Sent from the POI - User mailing list archive at
>> Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Is-it-possible-to-read-a-table-from-Excel-tp28356078p28361330.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Is it possible to read a table from Excel
Posted by Sony Antony <so...@yahoo.com>.
Thank you very much for the reply Mark :
Actually I had already tried getAllEmbedds().
( Nothing gets printed below ) L
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
List<PackagePart> emb = wb.getAllEmbedds() ;
for( PackagePart pp : emb ){
System.out.println("ZZZ"+pp.getContentType());
}
Does it make any difference how the XSSFWorkbook object is instantiated. I mean, am I using the wrong constructor. ?
( The way it is, I can read individual cell's contents. I just cant retrieve them as individual tables. )
--sony
--- On Sun, 4/25/10, MSB <ma...@tiscali.co.uk> wrote:
> From: MSB <ma...@tiscali.co.uk>
> Subject: Re: Is it possible to read a table from Excel
> To: user@poi.apache.org
> Date: Sunday, April 25, 2010, 9:51 AM
>
> Take a look at this;
> http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#getAllEmbedds%28%29
> because, as the tables are embedded, this is the most
> likely way to retrieve
> a reference to them.
>
> That method returns a List containing zero, one or more
> elements of type
> org.apache.poi.openxml4j.opc.PackagePart which you can then
> process using
> POI again - that is assuming they are all Excel workbooks.
> If you look at
> the constructor for the XSSFWorkbook class, you will see
> that one takes a
> reference to an OPCPackage object and it is possible to
> retrieve just such
> an object from the PackagePart by calling it's - that is
> the PackagePart(s)
> - getPackage() method.
>
> Have never tried it myself but I think that will work.
>
> Yours
>
> Mark B
>
>
>
> Sony Antony-3 wrote:
> >
> > Hello :
> > We have an excel workbook in the new XSSF format, with
> multiple embedded
> > tables.
> > When I do a Workbook.getNumberOfNames(), I get zero.
> >
> > Is it possible to read embedded tables using POI.
> >
> > Any help will be greatly appreciated.
> >
> > Thanks
> > --sony
> >
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
> >
> >
>
> --
> View this message in context: http://old.nabble.com/Is-it-possible-to-read-a-table-from-Excel-tp28356078p28356524.html
> Sent from the POI - User mailing list archive at
> Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Is it possible to read a table from Excel
Posted by MSB <ma...@tiscali.co.uk>.
Take a look at this;
http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#getAllEmbedds%28%29
because, as the tables are embedded, this is the most likely way to retrieve
a reference to them.
That method returns a List containing zero, one or more elements of type
org.apache.poi.openxml4j.opc.PackagePart which you can then process using
POI again - that is assuming they are all Excel workbooks. If you look at
the constructor for the XSSFWorkbook class, you will see that one takes a
reference to an OPCPackage object and it is possible to retrieve just such
an object from the PackagePart by calling it's - that is the PackagePart(s)
- getPackage() method.
Have never tried it myself but I think that will work.
Yours
Mark B
Sony Antony-3 wrote:
>
> Hello :
> We have an excel workbook in the new XSSF format, with multiple embedded
> tables.
> When I do a Workbook.getNumberOfNames(), I get zero.
>
> Is it possible to read embedded tables using POI.
>
> Any help will be greatly appreciated.
>
> Thanks
> --sony
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Is-it-possible-to-read-a-table-from-Excel-tp28356078p28356524.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org