Reputation: 1
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
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