How to export two datatables to excel

prabhu p

9y2.9k.NET

I have 2 datatable, from that I need Export to excel in single

sheet, I need output as like this but iam getting only one data table that lastly bind

----------------

Stockist not present in master distributor(title)

Stockistcode(Columnheader)

---

-----

----

linr break

List of CompanyFF User Id Not Present(title)

Userid (Columnheader)

------

--------

-------

My code

---------

Public Sub ExportToSpreadsheett(ByVal table As DataTable, ByVal table1 As DataTable, ByVal name As String, ByVal strReadString As String)

Try

If table.Rows.Count > 0 Then

Dim attachment As String = "attachment; filename=" & name & Format(CDate(Now), " ddMMyyyy_hhmmss") & ".xls"

HttpContext.Current.Response.ClearContent()

HttpContext.Current.Response.AddHeader("content-disposition", attachment)

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"

Dim userrtype As Integer

userrtype = CType(HttpContext.Current.Session("UserType"), Integer)

' HttpContext.Current.Response.Write("
")

HttpContext.Current.Response.Write("
")

HttpContext.Current.Response.Write(strReadString)

HttpContext.Current.Response.Write("
")

HttpContext.Current.Response.Write("Stockist not present in master distributor : ")

HttpContext.Current.Response.Write(vbLf)

Label1.Text = strReadString.ToString()

Dim GridView1 As New GridView()

GridView1.DataSource = table

GridView1.AllowPaging = False

GridView1.DataBind()

HttpContext.Current.Response.Write("
")

HttpContext.Current.Response.Write(" List of CompanyFF User Id Not Present ")

HttpContext.Current.Response.Write(vbLf)

Dim GRidView2 As New GridView()

GridView1.DataSource = table1

GridView1.AllowPaging = False

'GRidView2.DataBind()

GridView1.DataBind()

Dim sw As New StringWriter()

Dim hw As New HtmlTextWriter(sw)

'Change the Header Row back to white color

GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")

GridView1.HeaderStyle.Font.Bold = True

'Apply style to Individual Cells

For i As Integer = 0 To table.Columns.Count - 1

GridView1.HeaderRow.Cells(i).Style.Add("background-color", "#C2D69B")

GridView1.RenderControl(hw)

Response.Output.Write(sw.ToString())

HttpContext.Current.Response.Flush()

HttpContext.Current.Response.SuppressContent = True

HttpContext.Current.ApplicationInstance.CompleteRequest()

'HttpContext.Current.ApplicationInstance.CompleteRequest()

Else

DisplayAlert("No Records Found!")

End If

Catch excp As Exception

Errors.WriteToErrorLogWithUserName(excp, "", Session("UserName").ToString())

End Try

End Sub

Please do need full to me

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sahil Saini9y ago
Firstly install ClosedXML refrence in your project.
Then write this code.
Add your tables to dataset and store data in different worksheets of excel within the same file.
public DataSet getDataSetExportToExcel() {
DataSet ds = new DataSet();
DataTable dtEmp = new DataTable("Employee");
dtEmp = getAllEmployeesList();
DataTable dtEmpOrder = new DataTable("Order List");
dtEmpOrder = getAllEmployeesOrderList();
ds.Tables.Add(dtEmp);
ds.Tables.Add(dtEmpOrder);
return ds;
}

protected void btn_Export_Click(object sender, EventArgs e) {
DataSet ds = getDataSetExportToExcel();
using(XLWorkbook wb = new XLWorkbook()) {
wb.Worksheets.Add(ds);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename= EmployeeAndOrderReport.xlsx");
using(MemoryStream MyMemoryStream = new MemoryStream()) {
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
Amit Gupta9y ago
Before merging two tables, make sure that both the table has same column names. Loop through the no of columns and add to the 2nd table and vice versa to match the columns then follow the below link to merge it and finally export it to excel.

http://www.c-sharpcorner.com/Forums/append-records-to-a-gridview-from-a-control

Do let me know if it solves your problem.