Not getting anything out of my xmlNodes in C#

Question

I've write a quick code to try getting elements from an XML file and put them in an excel table but I don't get anyhting out of my nodes. I don't understand why.

I've been trying different mean of getting what I need from my nodes using the microsoft documentation of XMLDocument but the result is always the same, it's printing me empty lines.

    [STAThread]
    static void Main(string[] args)
    {
        OpenFileDialog openFileDialog = new OpenFileDialog();
        XmlDocument xmlDocument = new XmlDocument();
        System.Data.DataTable table = new System.Data.DataTable();
        Microsoft.Office.Interop.Excel.Application excel;
        Microsoft.Office.Interop.Excel.Workbook workBook;
        Microsoft.Office.Interop.Excel.Worksheet workSheet;
        int rowCount = 1;

        openFileDialog.Filter = "XML Files|*.xml";
        openFileDialog.Title = "Select the SMS backup";
        if (openFileDialog.ShowDialog() == DialogResult.OK)
        {
            xmlDocument.PreserveWhitespace = true;
            try
            {
                xmlDocument.Load(openFileDialog.FileName);
            }
            catch (System.IO.FileNotFoundException)
            {
            }
            table.Columns.Add("Contact name", typeof(string));
            table.Columns.Add("Date", typeof(DataFormats));
            table.Columns.Add("Message", typeof(string));
            foreach (XmlNode node in xmlDocument.DocumentElement.ChildNodes)
            {
                System.Diagnostics.Debug.WriteLine(node.InnerText);
                if (node.InnerText.Contains("contact_name"))
                {
                    table.Rows.Add(
                        node.Attributes["contact_name"]?.InnerText,
                        node.Attributes["readable_date"]?.InnerText,
                        node.Attributes["body"]?.InnerText
                        );
                }
            }
            try
            {
                excel = new Microsoft.Office.Interop.Excel.Application
                {
                    Visible = true,
                    DisplayAlerts = false
                };
                workBook = excel.Workbooks.Add(Type.Missing);
                workSheet = (Microsoft.Office.Interop.Excel.Worksheet) workBook.ActiveSheet;
                workSheet.Name = "SMS backup";
                foreach (System.Data.DataRow dataRow in table.Rows)
                {
                    rowCount += 1;
                    for (int i = 1; i <= table.Columns.Count; i++)
                    {
                        workSheet.Cells[rowCount, i] = dataRow[i - 1].ToString();
                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                workSheet = null;
                workBook = null;
            }
        }
    }

Use EPplus, read your xml file into a datatable, output to excel. It'll take about 10 lines of code — Caius Jard, Dec 23 '18 at 22:58

score 1 · Accepted Answer · answered Dec 24 '18 at 05:17

Following up on my comment, here's a bit more in depth advice. I'm writing this on a cell phone so please forgive that it's not a full working code

Right click your solution node in solution explorer and choose Manage Nuget packages for solution

Install epplus - it creates excel files directly (they're actually just xml files inside a zip file named xlsx) without needing excel installed interop can be a pain in the backside and is best avoided

Read your xml document into a dataset:

DataSet da = new DataSet();
da.ReadXml(path to your file);

Place a breakpoint on the readxml line and launch the app. Step over the readxml then point to dsand click the magnifying glass that appears in the tooltip

Familiarise yourself with how the xml document is presented in the DataSet - nodes that have child nodes tend to be represented as separated tables with relations linking them together, so you might end up, for an xml like

xml
  parentnode1
    childnode1
  parentnode2
    childnode2
/xml

You'd have two tables, called parentnode and childnode, and you'd read them in a loopset like:

foreach(var ro in da.Tables["parentnode"].Rows)
  //do stuff with parent rows

  //iterate child rows
  foreach(var cro in ro.GetChildRows()){
    //do stuff with child rows
  }
}

It doesn't look like your xml is structured this way right now but it's not easy to tell

So, you now have your xml document as a set of tables in a DataSet and you want them as an excel sheet. Epplus can create an excel file from a datatable (a DataSet is a collection of datatables) in a few lines of code, see Export DataTable to excel with EPPlus

If your DataSet only contains 1 table then the job is pretty much over. If it has multiple tables I recommend you make yourself a new DataTable then loop over the DataSet picking values out of various ds.Tables["table name here"] and populating your DataTable

Thus to implement your program implement this set of comments as code

//read xml file to DataSet - 2 lines of code

//EITHER xml is flat structure, results in one datatable in the set 
//so just remove or rename columns as required - few lines

//OR DataSet has 3 tables, make a new datatable and loop over the 3, 
//populating the new one, to flatten it - few lines

//use epplus to turn the single datatable to an excel sheet - 4 lines

Problem solved, hopefully in around 10 lines of code

Not getting anything out of my xmlNodes in C#

1 Answers1