Solved

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

Posted on 2010-09-01
4
896 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

774 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