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(o bject sender, System.EventArg s e)
{
string s = "SELECT TOP 10 * FROM [Order Details]";
string OutXML = @"C:\Documen ts and Settings\gbarke r\My
Documents\Excel XMLTest\Orders. xml";
string XSL = @"C:\Documen ts and Settings\gbarke r\My
Documents\Excel XMLTest\Orders. xsl";
System.Data.Sql Client.SqlConne ction cn = new
System.Data.Sql Client.SqlConne ction("Server=W IN2KDEV1;Databa se=Northwind;US ER
ID=SA;Password= password");
System.Data.Sql Client.SqlComma nd cmd = new
System.Data.Sql Client.SqlComma nd(s, cn);
cn.Open();
System.Data.Dat aTable dt = new DataTable();
System.Data.Dat aSet ds = new DataSet();
System.Data.Sql Client.SqlDataA dapter da = new
System.Data.Sql Client.SqlDataA dapter();
da.SelectComman d = cmd;
da.Fill(ds);
cn.Close();
ds.WriteXml(Out XML);
System.Xml.XPat h.XPathDocument xpd = new
System.Xml.XPat h.XPathDocument (OutXML);
System.IO.FileS tream fs = new System.IO.FileS tream(OutXML,
System.IO.FileM ode.Create);
System.Xml.XmlT extWriter xtw = new System.Xml.XmlT extWriter(fs,
System.Text.Enc oding.Unicode);
System.Xml.Xsl. XslTransform xslt = new
System.Xml.Xsl. XslTransform();
try
{
xslt.Load(XSL);
}
catch (System.Excepti on ex)
{
System.Diagnost ics.Debug.Write (ex.Message);
}
try
{
xslt.Transform( xpd, null, xtw, null);
}
catch (System.Excepti on ex)
{
System.Diagnost ics.Debug.Write (ex.Message);
}
}
<?xml version="1.0"?>
<xsl:styleshe et 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:processi ng-instruction
name="mso-application"><x sl:text>progid= "Excel.Shee t"</xsl:text></xsl:processing-instruction>
<Workbook xmlns="urn:sche mas-microsoft-com:office:spre adsheet"
xmlns:o="urn:sc hemas-microsoft-com:office:offi ce"
xmlns:x="urn:sc hemas-microsoft-com:office:exce l"
xmlns:ss="urn:s chemas-microsoft-com:office:spre adsheet"
xmlns:html="htt p://www.w3.org/TR/REC-html40">
<ExcelWorkboo k xmlns="urn:sche mas-microsoft-com:office:exce l">
</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:valu e-of
select="OrderID "/></Data></Cell>
<Cell><Data ss:Type="Number "><xsl:valu e-of
select="Product ID"/></Data></Cell>
<Cell><Data ss:Type="Number "><xsl:valu e-of
select="UnitPri ce"/></Data></Cell>
<Cell><Data ss:Type="Number "><xsl:valu e-of
select="Quantit y"/></Data></Cell>
<Cell><Data ss:Type="Number "><xsl:valu e-of
select="Discoun t"/></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="=SU M(R2C:R[-1]C)"><Data
ss:Type="Number ">0</Data></Cell>
</Row>
</Table>
<WorksheetOptio ns xmlns="urn:sche mas-microsoft-com:office:exce l">
<Print>
<ValidPrinterIn fo/>
<HorizontalReso lution>600</HorizontalResol ution>
<VerticalResolu tion>600</VerticalResolut ion>
</Print>
<Selected/>
<DoNotDisplayGr idlines/>
<FreezePanes/>
<FrozenNoSpli t/>
<SplitHorizonta l>1</SplitHorizontal >
<TopRowBottomPa ne>1</TopRowBottomPan e>
<ActivePane>2 </ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>0</ActiveRow>
</Pane>
</Panes>
<ProtectObjects >False</ProtectObjects>
<ProtectScenari os>False</ProtectScenario s>
</WorksheetOption s>
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
