PINAK THAKKAR
PINAK THAKKAR

Reputation: 1

How to export data from SQL server database to Excel 2007 in ASP.NET?

I am developing an application where I am required to provide the functionality of exporting my SQL database to Excel 2007 format using ASP.NET.

I have done it by first binding the grid and then on a click of the button the data is exported to Excel. But when the data is huge, it takes time to bind the grid and thus when I click the button the connection is lost.

Upvotes: 0

Views: 3750

Answers (1)

Michael Jasper
Michael Jasper

Reputation: 8058

Take a look at EPPlus. It is a google code hosted project that can "Create advanced Excel 2007/2010 spreadsheets on the server. EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx)."

It took me some time after importing the files into my project to get it actually working, so I'll give you some sample code of mine that is working. The code will probably not be exactly what you want to do with your file, but it will give you a good template.

In terms of page life-cycle: the code is on a .ashx handler page, so I open domain.com/toexcel.ashx in the browser and it downloads the file.

This library has worked very well for me, and the file-outputs seem to be completely valid/compatible Excel file.

BTW, I'm not affiliated, just a big fan :)

<%@ WebHandler Language="C#" Class="excel" %>

using System;
using System.Web;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
using System.Drawing;
using System.Data;


public class excel : IHttpHandler {

    public void ProcessRequest (HttpContext context) {
        using (ExcelPackage pck = new ExcelPackage())
        {
            int id = int.Parse(context.Request.QueryString["id"]);
            DateTime now = DateTime.Now;

            //get and format datatable
            Project proj = new Project(id);
            DataTable items = proj.getItemsDataTable();

            items = PmFunctions.prettyDates(items);
            items = PmFunctions.prettyMoney(items);

            //new worksheet
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add(proj.getTitle());

            //load data
            ws.Cells["A1"].Value = proj.getTitle();
            ws.Cells["A1"].Style.Font.Size = 20;

            ws.Cells["A2"].Value = "Report Date:";
            ws.Cells["C2"].Value = now.ToShortDateString();

            ws.Cells["A4"].Value = "Estimate Total:";
            ws.Cells["C4"].Value = String.Format("{0:C}", proj.getProjectEstimate());

            ws.Cells["A5"].Value = "Actual Total:";
            ws.Cells["C5"].Value = String.Format("{0:C}", proj.getProjectTotal());

            ws.Cells["A7"].LoadFromDataTable(items, true);

            //Stylings
            using (ExcelRange rng = ws.Cells["A7:J7"])
            {
                rng.Style.Font.Bold = true;
                rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));  //Set color to dark blue
                rng.Style.Font.Color.SetColor(Color.White);
            }



            //Write to the response
            context.Response.Clear();
            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            context.Response.AddHeader("content-disposition", "attachment;  filename=" + proj.getTitle() + " Report - " + now.ToShortDateString() + ".xlsx");
            context.Response.BinaryWrite(pck.GetAsByteArray());
        }
    }

    public bool IsReusable {
        get {
            return false;
        }
    }
}

Upvotes: 1

Related Questions