Reading Excel 5.0/7.0 files

[Praveen Gattu]

I have a Java class that reads a MS Excel file generated by a PeopleSoft
reporting tool and loads the data into a Oracle database. The PeopleSoft
reporting tool creates the file in Excel 5.0/7.0 format I guess, cuz when I
try to read the file, I get the following error:

The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only
supports BIFF8 format (from Excel versions 97/2000/XP/2003)

Is there a way POI can read this file? If not, how can I convert this BIFF5
format file to BIFF8 format file?

[Daniel Noll]

Post by Praveen Gattu
Is there a way POI can read this file? If not, how can I convert this BIFF5
format file to BIFF8 format file?

Open it and save it in Excel?

Or add support to HSSF so it can handle these older formats. A lot of
people would thank you. :-)

Daniel

[Praveen Gattu]

Open and save in Excel is not a viable option since the process is automated
and I don't want to introduce a manual step in between.
If HSSF cannot handle the older formats, is there another API to convert
BIFF5 files to BIFF8?

-- Praveen

Post by Daniel Noll

Post by Praveen Gattu
Is there a way POI can read this file? If not, how can I convert this BIFF5
format file to BIFF8 format file?

Open it and save it in Excel?
Or add support to HSSF so it can handle these older formats. A lot of
people would thank you. :-)
Daniel
--
Daniel Noll Forensic and eDiscovery Software
Senior Developer The world's most advanced
Nuix email data analysis
http://nuix.com/ and eDiscovery software
---------------------------------------------------------------------

[Anthony Andrews]

Have you looked into using ODBC to strip the data from the Excel files for you? Have a look at this link

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/opt/tlsodb13.htm

As a technique, it does not seem anywhere near as flexible as HSSF but it may help you deal with this format because I do not believe there is any API that will read it nor convert it for you.

--- On Wed, 10/22/08, Praveen Gattu <***@gmail.com> wrote:
From: Praveen Gattu <***@gmail.com>
Subject: Re: Reading Excel 5.0/7.0 files
To: "POI Users List" <***@poi.apache.org>
Date: Wednesday, October 22, 2008, 7:47 PM

Open and save in Excel is not a viable option since the process is automated
and I don't want to introduce a manual step in between.
If HSSF cannot handle the older formats, is there another API to convert
BIFF5 files to BIFF8?

-- Praveen

Post by Daniel Noll

Post by Praveen Gattu
Is there a way POI can read this file? If not, how can I convert this BIFF5
format file to BIFF8 format file?

Open it and save it in Excel?
Or add support to HSSF so it can handle these older formats. A lot of
people would thank you. :-)
Daniel
--
Daniel Noll Forensic and eDiscovery Software
Senior Developer The world's most

advanced

Post by Daniel Noll
Nuix email data analysis
http://nuix.com/ and eDiscovery software
---------------------------------------------------------------------

[Christian Gosch]

Try to use "jxl" (JExcelAPI) by Andy Khan.

It is also open source etc. and has some drawbacks, but the development
is driven somewhat more "by practical demand", at least as it was during
the last years.

http://jexcelapi.sourceforge.net/

As far as I know the project was not driven by getting an API to handle
MS OLE document formats generally in Java, but just handling Excel files
for read and write access. It cannot build any internal serializable
representation of the data -- to get something storable you must read a
given Excel file by the API, or you must stream out a created Excel file
to permanent storage.

I do not know for sure if it still handles BIFF5, but of course it did.

On the other hand, opening the given file for read access using a
suitable JDBC/ODBC bridge driver may be a good idea to abstract from the
actual file type in your scenario. (as suggested by Anthony Andrews
meanwhile)

hth,
Christian Gosch

-----Original Message-----
Sent: Thursday, October 23, 2008 12:58 AM
Subject: Reading Excel 5.0/7.0 files
I have a Java class that reads a MS Excel file generated by a

PeopleSoft

reporting tool and loads the data into a Oracle database. The

PeopleSoft

reporting tool creates the file in Excel 5.0/7.0 format I guess, cuz

when

I
The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only
supports BIFF8 format (from Excel versions 97/2000/XP/2003)
Is there a way POI can read this file? If not, how can I convert this BIFF5
format file to BIFF8 format file?
--
Thanks,
Praveen
!DSPAM:48ffb00b22931434354569!

[Anthony Andrews]

Must admit that I forgot about JExcel completely. Have had a quick look on Andy Khan's wb page (http://www.andykhan.com/jexcelapi/index.html) and found this;


Excel Versions



JExcelApi will read workbooks created in Excel 95, 97 and 2000, and
will generate workbooks that can be read by Excel 97 and later.

Do not know if that helps.

--- On Thu, 10/23/08, Christian Gosch <***@inovex.de> wrote:
From: Christian Gosch <***@inovex.de>
Subject: RE: Reading Excel 5.0/7.0 files
To: "user" <***@poi.apache.org>
Date: Thursday, October 23, 2008, 12:29 AM

Try to use "jxl" (JExcelAPI) by Andy Khan.

It is also open source etc. and has some drawbacks, but the development
is driven somewhat more "by practical demand", at least as it was
during
the last years.

http://jexcelapi.sourceforge.net/

As far as I know the project was not driven by getting an API to handle
MS OLE document formats generally in Java, but just handling Excel files
for read and write access. It cannot build any internal serializable
representation of the data -- to get something storable you must read a
given Excel file by the API, or you must stream out a created Excel file
to permanent storage.

I do not know for sure if it still handles BIFF5, but of course it did.

On the other hand, opening the given file for read access using a
suitable JDBC/ODBC bridge driver may be a good idea to abstract from the
actual file type in your scenario. (as suggested by Anthony Andrews
meanwhile)

hth,
Christian Gosch

-----Original Message-----
Sent: Thursday, October 23, 2008 12:58 AM
Subject: Reading Excel 5.0/7.0 files
I have a Java class that reads a MS Excel file generated by a

PeopleSoft

reporting tool and loads the data into a Oracle database. The

PeopleSoft

reporting tool creates the file in Excel 5.0/7.0 format I guess, cuz

when

I
The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only
supports BIFF8 format (from Excel versions 97/2000/XP/2003)
Is there a way POI can read this file? If not, how can I convert this BIFF5
format file to BIFF8 format file?
--
Thanks,
Praveen
!DSPAM:48ffb00b22931434354569!

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

[Praveen]

Post by Christian Gosch
Try to use "jxl" (JExcelAPI) by Andy Khan.
It is also open source etc. and has some drawbacks, but the development
is driven somewhat more "by practical demand", at least as it was during
the last years.
http://jexcelapi.sourceforge.net/

Thanks for the tip about jExcel. It works with BIFF5 files. I am switching to
jExcel for this.

[bestfrieend]

when i try to run jexcel api on excel 95 files it throws the following
exception

java.lang.StringIndexOutOfBoundsException: String index out of range: 120

can anyone please tell me how to read older excel files?

Thanks,

Wasif



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Reading-Excel-5-0-7-0-files-tp2302502p5721022.html
Sent from the POI - User mailing list archive at Nabble.com.

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

[Nick Burch]

Post by bestfrieend
can anyone please tell me how to read older excel files?

Apache POI doesn't have any high-level APIs for working with the older
Excel file formats.

If you just want the textual contents, then there's
org.apache.poi.hssf.extractor.OldExcelExtractor which will pull out the
text and numbers from the file

If you need the values in specific cells, then you'll need to take an
approach a bit like OldExcelExtractor, process the file at the record
level, and check for the co-ordinates on OldStringRecord, NumberRecord,
OldFormulaRecord and friends

Nick

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