CODEWITHSUNDEEP
c#xmlxml-namespaces
Wasabi
Wasabi

Reputation: 3061

XmlWriter with namespaces and .WriteNode()

I am writing some code which will create an Excel .xml file. The file is highly structured and formatted in a constant way, so there are some 3000 lines of the file which do not change. After seeing the answer given to this question, I adopted this solution to create the file.

The code is as follows

XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.Indent = true;
XmlWriter writer = XmlWriter.Create("results.xml", writerSettings);

writer.WriteStartDocument();
writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
writer.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
writer.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
writer.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
writer.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");
string str = "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\n  <Author>Bananal</Author>\n  <LastAuthor>PedroK</LastAuthor>\n  <LastPrinted>2013-08-01T22:10:44Z</LastPrinted>\n  <Created>2013-07-17T21:18:47Z</Created>\n  <LastSaved>2013-12-16T17:44:10Z</LastSaved>\n  <Version>14.00</Version>\n </DocumentProperties>";
XmlReaderSettings readerSettings = new XmlReaderSettings();
readerSettings.IgnoreWhitespace = true;
XmlReader reader = XmlReader.Create(new StringReader(str), readerSettings);
writer.WriteNode(reader, true);
str = "<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\n  <AllowPNG/>\n </OfficeDocumentSettings>";
reader = XmlReader.Create(new StringReader(str), readerSettings);
writer.WriteNode(reader, true);
str = "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n  <WindowHeight>12432</WindowHeight>\n  <WindowWidth>23256</WindowWidth>\n  <WindowTopX>0</WindowTopX>\n  <WindowTopY>0</WindowTopY>\n  <TabRatio>755</TabRatio>\n  <ActiveSheet>9</ActiveSheet>\n  <ProtectStructure>False</ProtectStructure>\n  <ProtectWindows>False</ProtectWindows>\n </ExcelWorkbook>";
reader = XmlReader.Create(new StringReader(str), readerSettings);
writer.WriteNode(reader, true);
str = "<Styles>\n  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\n   <Alignment ss:Vertical=\"Bottom\"/>\n   <Borders/>\n   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>\n   <Interior/>\n   <NumberFormat/>\n   <Protection/>\n  </Style></Styles>"
reader = XmlReader.Create(new StringReader(str), readerSettings);
writer.WriteNode(reader, true);

In this way, I have batched everything as efficiently as I can think of. This works well until the last segment (regarding the <Styles>), where writer.WriteNode() throws an exception saying 'ss' is an undeclared namespace. Have I done something wrong regarding the definition of the namespaces or is there something else I have to do with the raw string to make it work with .WriteNode()?

Upvotes: 1

Views: 1032

Answers (1)

rene
rene

Reputation: 42414

If you use an XML Reader the xml supplied needs to be valid. If you use namespace aliases on attibutes or elements it is required to also qualify the namespace.

Quick fix solution
You have to add the namespaces ss and x to your Styles tag like so:

 str = "<Styles xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\n  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\n   <Alignment ss:Vertical=\"Bottom\"/>\n   <Borders/>\n   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>\n   <Interior/>\n   <NumberFormat/>\n   <Protection/>\n  </Style></Styles>";
 reader = XmlReader.Create(new StringReader(str), readerSettings);
 writer.WriteNode(reader, true);

Alternative solution
Provide a parserContext with the namespaces needed to the reader. The pros is that you don't have to change the xmlmarkup you currently have.

NameTable nt = new NameTable();
XmlNamespaceManager nsmgr = new XmlNamespaceManager(nt);
nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
nsmgr.AddNamespace("x", "urn:schemas-microsoft-com:office:excel");

//Create the XmlParserContext (this can be reused in every reader!)
XmlParserContext ctx = new XmlParserContext(null, nsmgr, null, XmlSpace.None);

/* other code here */

str = "<Styles >\n  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\n   <Alignment ss:Vertical=\"Bottom\"/>\n   <Borders/>\n   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>\n   <Interior/>\n   <NumberFormat/>\n   <Protection/>\n  </Style></Styles>";
reader = XmlReader.Create(new StringReader(str), readerSettings, ctx); // NOTICE ctx here
writer.WriteNode(reader, true);

Side note
It looks like you are producing a file for use by Excel. Did you consider The Open XML SDK?

Upvotes: 1

Related Questions