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) < 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])) > 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(.) > $length]) > 0"> <xsl:call-template name="longest"> <xsl:with-param name="nodeset" select="$nodeset[string-length(.) > $length]"/> <xsl:with-param name="length" select="string-length($nodeset[string-length(.) > $length][1])"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:value-of select="$nodeset"/> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet>
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
[xsl] Web app customization - XSLT , Michael Ludwig | Thread | [xsl] XSL/XPath 2.0 - most efficien, Robert Koberg |
Re: [xsl] Conditional String Substi, Dimitre Novatchev | Date | Re: [xsl] Conditional String Substi, Nathan Potter |
Month |