I need to generate an excel file for a user on the fl upon button click. I was using Netoffice before which worked fine for desktop applications. But now I want to do the same thing with an asp.net app. This way my server code doesn't have an access to the client's copy of excel. What approach should I take?
1
-
Looks a lot like stackoverflow.com/questions/150339/…Mathias– Mathias2012-04-08 17:16:35 +00:00Commented Apr 8, 2012 at 17:16
Add a comment
|
5 Answers
Use EPPlus. It allows you to create Excel spreadsheets on the server. I've used it and it worked great. It supports advanced functions.
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:C1"])
{
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);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=file.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();
}
3 Comments
user194076
Looks cool. Though I cannot find an example on how to create a workbook and download it to the client without saving to the server? Do you know an easy way of doing that?
Tyler Treat
I added some code which shows creating a workbook with a spreadsheet and writing it to the client.
Rick
Should EPPlus be downloaded as Dll and linked to reference of my project? I would like to use it...
The most flexible and likely to do exactly what you need is going to take some work, but it's free -- and really works. Use the toolkit to look at existing documents to see how to create the features you want.
Comments
Netoffice requires MS Office on executing machine. Does your server has it?
Comments
You can try simple HTML table (inlcude html, head, and body tags). Just save it with XLS extension.
1 Comment
user194076
No, I need some complex excel manipulation. netoffice would work great, but I do not understand why it cannot work with asp.net
You can use a DataGrid to create Excel files on the fly. It doesn't require Excel.
public static void ExportDataSetToExcel(DataSet ds, string filename)
{
HttpResponse response = HttpContext.Current.Response;
// first let's clean up the response.object
response.Clear();
response.Charset = "";
// set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
response.AddHeader(
"Content-Disposition",
"attachment;filename=\"" + filename + "\""
);
// create a string writer
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// instantiate a datagrid
DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables[0];
dg.DataBind();
dg.RenderControl(htw);
response.Write(sw.ToString());
dg.Dispose();
ds.Dispose();
response.End();
}
}
}
1 Comment
Ian Boyd
This solution returns HTML to the client, but declares that it is actually an Excel file. The problem with that is that Excel is on to your little deception, and throws a warning dialog at the user explaining that someone's trying to lie to them.