In a recent post, I talked about quickly transforming a .NET DataSet to Excel XML format using an XSL transform. One of the problems I encountered is Excel's handling of the DateTime data type. DataSets serialize a DateTime data type in UTC format. Excel does not recognize this format; specifically, it does not handle the time zone information that is part of the UTC format ...
To solve this problem, I created an XSL extension object that implements a custom routine to strip the trailing ten characters of time zone information. Here's a code sample:
private void button1_Click(object sender, System.EventArgs e)
{
// Get a DataSet ...
string conn = "<your connection string here ...>";
DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text,
"select getdate() as ADate, 1.3 as ANumber1, 2.7 as ANumber2, 'Hello world' as AString");
// Load the DataSet in an XmlDataDocument ...
XmlDataDocument xmlDoc = new XmlDataDocument(ds);
// Load the transform ...
XslTransform xslTran = new XslTransform();
xslTran.Load("transform.xslt");
// Add the DateConverter extension object so we can call the FormatDateTime()
// method from the transform ...
DateConverter dc = new DateConverter();
XsltArgumentList args = new XsltArgumentList();
args.AddExtensionObject("urn:my-scripts", dc);
// Create an XmlTextWriter for the output Excel workbook ...
XmlTextWriter tw = new XmlTextWriter("test.xls", Encoding.UTF8);
tw.Formatting = Formatting.Indented;
tw.Indentation = 3;
// Transform the XmlDataDocument ...
tw.WriteStartDocument();
xslTran.Transform(xmlDoc, args, tw, null);
tw.Close();
}
}
public class DateConverter
{
public DateConverter() {}
// Excel cannot handle DateTime in UTC format. We'll simply strip
// the last 10 characters that contain the time zone info ...
public string FormatDateTime(string data)
{
return data.Remove(23, 10);
}
}
To run this sample, create a Windows Forms application with a button. Use the button click handler and DateConverter class shown above. The following are the contents of a "transform.xslt" file that you put in the same directory as your executable:
<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:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:custom="urn:my-scripts" >
<xsl:template match="NewDataSet">
<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="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Font ss:Bold="1"/>
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s22">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s23">
<NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(@_)"/>
</Style>
<Style ss:ID="s24">
<NumberFormat ss:Format="Short Date"/>
</Style>
</Styles>
<Worksheet ss:Name="My Worksheet">
<Table ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
<xsl:attribute name="ss:ExpandedRowCount" >
<xsl:value-of select="count(Table)+1"/>
</xsl:attribute>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Column ss:AutoFitWidth="1"/>
<Row>
<Cell ss:StyleID="s21">
<Data ss:Type="String">ADate</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">AString</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">ANumber1</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">ANumber2</Data>
</Cell>
<Cell ss:StyleID="s21">
<Data ss:Type="String">Sum</Data>
</Cell>
</Row>
<xsl:apply-templates select="Table"/>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match="Table">
<Row>
<Cell ss:StyleID="s24">
<Data ss:Type="DateTime">
<xsl:value-of select="custom:FormatDateTime(ADate)"/>
</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">
<xsl:value-of select="AString"/>
</Data>
</Cell>
<Cell ss:StyleID="s23">
<Data ss:Type="Number">
<xsl:value-of select="ANumber1"/>
</Data>
</Cell>
<Cell ss:StyleID="s23">
<Data ss:Type="Number">
<xsl:value-of select="ANumber2"/>
</Data>
</Cell>
<Cell ss:StyleID="s23" ss:Formula="=SUM(RC[-2]:RC[-1])">
<Data ss:Type="Number" />
</Cell>
</Row>
</xsl:template>
</xsl:stylesheet>
Note the use of the "urn:my-scripts" namespace which invokes the FormatDateTime() method of the DateConverter class in the transform ...