Import Data to Excel SpreadSheet in .NET

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.
  1. HttpContext context = HttpContext.Current;  
  2. context.Response.Clear(); 
Step 2
 
Fetch the records from the DataTable and write these records to Context.Response.
  1. foreach(DataColumn column in dataTable.Columns) {  
  2.     context.Response.Write(column.ColumnName + ",");  
  3. }  
  4. context.Response.Write(Environment.NewLine);  
  5.   
  6. foreach(DataRow row in dataTable.Rows) {  
  7.     for (int i = 0; i < dataTable.Columns.Count; i++) {  
  8.         context.Response.Write(row[i].ToString() + ",");  
  9.     }  
  10.     context.Response.Write(Environment.NewLine);  

Step 3
 
Set the content type and header to context.Response.
  1. context.Response.ContentType = "application / ms - excel";  
  2. context.Response.AppendHeader("Content-Disposition""attachment; filename=" + fileName + ".csv");  
  3. context.Response.End(); 
Here is the full code.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.UI;  
  8. using System.Web.UI.WebControls;  
  9.   
  10. namespace WebApplication1 {  
  11.     public partial class MyExample: System.Web.UI.Page {  
  12.         protected void Page_Load(object sender, EventArgs e) {  
  13.             SqlDataAdapter da = new SqlDataAdapter("Select * from UserDetail""Data Source=MYPC;Initial Catalog=Student;Persist Security Info=True;User ID=sa;Password=wintellect");  
  14.   
  15.             da.Fill(ds);  
  16.             GridView1.DataSource = ds.Tables[0];  
  17.             GridView1.DataBind();  
  18.         }  
  19.         DataSet ds = new DataSet();  
  20.         protected void Button1_Click(object sender, EventArgs e) {  
  21.             ExportToExcel(ds.Tables[0], "toni");  
  22.         }  
  23.         protected void ExportToExcel(DataTable dataTable, string fileName) {  
  24.             HttpContext context = HttpContext.Current;  
  25.             context.Response.Clear();  
  26.             foreach(DataColumn column in dataTable.Columns) {  
  27.                 context.Response.Write(column.ColumnName + ",");  
  28.             }  
  29.             context.Response.Write(Environment.NewLine);  
  30.   
  31.             foreach(DataRow row in dataTable.Rows) {  
  32.                 for (int i = 0; i < dataTable.Columns.Count; i++) {  
  33.                     context.Response.Write(row[i].ToString() + ",");  
  34.                 }  
  35.                 context.Response.Write(Environment.NewLine);  
  36.             }  
  37.             context.Response.ContentType = "application / ms - excel";  
  38.             context.Response.AppendHeader("Content-Disposition""attachment; filename=" + fileName + ".csv");  
  39.             context.Response.End();  
  40.         }  
  41.     }  

Output
 
Export-data-to-Excel-in-Asp.net(1).jpg
download-file-in-asp.net.jpg
 
In Window Forms Application
 
Here I will show you to export an Excel sheet in a Window 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.
 
Export-data-to-Excel-in-Asp.net.jpg
 
Step 3
 
Add the namespace for using the classes of this library, as in:
  1. 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 save it.
  1. private void ExporttoExcel(DataTable table) {  
  2.     saveFileDialog1.ShowDialog();  
  3.     Workbook workbook = new Workbook();  
  4.     Worksheet worksheet = workbook.Worksheets[0];  
  5.     // worksheet.Cells["A1"].PutValue("export excel ");       
  6.     // worksheet.Cells.ImportDataTable(table,true,"A2");      
  7.     worksheet.Cells.ImportDataTable(table, true"A2");  
  8.     // workbook.FileFormat = FileFormatType.Xlsx;            
  9.     worksheet.AutoFitRows();  
  10.     worksheet.AutoFitColumns();  
  11.     workbook.Save(saveFileDialog1.FileName, SaveFormat.Auto);  

Output
 
export-excel-sheet-in-windows-forms-application.jpg