XML export for Excel table using XmlDocument

Question

I am trying to write the following into an XML document:

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

    <head>
     <xml> 
      <x:ExcelWorkbook>
       <x:ExcelWorksheets>
        <x:ExcelWorksheet>
         other code here
        </x:ExcelWorksheet>
       </x:ExcelWorksheets>
      </x:ExcelWorkbook>
     </xml>
    </head>
   </html>

However, if I use the following code, it strips out the 'x:'.

System.Xml.XmlDocument document = new System.Xml.XmlDocument();

System.Xml.XmlElement htmlNode = document.CreateElement("html");
htmlNode.SetAttribute("xmlns:o", "urn:schemas-microsoft-com:office:office");
htmlNode.SetAttribute("xmlns:x", "urn:schemas-microsoft-com:office:excel");
htmlNode.SetAttribute("xmlns", "http://www.w3.org/TR/REC-html40");
document.AppendChild(htmlNode);

System.Xml.XmlElement headNode = document.CreateElement("head");
htmlNode.AppendChild(headNode);
headNode.AppendChild(
  document.CreateElement("xml")).AppendChild(
  document.CreateElement("x:ExcelWorkbook"))).AppendChild(
  document.CreateElement("x:ExcelWorksheets")).AppendChild(
  document.CreateElement("x:ExcelWorksheet")).InnerText="other code here";

How can I stop this from happening?

How come you're trying to write the xml yourself and not using a standard library? — Yuriy Faktorovich
– Yuriy Faktorovich, Commented Jan 13, 2011 at 15:53
I did not know that there was an easier way. Do you have any useful links? — Mark
– Mark, Commented Jan 13, 2011 at 16:11
@Yuriy: I can't think of any library that is going to make it any easier than what @Mark is already doing... — James
– James, Commented Jan 13, 2011 at 18:01

James · Accepted Answer · 2011-01-14 11:58:37Z

2

Use the XmlDocument.CreateElement overload which allows you to specify the required prefix & namespace of the element e.g.

document.CreateElement("x", "ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

Example

From your comment I think you have went ahead and create every element in the way I have suggested. What I was referring to was how to get your Excel specific elements to have the correct namespace prefix. See my example below to see how to properly associate each of your elements with the correct namespace prefix:

// store ns in a local variable as it is going to be re-used
const string HTML_NS = "http://www.w3.org/TR/REC-html40";
const string EXCEL_NS = "urn:schemas-microsoft-com:office:excel";

XmlDocument doc = new XmlDocument();
// create the HTML element and set the HTML namespace as the default
XmlElement htmlElement = doc.CreateElement("html", HTML_NS);
// add the office/excel namespaces into the HTML element (so we can reference them later)
htmlElement.SetAttribute("xmlns:o", "urn:schemas-microsoft-com:office:office");
htmlElement.SetAttribute("xmlns:x", EXCEL_NS);
doc.AppendChild(htmlElement);
// associate the HEAD element with the HTML namespace as this is a HTML element
XmlElement headElement = doc.CreateElement("head", HTML_NS);    
htmlElement.AppendChild(headElement);
// now this is the one I am not too sure about as I don't know which 
// namespace you would associate the XML tag with. If you run the code 
// as it is specified here it will write out as <xml xmlns=""> which is
// correct as we aren't associating it with a namespace. As a workaround 
// you could associate it with the HTML NS if it is just for writing out 
XmlElement xmlElement = doc.CreateElement("xml", HTML_NS);   
headElement.AppendChild(xmlElement);
// create ExcelWorkbook element and associate with the excel namespace
// Unlike the HTML/HEAD tags we need to supply the prefix here because the Excel
// namespace is not the default
XmlElement xlWorkbookElement = doc.CreateElement("x", "ExcelWorkbook", EXCEL_NS);
xmlElement.AppendChild(xlWorkbookElement);
// create ExcelWorksheets element and associate with the excel namespace
XmlElement xlWorksheetsElement = doc.CreateElement("x", "ExcelWorksheets", EXCEL_NS);
xlWorkbookElement.AppendChild(xlWorksheetsElement);
// create the ExcelWorksheet element and associate with the excel namespace
XmlElement xlWorksheetElement = doc.CreateElement("x", "ExcelWorksheet", EXCEL_NS);
xlWorksheetsElement.AppendChild(xlWorksheetElement);

Hope that clears things up for you.

edited Jan 14, 2011 at 11:58

answered Jan 13, 2011 at 15:51

James

82.4k19 gold badges172 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Over a year ago

Thanks, but if the URN part was already declared up in the <html> tag, will it add the extra xmlns:x="URN..." to every entry?

James Over a year ago

@Mark: It won't add the namespace to every element as long as you define a prefix along with it like the example shows. You need to supply the namespace so the element knows which namespace the prefix is referring to.

Mark Over a year ago

This is so close to working - but 2 issues: 1) The first tag now becomes x:html and 2) every sub element now has xmlns="" inside the opening tag. Do you know how to correct either of these? (Mainly the first one!)

Michael Goldshteyn · Accepted Answer · 2011-01-13 15:49:41Z

2

Use the two argument version of CreateElement, and specify your namespace as the second argument. For example:

System.Xml.XmlElement myElement = document.CreateElement(
                                      "x:ExcelWorkbook",
                                      "urn:schemas-microsoft-com:office:excel");

answered Jan 13, 2011 at 15:49

Michael Goldshteyn

74.9k25 gold badges136 silver badges183 bronze badges

Collectives™ on Stack Overflow

XML export for Excel table using XmlDocument

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related