bickes0724
asked on
Add styles like vertical-align and max-column-width to XSLT
Dear Experts,
I have an XmlDocument that I am generating dynamically based on a query to a SQL server database. I am exporting the result to a Excel file with the following code.
DataTable dataTable = GetPosts();
XmlDocument xmlDoc = BuildXml(dataTable);
String fileName;
fileName = lblFormCode.Text + "_Export_" + String.Format("{0:MM_dd_yy yy}", _startDate) + "_To_" + String.Format("{0:MM_dd_yy yy}", _endDate);
if (fileName.IndexOfAny(Syste m.IO.Path. GetInvalid FileNameCh ars()) != -1)
{
fileName = "Form_Export_From" + "_" + String.Format("{0:MM_dd_yy yy}", _startDate) + "_To_" + String.Format("{0:MM_dd_yy yy}", _endDate);
}
//Create the FileStream to write with.
MemoryStream memStream = new MemoryStream();
//Create an XmlTextWriter for the FileStream.
XmlTextWriter xtw = new XmlTextWriter(memStream, System.Text.Encoding.Unico de);
//Transform the XML using the stylesheet.
XslCompiledTransform trans = new XslCompiledTransform();
XsltArgumentList arguments = new XsltArgumentList();
trans.Load(Server.MapPath( "XSL.xsl") );
Response.Clear();
Response.AddHeader("Conten t-disposit ion", "attachment; filename=" + fileName + ".xls");
Response.ContentType = "application/vnd.ms-excel" ;
trans.Transform(xmlDoc, arguments, Response.OutputStream);
Response.End();
PROBLEM:
I have an XLST stylesheet that I have pasted below. I need the vertical alignment of every column to be at the top. I need column widths and heights to be auto, and headers to be bold. I would like an alternating row color (any color, I can change it later). Finally, I have two columns (Recipe Ingredients, and Instructions) that can get quite lengthy. I went through some trouble to preserve the users page breaks in the XML. Right now, those page breaks are not being shown.
Any help would be appreciated
<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microso ft-com:off ice:spread sheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-m icrosoft-c om:xslt"
xmlns:user="urn:my-scripts "
xmlns:o="urn:schemas-micro soft-com:o ffice:offi ce"
xmlns:x="urn:schemas-micro soft-com:o ffice:exce l"
xmlns:ss="urn:schemas-micr osoft-com: office:spr eadsheet" >
<xsl:template match="/">
<Workbook xmlns="urn:schemas-microso ft-com:off ice:spread sheet"
xmlns:o="urn:schemas-micro soft-com:o ffice:offi ce"
xmlns:x="urn:schemas-micro soft-com:o ffice:exce l"
xmlns:ss="urn:schemas-micr osoft-com: office:spr eadsheet"
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>
<xsl:attribute name="bcolor"></xsl:attrib ute>
<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>
Thanks in advance!!!!!
I have an XmlDocument that I am generating dynamically based on a query to a SQL server database. I am exporting the result to a Excel file with the following code.
DataTable dataTable = GetPosts();
XmlDocument xmlDoc = BuildXml(dataTable);
String fileName;
fileName = lblFormCode.Text + "_Export_" + String.Format("{0:MM_dd_yy
if (fileName.IndexOfAny(Syste
{
fileName = "Form_Export_From" + "_" + String.Format("{0:MM_dd_yy
}
//Create the FileStream to write with.
MemoryStream memStream = new MemoryStream();
//Create an XmlTextWriter for the FileStream.
XmlTextWriter xtw = new XmlTextWriter(memStream, System.Text.Encoding.Unico
//Transform the XML using the stylesheet.
XslCompiledTransform trans = new XslCompiledTransform();
XsltArgumentList arguments = new XsltArgumentList();
trans.Load(Server.MapPath(
Response.Clear();
Response.AddHeader("Conten
Response.ContentType = "application/vnd.ms-excel"
trans.Transform(xmlDoc, arguments, Response.OutputStream);
Response.End();
PROBLEM:
I have an XLST stylesheet that I have pasted below. I need the vertical alignment of every column to be at the top. I need column widths and heights to be auto, and headers to be bold. I would like an alternating row color (any color, I can change it later). Finally, I have two columns (Recipe Ingredients, and Instructions) that can get quite lengthy. I went through some trouble to preserve the users page breaks in the XML. Right now, those page breaks are not being shown.
Any help would be appreciated
<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microso
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-m
xmlns:user="urn:my-scripts
xmlns:o="urn:schemas-micro
xmlns:x="urn:schemas-micro
xmlns:ss="urn:schemas-micr
<xsl:template match="/">
<Workbook xmlns="urn:schemas-microso
xmlns:o="urn:schemas-micro
xmlns:x="urn:schemas-micro
xmlns:ss="urn:schemas-micr
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>
<xsl:attribute name="bcolor"></xsl:attrib
<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>
Thanks in advance!!!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! That is working now. I am going to keep working on the rest...
If I have answered to your satisfaction, please accept and grade my answer.
Thanks!
kmartin7
Thanks!
kmartin7
Open in new window