Creating Server-Side Excel Workbooks

Introduction
Hello and welcome to Pluralsight. My name is Jason Sauer and this is Creating Server-Side Excel Workbooks. In this course we will discuss the desire and, in some cases, the need that many users have for receiving data from your applications in Excel format. We will talk about a few of the traditional methods used to deliver Excel data and introduce some newer methods that may make this process not only easier, but more powerful and flexible as well. Finally, we will focus on EPPlus, a package that you can use to easily deliver true Excel data to your client.

Just Dump It Out
Sometimes all you need to do is get the data out of the system and into your user's hands. EPPlus provides several easy mechanisms to do just that and each is suited for a different type of data on your server-side. EPPlus allows you to easily export data to Excel, whether it is in the form of a text file, a dataset, an array or a collection. In this module we'll take a look at each of these to see how easy it is to just dump out your data into Excel.

I Didn't Really Mean Just Dump It...
In the last module we looked at how EPPlus can make getting data out of the system a very easy process. The methods we've explored so far, from exporting CSV and HTML tables to using EPPlus methods to export various data sources to Excel, all met the minimum requirement of getting the data out, but wouldn't it be nice if you could give your users something that looks a little nice than just plain old columns and rows? In this module we are going to explore how to add a little style to that substance, starting with row and column formatting. Then we will look at how easy it is to add functions into our spreadsheets.

Get a Head Start With a Template
EPPlus gives us the ability to programmatically create Excel content on the server-side and also to format that data into an attractive report. However, when we have a consistent data source it might be even easier to do some of that formatting directly in Excel and use EPPlus to populate the data. Here's the latest version of our demo spreadsheet. In previous modules we introduced table formatting, column alignment, data formats, text formats, and formula insertion. Reviewing the code we used to get there we can see that it was not very hard for us to do all that. In fact, a lot of this code is repetitive and could be streamlined even further if we were so inclined. Short of doing that, perhaps we can still make this process easier for ourselves. We've found that EPPlus makes it very easy for us to place data specifically where we want it within our worksheet. If we can target specific cells to populate, why write the code to perform these repetitive formatting steps? What if we can just use Excel to do it directly? In this module we will create a new workbook in Excel that we will style and then use as a preformatted template for EPPlus to populate with our data.

Can I Get Pie With That?
There are plenty of options for you to choose from if you just want to present data, but one of the things that makes Excel such a popular application is its toolbox of rich features that can easily be added to a given spreadsheet to help users better understand their data. Often a simple graphic can help a user identify trends or see relationships that may be hard to spot by looking at raw data. Some of the tools Excel provides that can help with that are charts, images, and comments. In this module we are going to use EPPlus to take us beyond simple rows and columns to add charts, images, and comments into our Excel worksheets.