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.

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.

Introduction

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.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
using System.Xml;
In form load iam calling BindDropDown method
private void Form1_Load(object sender, EventArgs e) {
    BindDropDown();
}
BindDropDown Method: public void BindDropDown() {
    /*Here for testing put your xml file in debuger folder*/
    XmlTextReader xmdatareader = new XmlTextReader("Xml/Tablenames.xml");
    DataSet _objdataset = new DataSet();
    _objdataset.ReadXml(xmdatareader);
    comboBox1.DataSource = _objdataset.Tables[0];
    comboBox1.DisplayMember = "title_Text";
    comboBox1.ValueMember = "title_Text";
}

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.

comboBox1.DataSource = _objdataset.Tables[0];
comboBox1.DisplayMember = "title_Text";
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.

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    string selectedtable = Convert.ToString(comboBox1.SelectedValue);
    string constring = "data source=SQLSERVERNAME;initial catalog=khadar;persist security info=True;user id=sa;password=qwaszx@123";
    if (!string.IsNullOrEmpty(selectedtable) && selectedtable != "System.Data.DataRowView")
    {
        string query = "select top 15 * from dbo." + selectedtable;
        SqlConnection con = new SqlConnection(constring);
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = query;
        cmd.CommandType = CommandType.Text;
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        // this.dataGrid1.SetDataBinding(ds,"MyTable"); or
        dataGridView1.DataSource = dt;
    }
}

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.

Code

private void ExpoertToExcel_Click(object sender, EventArgs e)
{
    Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
    // creating new WorkBook within Excel application
    Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
    // creating new Excelsheet in workbook
    Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
    // see the excel sheet behind the program
    app.Visible = true;
    // get the reference of first sheet. By default its name is Sheet1.
    // store its reference to worksheet
    worksheet = workbook.Sheets["Sheet1"];
    worksheet = workbook.ActiveSheet;
    // changing the name of active sheet
    worksheet.Name = "Exported from gridview";
    // storing header part in Excel
    for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) {
        worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
    }
    // storing Each row and column value to excel sheet
    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) {
        for (int j = 0; j < dataGridView1.Columns.Count; j++) {
            worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
        }
    }
    // save the application
    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);
    // Exit from the application
    app.Quit();
}

This code will extract the Excel in dcuments.

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