From Office XML to DataTable

Question

n.b. this is not an Office Open XML document, it's the old kind of Office XML document that was designed circa 2003.

https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats#Excel_XML_Spreadsheet_example

I understand that XML can be loaded into a DataSet easily, and this format of XML document does import to a DataSet already.

However it does not present itself in a very accessible way. What I need is essentially a DataTable representation of how the data is arranged when you view the XML as it opens in Excel in a Worksheet tab.

Does anyone know of any extensions to C# that make this possible? Otherwise, the solution would be in processing all the XML and manually constructing the DataTable - but I would be surprised if no one has needed to access data in an Office XML file as a DataTable version of a Worksheet.

Can't you use a library for that? Parsing the office XML is very tedious and is often dependend on culture settings of the office application itself (Like Headers have the Tag "Berschrift" in German versions omitting the Umlaut). I can recommend EPPLUS for XLSX and NPOI for XLS. When you need something else than Excel, there are also ways and libraries, but I know EPPLUS and NPOI are good — Jan W., Sep 17 '13 at 17:56
The software creating these XML files is commercial and I can't change what it spits out. I have to deal with Office XML in my program. — deed02392, Sep 17 '13 at 18:20
Well you don't need to change it. The xml is only packed into a zip library. Do you use this XML to create Excel/Word or other Office based files? — Jan W., Sep 17 '13 at 18:23
Ah, no you're confusing the 2007+ Office XML. This is the 2003 standard as referenced at Wikipedia in the question. — deed02392, Sep 17 '13 at 18:25
Got it, excuse me, sorry. I confused them, nevermind. Have you found this thread yet? http://stackoverflow.com/questions/7089039/how-to-load-old-microsoft-office-xml-file-excel-using-java — Jan W., Sep 17 '13 at 18:28
https://stackoverflow.com/questions/12897082/how-to-read-excel-xml-c — Stephen Turner, Aug 14 '19 at 10:17

score -1 · Answer 1 · answered Sep 17 '13 at 17:37

Maybe will help this example for simple xml file:

using System;
using System.Xml;

namespace ReadXMLfromFile
{
/// <summary>
/// Общее описание класса Class1.
/// </summary>
class Class1
{
    static void Main(string[] args)
    {
        XmlTextReader reader = new XmlTextReader ("books.xml");
        while (reader.Read())  
        {
            switch (reader.NodeType)  
            {
                case XmlNodeType.Element: // Узел является элементом.
                    Console.Write("<" + reader.Name);
                    Console.WriteLine(">");
                    break;
                case XmlNodeType.Text: // Вывести текст в каждом элементе.
                    Console.WriteLine (reader.Value);
                    break;
                case XmlNodeType.EndElement: // Вывести конец элемента.
                    Console.Write("</" + reader.Name);
                    Console.WriteLine(">");
                    break;
            }
        }
        Console.ReadLine();
    }
}
}

This is a simple XML reader. I know how to read and process XML but it's a solution specifically for Office XML I need. — deed02392, Sep 17 '13 at 18:21

From Office XML to DataTable

1 Answers1