I’ve been working on moving the reporting functionality of an existing application from HTML to Microsoft Excel format. I find HTML fine for creating simple reports or sometimes log files from C# applications, but these reports were churning out at around 50MB, which Internet Explorer was having serious problems dealing with. So, I decided to solve this issue by moving all reporting functionality in the application to Microsoft Excel format, which with hindsight I would have done in the original design.
EPPlus is an open source .NET library for reading an writing Excel files. I’ve used this in many projects, and have found it invaluable when the requirement to either read or write Excel files crops up. In this post, I’ll give examples of using EPPlus to write to a Microsoft Excel file.
Setup
Create a new C# console application project in Visual Studio. Download the EPPlus binaries from the Downloads section on the EPPlus CodePlex Site.
Extract the files and add EPPlus.dll as a reference to your project.
Writing to Excel
EPPlus writes Excel 2007/2010 files using the Open Office Xml format (xlsx). The first thing to do, after the initial setup has been completed, is to add the following imports to your code:
using OfficeOpenXml; using OfficeOpenXml.Style;
Next, let’s create a new ExcelPackage and add some properties to it such as the author, title and company:
using (ExcelPackage p = new ExcelPackage())
{
p.Workbook.Properties.Author = "Miles Dyson";
p.Workbook.Properties.Title = "SkyNet Monthly Report";
p.Workbook.Properties.Company = "Cyberdyne Systems";
// The rest of our code will go here...
}
Now we’ll need to create a new worksheet where we will add our data:
p.Workbook.Worksheets.Add("April 2012");
ExcelWorksheet ws = p.Workbook.Worksheets[1]; // 1 is the position of the worksheet
ws.Name = "April 2012";
We’ll be adding some simple data to this worksheet, contained in 3 columns. We might want to add a header to this worksheet with some column names, and some basic formatting, like making the column header background color something different.
This is simple to achieve using EPPlus:
int rowIndex = 1;
int colIndex = 1;
do
{
// Set the background colours
var cell = ws.Cells[rowIndex, colIndex];
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
colIndex++;
}
while (colIndex != 4);
// Set the cell values
var cell_actionName = ws.Cells[1, 1];
var cell_timeTaken = ws.Cells[1, 2];
var cell_processorsUsed = ws.Cells[1, 3];
cell_actionName.Value = "Action Name";
cell_timeTaken.Value = "Time Taken";
cell_processorUsed.Value = "Processing Unit";
The above two actions will be quite common if you use EPPlus to write to Excel files in a lot of different projects. I’d recommend created a static helper class to perform both of these functions (adding the properties and creating a header), I’ve done this with these and other common functions, and I’ve found it’s saved me some time.
Note that we haven’t actually saved the Excel file yet, it’s in memory but we haven’t saved it to disk. Before we do, let’s add some data to it as well as the header. For the purpose of this example, let’s say we already have the data (wherever it may have come from), defined as a List of hypothetical ProcessorAction Objects.
In order to write the data to the file, we can just iterate over this List and write a new row for each ProcessorAction Object to the Excel file:
// Get hypothetical data...
List processorActions = DataAccessHelper.GetProcessorData(DataTime.Now);
// Column indexes for clarity
int actionColIndex = 1;
int timeColIndex = 2;
int processorColIndex = 3;
int rowIndex = 2; // Row 1 is the header
foreach(ProcessorAction p in processorActions)
{
// Action
var actionCell = ws.Cells[rowIndex, actionColIndex];
actionCell.Value = p.Action;
// Time
var timeCell = ws.Cells[rowIndex, timeColIndex];
timeCell.Value = p.Time;
// Processor
var processorCell = ws.Cells[rowIndex, processorColIndex];
processorCell.Value = p.Processor;
rowIndex++;
}
Now that all our data is written, we want to save the Excel file for distribution:
// Save the Excel file
Byte[] bin = p.GetAsByteArray();
File.WriteAllBytes(@"C:\Reports\Report.xlsx, bin);
Your file should save successfully. That’s the basics of writing to Excel files using EPPlus. In the next post, I’ll outline how to read data contained in an Excel file into memory.
Happy Coding 😉
