Background: I have taken an excel spreadsheet with the formatting I
want, saved it as XML, then converted it to an XSL document to use it
as a sort of template. Added some for-each select functionality and am
able to populate it with data from good old Northwind.
Everything is working great except one exasperating problem: the final
XML document does not include an xml declaration (i.e. <?xml
version="1.0"?>). It seems that the following line in my XSL document
should do the trick: <xsl:output method="xml" version="1.0"
encoding="utf-16"/> But sadly it does not.
I thought this was a simple problem but have been googling for two days
now with no luck. Can anyone shed some light?
The C# code and XSL follow (Uses the Northwind database):
private void button1_Click(object sender, System.EventArgs e)
{
string s = "SELECT TOP 10 * FROM [Order Details]";
string OutXML = @"C:\Documents and Settings\gbarker\My
Documents\ExcelXMLTest\Orders.xml";
string XSL = @"C:\Documents and Settings\gbarker\My
Documents\ExcelXMLTest\Orders.xsl";
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection("Server=WIN2KD EV1;Database=Northwind;USER
ID=SA;Password=password");
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand(s, cn);
cn.Open();
System.Data.DataTable dt = new DataTable();
System.Data.DataSet ds = new DataSet();
System.Data.SqlClient.SqlDataAdapter da = new
System.Data.SqlClient.SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
cn.Close();
ds.WriteXml(OutXML);
System.Xml.XPath.XPathDocument xpd = new
System.Xml.XPath.XPathDocument(OutXML);
System.IO.FileStream fs = new System.IO.FileStream(OutXML,
System.IO.FileMode.Create);
System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(fs,
System.Text.Encoding.Unicode);
System.Xml.Xsl.XslTransform xslt = new
System.Xml.Xsl.XslTransform();
try
{
xslt.Load(XSL);
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.Write(ex.Message);
}
try
{
xslt.Transform(xpd, null, xtw, null);
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.Write(ex.Message);
}
}
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output method="xml" version="1.0" encoding="utf-16"/>
<xsl:template match="/">
<xsl:processing-instruction
name="mso-application"><xsl:text>progid="Excel.Sheet"</xsl:text></xsl:processing-instruction>
<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">
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
</ExcelWorkbook>
<Worksheet ss:Name="Sheet1">
<Table>
<Column/>
<Column/>
<Column/>
<Row>
<Cell><Data ss:Type="String">Order ID</Data></Cell>
<Cell><Data ss:Type="String">Product ID</Data></Cell>
<Cell><Data ss:Type="String">Unit Price</Data></Cell>
<Cell><Data ss:Type="String">Quantity</Data></Cell>
<Cell><Data ss:Type="String">Discount</Data></Cell>
<Cell><Data ss:Type="String">Total</Data></Cell>
</Row>
<xsl:for-each select="//Table">
<Row>
<Cell><Data ss:Type="String"><xsl:value-of
select="OrderID"/></Data></Cell>
<Cell><Data ss:Type="Number"><xsl:value-of
select="ProductID"/></Data></Cell>
<Cell><Data ss:Type="Number"><xsl:value-of
select="UnitPrice"/></Data></Cell>
<Cell><Data ss:Type="Number"><xsl:value-of
select="Quantity"/></Data></Cell>
<Cell><Data ss:Type="Number"><xsl:value-of
select="Discount"/></Data></Cell>
<Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data
ss:Type="Number">0</Data></Cell>
</Row>
</xsl:for-each>
<Row>
<Cell/>
<Cell/>
<Cell/>
<Cell/>
<Cell/>
<Cell ss:Formula="=SUM(R2C:R[-1]C)"><Data
ss:Type="Number">0</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<DoNotDisplayGridlines/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>0</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
