How to convert a XML Schema file with a header to an Excel file using C#?

Question

XML File:

<?xml version="1.0" encoding="utf-8"?>
<Data xmlns:xsd="http://www.hbjhgjgjg.com/1889/XMLSchema" 
xmlns:xsi="http://www.hbjhgjgjg.com/1889/XMLSchema-instance" 
xmlns="http://www.ytutu.com/data/schema/XXXa/1829">
    <Header>
        <ContentDate>2018-03-16T00:00:37.511-06:00</ContentDate>
        <Originator>12344</Originator>
        <FileContent>abc</FileContent>
        <RecordCount>890</RecordCount>
    </Header>
    <Records>
        <XXXRecord>
            <XXX>8888834</XXX>
            <Entity>
                <Name xml:lang="es">NAME CV</Name>
                <OtherEntityNames>
                    <OtherEntityName xml:lang="es" type="OTHER NAME"></OtherEntityName>
                    <OtherEntityName xml:lang="es" type="OTHER NAME1">ABGT</OtherEntityName>
                </OtherEntityNames>
                <Address xml:lang="es">
                    <FirstAddressLine>898 RTT</FirstAddressLine>
                    <AddressNumber>32</AddressNumber>
                    <AdditionalAddressLine>eddff</AdditionalAddressLine>
                    <AdditionalAddressLine>hghg</AdditionalAddressLine>
                    <City>CITY</City>
                    <Region>REGION</Region>
                    <Country>COUNTRY</Country>
                    <PostalCode>79799</PostalCode>
                </Address>
                <FirstAddress xml:lang="es">
                    <FirstAddressLine>rafgaf</FirstAddressLine>
                    <AddressNumber>32</AddressNumber>
                    <AdditionalAddressLine>gggg</AdditionalAddressLine>
                    <AdditionalAddressLine>jjgjgg</AdditionalAddressLine>
                    <City>CITY</City>
                    <Region>REGION</Region>
                    <Country>COUNTRY</Country>
                    <PostalCode>34433</PostalCode>
                </FirstAddress>
                <OtherAddresses>
                    <OtherAddress xml:lang="es" type="ADDRESS">
                        <FirstAddressLine>ggh</FirstAddressLine>
                        <AddressNumber>32</AddressNumber>
                        <AdditionalAddressLine>guhgu</AdditionalAddressLine>
                        <AdditionalAddressLine>tryryry</AdditionalAddressLine>
                        <City>CITY</City>
                        <Region>REGION</Region>
                        <Country>COUNTRY</Country>
                        <PostalCode>09090</PostalCode>
                    </OtherAddress>
                </OtherAddresses>
                <Confirmation>
                    <ID>77878</ID>
                    <EntityID>dsfdd</EntityID>
                </Confirmation>
            </Entity>
        </Record>
and many similar records below...

I used the below code to convert XML to EXCEL file. I am using .net windows application. But the output excel file shows only the header data with 4 row counts. It is not reading all the values from the dataset. The dataset has all the values.

Is there any other method I can implement this?

private void Convert_Click(object sender, EventArgs e)
{
    progressBar1.Value = 0;
    if (checkBox1.Checked && txtCustomeFileName.Text != "" && txtXmlFilePath.Text != "") // using Custome Xml File Name  
    {
        if (File.Exists(txtXmlFilePath.Text))
        {
            string CustXmlFilePath = Path.Combine(new FileInfo(txtXmlFilePath.Text).DirectoryName, txtCustomeFileName.Text); // Creating Path for Xml Files  
            System.Data.DataTable dt = CreateDataTableFromXml(txtXmlFilePath.Text);
            ExportDataTableToExcel(dt, CustXmlFilePath);

            MessageBox.Show("Conversion Completed!!");
        }

    }
    else if (!checkBox1.Checked || txtXmlFilePath.Text != "") // Using Default Xml File Name  
    {
        if (File.Exists(txtXmlFilePath.Text))
        {
            FileInfo fi = new FileInfo(txtXmlFilePath.Text);
            string XlFile = fi.DirectoryName + "\\" + fi.Name.Replace(fi.Extension, ".xlsx");
            System.Data.DataTable dt = CreateDataTableFromXml(txtXmlFilePath.Text);
            ExportDataTableToExcel(dt, XlFile);

            MessageBox.Show("Conversion Completed!!");
        }
    }
    else
    {
        MessageBox.Show("Please Fill Required Feilds!!");
    }
}

// Creating DataTable With Xml Data  
public System.Data.DataTable CreateDataTableFromXml(string XmlFile)
{
    System.Data.DataTable Dt = new System.Data.DataTable();
    try
    {
        DataSet ds = new DataSet();
        ds.ReadXml(XmlFile);
        Dt.Load(ds.CreateDataReader());
    }
    catch (Exception ex)
    {
    }
    return Dt;
}

private void ExportDataTableToExcel(System.Data.DataTable table, string Xlfile)
{
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    Workbook book = excel.Application.Workbooks.Add(Type.Missing);
    excel.Visible = false;
    excel.DisplayAlerts = false;
    Worksheet excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
    excelWorkSheet.Name = table.TableName;

    progressBar1.Maximum = table.Columns.Count;
    for (int i = 1; i < table.Columns.Count + 1; i++) // Creating Header Column In Excel  
    {
        excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
        if (progressBar1.Value < progressBar1.Maximum)
        {
            progressBar1.Value++;
            int percent = (int)(((double)progressBar1.Value / (double)progressBar1.Maximum) * 100);
            progressBar1.CreateGraphics().DrawString(percent.ToString() + "%", new System.Drawing.Font("Arial", (float)8.25, FontStyle.Regular), Brushes.Black, new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
            System.Windows.Forms.Application.DoEvents();
        }
    }

    progressBar1.Maximum = table.Rows.Count;
    for (int j = 0; j < table.Rows.Count; j++) // Exporting Rows in Excel  
    {
        for (int k = 0; k < table.Columns.Count; k++)
        {
            excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
        }

        if (progressBar1.Value < progressBar1.Maximum)
        {
            progressBar1.Value++;
            int percent = (int)(((double)progressBar1.Value / (double)progressBar1.Maximum) * 100);
            progressBar1.CreateGraphics().DrawString(percent.ToString() + "%", new System.Drawing.Font("Arial", (float)8.25, FontStyle.Regular), Brushes.Black, new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
            System.Windows.Forms.Application.DoEvents();
        }
    }

    book.SaveAs(Xlfile);
    book.Close(true);
    excel.Quit();

    Marshal.ReleaseComObject(book);
    Marshal.ReleaseComObject(book);
    Marshal.ReleaseComObject(excel);
    }
}

Is there any other method I can use?

https://stackoverflow.com/a/13808590/9505307 says it best. Good luck. — nitsram, Mar 20 '18 at 22:18
You have an Xml file (not schema) with a namespace which is calling out schema that was used to generate the Xml. Parsing the file use a DataTable conversion will give fragmented results that will not be usable. — jdweng, Mar 21 '18 at 14:53

score 0 · Accepted Answer · answered Mar 21 '18 at 19:33

The code below uses oledb to write a new excel file with values from the xml. It uses xml linq to parse the xml file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;
using System.Data.OleDb;
using System.IO;



namespace ConsoleApplication31
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        const string XML_FILENAME = @"c:\temp\text.xls";
        const string SHEET_NAME = "sheet1";

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ContentDate", typeof(DateTime));
            dt.Columns.Add("Originator", typeof(int));
            dt.Columns.Add("FileContent", typeof(string));
            dt.Columns.Add("RecordCount", typeof(int));


            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("OtherEntityName", typeof(string));
            dt.Columns.Add("ID", typeof(string));
            dt.Columns.Add("EntityID", typeof(string));

            dt.Columns.Add("FirstAddressLine", typeof(string));
            dt.Columns.Add("AddressNumber", typeof(string));
            dt.Columns.Add("AdditionalAddressLine", typeof(string));
            dt.Columns.Add("City", typeof(string));
            dt.Columns.Add("Region", typeof(string));
            dt.Columns.Add("Country", typeof(string));
            dt.Columns.Add("PostalCode", typeof(string));

            XDocument doc = XDocument.Load(FILENAME);

            XElement root = doc.Root;
            XNamespace ns = root.GetDefaultNamespace();

            XElement body = root.Element(ns + "Header");

            DateTime contentDate = (DateTime)body.Element(ns + "ContentDate");
            int originator = (int)body.Element(ns + "Originator");
            string fileContent = (string)body.Element(ns + "FileContent");
            int recordCount = (int)body.Element(ns + "RecordCount");

            List<XElement> records = root.Descendants(ns + "Records").FirstOrDefault().Elements().ToList();

            foreach (XElement record in records)
            {
                XElement entity = record.Element(ns + "Entity");
                XElement confirmation = entity.Element(ns + "Confirmation");
                string id = (string)confirmation.Element(ns + "ID");
                string entityID = (string)confirmation.Element(ns + "EntityID");

                foreach (XElement address in entity.Elements().Where(x => x.Name.LocalName.Contains("Address")))
                {

                    DataRow newRow = dt.Rows.Add();
                    newRow["ContentDate"] = contentDate;
                    newRow["Originator"] = originator;
                    newRow["FileContent"] = fileContent;
                    newRow["RecordCount"] = recordCount;

                    newRow["ID"] = id;
                    newRow["EntityID"] = entityID;

                    newRow["Name"] = (string)entity.Element(ns + "Name");
                    newRow["OtherEntityName"] = string.Join(",", entity.Descendants(ns + "OtherEntityName").Select(x => (string)x));

                    XElement xAddress = address;
                    if (xAddress.Name.LocalName == "OtherAddresses") xAddress = address.Element(ns + "OtherAddress");

                    newRow["FirstAddressLine"] = (string)xAddress.Element(ns + "FirstAddressLine");
                    newRow["AddressNumber"] = (string)xAddress.Element(ns + "FirstAddressLine");
                    newRow["AdditionalAddressLine"] = string.Join(",", xAddress.Elements(ns + "FirstAddressLine").Select(x => (string)x));
                    newRow["City"] = (string)xAddress.Element(ns + "City");
                    newRow["Region"] = (string)xAddress.Element(ns + "Region");

                    newRow["Country"] = (string)xAddress.Element(ns + "Country");
                    newRow["PostalCode"] = (string)xAddress.Element(ns + "PostalCode");
                }
            }

            if(File.Exists(XML_FILENAME)) File.Delete(XML_FILENAME);
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter adapter = new OleDbDataAdapter();

            string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=ReadWrite;Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';", XML_FILENAME);
            OleDbConnection conn = new OleDbConnection(connectionString);

            conn.Open();

            Boolean firstRow = true;
            string[] columnNames = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();
            cmd = new OleDbCommand();
            cmd.Connection = conn;
            string dataType = "";
            foreach (DataColumn dc in dt.Columns)
            {
                switch (dc.DataType.Name)
                {
                    case "Int32" : 
                        dataType = "integer";
                        break;
                    default:
                        dataType = dc.DataType.Name;
                        break;
                }


                if (firstRow)
                {
                    string query = string.Format("CREATE TABLE [{0}] ( [{1}] {2})", SHEET_NAME, dc.ColumnName, dataType);
                    cmd.CommandText = query;

                    cmd.ExecuteNonQuery();
                    firstRow = false;
                }
                else
                {
                    string query = string.Format("Alter TABLE [{0}] ADD [{1}] {2}", SHEET_NAME, dc.ColumnName, dataType);
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
            }


            string insertQuery = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});", SHEET_NAME, string.Join(",", columnNames), string.Join(",", dt.Columns.Cast<DataColumn>().Select(x =>  "@" + x.ColumnName)));
            cmd.CommandText = insertQuery;

            foreach (DataRow row in dt.AsEnumerable())
            {

                for(int colNumber = 0; colNumber < dt.Columns.Count; colNumber++)
                {
                    cmd.Parameters.AddWithValue("@" + dt.Columns[colNumber].ColumnName, row[colNumber].ToString());
                }
                cmd.ExecuteNonQuery();

            }
            conn.Close();
        }
    }
}

Thanks for the answer.. I may load huge date file(1.5gb), will this code work fine or it will create memory issues? — Pragan, Mar 30 '18 at 02:08
The code doesn't use a lot of memory. oledb does not actually open a file in the conventional manner so the memory is kept small and write quickly. The issue is with the JET windows driver that only allows for 64,000 rows of data on each sheet. Jet was used with Excel 2003 . So you have to open another worksheet when you have over 64,000 rows. You can also replace the Jet with ACE. ACE comes with Microsoft Office 2007 and newer which requires a license. ACE is also available as a free run time library from Microsoft but you have to install the ACE when you do not have Office. — jdweng, Mar 30 '18 at 06:44

How to convert a XML Schema file with a header to an Excel file using C#?

1 Answers1