Convert DataSet to SpreedsheetML using XSLT

[Danilo Corallo]

Hi to everybody, I need some help :doh: to convert a XML DataSet to an Excel file. I actually using this code: Private Sub TransformXML(ByVal xmlDoc As XmlDataDocument, _ ByVal strXSLPath As String, _ ByVal strSavePath As String)   Dim xt As New XslCompiledTransform   Dim tw As XmlTextWriter   tw = New XmlTextWriter(strSavePath, System.Text.Encoding.UTF8)   tw.Formatting = Formatting.Indented   tw.Indentation = 3   tw.WriteStartDocument()   xt.Load(My.Application.Info.DirectoryPath & strXSLPath)   xt.Transform(xmlDoc, Nothing, tw)   tw.Close() End Sub The XSLT follow here :^): <xsl:stylesheet version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 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="/"> <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"> xsl:apply-templates/ </Workbook> </xsl:template> <xsl:template match="/*"> <Worksheet> <xsl:attribute name="ss:Name"> <xsl:value-of select="local-name(/*/*)"/> </xsl:attribute> <Table x:FullColumns="1" x:FullRows="1"> <Row> <xsl:for-each select="*[position() = 1]/*"> <Cell> <Data ss:Type="String"> <xsl:value-of select="local-name()"/> </Data> </Cell> </xsl:for-each> </Row> xsl:apply-templates/ </Table> </Worksheet> </xsl:template> <xsl:template match="/*/*"> <Row> xsl:apply-templates/ </Row> </xsl:template> <xsl:template match="/*/*/*"> <Cell> <Data ss:Type="String"> <xsl:value-of select="."/> </Data> </Cell> </xsl:template> </xsl:stylesheet> The problem is that the XSLT must be

[Ron S]

Dim ds4xmlcsv As New DataSet Dim export As New ConvertXMLtoCSV Dim f1 As File Dim fullpath As String Try ds4xmlcsv.ReadXml(cc) export.Export(ds4xmlcsv, True) ds4xmlcsv.Clear() If MsgBox("The Transfer is Complete", MsgBoxStyle.MsgBoxSetForeground.OKOnly, "Thank You") = MsgBoxResult.OK Then btntrs2csv.Enabled = False Exit Sub End If Catch errormsg As Exception MsgBox(errormsg.Message + sessiontime.Now) End Try Public Function Export(ByVal ds As DataSet, ByVal exportcolumnheadings As Boolean) As String 'Dim ts11 As String 'ts11 = txtinputxmltext 'TextBox1.Text = "" Dim ts As String 'Dim rep As String Dim ts22 As String ts22 = "XML2CSV.csv" ts = Path.GetFileName(txtsavetext) fname = txtsavetext.Replace(ts, ts22) Dim header As String Dim body As String Dim record As String Dim f As file Dim ii As Integer 'Dim cc As String 'cc = "C:\bin\XMLInput.csv" 'f.CreateText(cc) If f.Exists(fname) Then f.Delete(fname) End If Dim writer As StreamWriter = New StreamWriter(fname) If exportcolumnheadings Then For Each col As DataColumn In ds.Tables(0).Columns header = header & Chr(34) & col.ColumnName & Chr(34) & "," Next header = header.Substring(0, header.Length - 1) writer.WriteLine(header) writer.Close() End If For Each row As DataRow In ds.Tables(0).Rows Dim arr() As Object = row.ItemArray() For i As Integer = 0 To arr.Length - 1 If arr(i).ToString().IndexOf(",") > 0 Then record = record & Chr(34) & arr(i).ToString() & Chr(34) & "," Else record = record & arr(i).ToString() & "," 'cc.Concat(header, record) End If Next 'If ii = 0 Then ' Dim writer2 As StreamWriter = New StreamWriter(cc, False) ' writer2.WriteLine(record) ' writer2.Close() ' ii = 1 'Else Dim writer2 As StreamWriter = New StreamWriter(fname, True) writer2.WriteLine(record) writer2.Close() 'End If

[Danilo Corallo]

Thanks for your reply, but I want to convert my XML to XLS using XSLT. Here an example of what I want to have! http://support.microsoft.com/?kbid=319180 The code posted already work, I need only a better implementation of the stylesheet... Thanks again :)