Create Excel workbook in asp.net

Question

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?

Solution 1

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

Solution 2

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.

Open XML 2.0 SDK