xslt Transform efficiently

[Doug Heeren]

I have the following section of VB.NET code that transforms a simple dataset
into an Excel xml workbook. It works fine for < 50 rows or so, but I have
about 8,000 rows I need to transform. Is there a better way than what I am
doing. This is part of an ASP.NET applicaiton.

'Clear the output stream
Response.ClearHeaders()
Response.ClearContent()
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Server.ScriptTimeout = 99999
Dim co As Boolean = CType(Request.QueryString("co"), Boolean)
Dim oo As Boolean = CType(Request.QueryString("oo"), Boolean)
Dim List As New _Class
Dim ds As DataSet = List.GetDetailedTrailerListing(oo, co)
ds.DataSetName = "ExcelDS"
Dim tbl As New DataTable
tbl.TableName = "HeaderTbl"
tbl.Columns.Add("RUN_DATE")
tbl.Columns(0).DataType = GetType(String)
Dim rw As DataRow = tbl.NewRow
rw("RUN_DATE") = DateTime.Now.Date.ToShortDateString
ds.Tables.Add(tbl)

Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
Dim root As XmlElement = xmlDoc.DocumentElement
Dim nav As XPath.XPathNavigator = root.CreateNavigator
Dim xslTran As XslTransform = New XslTransform
xslTran.Load(context.Server.MapPath("DetailedTrail erListing.xslt"))
Dim writer As XmlTextWriter = New
XmlTextWriter(context.Response.OutputStream, System.Text.Encoding.UTF8)
xslTran.Transform(nav, Nothing, writer, Nothing)

Response.Flush()
Response.Close()

The xslt file is:

<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="ExcelDS">

<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">

<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="_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)"/>
</Style>
<Style ss:ID="s24">
<NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(*
"-"??_);_(@_)"/>
</Style>
<Style ss:ID="s25">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s26">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s27">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
<NumberFormat ss:Format="_(* ##0_);_(* \(##0\);_(* "-"??_);_(@_)"/>
</Style>
<Style ss:ID="s28">
<NumberFormat ss:Format="_(* #,##0.0000_);_(* \(#,##0.0000\);_(*
"-"??_);_(@_)"/>
</Style>
</Styles>

<Worksheet>
<xsl:attribute name="ss:Name">
<xsl:value-of select='concat("Deatiled Trailer ", HeaderTbl/RUN_DATE)'/>
</xsl:attribute>
<Table ss:ExpandedColumnCount="36">
<xsl:attribute name="ss:ExpandedRowCount" >
<xsl:value-of select="count(TrlList)+2"/>
</xsl:attribute>

<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>

<Row>
<Cell ss:StyleID="s21"><Data ss:Type="String">Trailer #</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Owner Name</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Owner Address</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Owner City</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Owner State</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Owner Zip</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Owner ID</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Trailer Type</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Location Name</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Location City</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Location State</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Location Date</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Status</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Make</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Model</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Year</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Color</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Weight</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Purchase Date</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Purchase Price</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Factory Price</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">VIN</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Title</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Reference</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Lease Date</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Termination
Code</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Length</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Side Kit</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Inspection Due</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">TRL_DIV</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Kingpin
Dimensions</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Deck Height</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">In Use</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Width</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">V Boards</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="String">Tire Inflation
System</Data></Cell>
</Row>
<xsl:apply-templates select="TrlList"/>
</Table>
</Worksheet>
</Workbook>
</xsl:template>


<xsl:template match="TrlList">
<Row>
<Cell><Data ss:Type="String"><xsl:value-of
select="TRAILER"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="OWNER_NAME"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="OWNER_ADDR"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="OWNER_CITYABBR"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="OWNER_STATE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="OWNER_ZIP"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="OWNER_ID"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TRL_TYPE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="LOCATOIN_NAME"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="LOCATION_CITYABBR"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="LOCATION_STATE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="LOCATION_DATE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="STATUS"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="MAKE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="MODEL"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TRL_YEAR"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="COLOR"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="WEIGHT"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="PURCHASE_DATE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="PURCHASE_PRICE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="FACTORY_PRICE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TRL_VIN"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TITLE_NUMBER"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TRL_REF"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="LEASE_DATE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TERM_CODE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="LENGTH"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="SIDES"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="INSPECT_DUE_DATE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TRL_DIV"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="KINGPIN_DIM"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="DECK_HEIGHT"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="IN_USE"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="WIDTH"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="V_BOARD"/></Data></Cell>
<Cell><Data ss:Type="String"><xsl:value-of
select="TIRE_INFLATION"/></Data></Cell>

</Row>
</xsl:template>

</xsl:stylesheet>
--
Doug Heeren
Project Manager
Annett Holdings, Inc.
[phone]

[Pascal Schmitt]

Doug Heeren wrote:

I have the following section of VB.NET code that transforms a simple dataset
into an Excel xml workbook. It works fine for < 50 rows or so, but I have
about 8,000 rows I need to transform. Is there a better way than what I am
doing. This is part of an ASP.NET applicaiton.

Don't use XSLT.
In your case (the input and output are very simple, the data's structure
does not change, you don't have to sort or group anything) it may be
better to use an XmlWriter and directly create the Output-XML-Document
from the Dataset.

--
Pascal Schmitt

[Doug Heeren]

Thank you. I was afraid that was the answer. All I'm really trying to do is
return a single table from a web page into an Excel document. Can you tell me
what the best approach is for this?
--
Doug Heeren
Project Manager
Annett Holdings, Inc.
[phone]
"Pascal Schmitt" wrote:

Doug Heeren wrote:

I have the following section of VB.NET code that transforms a simple dataset
into an Excel xml workbook. It works fine for < 50 rows or so, but I have
about 8,000 rows I need to transform. Is there a better way than what I am
doing. This is part of an ASP.NET applicaiton.

Don't use XSLT.
In your case (the input and output are very simple, the data's structure
does not change, you don't have to sort or group anything) it may be
better to use an XmlWriter and directly create the Output-XML-Document
from the Dataset.

--
Pascal Schmitt

[Pascal Schmitt]

Doug Heeren wrote:

Thank you. I was afraid that was the answer. All I'm really trying to do is
return a single table from a web page into an Excel document. Can you tell me
what the best approach is for this?

Well, maybe something like this (I don't use the Data-Objects so the
Names may be wrong):

....
Dim ds As DataSet = List.GetDetailedTrailerListing(oo, co)
ds.DataSetName = "ExcelDS"
Dim rows As DataRowCollection = ds.Tables(0).Rows

Dim w As New XmlTextWriter(Response.OutputStream)

....
For i = 0 To 36
w.WriteStartElement("Column")
w.WriteAttributeString("ss:AutoFitWidth", "1")
w.WriteEndElement()
Next
....

For i = 0 To rows.Count-1
w.WriteStartElement("Row")
For j = 0 To rows(i).Cells.Count-1
w.WriteStartElement("Cell")
w.WriteAttributeString("ss:StyleID", "s21")
w.WriteStartElement("Data")
w.WriteAttributeString("ss:Type", "string")
w.WriteText( rows(i).Cells(j).Value )
w.WriteEndElement()
w.WriteEndElement()
Next
w.WriteEndElement()
Next

....
--
Pascal Schmitt

[Doug Heeren]

Mr. Schmitt,
Thank you very much. I though about using xPath with a static xml document
and adding nodes. Pretty much the same results as what your example would do.
To test this before I spent lots of time writing the code. I wrote a console
application to generate the Excel xlm transformation above. It took more than
45 minute for Excel to load the document on an amd 2ghz with a gig of memory.
Unfortunately I don't think an XML Excel document of this size has any
utility. This is sooo disappointing because of the capabilities of the
transformation make it very attractive, but it does work great for very very
small amounts of data and I use it elsewhere.

My final solution to this will be listed below. I'm not proud of it. There
should be a better way, but deadlines being what they are and users being
what they are; it returns something to Excel in a second or two. It is simple
but UGLY:

Response.ClearHeaders()
Response.ClearContent()
Response.ContentType = "text/txt"
Response.Charset = ""
Response.ContentEncoding = Encoding.UTF8

System.Web.HttpContext.Current.Response.AddHeader( "Content-Disposition",
"attachment; filename=DetailedTrailerListing.csv")
Server.ScriptTimeout = 99999

Dim co As Boolean = CType(Request.QueryString("co"), Boolean)
Dim oo As Boolean = CType(Request.QueryString("oo"), Boolean)
Dim List As New _Class
Dim ds As DataSet = List.GetDetailedTrailerListing(oo, co)
Dim strOut As New StringBuilder

Dim cl As DataColumn
For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
cl = ds.Tables(0).Columns(i)
If (i = 0) Then
strOut.Append("""" & cl.ColumnName & """")
Else
strOut.Append(",""" & cl.ColumnName & """")
End If
Next
strOut.Append(ControlChars.CrLf)
For Each rw As DataRow In ds.Tables(0).Rows
For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
If (i = 0) Then
strOut.Append("""" & rw.Item(i) & """")
Else
strOut.Append(",""" & rw.Item(i) & """")
End If
Next
strOut.Append(ControlChars.CrLf)
Next
Response.Write(strOut.ToString)

Response.Flush()
Response.Close()


--
Doug Heeren
Project Manager
Annett Holdings, Inc.
[phone]
"Pascal Schmitt" wrote:

Doug Heeren wrote:

Thank you. I was afraid that was the answer. All I'm really trying to do is
return a single table from a web page into an Excel document. Can you tell me
what the best approach is for this?

Well, maybe something like this (I don't use the Data-Objects so the
Names may be wrong):

....
Dim ds As DataSet = List.GetDetailedTrailerListing(oo, co)
ds.DataSetName = "ExcelDS"
Dim rows As DataRowCollection = ds.Tables(0).Rows

Dim w As New XmlTextWriter(Response.OutputStream)

....
For i = 0 To 36
w.WriteStartElement("Column")
w.WriteAttributeString("ss:AutoFitWidth", "1")
w.WriteEndElement()
Next
....

For i = 0 To rows.Count-1
w.WriteStartElement("Row")
For j = 0 To rows(i).Cells.Count-1
w.WriteStartElement("Cell")
w.WriteAttributeString("ss:StyleID", "s21")
w.WriteStartElement("Data")
w.WriteAttributeString("ss:Type", "string")
w.WriteText( rows(i).Cells(j).Value )
w.WriteEndElement()
w.WriteEndElement()
Next
w.WriteEndElement()
Next

....
--
Pascal Schmitt

[Pascal Schmitt]

Hello!

Although it is ugly, you can optimize it: don't use the StringBuilder,
don't concat strings using & (because .NET will copy all the data and
create a new string - a waste of time and memory) write directly to the
Response - the Documentary says this is faster:
Response.ClearHeaders()
Response.ClearContent()
Response.ContentType = "text/txt"
Response.Charset = "UTF8" 'you should provide a charset!
Response.ContentEncoding = Encoding.UTF8

Response.AddHeader("Content-Disposition", "attachment;
filename=DetailedTrailerListing.csv")
Server.ScriptTimeout = 99999

Dim co As Boolean = CType(Request.QueryString("co"), Boolean)
Dim oo As Boolean = CType(Request.QueryString("oo"), Boolean)
Dim List As New _Class
Dim ds As DataSet = List.GetDetailedTrailerListing(oo, co)

Dim cl As DataColumn
For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
cl = ds.Tables(0).Columns(i)
If (i = 0) Then
Response.Write("""")
Else
Response.Write(",""")
End If
Response.Write(cl.ColumnName)
Response.Write("""")
Next

Response.Write(ControlChars.CrLf)
Response.Flush()

For Each rw As DataRow In ds.Tables(0).Rows
For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
If (i = 0) Then
Response.Write("""")
Else
Response.Write(",""")
End If
Response.Write(rw.Item(i))
Response.Write("""")
Next
Response.Write(ControlChars.CrLf)
Response.Flush()
Next

Response.Flush()
Response.Close()
When you have a huge amount of data and need some time to build the
response it is nice to use multiple Response.Flush()-Calls, here every
Row is flushed to the client who begins to download the CSV-File before
it is complete processed - this can make the Application feel much faster.

[Oleg Tkachenko [MVP]]

Doug Heeren wrote:

Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)

Never use XmlDataDocument with XSLT if you need any kind of efficiency,
it's very slow. Use XPathDocument, but that means you need to copy all
your data from a DataSet to XPathDocument.
Basically as your stylesheet is pretty simple you better go with custom
XmlWriter processing as others suggested.

--
Oleg Tkachenko [XML MVP, MCAD]
http://blog.tkachenko.com

[Doug Heeren]

Thank you. I didn't know that about the & operator.

I will have a bunch of these interfaces to write. Are there any controls or
objects from 3rd parties that you are aware of that can simplify this?

--
Doug Heeren
Project Manager
Annett Holdings, Inc.
[phone]
"Pascal Schmitt" wrote:

Hello!

Although it is ugly, you can optimize it: don't use the StringBuilder,
don't concat strings using & (because .NET will copy all the data and
create a new string - a waste of time and memory) write directly to the
Response - the Documentary says this is faster:
Response.ClearHeaders()
Response.ClearContent()
Response.ContentType = "text/txt"
Response.Charset = "UTF8" 'you should provide a charset!
Response.ContentEncoding = Encoding.UTF8

Response.AddHeader("Content-Disposition", "attachment;
filename=DetailedTrailerListing.csv")
Server.ScriptTimeout = 99999

Dim co As Boolean = CType(Request.QueryString("co"), Boolean)
Dim oo As Boolean = CType(Request.QueryString("oo"), Boolean)
Dim List As New _Class
Dim ds As DataSet = List.GetDetailedTrailerListing(oo, co)

Dim cl As DataColumn
For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
cl = ds.Tables(0).Columns(i)
If (i = 0) Then
Response.Write("""")
Else
Response.Write(",""")
End If
Response.Write(cl.ColumnName)
Response.Write("""")
Next

Response.Write(ControlChars.CrLf)
Response.Flush()

For Each rw As DataRow In ds.Tables(0).Rows
For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
If (i = 0) Then
Response.Write("""")
Else
Response.Write(",""")
End If
Response.Write(rw.Item(i))
Response.Write("""")
Next
Response.Write(ControlChars.CrLf)
Response.Flush()
Next

Response.Flush()
Response.Close()
When you have a huge amount of data and need some time to build the
response it is nice to use multiple Response.Flush()-Calls, here every
Row is flushed to the client who begins to download the CSV-File before
it is complete processed - this can make the Application feel much faster.