Reader Level:
Article

Import Data to Excel SpreadSheet in .NET

By Gaurav Gupta on Oct 29, 2012
In this article we are learn how to import data to an Excel sheet in ASP.NET and Windows Forms Application.

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)

        {

            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);

            GridView1.DataSource = ds.Tables[0];

            GridView1.DataBind();

        }

        DataSet ds = new DataSet();

        protected void Button1_Click(object sender, EventArgs e)

        {

            ExportToExcel(ds.Tables[0], "toni");

        }

        protected void ExportToExcel(DataTable dataTable, string fileName)

        {

            HttpContext context = HttpContext.Current;

            context.Response.Clear();

            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);

            }

            context.Response.ContentType = "application / ms - excel";

            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");

            context.Response.End();

        }

    }

}

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 a 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:

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.

private void ExporttoExcel(DataTable table)

{

     saveFileDialog1.ShowDialog();

     Workbook workbook = new Workbook();

     Worksheet worksheet = workbook.Worksheets[0];

            // worksheet.Cells["A1"].PutValue("export excel ");     

            // worksheet.Cells.ImportDataTable(table,true,"A2");    

     worksheet.Cells.ImportDataTable(table, true, "A2");

            // workbook.FileFormat = FileFormatType.Xlsx;          

     worksheet.AutoFitRows();

     worksheet.AutoFitColumns();

     workbook.Save(saveFileDialog1.FileName, SaveFormat.Auto);
}

Output

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

Article Extensions
Contents added by mahmut kantar on Jun 05, 2013
Gaurav Gupta

I am a student and Developer. I like to explore new technologies, mostly working on web development.

COMMENT USING