CREATING MULTIPLE WORKSHEETS PER EXCEL WORKBOOK USING XSLT

[jaipragash]

Hi everybody,

My query is on how multiple worksheets per excel file can be created using
xslt programming.
From my report program i call the xslt transformation passing an internal
table. Based on the changes in the values of a particular field in the table,
multiple worksheets are to be created in a single excel file.

A snippet of the code ive generated is displayed below .But i get the error
saying its unable to open the file. I need to know where exactly to place the
<for-each select> and <worksheet> statements in order to successfully
implement the solution. Would greatly appreciate any help offerred in this
direction. Thanks

<xsl:template match="/">
<workbook>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
</ExcelWorkbook>

<xsl:apply-templates />

</Workbook>
</xsl:template>

<xsl:template match="asx:values">

<xsl:for-each select="ROWS/ZWORKSHEET">

<Worksheet ss:Name="FIRSTCOL">
<Table ss:ExpandedColumnCount="10" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="66" ss:DefaultRowHeight="14.25">
<Row 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">
<Cell ss:StyleID="s23"><Data ss:Type="String">SAP Material
Number</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">CLP</Data></Cell>
</Row>

</Table>
</Worksheet>
</xsl:for-each>
</xsl:template>
</xsl:transform>

[Martin Honnen]

Move all the namespace declarations to the root element of your
stylesheet e.g.
<xsl:transform
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"

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">

then simply make sure you create the result elements in the order they
appear in a sample Excel XML document. To find out how that looks simply
use Excel, create a sample spreadsheet, then save in XML format, then
look at the result with a text editor.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

[jaipragash]

Hi martin,

I call this xslt transformation from an SAP-ABAP program and i pass an
internal table containing multiple records as parameter. Looping through
these records, based on the change in value of a particular field the no. of
excel worksheets need to be generated dynamically. To implement this, i coded
the <for each-select> statement inside which i coded the <worksheet>
statement so that a new worksheet is generated each time for the particular
scenario. But i get an error saying 'unable to open file' .
Hence i need to know if the no. of worksheets required can be calculated
dynamically, and if yes how exactly should it be coded.

[shreya...@gmail.com]

Hello JaiPragash,

Did you find the solution of your question above??

I am stuck exactly at the same point as you were. Couldn't find any solution yet.

If you were able to find the solution could you help me through it??