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

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