CLient Side :
----------------------------------------
<pre lang="xml"><form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:GridView ID="gdvreport1" runat="server">
</asp:GridView>
</td>
</tr>
<br /><br />
<tr>
<td>
<asp:GridView ID="grvreport2" runat="server">
</asp:GridView>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnget" runat="server" Text="GetData" OnClick="btnget_Click" />
</td>
<td>
<asp:Button ID="btnexport" runat="server" Text="Export" OnClick="btnexport_Click" />
</td>
</tr>
</table>
</div>
</form></pre>
Server Side :
-------------------------------------------
<pre>using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MDIndia.ClassLibrary.ApplicationSupport;
using System.Data;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Text;
using System.Web.UI.HtmlControls;
public partial class NewTwo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnget_Click(object sender, EventArgs e)
{
try
{
}
catch (Exception)
{
throw;
}
}
private void GetconnectToAuth()
{
DBManager.CurrentDatabaseProvider = DatabaseProvider.MSSQLServer;
DBManager.ConnectionString = "Data Source=MDICENTRAL\\AUTHORISATION;Initial Catalog=PIMS;User ID=mdindia1;Password=md!nd!@@123";
}
protected void btnexport_Click(object sender, EventArgs e)
{
GetconnectToAuth();
string query1 = "Select ID,ICName from ICMaster";
System.Data.DataTable dt = new System.Data.DataTable();
dt = DBManager.SelectDataTable(query1);
System.Data.DataTable dt2 = new System.Data.DataTable();
query1 = "Select LabID,LabName from LabMaster";
dt2 = DBManager.SelectDataTable(query1);
dt2.TableName = "B";
DataSet dataset = new DataSet();
dataset.Tables.Add(dt.Copy());
dataset.Tables.Add(dt2.Copy());
Excel.Application excel = new Excel.Application();
var workbook = (Excel._Workbook)(excel.Workbooks.Add(Missing.Value));
for (var i = 0; i < dataset.Tables.Count; i++)
{
if (workbook.Sheets.Count <= i)
{
workbook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
}
var currentSheet = (Excel._Worksheet)workbook.Sheets[i + 1];
switch (Convert.ToInt32(i))
{
case 0:
currentSheet.Name = "Projects";
break;
case 1:
currentSheet.Name = "Employees";
break;
}
for (var j = 0; j < dataset.Tables[i].Columns.Count; j++)
{
currentSheet.Cells[1, j + 1] = dataset.Tables[i].Columns[j].ToString();
}
for (var y = 0; y < dataset.Tables[i].Rows.Count; y++)
{
for (var x = 0; x < dataset.Tables[i].Rows[y].ItemArray.Count(); x++)
{
currentSheet.Cells[y + 2, x + 1] = dataset.Tables[i].Rows[y].ItemArray[x];
}
}
}
workbook.SaveAs(Server.MapPath("Test.xlsx"), Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excel.Quit();
var filePath = Server.MapPath("Test.xlsx");
FileInfo targetFile = new System.IO.FileInfo(filePath);
try
{
FileInfo objFileInfo = new FileInfo(filePath);
Response.Clear();
Response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
Response.AddHeader("Content-Disposition", ("attachment; filename=" + objFileInfo.Name));
Response.AddHeader("Content-Length", objFileInfo.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(filePath);
Response.End();
}
catch (Exception ex)
{
}
}
}</pre>