You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Patrick GUERIN <pa...@ontomantics.com> on 2009/01/07 11:28:13 UTC
Can't read excel 2003 xls file
Hello,
I use the last beta version of apache poi to read data in excel spreadsheet.
But, unfortunaly, files created or modified with Excel 2003 can't be
used with apache POI.
It work fine with files created by OpenOffice or older version of Excel.
This exception thrown:
java.lang.IllegalArgumentException: Your InputStream was neither an OLE2
stream, nor an OOXML stream
at
org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:64)
.............
The source code is easy:
try
{
InputStream inp = new FileInputStream(file);
Workbook wb = WorkbookFactory.create(inp);
// .......
}
catch (Exception e)
{
e.printStackTrace();
}
My problem is all the Excel files to be read have been created with
Excel 2003 and I can not ask my customers to convert with openoffice...
Are you planning to support this new Excel 2003 xls format soon?
Or do you know a method/java class to convert Excel 2003 xls files to
the previous Excel file format working with POI?
Regards,
Patrick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can't read excel 2003 xls file
Posted by David Fisher <df...@jmlafferty.com>.
> i found that, others have the same probleme:
> http://www.nabble.com/XSSFWoorkbook.write()-problem-td21071450.html
This is not necessarily the same problem, that was about XSSFWorkbook
which is new and under beta.
You are using HSSFWorkbook and the trouble is that you are serving
binary information through struts2 and jsp2 and that is meant for
text. Excel files are binary.
>> curl -s http://localhost:8080/yourwebapps/yourtemplaterequest |
>> hexdump -n 8 -e
Please try this and we can see if we understand. Otherwise you really
have a server configuration problem with struts.
This is not the list for that, please try http://www.nabble.com/Struts---User-f206.html
Regards,
Dave
On Jan 30, 2009, at 6:22 PM, rahali wrote:
>
> i devlope a j2ee application with struts2 and jsp2 technology, i'm
> under xp
> service pack2, and my browsers are firfox, IE.
>
> *my IDE is eclipse 3.2.1.
>
> my java code is :
>
> String fs = System.getProperty("file.separator");
>
> POIFSFileSystem fs1 = new POIFSFileSystem(new FileInputStream(
> ServletActionContext.getServletContext().getRealPath("") + fs +
> "template" + fs + fileName));
> HSSFWorkbook wb1 = new HSSFWorkbook(fs1, true);
>
> pResponse.setHeader("Content-Disposition",
> "attachment;filename="+fileName);
> pResponse.setContentType("application/vnd.ms-excel");
> // Write the output
> OutputStream out = pResponse.getOutputStream();
> wb1.write(out);
> out.close();
>
> i want just to load a file from my browser.
>
> the input file hase colors, styles, borders .... (formattting)
>
> but the output file lost its formatting.
>
> accuelly, am using poi-3.0-alpha2-20060616.jar
>
> i found that, others have the same probleme:
> http://www.nabble.com/XSSFWoorkbook.write()-problem-td21071450.html
>
>
> to circumvent my problem : I associate xls extension to OpenOffice.
> then, when i load the file, its opened with OpenOffice.
>
>
>
> --------------------------------------------------
>
> David Fisher wrote:
>>
>> I wonder if the problem is not with POI.
>>
>>> i laod my excel file with httpresponse and, my file lose the format,
>>> i got a
>>> message where the Ms Excel informe me that this file is endomaged.
>>> but the
>>> file is opened with MsExcel 2000 and openOffice.
>>
>> Tell us about your workflow.
>>
>> (1) Are you making your HTTP request from within Excel and
>> OpenOffice,
>> or are you making the request in a Browser like Internet Explorer and
>> then saving the file?
>>
>> (2) What servlet container do you use? What version?
>>
>> (3) Are you using a servlet or a JSP?
>>
>> JSPs are meant for Text only and you get in trouble if you don't have
>> every "%>" immediately followed by a "<%". If you have this:
>>
>> .... %>
>> <% ....
>>
>> before your code then JSP will send a whitespace character in the
>> response and this could certainly cause trouble.
>>
>> You can see if you use Josh's command but with the url you use to hit
>> your server.
>>
>> curl -s http://localhost:8080/yourwebapps/yourtemplaterequest |
>> hexdump -n 8 -e
>>
>> (4) Does your url end with ".xls"?
>>
>> Older version of windows and IE seem to require this help. We used to
>> add an extra parameter to the request to fix the issue.
>>
>> HTH,
>> Dave
>>
>> On Jan 30, 2009, at 4:29 PM, Josh Micich wrote:
>>
>>> Hello Hicham,
>>>
>>> The two files you uploaded open OK in POI (i.e.
>>> WorkbookFactory.create()
>>> completes normally).
>>>
>>>> I do not know how to verify the spreadsheet formats
>>> You can also check the initial bytes of these files easily from a
>>> unix
>>> prompt with something like this:
>>>
>>> $ curl -s http://www.nabble.com/file/p21746193/input.xls | hexdump -
>>> n 8 -e
>>> '16/1 "%02X " "\n"'
>>> D0 CF 11 E0 A1 B1 1A E1
>>>
>>> $ curl -s http://www.nabble.com/file/p21746193/outout+2+.xls |
>>> hexdump -n 8
>>> -e '16/1 "%02X " "\n"'
>>> D0 CF 11 E0 A1 B1 1A E1
>>>
>>> These 8 bytes are consistent what is expected in the 'Microsoft
>>> Compound
>>> Document' format. This is expected since POI didn't report an
>>> error.
>>>
>>> I am having trouble understanding what the exact problem is. I
>>> tried
>>> opening the two files in Excel (2007) and that was OK too.
>>>
>>> regards,
>>> Josh
>>
>>
>> ---------------------------------------------------------------------
>> 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://www.nabble.com/Can%27t-read-excel-2003-xls-file-tp21328806p21758247.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: Can't read excel 2003 xls file
Posted by rahali <ra...@gmail.com>.
i got the same thing with poi-3.5-beta4-20081128.jar
best regards.
hicham
---------------------------------------------------------------
rahali wrote:
>
> i devlope a j2ee application with struts2 and jsp2 technology, i'm under
> xp service pack2, and my browsers are firfox, IE.
>
> *my IDE is eclipse 3.2.1.
>
> my java code is :
>
> String fs = System.getProperty("file.separator");
>
> POIFSFileSystem fs1 = new POIFSFileSystem(new FileInputStream(
> ServletActionContext.getServletContext().getRealPath("") + fs +
> "template" + fs + fileName));
> HSSFWorkbook wb1 = new HSSFWorkbook(fs1, true);
>
> pResponse.setHeader("Content-Disposition",
> "attachment;filename="+fileName);
> pResponse.setContentType("application/vnd.ms-excel");
> // Write the output
> OutputStream out = pResponse.getOutputStream();
> wb1.write(out);
> out.close();
>
> i want just to load a file from my browser.
>
> the input file hase colors, styles, borders .... (formattting)
>
> but the output file lost its formatting.
>
> accuelly, am using poi-3.0-alpha2-20060616.jar
>
> i found that, others have the same probleme:
> http://www.nabble.com/XSSFWoorkbook.write()-problem-td21071450.html
>
>
> to circumvent my problem : I associate xls extension to OpenOffice.
> then, when i load the file, its opened with OpenOffice.
>
>
>
> --------------------------------------------------
>
> David Fisher wrote:
>>
>> I wonder if the problem is not with POI.
>>
>>> i laod my excel file with httpresponse and, my file lose the format,
>>> i got a
>>> message where the Ms Excel informe me that this file is endomaged.
>>> but the
>>> file is opened with MsExcel 2000 and openOffice.
>>
>> Tell us about your workflow.
>>
>> (1) Are you making your HTTP request from within Excel and OpenOffice,
>> or are you making the request in a Browser like Internet Explorer and
>> then saving the file?
>>
>> (2) What servlet container do you use? What version?
>>
>> (3) Are you using a servlet or a JSP?
>>
>> JSPs are meant for Text only and you get in trouble if you don't have
>> every "%>" immediately followed by a "<%". If you have this:
>>
>> .... %>
>> <% ....
>>
>> before your code then JSP will send a whitespace character in the
>> response and this could certainly cause trouble.
>>
>> You can see if you use Josh's command but with the url you use to hit
>> your server.
>>
>> curl -s http://localhost:8080/yourwebapps/yourtemplaterequest |
>> hexdump -n 8 -e
>>
>> (4) Does your url end with ".xls"?
>>
>> Older version of windows and IE seem to require this help. We used to
>> add an extra parameter to the request to fix the issue.
>>
>> HTH,
>> Dave
>>
>> On Jan 30, 2009, at 4:29 PM, Josh Micich wrote:
>>
>>> Hello Hicham,
>>>
>>> The two files you uploaded open OK in POI (i.e.
>>> WorkbookFactory.create()
>>> completes normally).
>>>
>>>> I do not know how to verify the spreadsheet formats
>>> You can also check the initial bytes of these files easily from a unix
>>> prompt with something like this:
>>>
>>> $ curl -s http://www.nabble.com/file/p21746193/input.xls | hexdump -
>>> n 8 -e
>>> '16/1 "%02X " "\n"'
>>> D0 CF 11 E0 A1 B1 1A E1
>>>
>>> $ curl -s http://www.nabble.com/file/p21746193/outout+2+.xls |
>>> hexdump -n 8
>>> -e '16/1 "%02X " "\n"'
>>> D0 CF 11 E0 A1 B1 1A E1
>>>
>>> These 8 bytes are consistent what is expected in the 'Microsoft
>>> Compound
>>> Document' format. This is expected since POI didn't report an error.
>>>
>>> I am having trouble understanding what the exact problem is. I tried
>>> opening the two files in Excel (2007) and that was OK too.
>>>
>>> regards,
>>> Josh
>>
>>
>> ---------------------------------------------------------------------
>> 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://www.nabble.com/Can%27t-read-excel-2003-xls-file-tp21328806p21758404.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: Can't read excel 2003 xls file
Posted by rahali <ra...@gmail.com>.
i devlope a j2ee application with struts2 and jsp2 technology, i'm under xp
service pack2, and my browsers are firfox, IE.
*my IDE is eclipse 3.2.1.
my java code is :
String fs = System.getProperty("file.separator");
POIFSFileSystem fs1 = new POIFSFileSystem(new FileInputStream(
ServletActionContext.getServletContext().getRealPath("") + fs +
"template" + fs + fileName));
HSSFWorkbook wb1 = new HSSFWorkbook(fs1, true);
pResponse.setHeader("Content-Disposition",
"attachment;filename="+fileName);
pResponse.setContentType("application/vnd.ms-excel");
// Write the output
OutputStream out = pResponse.getOutputStream();
wb1.write(out);
out.close();
i want just to load a file from my browser.
the input file hase colors, styles, borders .... (formattting)
but the output file lost its formatting.
accuelly, am using poi-3.0-alpha2-20060616.jar
i found that, others have the same probleme:
http://www.nabble.com/XSSFWoorkbook.write()-problem-td21071450.html
to circumvent my problem : I associate xls extension to OpenOffice.
then, when i load the file, its opened with OpenOffice.
--------------------------------------------------
David Fisher wrote:
>
> I wonder if the problem is not with POI.
>
>> i laod my excel file with httpresponse and, my file lose the format,
>> i got a
>> message where the Ms Excel informe me that this file is endomaged.
>> but the
>> file is opened with MsExcel 2000 and openOffice.
>
> Tell us about your workflow.
>
> (1) Are you making your HTTP request from within Excel and OpenOffice,
> or are you making the request in a Browser like Internet Explorer and
> then saving the file?
>
> (2) What servlet container do you use? What version?
>
> (3) Are you using a servlet or a JSP?
>
> JSPs are meant for Text only and you get in trouble if you don't have
> every "%>" immediately followed by a "<%". If you have this:
>
> .... %>
> <% ....
>
> before your code then JSP will send a whitespace character in the
> response and this could certainly cause trouble.
>
> You can see if you use Josh's command but with the url you use to hit
> your server.
>
> curl -s http://localhost:8080/yourwebapps/yourtemplaterequest |
> hexdump -n 8 -e
>
> (4) Does your url end with ".xls"?
>
> Older version of windows and IE seem to require this help. We used to
> add an extra parameter to the request to fix the issue.
>
> HTH,
> Dave
>
> On Jan 30, 2009, at 4:29 PM, Josh Micich wrote:
>
>> Hello Hicham,
>>
>> The two files you uploaded open OK in POI (i.e.
>> WorkbookFactory.create()
>> completes normally).
>>
>>> I do not know how to verify the spreadsheet formats
>> You can also check the initial bytes of these files easily from a unix
>> prompt with something like this:
>>
>> $ curl -s http://www.nabble.com/file/p21746193/input.xls | hexdump -
>> n 8 -e
>> '16/1 "%02X " "\n"'
>> D0 CF 11 E0 A1 B1 1A E1
>>
>> $ curl -s http://www.nabble.com/file/p21746193/outout+2+.xls |
>> hexdump -n 8
>> -e '16/1 "%02X " "\n"'
>> D0 CF 11 E0 A1 B1 1A E1
>>
>> These 8 bytes are consistent what is expected in the 'Microsoft
>> Compound
>> Document' format. This is expected since POI didn't report an error.
>>
>> I am having trouble understanding what the exact problem is. I tried
>> opening the two files in Excel (2007) and that was OK too.
>>
>> regards,
>> Josh
>
>
> ---------------------------------------------------------------------
> 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://www.nabble.com/Can%27t-read-excel-2003-xls-file-tp21328806p21758247.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: Can't read excel 2003 xls file
Posted by David Fisher <df...@jmlafferty.com>.
I wonder if the problem is not with POI.
> i laod my excel file with httpresponse and, my file lose the format,
> i got a
> message where the Ms Excel informe me that this file is endomaged.
> but the
> file is opened with MsExcel 2000 and openOffice.
Tell us about your workflow.
(1) Are you making your HTTP request from within Excel and OpenOffice,
or are you making the request in a Browser like Internet Explorer and
then saving the file?
(2) What servlet container do you use? What version?
(3) Are you using a servlet or a JSP?
JSPs are meant for Text only and you get in trouble if you don't have
every "%>" immediately followed by a "<%". If you have this:
.... %>
<% ....
before your code then JSP will send a whitespace character in the
response and this could certainly cause trouble.
You can see if you use Josh's command but with the url you use to hit
your server.
curl -s http://localhost:8080/yourwebapps/yourtemplaterequest |
hexdump -n 8 -e
(4) Does your url end with ".xls"?
Older version of windows and IE seem to require this help. We used to
add an extra parameter to the request to fix the issue.
HTH,
Dave
On Jan 30, 2009, at 4:29 PM, Josh Micich wrote:
> Hello Hicham,
>
> The two files you uploaded open OK in POI (i.e.
> WorkbookFactory.create()
> completes normally).
>
>> I do not know how to verify the spreadsheet formats
> You can also check the initial bytes of these files easily from a unix
> prompt with something like this:
>
> $ curl -s http://www.nabble.com/file/p21746193/input.xls | hexdump -
> n 8 -e
> '16/1 "%02X " "\n"'
> D0 CF 11 E0 A1 B1 1A E1
>
> $ curl -s http://www.nabble.com/file/p21746193/outout+2+.xls |
> hexdump -n 8
> -e '16/1 "%02X " "\n"'
> D0 CF 11 E0 A1 B1 1A E1
>
> These 8 bytes are consistent what is expected in the 'Microsoft
> Compound
> Document' format. This is expected since POI didn't report an error.
>
> I am having trouble understanding what the exact problem is. I tried
> opening the two files in Excel (2007) and that was OK too.
>
> regards,
> Josh
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Can't read excel 2003 xls file
Posted by Josh Micich <jo...@gmail.com>.
Hello Hicham,
The two files you uploaded open OK in POI (i.e. WorkbookFactory.create()
completes normally).
> I do not know how to verify the spreadsheet formats
You can also check the initial bytes of these files easily from a unix
prompt with something like this:
$ curl -s http://www.nabble.com/file/p21746193/input.xls | hexdump -n 8 -e
'16/1 "%02X " "\n"'
D0 CF 11 E0 A1 B1 1A E1
$ curl -s http://www.nabble.com/file/p21746193/outout+2+.xls | hexdump -n 8
-e '16/1 "%02X " "\n"'
D0 CF 11 E0 A1 B1 1A E1
These 8 bytes are consistent what is expected in the 'Microsoft Compound
Document' format. This is expected since POI didn't report an error.
I am having trouble understanding what the exact problem is. I tried
opening the two files in Excel (2007) and that was OK too.
regards,
Josh
Re: Can't read excel 2003 xls file
Posted by rahali <ra...@gmail.com>.
hello Josh,
you find the attached document that I try to load (input.xls) create on
MSExcel 2003, and the result file (outout 2.xls) and read on MSExcel 2003.
I do not know how to verify the spreadsheet formats
http://www.nabble.com/file/p21746193/input.xls input.xls
http://www.nabble.com/file/p21746193/outout%2B2%2B.xls outout+2+.xls
Thank you for your reply.
-----------------------------------------------------
Josh Micich wrote:
>
> Hello Hicham,
>
> The exception that Patrick received was:
> java.lang.IllegalArgumentException: Your InputStream was neither an OLE2
> stream, nor an OOXML stream
> at
> org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:64)
>
> ...which signifies that the stream is not 'OpenDocument format' (starting
> with 50 4B 03 04) nor a 'Microsoft Compound Document'/'OLE' file (starting
> with D0 CF 11 E0 A1 B1 1A E1). As Nick mentioned above, having an
> extension
> ".xls" and being readable by Excel does not guarantee that a file is
> either
> of these two formats. For example, the file may be plain text, html, csv,
> or even a prior BIFF (<8) version.
>
> POI reads two spreadsheet formats:
> BIFF8 - within a 'Microsoft Compound Document'
> OOXML - within an 'OpenDocument'
>
> If you are sure that the offending document really *is* of one of these
> two
> formats, please upload it to help troubleshoot this issue further.
>
> regards,
> Josh
>
>
--
View this message in context: http://www.nabble.com/Can%27t-read-excel-2003-xls-file-tp21328806p21746193.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: Can't read excel 2003 xls file
Posted by Josh Micich <jo...@gmail.com>.
Hello Hicham,
The exception that Patrick received was:
java.lang.IllegalArgumentException: Your InputStream was neither an OLE2
stream, nor an OOXML stream
at
org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:64)
...which signifies that the stream is not 'OpenDocument format' (starting
with 50 4B 03 04) nor a 'Microsoft Compound Document'/'OLE' file (starting
with D0 CF 11 E0 A1 B1 1A E1). As Nick mentioned above, having an extension
".xls" and being readable by Excel does not guarantee that a file is either
of these two formats. For example, the file may be plain text, html, csv,
or even a prior BIFF (<8) version.
POI reads two spreadsheet formats:
BIFF8 - within a 'Microsoft Compound Document'
OOXML - within an 'OpenDocument'
If you are sure that the offending document really *is* of one of these two
formats, please upload it to help troubleshoot this issue further.
regards,
Josh
Re: Can't read excel 2003 xls file
Posted by rahali <ra...@gmail.com>.
i have like this problem with poi and excel 2003 :
i laod my excel file with httpresponse and, my file lose the format, i got a
message where the Ms Excel informe me that this file is endomaged. but the
file is opened with MsExcel 2000 and openOffice.
there is my code :
String fs = System.getProperty("file.separator");
POIFSFileSystem fs1 = new POIFSFileSystem(new FileInputStream(
ServletActionContext.getServletContext().getRealPath("") + fs + "template"
+ fs + "G-Chiffrage-M_Graphtalk AIA_NumDemande_v1.1.xls"));
HSSFWorkbook wb1 = new HSSFWorkbook(fs1, true);
HttpServletResponse pResponse = ServletActionContext.getResponse();
pResponse.setContentType("application/vnd.ms-excel");
pResponse.setHeader("Content-Disposition", "attachment;
filename=outout.xls");
OutputStream lOutput = pResponse.getOutputStream();
wb1.write(lOutput);
lOutput.close();
any idea.
thanks a lot.
hicham
Nick Burch wrote:
>
> On Wed, 7 Jan 2009, Patrick GUERIN wrote:
>> But, unfortunaly, files created or modified with Excel 2003 can't be used
>> with apache POI.
>>
>> It work fine with files created by OpenOffice or older version of Excel.
>>
>> This exception thrown:
>> java.lang.IllegalArgumentException: Your InputStream was neither an OLE2
>> stream, nor an OOXML stream
>
> I'd check that your file really is an excel .xls file. You get that
> exception if you try to feed POI something that isn't really a .xls or
> .xlsx, for example a csv file, or html (both of which excel will happily
> open when named .xls)
>
> Nick
>
> ---------------------------------------------------------------------
> 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://www.nabble.com/Can%27t-read-excel-2003-xls-file-tp21328806p21739287.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: Can't read excel 2003 xls file
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 7 Jan 2009, Patrick GUERIN wrote:
> But, unfortunaly, files created or modified with Excel 2003 can't be used
> with apache POI.
>
> It work fine with files created by OpenOffice or older version of Excel.
>
> This exception thrown:
> java.lang.IllegalArgumentException: Your InputStream was neither an OLE2
> stream, nor an OOXML stream
I'd check that your file really is an excel .xls file. You get that
exception if you try to feed POI something that isn't really a .xls or
.xlsx, for example a csv file, or html (both of which excel will happily
open when named .xls)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org