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_yyyy}", _startDate) + "_To_" + String.Format("{0:MM_dd_yyyy}", _endDate);
            if (fileName.IndexOfAny(System.IO.Path.GetInvalidFileNameChars()) != -1)
            {
                fileName = "Form_Export_From" + "_" + String.Format("{0:MM_dd_yyyy}", _startDate) + "_To_" + String.Format("{0:MM_dd_yyyy}", _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.Unicode);
            //Transform the XML using the stylesheet.
            XslCompiledTransform trans = new XslCompiledTransform();
            XsltArgumentList arguments = new XsltArgumentList();
            trans.Load(Server.MapPath("XSL.xsl"));
            Response.Clear();
            Response.AddHeader("Content-disposition", "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-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>
              <xsl:attribute name="bcolor"></xsl:attribute>
              <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!!!!!

bickes0724Asked:
Who is Participating?
 
kmartin7Connect With a Mentor Commented:
What's missing from your template are the worksheet styles. They look similar to the following:

 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Top"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>

Notice the first ss:ID="Default". By default, Alignment/@ss:Vertical is set to "Bottom". I changed it to read "Top". If you can properly add the <Styles> as listed above into your XSLT, you will have each cell default vertical alignment to "Top".
0
 
kmartin7Commented:
So I made a quick change to your XSLT that will make each cell default to "Top":
<?xml version="1.0"?>
<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:output indent="yes"/>

	<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">
				  <Styles>
			<Style ss:ID="Default" ss:Name="Normal">
				<Alignment ss:Vertical="Top"/>
				<Borders/>
				<Font/>
				<Interior/>
				<NumberFormat/>
				<Protection/>
			</Style>
		</Styles>
			<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:attribute>
							<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>

Open in new window

0
 
bickes0724Author Commented:
Thank you! That is working now. I am going to keep working on the rest...
0
 
kmartin7Commented:
If I have answered to your satisfaction, please accept and grade my answer.

Thanks!

kmartin7
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.