Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

ssikuro
Hi,
I created an .xlsx file using Apache POI XML (using XSSFWorkbook class).
The .xlsx file created by a unit test could be opened from Excel but when I
tried to open the .xlsx file from a real run, Excel won't open it with an
error message like this in its pop up:
----
Excel cannot open the file 'myfile.xlsx' because the file format or file
extension is not valid. Verify that the file has not been corrupted and
that the file extension matches the format of the file.
----

Is there a way to make Excel printout more detailed error message?
Are there any other debugging tips?

I unzipped the generated .xlsx file and the one that can be opened,
and I found these files are missing from the one that won't open.
Do these need to exist?

xl/theme/theme1.xml
xl/connections.xml
xl/queryTable/queryTable1.xml

--
Kuro
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

kiwiwings
Hi,

I'd do the same in the unit test as in the real run.
Unzip and compare both files.
What's the difference?

What is a real run like? is this a web application?
Do you have duplicated POI jars in the classpath?

Andi

On 03.02.20 20:55, Kuro Kurosaka wrote:

> Hi,
> I created an .xlsx file using Apache POI XML (using XSSFWorkbook class).
> The .xlsx file created by a unit test could be opened from Excel but when I
> tried to open the .xlsx file from a real run, Excel won't open it with an
> error message like this in its pop up:
> ----
> Excel cannot open the file 'myfile.xlsx' because the file format or file
> extension is not valid. Verify that the file has not been corrupted and
> that the file extension matches the format of the file.
> ----
>
> Is there a way to make Excel printout more detailed error message?
> Are there any other debugging tips?
>
> I unzipped the generated .xlsx file and the one that can be opened,
> and I found these files are missing from the one that won't open.
> Do these need to exist?
>
> xl/theme/theme1.xml
> xl/connections.xml
> xl/queryTable/queryTable1.xml
>


signature.asc (499 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

ssikuro
Andi,
Thank you for the quick reply.
The .xlsx file from the test run has the same file structure as the .xlsx
from the real run that doesn't open.
The JAR I upload to an application server is shaded and includes the POI
library that is relocated to its version
specific packages to avoid collision. So it's the same version of POI as
the test run.

If there is no better way, I could somehow record all POI calls in the real
run, and ptu it to the unit test,
but I'd rather want to avoid this route as it is very time consuming. I am
hoping there's a way for
Excel to tell me what errors it is seeing.


On Mon, Feb 3, 2020 at 12:03 PM Andreas Beeker <[hidden email]> wrote:

> Hi,
>
> I'd do the same in the unit test as in the real run.
> Unzip and compare both files.
> What's the difference?
>
> What is a real run like? is this a web application?
> Do you have duplicated POI jars in the classpath?
>
> Andi
>
> On 03.02.20 20:55, Kuro Kurosaka wrote:
> > Hi,
> > I created an .xlsx file using Apache POI XML (using XSSFWorkbook class).
> > The .xlsx file created by a unit test could be opened from Excel but
> when I
> > tried to open the .xlsx file from a real run, Excel won't open it with an
> > error message like this in its pop up:
> > ----
> > Excel cannot open the file 'myfile.xlsx' because the file format or file
> > extension is not valid. Verify that the file has not been corrupted and
> > that the file extension matches the format of the file.
> > ----
> >
> > Is there a way to make Excel printout more detailed error message?
> > Are there any other debugging tips?
> >
> > I unzipped the generated .xlsx file and the one that can be opened,
> > and I found these files are missing from the one that won't open.
> > Do these need to exist?
> >
> > xl/theme/theme1.xml
> > xl/connections.xml
> > xl/queryTable/queryTable1.xml
> >
>
>
>

--
T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

kiwiwings
We have two entries in the FAQ [1] about file validation, which I haven't used myself yet ... and probably are futile in your case.
You can try to validate against the ECMA 376 schemes.

If I have similar problems I try to go step-wise from the simple case to the complex ...
and yes, it's sometimes quite time consuming.

Can you try your shaded jar in the unit test? ... my guess is, it might not include all XmlBeans files (*.xsb)

[1] https://poi.apache.org/help/faq.html

On 03.02.20 21:25, Kuro Kurosaka wrote:

> The .xlsx file from the test run has the same file structure as the .xlsx
> from the real run that doesn't open.
> The JAR I upload to an application server is shaded and includes the POI
> library that is relocated to its version
> specific packages to avoid collision. So it's the same version of POI as
> the test run.
>
> If there is no better way, I could somehow record all POI calls in the real
> run, and ptu it to the unit test,
> but I'd rather want to avoid this route as it is very time consuming. I am
> hoping there's a way for
> Excel to tell me what errors it is seeing.


signature.asc (499 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

ssikuro
OLE2 is a Windows file format, isn't it? I'm on Mac/Linux, and even if the
validator exists and runs for .xlsx, I can't run it. (I'm assuming it's an
.exe file.)
The link to the article is broken also.

I'll try to find if there's any code closer to validator in POI source.

Thank you for mentioning .xsb files. They exist in the shaded jar but they
weren't relocated. And if I did relocate them, I'm guessing there would be
lots of file-not-found exceptions. I temporarily stopped relocation. But
that didn't improve the situation.

On Mon, Feb 3, 2020 at 12:55 PM Andreas Beeker <[hidden email]> wrote:

> We have two entries in the FAQ [1] about file validation, which I haven't
> used myself yet ... and probably are futile in your case.
> You can try to validate against the ECMA 376 schemes.
>
> If I have similar problems I try to go step-wise from the simple case to
> the complex ...
> and yes, it's sometimes quite time consuming.
>
> Can you try your shaded jar in the unit test? ... my guess is, it might
> not include all XmlBeans files (*.xsb)
>
> [1] https://poi.apache.org/help/faq.html
>
> On 03.02.20 21:25, Kuro Kurosaka wrote:
> > The .xlsx file from the test run has the same file structure as the .xlsx
> > from the real run that doesn't open.
> > The JAR I upload to an application server is shaded and includes the POI
> > library that is relocated to its version
> > specific packages to avoid collision. So it's the same version of POI as
> > the test run.
> >
> > If there is no better way, I could somehow record all POI calls in the
> real
> > run, and ptu it to the unit test,
> > but I'd rather want to avoid this route as it is very time consuming. I
> am
> > hoping there's a way for
> > Excel to tell me what errors it is seeing.
>
>
>

--
T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

ssikuro
I've read this issue
https://bz.apache.org/bugzilla/show_bug.cgi?id=59738
which suggests version 3.10.1 works. And I tried this version and the
problem is gone!

Should I re-open this issue, if I can find a way to reproduce it?

On Mon, Feb 3, 2020 at 1:39 PM Kuro Kurosaka <[hidden email]>
wrote:

> OLE2 is a Windows file format, isn't it? I'm on Mac/Linux, and even if the
> validator exists and runs for .xlsx, I can't run it. (I'm assuming it's an
> .exe file.)
> The link to the article is broken also.
>
> I'll try to find if there's any code closer to validator in POI source.
>
> Thank you for mentioning .xsb files. They exist in the shaded jar but they
> weren't relocated. And if I did relocate them, I'm guessing there would be
> lots of file-not-found exceptions. I temporarily stopped relocation. But
> that didn't improve the situation.
>
> On Mon, Feb 3, 2020 at 12:55 PM Andreas Beeker <[hidden email]>
> wrote:
>
>> We have two entries in the FAQ [1] about file validation, which I haven't
>> used myself yet ... and probably are futile in your case.
>> You can try to validate against the ECMA 376 schemes.
>>
>> If I have similar problems I try to go step-wise from the simple case to
>> the complex ...
>> and yes, it's sometimes quite time consuming.
>>
>> Can you try your shaded jar in the unit test? ... my guess is, it might
>> not include all XmlBeans files (*.xsb)
>>
>> [1] https://poi.apache.org/help/faq.html
>>
>> On 03.02.20 21:25, Kuro Kurosaka wrote:
>> > The .xlsx file from the test run has the same file structure as the
>> .xlsx
>> > from the real run that doesn't open.
>> > The JAR I upload to an application server is shaded and includes the POI
>> > library that is relocated to its version
>> > specific packages to avoid collision. So it's the same version of POI as
>> > the test run.
>> >
>> > If there is no better way, I could somehow record all POI calls in the
>> real
>> > run, and ptu it to the unit test,
>> > but I'd rather want to avoid this route as it is very time consuming. I
>> am
>> > hoping there's a way for
>> > Excel to tell me what errors it is seeing.
>>
>>
>>
>
> --
> T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
>
>

--
T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

kiwiwings
Please open a new bugzilla entry if you find something - of course you can reference #59738 in the text.
If POI 3.10.1 is fine for you and you don't use POI to parse arbitrary files, then go for it.
Since POI 3.10.1 there were some CVEs - so depending on your usecase, it might be safer to use a current version.

It's been a while that I've deployed on an application server and I don't know how it reacts on shaded jars, but maybe you can specify the classloading strategy to "parent last". Also try to include a current xerces parser.


On 03.02.20 23:40, Kuro Kurosaka wrote:

> I've read this issue
> https://bz.apache.org/bugzilla/show_bug.cgi?id=59738
> which suggests version 3.10.1 works. And I tried this version and the
> problem is gone!
>
> Should I re-open this issue, if I can find a way to reproduce it?
>
> On Mon, Feb 3, 2020 at 1:39 PM Kuro Kurosaka <[hidden email]>
> wrote:
>
>> OLE2 is a Windows file format, isn't it? I'm on Mac/Linux, and even if the
>> validator exists and runs for .xlsx, I can't run it. (I'm assuming it's an
>> .exe file.)
>> The link to the article is broken also.
>>
>> I'll try to find if there's any code closer to validator in POI source.
>>
>> Thank you for mentioning .xsb files. They exist in the shaded jar but they
>> weren't relocated. And if I did relocate them, I'm guessing there would be
>> lots of file-not-found exceptions. I temporarily stopped relocation. But
>> that didn't improve the situation.
>>
>> On Mon, Feb 3, 2020 at 12:55 PM Andreas Beeker <[hidden email]>
>> wrote:
>>
>>> We have two entries in the FAQ [1] about file validation, which I haven't
>>> used myself yet ... and probably are futile in your case.
>>> You can try to validate against the ECMA 376 schemes.
>>>
>>> If I have similar problems I try to go step-wise from the simple case to
>>> the complex ...
>>> and yes, it's sometimes quite time consuming.
>>>
>>> Can you try your shaded jar in the unit test? ... my guess is, it might
>>> not include all XmlBeans files (*.xsb)
>>>
>>> [1] https://poi.apache.org/help/faq.html
>>>
>>> On 03.02.20 21:25, Kuro Kurosaka wrote:
>>>> The .xlsx file from the test run has the same file structure as the
>>> .xlsx
>>>> from the real run that doesn't open.
>>>> The JAR I upload to an application server is shaded and includes the POI
>>>> library that is relocated to its version
>>>> specific packages to avoid collision. So it's the same version of POI as
>>>> the test run.
>>>>
>>>> If there is no better way, I could somehow record all POI calls in the
>>> real
>>>> run, and ptu it to the unit test,
>>>> but I'd rather want to avoid this route as it is very time consuming. I
>>> am
>>>> hoping there's a way for
>>>> Excel to tell me what errors it is seeing.
>>>
>>>
>> --
>> T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
>>
>>


signature.asc (499 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

Jörn Franke
In reply to this post by ssikuro
Can you share the code of the unit test?

Maybe the file is not properly closed on the application server or there is an unlogged exception.

> Am 03.02.2020 um 23:41 schrieb Kuro Kurosaka <[hidden email]>:
>
> I've read this issue
> https://bz.apache.org/bugzilla/show_bug.cgi?id=59738
> which suggests version 3.10.1 works. And I tried this version and the
> problem is gone!
>
> Should I re-open this issue, if I can find a way to reproduce it?
>
>> On Mon, Feb 3, 2020 at 1:39 PM Kuro Kurosaka <[hidden email]>
>> wrote:
>>
>> OLE2 is a Windows file format, isn't it? I'm on Mac/Linux, and even if the
>> validator exists and runs for .xlsx, I can't run it. (I'm assuming it's an
>> .exe file.)
>> The link to the article is broken also.
>>
>> I'll try to find if there's any code closer to validator in POI source.
>>
>> Thank you for mentioning .xsb files. They exist in the shaded jar but they
>> weren't relocated. And if I did relocate them, I'm guessing there would be
>> lots of file-not-found exceptions. I temporarily stopped relocation. But
>> that didn't improve the situation.
>>
>> On Mon, Feb 3, 2020 at 12:55 PM Andreas Beeker <[hidden email]>
>> wrote:
>>
>>> We have two entries in the FAQ [1] about file validation, which I haven't
>>> used myself yet ... and probably are futile in your case.
>>> You can try to validate against the ECMA 376 schemes.
>>>
>>> If I have similar problems I try to go step-wise from the simple case to
>>> the complex ...
>>> and yes, it's sometimes quite time consuming.
>>>
>>> Can you try your shaded jar in the unit test? ... my guess is, it might
>>> not include all XmlBeans files (*.xsb)
>>>
>>> [1] https://poi.apache.org/help/faq.html
>>>
>>> On 03.02.20 21:25, Kuro Kurosaka wrote:
>>>> The .xlsx file from the test run has the same file structure as the
>>> .xlsx
>>>> from the real run that doesn't open.
>>>> The JAR I upload to an application server is shaded and includes the POI
>>>> library that is relocated to its version
>>>> specific packages to avoid collision. So it's the same version of POI as
>>>> the test run.
>>>>
>>>> If there is no better way, I could somehow record all POI calls in the
>>> real
>>>> run, and ptu it to the unit test,
>>>> but I'd rather want to avoid this route as it is very time consuming. I
>>> am
>>>> hoping there's a way for
>>>> Excel to tell me what errors it is seeing.
>>>
>>>
>>>
>>
>> --
>> T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
>>
>>
>
> --
> T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

ssikuro
I thought I created a test case by calling the APIs exactly as it does on
the app server, but it didn't quite work.
The generated .xlsx file opens successfully even when I used POI 3.17.
I will create a new issue if I find a way to reproduce this consistently.

I have been calling XSSFWorkbook.close() for POI 3.17. WIth POI
3.10.1, XSSFWorkbook
doesn't have a close() method.



On Mon, Feb 3, 2020 at 3:09 PM Jörn Franke <[hidden email]> wrote:

> Can you share the code of the unit test?
>
> Maybe the file is not properly closed on the application server or there
> is an unlogged exception.
>
> > Am 03.02.2020 um 23:41 schrieb Kuro Kurosaka <
> [hidden email]>:
> >
> > I've read this issue
> > https://bz.apache.org/bugzilla/show_bug.cgi?id=59738
> > which suggests version 3.10.1 works. And I tried this version and the
> > problem is gone!
> >
> > Should I re-open this issue, if I can find a way to reproduce it?
> >
> >> On Mon, Feb 3, 2020 at 1:39 PM Kuro Kurosaka <
> [hidden email]>
> >> wrote:
> >>
> >> OLE2 is a Windows file format, isn't it? I'm on Mac/Linux, and even if
> the
> >> validator exists and runs for .xlsx, I can't run it. (I'm assuming it's
> an
> >> .exe file.)
> >> The link to the article is broken also.
> >>
> >> I'll try to find if there's any code closer to validator in POI source.
> >>
> >> Thank you for mentioning .xsb files. They exist in the shaded jar but
> they
> >> weren't relocated. And if I did relocate them, I'm guessing there would
> be
> >> lots of file-not-found exceptions. I temporarily stopped relocation. But
> >> that didn't improve the situation.
> >>
> >> On Mon, Feb 3, 2020 at 12:55 PM Andreas Beeker <[hidden email]>
> >> wrote:
> >>
> >>> We have two entries in the FAQ [1] about file validation, which I
> haven't
> >>> used myself yet ... and probably are futile in your case.
> >>> You can try to validate against the ECMA 376 schemes.
> >>>
> >>> If I have similar problems I try to go step-wise from the simple case
> to
> >>> the complex ...
> >>> and yes, it's sometimes quite time consuming.
> >>>
> >>> Can you try your shaded jar in the unit test? ... my guess is, it might
> >>> not include all XmlBeans files (*.xsb)
> >>>
> >>> [1] https://poi.apache.org/help/faq.html
> >>>
> >>> On 03.02.20 21:25, Kuro Kurosaka wrote:
> >>>> The .xlsx file from the test run has the same file structure as the
> >>> .xlsx
> >>>> from the real run that doesn't open.
> >>>> The JAR I upload to an application server is shaded and includes the
> POI
> >>>> library that is relocated to its version
> >>>> specific packages to avoid collision. So it's the same version of POI
> as
> >>>> the test run.
> >>>>
> >>>> If there is no better way, I could somehow record all POI calls in the
> >>> real
> >>>> run, and ptu it to the unit test,
> >>>> but I'd rather want to avoid this route as it is very time consuming.
> I
> >>> am
> >>>> hoping there's a way for
> >>>> Excel to tell me what errors it is seeing.
> >>>
> >>>
> >>>
> >>
> >> --
> >> T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
> >>
> >>
> >
> > --
> > T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
Reply | Threaded
Open this post in threaded view
|

Re: Debugging tip for "Excel cannot open the file" ... when opening the file created by POI OOXML

Dominik Stadler
Hi,

Small addition: For comparing two .xlsx files we have a tool in the
dev-sources called OOXMLPrettyPrint which allows to reformat all the XML
inside the .xlsx so files created by POI and ones written by Excel look as
similar as possible for doing text/file-comparison between them. This can
make finding differences much easier.

Dominik.

On Tue, Feb 4, 2020 at 12:46 AM Kuro Kurosaka <[hidden email]>
wrote:

> I thought I created a test case by calling the APIs exactly as it does on
> the app server, but it didn't quite work.
> The generated .xlsx file opens successfully even when I used POI 3.17.
> I will create a new issue if I find a way to reproduce this consistently.
>
> I have been calling XSSFWorkbook.close() for POI 3.17. WIth POI
> 3.10.1, XSSFWorkbook
> doesn't have a close() method.
>
>
>
> On Mon, Feb 3, 2020 at 3:09 PM Jörn Franke <[hidden email]> wrote:
>
> > Can you share the code of the unit test?
> >
> > Maybe the file is not properly closed on the application server or there
> > is an unlogged exception.
> >
> > > Am 03.02.2020 um 23:41 schrieb Kuro Kurosaka <
> > [hidden email]>:
> > >
> > > I've read this issue
> > > https://bz.apache.org/bugzilla/show_bug.cgi?id=59738
> > > which suggests version 3.10.1 works. And I tried this version and the
> > > problem is gone!
> > >
> > > Should I re-open this issue, if I can find a way to reproduce it?
> > >
> > >> On Mon, Feb 3, 2020 at 1:39 PM Kuro Kurosaka <
> > [hidden email]>
> > >> wrote:
> > >>
> > >> OLE2 is a Windows file format, isn't it? I'm on Mac/Linux, and even if
> > the
> > >> validator exists and runs for .xlsx, I can't run it. (I'm assuming
> it's
> > an
> > >> .exe file.)
> > >> The link to the article is broken also.
> > >>
> > >> I'll try to find if there's any code closer to validator in POI
> source.
> > >>
> > >> Thank you for mentioning .xsb files. They exist in the shaded jar but
> > they
> > >> weren't relocated. And if I did relocate them, I'm guessing there
> would
> > be
> > >> lots of file-not-found exceptions. I temporarily stopped relocation.
> But
> > >> that didn't improve the situation.
> > >>
> > >> On Mon, Feb 3, 2020 at 12:55 PM Andreas Beeker <[hidden email]>
> > >> wrote:
> > >>
> > >>> We have two entries in the FAQ [1] about file validation, which I
> > haven't
> > >>> used myself yet ... and probably are futile in your case.
> > >>> You can try to validate against the ECMA 376 schemes.
> > >>>
> > >>> If I have similar problems I try to go step-wise from the simple case
> > to
> > >>> the complex ...
> > >>> and yes, it's sometimes quite time consuming.
> > >>>
> > >>> Can you try your shaded jar in the unit test? ... my guess is, it
> might
> > >>> not include all XmlBeans files (*.xsb)
> > >>>
> > >>> [1] https://poi.apache.org/help/faq.html
> > >>>
> > >>> On 03.02.20 21:25, Kuro Kurosaka wrote:
> > >>>> The .xlsx file from the test run has the same file structure as the
> > >>> .xlsx
> > >>>> from the real run that doesn't open.
> > >>>> The JAR I upload to an application server is shaded and includes the
> > POI
> > >>>> library that is relocated to its version
> > >>>> specific packages to avoid collision. So it's the same version of
> POI
> > as
> > >>>> the test run.
> > >>>>
> > >>>> If there is no better way, I could somehow record all POI calls in
> the
> > >>> real
> > >>>> run, and ptu it to the unit test,
> > >>>> but I'd rather want to avoid this route as it is very time
> consuming.
> > I
> > >>> am
> > >>>> hoping there's a way for
> > >>>> Excel to tell me what errors it is seeing.
> > >>>
> > >>>
> > >>>
> > >>
> > >> --
> > >> T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
> > >>
> > >>
> > >
> > > --
> > > T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
> >
>
> --
> T. Kuro Kurosaka, Software Engineer, Spartan Software Inc.
>