Generating a XML Excel document using XDocument

Question

I'm trying to generate a XML Excel document like this

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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" /></Style>
 </Styles>
 <Worksheet ss:Name="Worksheet Name">
  <Table>
   <Row ss:AutoFitHeight="0">
    <Cell>
     <Data ss:Type="String">Test</Data>
    </Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

My current result look like this:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<ss: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 xmlns="">
    <Style ss:ID="Default" />
  </Styles>
  <Worksheet ss:Name="Worksheet Name" xmlns="">
    <Table>
      <Row ss:AutoFitHeight="0">
        <Cell>
          <Data ss:Type="String">Test</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</ss:Workbook>

I have tried a lot of different solutions without any luck. I need to replace ss:Workbook with Workbook and to get rid of all the xmlns="".

My current code looks like this (LinqPad):

void Main()
{
    XNamespace ss = "urn:schemas-microsoft-com:office:spreadsheet";
    XNamespace ns = "urn:schemas-microsoft-com:office:spreadsheet";

    var workbook = new XElement(ns + "Workbook");
    workbook.Add(new XAttribute("xmlns", "urn:schemas-microsoft-com:office:spreadsheet"));
    workbook.Add(new XAttribute(XNamespace.Xmlns + "o", "urn:schemas-microsoft-com:office:office"));
    workbook.Add(new XAttribute(XNamespace.Xmlns + "x", "urn:schemas-microsoft-com:office:excel"));
    workbook.Add(new XAttribute(XNamespace.Xmlns + "ss", "urn:schemas-microsoft-com:office:spreadsheet"));
    workbook.Add(new XAttribute(XNamespace.Xmlns + "html", "http://www.w3.org/TR/REC-html40"));

    var styles = new XElement("Styles");
    workbook.Add(styles);
    var style = new XElement("Style");
    style.Add(new XAttribute(ss + "ID", "Default"));
    styles.Add(style);

    var worksheet = new XElement("Worksheet");
    worksheet.Add(new XAttribute(ss + "Name", "Worksheet Name"));
    workbook.Add(worksheet);

    var table = new XElement("Table");
    worksheet.Add(table);

    var row = new XElement("Row");
    row.Add(new XAttribute(ss + "AutoFitHeight", 0));
    table.Add(row);

    var cell = new XElement("Cell");
    var data = new XElement("Data");
    data.Add(new XAttribute(ss + "Type", "String"));
    data.Value = "Test";
    cell.Add(data);
    row.Add(cell);

    var document = new XDocument(new XDeclaration("1.0", "", null));
    document.Add(new XProcessingInstruction("mso-application", "progid=\"Excel.Sheet\""));
    document.Add(workbook);

    var sw = new CustomStringWriter();
    document.Save(sw);
    sw.ToString().Dump();
    //sw.ToString().Replace("ss:Workbook", "Workbook").Replace("xmlns=\"\"", "").Dump();
}

// Define other methods and classes here

class CustomStringWriter : StringWriter
{
    public override Encoding Encoding
    {
        get
        {
            return null;
        }
    }
}

Any suggestions?

My best hack at the moment is the leave out the namespace when adding the Workbook element and not adding the xmlns namespace. After converting the document to a string, I manually add the xmlns namespace to the Workbook element. It is not pretty but it works. — halvorsen, Jan 19 '16 at 07:57
This is a duplicate of http://stackoverflow.com/questions/12075157/unable-to-format-xml-for-excel?rq=1 for which the suggested solution is to use Open XML. This is not my preferred solution, though... — halvorsen, Jan 19 '16 at 08:07

Charles Mager · Answer 1 · 2016-01-19T08:48:11.317

1

Your issue is most probably that you haven't understood that default namespaces are inherited from parent elements. Take this XML, for example:

<parent xmlns="http://namespace.com/uri">
    <child />
</parent>

In this, child also has the namespace http://namespace.com/uri.

So the reason your Style, Worksheet elements etc. all have xmlns="" is because you've explicitly said these have no namespace.

You should amend you creation of all these elements to include the correct namespace, e.g:

var styles = new XElement(ss + "Styles");

As an aside, I'd also note that LINQ to XML supports a much more declarative approach than you're currently taking. You can pass all an element's content via the constructor. So you could rewrite your styles bit:

var styles = new XElement(ss + "Styles",
    new XElement(ss + "Style",
        new XAttribute("ID", "Default")
        )
    );

See this fiddle for your sample fixed & re-written in this style.

edited Jan 19 '16 at 08:48

answered Jan 19 '16 at 08:39

Charles Mager

25,735
2
35
45

I know the namespace is inherited, and that is the reason for ending up with xmlns="". Prepending with namespace does only solve half of my problem. Now I end up with the namespace everywhere. I don't even want it on the Workbook element. Hence, your solution is not working for me. I'm not a big fan of declarative approach that's why I don't use it ;-) The code example is put together from snippets from a larger class. – halvorsen Jan 19 '16 at 13:27
If you're referring to the prefix, you can't remove these due to the way LINQ to XML works when writing XML. As an example, see [this fiddle](https://dotnetfiddle.net/cAb189) - simply parsing and writing your 'required' XML will add the `ss` prefix to all elements in that namespace. Be assured the two are semantically identical, however. – Charles Mager Jan 19 '16 at 13:35
If you re-order to put the default namespace after the duplicate `ss` prefix, then the LINQ to XML namespace lookup for elements will reach that one first and use that (see [this fiddle](https://dotnetfiddle.net/mkhVgW)). That gets you closer if you prefer how it looks. I can only emphasise that all 3 repesentations are semantically identical. – Charles Mager Jan 19 '16 at 13:54
I really don't care how it looks - I care whether Excel accepts the XML :-) Excel accepts the result of your second fiddle. I think I will go with that one or just manually insert the xmlsns into the root. Haven't decided yet but thanks for the help. – halvorsen Jan 19 '16 at 16:24

score 0 · Answer 2 · answered Dec 07 '21 at 09:16

I had the same problem, and here's my solution

Did you generate SAP Value Mapping template files?

            var xmlstr="<Workbook...."
            var xdoc = XDocument.Parse(xmlstr);
            using (TextWriter writer = new StringWriter())
            {
                xdoc.Save(writer);
                var output = writer.ToString();
                // replace default namespace prefix:<ss:
                string result = Regex.Replace(Regex.Replace(output, "<ss:", "<"), "</ss:", "</");
                return Encoding.UTF8.GetBytes(result);
            }

Generating a XML Excel document using XDocument

2 Answers2