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.

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




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.



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

--