Super Easy Web App Excel Data and Charting (via EPPlus) (.NET)

So for a while I reported only numbers in all the reports that were requested of me, and handed them off to someone else to format and work into charts. Well recently a co-worker was asked to provide a report with a graph daily on top of his normal, busy, workload. I thought this was an injustice, so I set out to find a way I could automate the report and thus I stumbled upon EPPlus, which happens to be a nifty Excel Exporter.

You can get EPPlus from here: http://epplus.codeplex.com/

For some background, this report is a comparison of Opened and Closed Tickets per day. This is how it's started:

ExcelPackage pck = new ExcelPackage();

pck.Workbook.Properties.Title = "Daily *** Closed/Open Tickets";
pck.Workbook.Properties.Company = "***";
pck.Workbook.Properties.Author = "*** Reporting";

var ws = pck.Workbook.Worksheets.Add("Daily *** Closed/Open Tickets");

ws.View.ShowGridLines = false;

ws.Cells[1, 1].Value = "Date";
ws.Cells[1, 2].Value = "Opened";
ws.Cells[1, 3].Value = "Closed";
ws.Cells[1, 4].Value = "Total Open";

We create an Excel Package and specify some other fields and we have thus started creating an Excel workbook! The Cell Values at the bottom of the code is where I set headers for the information. We then go ahead and populate the data. Don't forget that Excel is 1 based while Arrays and Lists are 0 based, plus there are the headers so you need to offset by 2:

for (int row = 2; row <= DateOpenClose.Count() + 1; row++)
            {
                for (int col = 1; col <= 4; col++)
                {
                    if (col == 1)
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].Date;
                    else if (col == 2)
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].Opened;
                    else if (col == 3)
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].Closed;
                    else
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].TotalOpen;
                }
            }

Now for the fun stuff! Creating a clustered column chart, which in my opinion is a stupid chart to go with in this case but I am no business analyist so I do as I am told:

var chart = ws.Drawings.AddChart("Open Vs Closed *** Tickets", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
chart.SetPosition(1, 0, 4, 0);
chart.SetSize(600, 400);
chart.Series.Add(ExcelRange.GetAddress(2, 2, DateOpenClose.Count() + 1, 2), ExcelRange.GetAddress(2, 1, DateOpenClose.Count() + 1, 1)).Header = "Opened";
chart.Series.Add(ExcelRange.GetAddress(2, 3, DateOpenClose.Count() + 1, 3), ExcelRange.GetAddress(2, 1, DateOpenClose.Count() + 1, 1)).Header = "Closed";            
chart.Title.Text = "Open Vs Closed *** Tickets";

To break this down, we first create a chart and give it a name and type. Easy stuff so far. Then we set the position (according to cell), and set the size (pixel width by pixel height). Then we add series. This took a little while for me to get the hang of but the first parameter is the y values or the significant data, the second parameter is the x axis which is most of the time a time-frame or grouping of some sort. I also set the header for the data right there and round it out with the title.

This was much simpler than I was expecting and I am currently building a dynamic reporting site that will take advantage of this simplicity and will allow people to build charts into custom reports. Won't lie, it's going to be sexy. Please comment if you have any questions and follow my on twitter @WebDevProblems.