Generate an EXCEL report with XML (HELP)

[ale77]

Hi. I would like to now if someone has any informafion on how to generate an EXCEL report with XML. I don't know how to do that at all.

Right now I'm doing that with an HTML table but I'll be much better in EXCEL.

Any ideas or links or code examples are welcome!!!!

 

[ale77]

NOBODY HAS ANY IDEA?

 

[ale77]

anybody? Dows someone understands the question or needs more explanation?

 

[jel]

OK, your question is not realy clear.
how to generate an EXCEL report with XML
XML doest generate anything...
So: what is your problem: do you want export data to excel, and is that data in xml?
In that case: what does your xml look like?
Or do you want to generate a report in Excel? (in that case you might post on another forum)

 

[ale77]

I have a report (TABLE) in HTML. BUT people want to see it in EXCEL. SO I want to make an XML file that opens the report in an EXCEL worksheet. Instead of displaying a html table I want an excel spreadsheet but using XML.

I don't know much about XML. I have seen that in EXCEL 2003 you can create an XML file but I don't have EXCEL 2003.

What's the other forum????

 

[ale77]

by the way, thanks for your reply.

 

[jel]

forum68
It might be possible to translate an HTML-table to xml, but it is very tricky, because XML is much more strict than HTML.
Opening data in an excel-sheet is not so difficult, all you have to do is save the data as csv.
The primary question is: how did the data get in the HTML? Maybe you could reach the source of the data and convert it to csv.
If that is no option, do post the HTML-table, and we could have a look it it.

 

[ale77]

The code that I have is just a simple html table:

<%
Response.ContentType = "application/vnd.ms-excel"
%>
<table cellpadding="0" cellspacing="0" Height="0" border="1" width="800" ID="Table">
<caption>
<font class='fixedtext' style='font-size:14'>
<strong>Long-Term Capital Gains and Losses - Assets Held More than One Year </strong></font></caption>
  <tr bgcolor=#cecbff>
    <td align="center" width=300><b >Description of property</b></td>
    <td align="center" width=100><b >Date acquired</b></td>
    <td align="center" width=100><b >Date sold</b></td>
    <td align="center" width=100><b >Sales price</b></td>
    <td align="center" width=100><b >Cost basis</b></td>
    <td align="center" width=100><b >Gain or (loss)</b></td></tr>
  <tr bgcolor=silver>
    <td align="left" nowrap  ><font size=2>BRK/A / BERKSHIRE HATHAWAY</font></td>
    <td align="center" nowrap ><font size=2>04/02/2002</font></td>
    <td align="center" nowrap ><font size=2>09/02/2003</font></td>
    <td align="right" nowrap ><font size=2>1,669,970.0100</font></td>
    <td align="right" nowrap ><font size=2>1,812,500.0000</font></td>
    <td align="right" nowrap ><font size=2>-142,529.9900</font></td></tr>
   <tr>
    <td align="right" colspan=3><b >Total ($)&nbsp;&nbsp;</b></td>
    <td align="right" valign="center" colspan=1>
        <font size=2 ><b>19,934,241.8331</b></font></td>
    <td align="right" valign="center" colspan=1>
       <font size=2 ><b>21,737,137.8595</b></font></td>
    <td align="right" valign="center" colspan=3>
       <font size=2 ><b>-1,802,896.0264</b></font></td>
</tr>
</table>

With that code, the window opens in an excel spreadsheet but I wnat to use XML instead.
The code that I'm going to show right now is somehting I found. I guess that's what I have to do but I don't see that working, I don't know how that works. Any ideas?

<xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] 
	xmlns:msxsl="urn:schemas-microsoft-com:xslt"
	xmlns:user="urn:my-scripts"
	xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel"
	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >   

<xsl:template match="Order">

<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="[URL unfurl="true"]http://www.w3.org/TR/REC-html40">[/URL]

 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <Font ss:Bold="1"/>
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  </Style>
  <Style ss:ID="s22">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <Font ss:Bold="1"/>
   <Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s23" ss:Name="Currency">
   <NumberFormat
    ss:Format="_(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;* \(#,##0.00\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)"/>
  </Style>
  <Style ss:ID="s24">
   <NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(* &quot;-&quot;??_);_(@_)"/>
  </Style>
  <Style ss:ID="s25">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  </Style>
 </Styles>

 <Worksheet>
 <xsl:attribute name="ss:Name">
   <xsl:value-of select='concat("Order #", Customer/OrderID)'/>
 </xsl:attribute>
  <Table ss:ExpandedColumnCount="3">
  <xsl:attribute name="ss:ExpandedRowCount" >
	<xsl:value-of select="count(Items)+10"/>
  </xsl:attribute>

   <Column ss:AutoFitWidth="0" ss:Width="150"/>
   <Column ss:AutoFitWidth="0" ss:Width="100"/>
   <Column ss:AutoFitWidth="0" ss:Width="75"/>
   
   <xsl:apply-templates select="Customer"/>

   <Row>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Item</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Quantity</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Total</Data></Cell>
   </Row>

   <xsl:apply-templates select="Items"/>
   
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">Subtotal</Data></Cell>
    <Cell ss:StyleID="s23" ss:Formula="=SUM(R8C:R[-1]C)"/>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">Freight</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="Number"><xsl:value-of select="Customer/Freight"/></Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">Total</Data></Cell>
    <Cell ss:StyleID="s23" ss:Formula="=R[-2]C+R[-1]C"/>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

</xsl:template>

<xsl:template match="Customer">
   <Row>
    <Cell><Data ss:Type="String"><xsl:value-of select="CompanyName"/></Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String"><xsl:value-of select="Address"/></Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String"><xsl:value-of select='concat(City, ", ", Region, " ", PostalCode)'/></Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String"><xsl:value-of select="Country"/></Data></Cell>
   </Row>
   <Row ss:Index="6">
    <Cell ss:MergeAcross="2" ss:StyleID="s22">
     <Data ss:Type="String">Order #<xsl:value-of select="OrderID"/></Data>
    </Cell>
   </Row>   
</xsl:template>

<xsl:template match="Items">
   <Row>
    <Cell><Data ss:Type="String"><xsl:value-of select="ProductName"/></Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="Number"><xsl:value-of select="Quantity"/></Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="Number"><xsl:value-of select="ItemTotal"/></Data></Cell>
   </Row>
</xsl:template>

</xsl:stylesheet>

 

[byam]

The code that you have is XSL, which is used by XSLT processor to tranfomer a input XML data into another form. In other word, you need to have XSLT processor, e.g. Xalan, take your data as XML with a XSL, and transform it into a Excel XML file.

By the way, if you have your HTML code XML conforming, i.e. XHTML, it's then consider XML. I won't bother doing any XLS transformation to make it into Excel schema unless you want to apply specific Excel style to the table.