Click here to Skip to main content
13,550,430 members
Click here to Skip to main content
Add your own
alternative version

Stats

6.9K views
323 downloads
11 bookmarked
Posted 27 Nov 2017
Licenced CPOL

Convert XML File to Excel File

, 27 Nov 2017
Rate this:
Please Sign up or sign in to vote.
Converting XML Data into DataTable and Exporting DataTable into Excel File

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’.

Create New Project

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.

Add Reference

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:

UI Design

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Altaf Ansari
Software Developer Prothious Engineering Services
India India
Team Leader .Net Developer, Working on Microsoft Technology Asp.Net, C#, SQL, Windows Application, Web Application.

Achievements :

13th January 2018 - Article of the Day - ASP.NET Community (SignalR Chat App With ASP.NET WebForm And BootStrap - Part One)


14th February 2018 - Article of the Day - ASP.NET Community (SignalR Chat App With ASP.NET WebForm And BootStrap - Part Two)

3rd March 2018 - Article of the Day - ASP.NET Community (SignalR Chat App With ASP.NET WebForm And BootStrap - Part Three)

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionJIT issue Pin
Sakti Mohapatra14-Mar-18 7:44
memberSakti Mohapatra14-Mar-18 7:44 
QuestionMessage Closed Pin
30-Nov-17 1:52
memberExpro Academy30-Nov-17 1:52 
QuestionThe name 'OFD' does not exist in the current context Pin
Member 1344045029-Nov-17 1:45
memberMember 1344045029-Nov-17 1:45 
AnswerRe: The name 'OFD' does not exist in the current context Pin
Altaf Ansari29-Nov-17 2:28
memberAltaf Ansari29-Nov-17 2:28 
GeneralRe: The name 'OFD' does not exist in the current context Pin
Member 1344045029-Nov-17 21:34
memberMember 1344045029-Nov-17 21:34 
GeneralRe: The name 'OFD' does not exist in the current context Pin
Altaf Ansari29-Nov-17 22:01
memberAltaf Ansari29-Nov-17 22:01 
QuestionCode should be improved Pin
Member 1297152628-Nov-17 20:45
memberMember 1297152628-Nov-17 20:45 
AnswerRe: Code should be improved Pin
Altaf Ansari28-Nov-17 23:37
memberAltaf Ansari28-Nov-17 23:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180515.1 | Last Updated 27 Nov 2017
Article Copyright 2017 by Altaf Ansari
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid