Tuesday, September 25, 2012

Downloading dynamic Excel files from ASP.Net MVC

Have been using the wonderful EPPlus library today - http://epplus.codeplex.com/

With it, is was really quick and easy to generate an ASP.Net MVC Controller action that allowed me to generate an excel spreadsheet with embedded data and with a chart! And with no Office or COM automation nastiness on the server :)

WooHoo :)


using System;
using System.Web.Mvc;
using Newtonsoft.Json;
using OfficeOpenXml;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;

namespace MyApp.Controllers
{
    public class ExportController : Controller
    {
        [Authorize]
        public ActionResult SafeDownload()
        {
            using (var package = new ExcelPackage())
            {
                package.Workbook.Worksheets.Add("Test");
                ExcelWorksheet ws = package.Workbook.Worksheets[1];
                ws.Name = "Test"; //Setting Sheet's name
                ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
                ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet

                //Merging cells and create a center heading for out table
                ws.Cells[1, 1].Value = "Sample DataTable Export"; // Heading Name
                ws.Cells[1, 1, 1, 10].Merge = true; //Merge columns start and end range
                ws.Cells[1, 1, 1, 10].Style.Font.Bold = true; //Font should be bold
                ws.Cells[1, 1, 1, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

                for (var i = 1; i < 11; i++)
                {
                    for (var j = 2; j < 45; j++)
                    {
                        var cell = ws.Cells[j, i];

                        //Setting Value in cell
                        cell.Value = i * (j - 1);
                    }
                }

                var chart = ws.Drawings.AddChart("chart1", eChartType.AreaStacked);
                //Set position and size
                chart.SetPosition(0, 630);
                chart.SetSize(800, 600);

                // Add the data series.
                var series = chart.Series.Add(ws.Cells["A2:A46"], ws.Cells["B2:B46"]);

                var memoryStream = package.GetAsByteArray();
                var fileName = string.Format("MyData-{0:yyyy-MM-dd-HH-mm-ss}.xlsx", DateTime.UtcNow);
                // mimetype from http://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc
                return base.File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
            }
        }
    }
}

No comments:

Post a Comment