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

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.

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