XML to Excel Transformation

Here should go questions about transforming XML with XSLT and FOP.
tsm21
Posts: 25
Joined: Wed Apr 15, 2015 8:27 am

XML to Excel Transformation

Post by tsm21 »

Hi,

We have an xslt file which converts XML to Excel.

But when we give the output file extension type as '.xls' we are getting the following warning if we try to open the output excel file.
The file you are trying to open, 'abc.xls', is in a different format than specified by the extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

and when we give the output file extension type as '.xlsx', we are getting an error and the file wouldn't open.

Excel cannot open the file 'abc.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 this because of the namespaces we gave in the xsl file?

Code: Select all

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40"
>
adrian
Posts: 2850
Joined: Tue May 17, 2005 4:01 pm

Re: XML to Excel Transformation

Post by adrian »

Hi,

Please note that an XSLT transformation cannot create from scratch an Excel file. An Excel file is a binary file in either the old proprietary '.xls' format or the newer ZIP-like container that is the '.xlsx' format.
It is however possible for a transformation from Oxygen to inject/rewrite an XML inside the ZIP container from an existing Excel (.xlsx) document/template.
See here an example of how to Export to Excel by using this method.

Regards,
Adrian
Adrian Buza
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com
tsm21
Posts: 25
Joined: Wed Apr 15, 2015 8:27 am

Re: XML to Excel Transformation

Post by tsm21 »

Hi,

We have tried to open the ".xls" file as per the solution given, but still we are unable to open the excel file in oXygen XML editor.
Right Click ".xls" file --> open --> Archive Browser window
Instead a pop up error window is displayed in editor saying that "Unknown Archive format or corrupted Archive".


Could you please help us with a solution?




Thank you
Sai
adrian
Posts: 2850
Joined: Tue May 17, 2005 4:01 pm

Re: XML to Excel Transformation

Post by adrian »

Hi,

I'm not sure where you got the idea for this solution that involves opening the '.xls' file in Oxygen.
Oxygen does not support opening the old proprietary Excel format ('.xls'). It supports opening in the Archive Browser only the new '.xlsx' format (Office 2007), which is a zip-like container.

Regards,
Adrian
Adrian Buza
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com
tsm21
Posts: 25
Joined: Wed Apr 15, 2015 8:27 am

Re: XML to Excel Transformation

Post by tsm21 »

Hi,

Actually we have tried opening ".xlsx" in the editor too and could not open it, as the same error is displayed.
Unknown Archive format or corrupted Archive




Thank you
Sai
adrian
Posts: 2850
Joined: Tue May 17, 2005 4:01 pm

Re: XML to Excel Transformation

Post by adrian »

Hi,
tsm21 wrote:Actually we have tried opening ".xlsx" in the editor too and could not open it, as the same error is displayed.
Unknown Archive format or corrupted Archive
Probably a bad file. Hopefully this is not the same file for which you mentioned you changed the extension to '.xlsx'" before. If I wasn't clear before, changing a file extension doesn't convert it to the desired format, so that can't possibly work.

The ".xlsx" file you open needs to be a valid Excel 2007 (or later) file. If Excel can't open it and reports errors, then Oxygen won't be able to open it either.

To keep things simple when testing:
1. Start Excel and create and save a new .xlsx file.
2. Close Excel (or just the document you created).
3. In Oxygen open the .xlsx file created at step 1.
4. Do you still get the "Unknown Archive format" error?

Regards,
Adrian
Adrian Buza
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com
tsm21
Posts: 25
Joined: Wed Apr 15, 2015 8:27 am

Re: XML to Excel Transformation

Post by tsm21 »

Hi,

We are creating an excel sheet with ".xlsx" extension using xslt file and the error is displayed for this generated excel sheet.

If we manually create an excel sheet with ".xlsx" format, we are able to open the file in oxygen XML editor.





Thank you
Sai
adrian
Posts: 2850
Joined: Tue May 17, 2005 4:01 pm

Re: XML to Excel Transformation

Post by adrian »

Hi,
tsm21 wrote:We are creating an excel sheet with ".xlsx" extension using xslt file and the error is displayed for this generated excel sheet.
I mentioned it before, you cannot "create" a valid ".xlsx" file from an XSL transformation. So, whatever you are creating from the XSL transformation, that is not a usable Excel/.xlsx document. You need to use the injection method to make changes inside an existing .xlsx template. See my first reply from this thread. Watch the video demo again if it's not clear how to accomplish this.

Regards,
Adrian
Adrian Buza
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com
Post Reply