Convert XML File to Excel File

• Download source code - 591.7 KB

Introduction

In this tip, I am going to share with you the conversion methods of XML to Excel in C#.

Here, we are going to learn to read XML files in C# WindowsForm applications. We are going to make a small single-form application which takes an XML file from the user and converts XML Data to Excel Data and Returns an Excel File.

Targeted Audiences

The targeted audience is people with basic knowledge of C#.

Explanation

Things to do:

• Make a C# WinForm application
• Add a reference
• Create UI
• Code

Create a New Project and give it a suitable name as I gave the project name ‘ConvertXmlToXl’.

After creating a project, you have to add a reference named “Microsoft Excel Object library” in your project. You will find this reference in ‘COM’ Section.

Now, make a UI which contains two Buttons, two Textboxes, one CheckBox, a single ProgressBar and a OpenFileDialog tool. A button is used to browse an XML file and checkbox to give option to user for custom Excel file name. And progressbar to show the progress of conversion. And another button for converting the XML file to Excel.

So, here, our UI looks like the following:

Now, code the click event of the button to browse computer files so that the user can select his XML file. Set Filter in OpenFileDialog tool ‘XML File (*.xml)|*.xml|All files (*.*)|*.*’ so it’s preferred Excel files while browsing the file. User can give custom Excel file name by checking ‘Excel File Name’ checkbox and enter Excel's file in respective textbox. And after that, user can convert XML file by simply clicking on ‘Convert’ Button. So, double-click on the ‘Convert’ button to open the click event.

Code

using System.Data;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

Code for the file browse button click event:

private void btnBrowseFolder_Click(object sender, EventArgs e)
 {
     DialogResult drResult = OFD.ShowDialog();
     if (drResult == System.Windows.Forms.DialogResult.OK)
         txtXmlFilePath.Text = OFD.FileName;
 }

Code for the ‘Convert’ button click event:

private void btnConvert_Click(object sender, EventArgs e)
        {
            // Resetting the progress bar Value
            progressBar1.Value = 0;

            if (chkCustomeName.Checked && txtCustomeFileName.Text != "" && 
            txtXmlFilePath.Text != "") // using Custom Xml File Name
            {
                if (File.Exists(txtXmlFilePath.Text)) // Checking XMl File is Exist or Not
                {
                    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 (!chkCustomeName.Checked || 
                         txtXmlFilePath.Text != "") // Using Default Xml File Name
            {
                if (File.Exists(txtXmlFilePath.Text)) // Checking XMl File is Exist or Not
                {
                    FileInfo fi = new FileInfo(txtXmlFilePath.Text);
                    string XlFile = fi.DirectoryName + "\\" + fi.Name.Replace
                    (fi.Extension,".xlsx"); // CReating Default File Name
                    System.Data.DataTable dt = CreateDataTableFromXml
                    (txtXmlFilePath.Text); // Getting XML Data into DataTable
                    ExportDataTableToExcel(dt, XlFile);

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

In "CreateDataTableFromXml" function, we are simply converting XML data into ‘DataTable’. This function returns a DataTable and we export this datatable into the Excel file.

// 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;
       }

In "ExportDataTableToExcel" function, we pass DataTable and Excel File Name through function’s parameters. We are creating new Excel file and we are exporting DataTable’s Column Names as Header row in Excel File. And also exporting DataRow as Excel rows.

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);
        }

Conclusion

By using these easy and simple methods, we can convert XML files into DataTable. And from DataTable, we can export the data into Excel file or we can create a new Excel file and export the DataTable’s Data and also we can display DataTable’s Data in “DataGridView”, simply setting DataGridView property “DataSource” to DataTable.

Hope this will help you and you would like this article.

Please give your valuable feedback in the comments below.