Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-01
4
Medium Priority
?
904 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
kmartin7 earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
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…

618 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