In this article, we are going to learn how to export data to an Excel spreadsheet from a DataTable in a Windows Forms application and ASP.NET application.
In ASP.NET application
Here is how to export data to an Excel Sheet.
Step 1. Create an object of HttpContext Class.
HttpContext context = HttpContext.Current;
context.Response.Clear();
Step 2. Fetch the records from the DataTable and write these records to Context. Response.
foreach (DataColumn column in dataTable.Columns)
{
context.Response.Write(column.ColumnName + ",");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
context.Response.Write(row[i].ToString() + ",");
}
context.Response.Write(Environment.NewLine);
}
Step 3. Set the content type and header to context. Response.
context.Response.ContentType = "application/ms-excel";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");
context.Response.End();
Here is the full code.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication1
{
public partial class MyExample : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
// Establish connection and retrieve data
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM UserDetail", "Data Source=MYPC;Initial Catalog=Student;Persist Security Info=True;User ID=sa;Password=wintellect");
da.Fill(ds);
// Bind data to GridView
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
// Export data to Excel
ExportToExcel(ds.Tables[0], "toni");
}
protected void ExportToExcel(DataTable dataTable, string fileName)
{
HttpContext context = HttpContext.Current;
// Clear response
context.Response.Clear();
// Write column headers
foreach (DataColumn column in dataTable.Columns)
{
context.Response.Write(column.ColumnName + ",");
}
context.Response.Write(Environment.NewLine);
// Write data rows
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
context.Response.Write(row[i].ToString() + ",");
}
context.Response.Write(Environment.NewLine);
}
// Set response content type for Excel
context.Response.ContentType = "application/ms-excel";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");
context.Response.End();
}
}
}
Output
![Export-data]()
![Download]()
In Windows Forms Application
Here I will show you how to export an Excel sheet in a Windows Forms Application with the help of Aspose.Cells for .NET library. This library has built-in support for exporting and importing data from/to an Excel spreadsheet.
You can download this library from here.
http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx
Step 1. After downloading the library, unzip it.
Step 2. Go to the Solution Explorer and add the reference of this library to your project.
![Solution Explorer]()
Step 3. Add the namespace for using the classes of this library, as in.
using Aspose.Cells;
Step 4. Here I use a SaveFileDialog box to save the Excel file in the computer location.
Here is the code for exporting data to an Excel sheet and saving it.
private void ExporttoExcel(DataTable table)
{
saveFileDialog1.ShowDialog();
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.ImportDataTable(table, true, "A2");
worksheet.AutoFitRows();
worksheet.AutoFitColumns();
workbook.Save(saveFileDialog1.FileName, SaveFormat.Auto);
}
Output
![Output]()