Hi Jurn, I can send my files?
just attach them to this thread, so other people can also help.
Hi,
Even I have a similar problem hope I will get some help here.
I am trying to generate excel report from an ASP application using xsl and excel 2010.
When I generate the report, all the data is populating in only one cell in the excel file instead of being populated as a report with multiple rows.
I have attached the .xsl file. can anyone help me correct it?
<?xml version=“1.0”?>
<xsl:stylesheet xmlns:xsl=“http://www.w3.org/TR/WD-xsl”>
<xsl:template match=“/”>
<xsl:pi name=“xml”>version=“1.0”</xsl:pi>
<Workbook 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”>
<Worksheet ss:Name=“XLReport”>
<Table ss:ExpandedColumnCount=“23” ss:ExpandedRowCount=“1” x:FullColumns=“1”
x:FullRows=“1” ss:DefaultRowHeight=“15”>
<Row ss:Height=“45”>
<Cell><Data ss:Type=“String”>FEEDBACKNUMBER</Data></Cell>
<Cell><Data ss:Type=“String”>CA</Data></Cell>
<Cell><Data ss:Type=“String”>SA</Data></Cell>
<Cell><Data ss:Type=“String”>CUSTOMERNAME</Data></Cell>
<Cell><Data ss:Type=“String”>TYPE</Data></Cell>
<Cell><Data ss:Type=“String”>ASGNTO</Data></Cell>
<Cell><Data ss:Type=“String”>RECVDATE</Data></Cell>
<Cell><Data ss:Type=“String”>DUEDATE</Data></Cell>
<Cell><Data ss:Type=“String”>RESPDATE</Data></Cell>
<Cell><Data ss:Type=“String”>CLOSEDATE</Data></Cell>
<Cell><Data ss:Type=“String”>SENTDATE</Data></Cell>
<Cell><Data ss:Type=“String”>XREFNUM</Data></Cell>
<Cell><Data ss:Type=“String”>DEPTKIND</Data></Cell>
<Cell><Data ss:Type=“String”>SRCDEPT</Data></Cell>
<Cell><Data ss:Type=“String”>CLASS</Data></Cell>
<Cell><Data ss:Type=“String”>CATG</Data></Cell>
<Cell><Data ss:Type=“String”>ESCFLAG</Data></Cell>
<Cell><Data ss:Type=“String”>MEDIA</Data></Cell>
<Cell><Data ss:Type=“String”>PRIORITY</Data></Cell>
<Cell><Data ss:Type=“String”>BILLSRV</Data></Cell>
<Cell><Data ss:Type=“String”>DISP</Data></Cell>
<Cell><Data ss:Type=“String”>ASGNDATE</Data></Cell>
<Cell><Data ss:Type=“String”>a.Cust_Num</Data></Cell>
</Row>
<xsl:for-each select=“xml/rs:data/z:row”>
<Row ss:AutoFitHeight=“0” ss:Height=“16.5”>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@FEEDBACKNUMBER”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CA”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@SA”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CUSTOMERNAME”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@TYPE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@ASGNTO”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@RECVDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@DUEDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@RESPDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CLOSEDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@SENTDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@XREFNUM”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@DEPTKIND”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@SRCDEPT”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CLASS”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@CATG”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@ESCFLAG”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@MEDIA”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@PRIORITY”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@BILLSRV”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@DISP”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@ASGNDATE”/></Data></Cell>
<Cell><Data ss:Type=“String”><xsl:value-of select=“@a.Cust_Num”/></Data></Cell>
</Row>
</xsl:for-each>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
asp code
Const sConn = “Connect to DB2 Database”
Const sXSL = “C:\Feedback.xsl”
Response.Buffer = True
'Retrieve an ADO recordset of the Orders Detail table in Northwind
Dim strSQL, rs, nRecords
Set rs = CreateObject("ADODB.Recordset")
rs.Open "strRevSql = "Select ..... ", sConn, 3, 3
'Persist the recordset to a new DOMDocument and store the record count
Dim oXML
Set oXML = CreateObject("Microsoft.XMLDOM")
rs.Save oXML, 1
nRecords = rs.RecordCount
rs.Close
'Load the XSL (the workbook template with XSL directives) into a DOMDocument
Dim oXSL
Set oXSL = CreateObject("Microsoft.XMLDOM")
oXSL.Load sXSL
'Transform the XML using the stylesheet
Dim oResults
Set oResults = CreateObject("Microsoft.XMLDOM")
oXML.transformNodeToObject oXSL, oResults
If oXSL.parseError.errorCode <> 0 Then
Response.Write "Parse Error: " & oResults.parseError.reason
Else
'Modify the ss:ExpandedRowCount attribute for the <table> node in the XSL.
Dim oTable
Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2
'Return the resulting XML Spreadsheet for display in Excel
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = "ISO-8859-1"
Response.Write oResults.XML
Response.Flush
End If
Query returns the data and If I save it in the local drive as xml file then it saves with all the data. I think my xsl file is wrong.
The ss:ExpandedRowCount attribute for Table element is updated to the total number of rows in the xml file that is saved in the c:\
Thanks in Advance
PK