Binding XML Data In DropDownList After Showing Table Data In Grid View And Download In Excel

In this article, we will discuss how to bind an XML File Data to a ComboBox Control. From combo box, we need to show the table data into GridView.


Here, we will learn how to bind XML data to a Combobox control. Prior to starting, let's see what XML is.

What is XML?
  • XML stands for eXtensible Markup Language
  • XML is a markup language much like HTML
  • XML was designed to store and transport data
  • XML was designed to be self-descriptive 
  • It was primarily designed to carry/transport data whereas HTML is used to display/present data although it is much like HTML.
  • XML documents contain structured information. It is based on tags. (See Note.xml file.)

I am taking an Employee Example and I will add all four of these  titles in my combo box. For this, first, open your Visual Studio and design a form like below.

After this, write the below code in your .cs file. For XmlTextReader, we need to import some namespaces.
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.IO;  
  5. using System.Windows.Forms;  
  6. using System.Xml;  
  7. In form load iam calling BindDropDown method  
  8. private void Form1_Load(object sender, EventArgs e) {  
  9.     BindDropDown();  
  10. }  
  11. BindDropDown Method: public void BindDropDown() {  
  12.     /*Here for testing put your xml file in debuger folder*/  
  13.     XmlTextReader xmdatareader = new XmlTextReader("Xml/Tablenames.xml");  
  14.     DataSet _objdataset = new DataSet();  
  15.     _objdataset.ReadXml(xmdatareader);  
  16.     comboBox1.DataSource = _objdataset.Tables[0];  
  17.     comboBox1.DisplayMember = "title_Text";  
  18.     comboBox1.ValueMember = "title_Text";  
  19. }  
In BindDropDown method, we are giving our XML file path. Actually, it is in my Debug folder - /XML/Employee.xml

After this, I am taking the DataSet. From this dataset, I am reading my XML file. After reading this, we are binding this to combobox.
  1. comboBox1.DataSource = _objdataset.Tables[0];  
  2. comboBox1.DisplayMember = "title_Text";  
  3. comboBox1.ValueMember = "title_Text";  
Here, title_Text is my column name. Now, my combo box is bound with XML data.

Now, if I select anything, I need to display that in my GridView. Here is code for that.
  1. private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)  
  2. {  
  3.     string selectedtable = Convert.ToString(comboBox1.SelectedValue);  
  4.     string constring = "data source=SQLSERVERNAME;initial catalog=khadar;persist security info=True;user id=sa;password=qwaszx@123";  
  5.     if (!string.IsNullOrEmpty(selectedtable) && selectedtable != "System.Data.DataRowView")  
  6.     {  
  7.         string query = "select top 15 * from dbo." + selectedtable;  
  8.         SqlConnection con = new SqlConnection(constring);  
  9.         con.Open();  
  10.         SqlCommand cmd = new SqlCommand();  
  11.         cmd.CommandText = query;  
  12.         cmd.CommandType = CommandType.Text;  
  13.         SqlDataAdapter adapter = new SqlDataAdapter(query, con);  
  14.         DataSet ds = new DataSet();  
  15.         DataTable dt = new DataTable();  
  16.         adapter.Fill(dt);  
  17.         // this.dataGrid1.SetDataBinding(ds,"MyTable"); or  
  18.         dataGridView1.DataSource = dt;  
  19.     }  
  20. }  
Give your connection string and now, we have bound this in grid view as well. Now, I need to download this in Excel format.

And, we need to add a dll file to download this in Excel. Go to Solution Explorer and add References and add the below-mentioned dll.


You can also try installing it in Visual Studio via Package Manager.

Run Install-Package Microsoft.Office.Interop.Excel in the Package Console. This will automatically add it as a project reference.

After this, we need to write a code in Button Click event.

  1. private void ExpoertToExcel_Click(object sender, EventArgs e)  
  2. {  
  3.     Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();  
  4.     // creating new WorkBook within Excel application  
  5.     Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);  
  6.     // creating new Excelsheet in workbook  
  7.     Microsoft.Office.Interop.Excel._Worksheet worksheet = null;  
  8.     // see the excel sheet behind the program  
  9.     app.Visible = true;  
  10.     // get the reference of first sheet. By default its name is Sheet1.  
  11.     // store its reference to worksheet  
  12.     worksheet = workbook.Sheets["Sheet1"];  
  13.     worksheet = workbook.ActiveSheet;  
  14.     // changing the name of active sheet  
  15.     worksheet.Name = "Exported from gridview";  
  16.     // storing header part in Excel  
  17.     for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) {  
  18.         worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;  
  19.     }  
  20.     // storing Each row and column value to excel sheet  
  21.     for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) {  
  22.         for (int j = 0; j < dataGridView1.Columns.Count; j++) {  
  23.             worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();  
  24.         }  
  25.     }  
  26.     // save the application  
  27.     workbook.SaveAs("C:\\Users\\khadarbasha.shaik\\Documentsoutput.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);  
  28.     // Exit from the application  
  29.     app.Quit();  
  30. }  
This code will extract the Excel in dcuments.

Please feel free to comment. If you have any doubts, please do contact me.

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now