Merge multiple DataTables and export to Excel in ASP.Net

Last Reply on Oct 16, 2015 09:09 AM By Shashikant

Posted on Oct 16, 2015 05:21 AM

I need one more help from your side...how to merge two data tables into sheet?I need like this...

name:xxx     from:rrr   to:fgfgfgfg
 
designation:
 
after that datatable data
 
rowid name subject 
You are viewing reply posted by: Shashikant on Oct 16, 2015 09:09 AM.
Posted on Oct 16, 2015 09:09 AM

Here i have created sample,In this I have merged two Datatable with primary key also exported to excel.

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataColumn col;
        DataTable table1 = new DataTable();
        table1.PrimaryKey = new DataColumn[] { 
            col = table1.Columns.Add("patient_id") 
        };
        col.DataType = typeof(int);
        col.Unique = true;
        col = table1.Columns.Add("appointment_time");
        col = table1.Columns.Add("patient_name");
        col = table1.Columns.Add("patient_doctor");

        table1.Rows.Add(1, "0900", "George Michael", "Dr. Jhon");
        table1.Rows.Add(2, "1000", "David Morkel", "Dr. Peter");
        table1.Rows.Add(3, "1100", "Jack Michael", "Dr. Smith");
        table1.Rows.Add(4, "1200", "Kevin Morkel", "Dr. Andrea");

        DataTable table2 = new DataTable();
        table2.PrimaryKey = new DataColumn[] { 
            col = table2.Columns.Add("patient_id") 
        };
        col.DataType = typeof(int);
        col.Unique = true;
        col = table2.Columns.Add("appointment_time");

        table2.Rows.Add(1, "0900");
        table2.Rows.Add(2, "1000");
        table2.Rows.Add(3, "1100");
        table2.Rows.Add(4, "1200");

        DataTable merged = new DataTable();
        merged.Merge(table1);
        merged.Merge(table2);
        ExportToExcel(merged);
    }
}

private void ExportToExcel(DataTable dt)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=RepeaterExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    string tab = "";
    foreach (DataColumn dc in dt.Columns)
    {
        Response.Write(tab + dc.ColumnName);
        tab = "\t";
    }
    Response.Write("\n");
    int i;
    foreach (DataRow dr in dt.Rows)
    {
        tab = "";
        for (i = 0; i < dt.Columns.Count; i++)
        {
            Response.Write(tab + dr[i].ToString());
            tab = "\t";
        }
        Response.Write("\n");
    }
    Response.Flush();
    Response.End();
}

Screenshot

I hope this will help you out.