Solved

Add styles like vertical-align and max-column-width to XSLT

Posted on 2010-09-01
4
895 Views
Last Modified: 2013-11-18
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!!!!!

0
Comment
Question by:bickes0724
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
kmartin7 earned 500 total points
ID: 33580397
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
 
LVL 11

Expert Comment

by:kmartin7
ID: 33580472
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
 

Author Comment

by:bickes0724
ID: 33581215
Thank you! That is working now. I am going to keep working on the rest...
0
 
LVL 11

Expert Comment

by:kmartin7
ID: 33581583
If I have answered to your satisfaction, please accept and grade my answer.

Thanks!

kmartin7
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now