?
Solved

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

Posted on 2010-09-01
4
Medium Priority
?
902 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

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.
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
Suggested Courses

777 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