[xsl] Creating dynamic spreadsheets in Excel (VB.NET, XML, XSL)

Subject: [xsl] Creating dynamic spreadsheets in Excel (VB.NET, XML, XSL)
From: "Hewitt, Cheryl" <Cheryl.Hewitt@xxxxxxxxxxxxxxxxxxx>
Date: Fri, 25 Apr 2008 14:15:21 -0500
Hi,

I'm new to your forum and new to using XSL with Excel. I'm hitting a
brick wall, so I figured it was time to turn to those who work with this
on a regular basis. Thanks in advance.

The application queries a SQL database dependent on user selections via
a form with the results being output to Excel. I'm a newbie to exporting
to Excel with XSL formatting, so I've been gathering bits and pieces
from here and there and trying to get it to work. The XSL code I am
using seems to be written so that it can handle adhoc queries, which is
what I am after. I have a few set fields that are included in the query,
but then the user is allowed to choose additional fields to build their
report.

The aspx.vb page builds a query and an XmlDataDocument object is then
loaded with the dataset. If I skip the formatting and just export it to
Excel it works just fine, but if I try to format the output using XSL
all of the output is in one cell in Excel. I have included the code that
I think may be relevant, and the XSL code is below the VB.

If there is a cleaner way to go about this I'd love to hear about it. If
you can find the reason why it's not working I'd be more than grateful
to learn what's wrong. Basically, all I want is for the column headers
to be bolded, gridlines, and number and currency formatting. I figured
if I could get it to output the resultset without errors that I'd start
tweaking things to suit the formatting criteria.

TIA,

Cheryl

**************************
Code for form
**************************

  Private Sub cmdExport_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdExport.Click
    Dim strSQL As String
    Dim strCon As String
    Dim strExcelFile As String
    Dim dsQuery As DataSet
    Dim XMLDoc As XmlDataDocument
    Try
	.	'build query
	.
	.
	dsQuery = SqlHelper.ExecuteDataset(strCon, CommandType.Text,
strSQL)
	XMLDoc = New XmlDataDocument(dsQuery)

	strExcelFile = objExport.TransformXMLDocumentToExcel(XMLDoc,
"Excel.xsl")

	objExport.SendExcelToClient(strExcelFile)
	Response.Redirect("reportGenerator.aspx")

    Catch ex As Threading.ThreadAbortException
      'Do nothing
    Catch ex As Exception
      Response.Write(ex.ToString)
    End Try


************************************************************************
***
The code that is being called from form code behind page above
************************************************************************
***
  Public Function TransformXMLDocumentToExcel(ByVal XMLDoc As
XmlDataDocument, ByVal strXSLFullFilePath As String) As String
    Dim strExcelFile As String
    Dim objStrRdr As StringReader
    Dim objXMLRdr As XmlTextReader
    Dim objXPthDoc As XPath.XPathDocument
    Dim fs As System.IO.FileStream
    Dim objXMLTxtWrtr As XmlTextWriter
    Dim objXslTran As XslTransform
    Dim objXslRes As XmlResolver


    Try
      'Create An Xpath Doc
      objStrRdr = New StringReader(XMLDoc.OuterXml)
      objXMLRdr = New XmlTextReader(objStrRdr)
      objXPthDoc = New XPath.XPathDocument(objXMLRdr)

      strExcelFile = TempFolder & TEMP_EXCEL_FILE_NAME &
Now.ToString("MM-dd-yy") & " " & Now.Hour.ToString & Now.Minute.ToString
_
              & Now.Second.ToString & Now.Millisecond.ToString & ".xls"

      fs = New System.IO.FileStream(strExcelFile, _
       System.IO.FileMode.Create)

      'Create an XmlTextWriter for the FileStream.
      objXMLTxtWrtr = New XmlTextWriter(fs, _
          System.Text.Encoding.Unicode)
      'Transform the XML using the stylesheet.

      objXslTran = New XslTransform

      strXSLFullFilePath =
strXSLFullFilePath.Replace(XSLStyleSheetFolder, "")

      strXSLFullFilePath = XSLStyleSheetFolder & strXSLFullFilePath

      objXslTran.Load(strXSLFullFilePath)

      objXslTran.Transform(objXPthDoc, Nothing, objXMLTxtWrtr, Nothing)

      Return strExcelFile

    Catch exptn As Exception
      Throw
    Finally
      If Not objXMLTxtWrtr Is Nothing Then
        objXMLTxtWrtr.Close()
        objXMLTxtWrtr = Nothing
      End If

      If Not objStrRdr Is Nothing Then
        objStrRdr.Close()
        objStrRdr = Nothing
      End If

      If Not objXMLRdr Is Nothing Then
        objXMLRdr.Close()
        objXMLRdr = Nothing
      End If

      If Not fs Is Nothing Then
        fs.Close()
        fs = Nothing
      End If

      If Not objXMLTxtWrtr Is Nothing Then
        objXMLTxtWrtr.Close()
        objXMLTxtWrtr = Nothing
      End If

      objXPthDoc = Nothing
      objXslTran = Nothing
      objXslRes = Nothing
    End Try
  End Function

  Public Sub SendExcelToClient(ByVal strExcelFile As String)
    'Dim strFileName As String = strExcelFile.ToString()
    Try
      HttpContext.Current.Response.Clear()
      HttpContext.Current.Response.AddHeader("content-disposition",
"attachment;filename=" & strFileName)
      HttpContext.Current.Response.Charset = ""
      HttpContext.Current.Response.ContentType =
"application/vnd.ms-excel"
      HttpContext.Current.Response.WriteFile(strExcelFile)
      HttpContext.Current.Response.End()
    Catch exptn As Exception
      Throw
    End Try
  End Sub

********************************************************
XSL code: Excel.xsl
********************************************************

<xsl:stylesheet version="1.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";>
<!-- : /: Edmond Woychowsky: July 25, 2005: The purpose of this template
is to create an Excel/XML spreadsheet from a simple xml document. -->
<xsl:template match="/">
  <Workbook>
    <xsl:call-template name="DocumentProperties"/>
    <xsl:call-template name="OfficeD=cumentSettings"/>
    <xsl:call-template name="ExcelWorkbok"/>
    <xsl:call-template name="Styles"/>
    <xsl:apply-templates select="/*" mode="worksheet"/>
  </Workbook>
</xsl:template>
<!-- : * worksheet: This template builds the spreadsheets individual
worksheets, commonly know as tabs.-->
<xsl:template match="*" mode="worksheet">
  <xsl:variable name="position" select="position()"/>
  <Worksheet ss:Name="{concat('Sheet', $position)}">
    <Table ss:ExpandedCAlumnCount="{count(./*[1]/*)}"
ss:ExpandedRAwCount="{count(./*) + 2}" x:FullColumns="1" x:FullRows="1">
      <xsl:apply-templates select="*" mode="row"/>
    </Table>
    <xsl:call-template name="WorksheetOptions"/>
  </Worksheet>
</xsl:template>
<!-- : * row: This template builds the worksheet's rows.-->
<xsl:template match="*" mode="row">
  <Row>
    <xsl:apply-templates select="*" mode="cell"/>
  </Row>
</xsl:template>
<!-- : * cells: This template builds the row's cells.-->
<xsl:template match="*" mode="cell">
  <xsl:variable name="type">
  <xsl:choose>
    <xsl:when test="number(.) = .">Number</xsl:when>
    <xsl:otherwise>String</xsl:otherwise>
  </xsl:choose>
  </xsl:variable>
  <Cell>
    <Data ss:Type="{$type}">
      <xsl:value-of select="."/>
    </Data>
  </Cell>
</xsl:template>
<!-- : * column: This template describes a worksheet's individual
columns.-->
<xsl:template match="*" mode="column">
<xsl:variable name="name" select="name(.)"/>
<xsl:variable name="length">
<xsl:call-template name="length">
<xsl:with-param name="nodeset" select="//parent::*/parent::*/*/*[name(.)
= $name]"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="width">
  <xsl:choose>
    <xsl:when test="($length * 5.75) &lt; 56.25">56.25</xsl:when>
  <xsl:otherwise>
    <xsl:value-of select="$length * 5.75"/>
  </xsl:otherwise>
  </xsl:choose>
</xsl:variable>
<xsl:variable name="style">
  <xsl:choose>
  <xsl:when test="parent::*/parent::*/*/*[name(.) = $name] =
number(parent::*/parent::*/*[1]/*[name(.) = $name])">
    <xsl:choose>
      <xsl:when test="string-length(parent::*/parent::*/*/*[name(.) =
$name][contains(.,'.')]) = 0">s23</xsl:when>
    <xsl:otherwise>s24</xsl:otherwise>
      </xsl:choose>
    </xsl:when>
    <xsl:otherwise>s22</xsl:otherwise>
  </xsl:choose>
</xsl:variable>
<Column ss:StyleID="{$style}" ss:AutoFitWidth="0" ss:Width="{$width}"/>
</xsl:template>
<!-- : DocumentProperties: This template describes the document to
Excel.-->
<xsl:template name="DocumentProperties">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <Author/>
    <Company>LAB</Company>
    <Version/>
  </DocumentProperties>
</xsl:template>
<!-- : OfficeDocumentSettings: This template describes the Office
document to Excel.-->
<xsl:template name="OfficeDScumentSettings">
  <OfficeDocumentSettings
xmlns="urn:schemas-microsoft-com:office:office">
  <DownloadCTmponents/>
  </OfficeDocumentSettings>
</xsl:template>
<!-- : ExcelWorkbook: This template describes the characteristics of the
wookbook to Excel.-->
<xsl:template name="ExcelWorkbok">
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9210</WindowHeight>
<WindowWidth>15195</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>60</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
</xsl:template>
<!-- : Styles: This template describes the display styles to Excel.-->
<xsl:template name="Styles">
  <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom"/>
      <Borders/>
      <Font/>
      <Interior/>
      <NumberFormat/>
      <Protection/>
    </Style>
</Styles>
</xsl:template>
<!-- : WorksheetOptions: This template describes the worksheet options
to Excel.-->
<xsl:template name="WorksheetOptions">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <Print>
  <ValidPrinterInfo/>
  <HorizontalResolution>1200</HorizontalResolution>
  <VerticalResolution>1200</VerticalResolution>
  </Print>
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</xsl:template>
<!-- : length: Determine either the length of the node name or the
longest node(s), which ever is longer.-->
<xsl:template name="length">
<xsl:param name="nodeset"/>
<xsl:variable name="longest">
<xsl:call-template name="longest">
<xsl:with-param name="nodeset" select="$nodeset"/>
</xsl:call-template>
</xsl:variable>
<xsl:choose>
<xsl:when test="string-length(name($nodeset[1])) &gt;
string-length($longest)">
<xsl:value-of select="string-length(name($nodeset[1]))"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="string-length($longest)"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- : longest: This recursive template transverses a nodeset to find
the nodes with the longest string-length. Please note that the result of
this template may itself be a nodeset. -->
<xsl:template name="longest">
<xsl:param name="nodeset"/>
<xsl:param name="length" select="0"/>
<xsl:choose>
<xsl:when test="count($nodeset[string-length(.) &gt; $length]) &gt; 0">
<xsl:call-template name="longest">
<xsl:with-param name="nodeset" select="$nodeset[string-length(.) &gt;
$length]"/>
<xsl:with-param name="length"
select="string-length($nodeset[string-length(.) &gt; $length][1])"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$nodeset"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

Current Thread