In my last post I introduced the EPPLus library and showed how to use it to read Microsoft Excel XLSX files. In this post I will show how we can use it to write these files. Writing is pretty similar to reading but there are a lot more options you are likely to use when writing. Here is the basic code you need to create an XLSX file:
using (var package = new ExcelPackage())
{
var workbook = package.Workbook;
var worksheet = workbook.Worksheets.Add("Sheet1");
var cell = worksheet.Cells["A1"];

cell.Value = 100;
package.SaveAs(new System.IO.FileInfo(@"c:\temp\demoOut.xlsx"));
}

We start out by creating an ExcelPackage object. Since we are going to be creating a new file, we don’t pass a FileInfo object to the constructor like we did when reading an existing file. Next we add a new worksheet to the Worksheets collection of the package. Now that we have a worksheet we can access the cells just like we did when reading, in this case we have a variable called ‘cell’ which points to cell A1. Next we put a value in the cell, in this case the number 100. Finally we need to save the file, we do this by calling the SaveAs function on the package. Note, just like when we open a file to read, SaveAs does NOT take a file name as a parameter, but a FileInfo object instead.

Beyond setting cell values, EPPLus also allows you to change the appearance of the sheet. To change the appearance of a single cell we use the Style property on the cell. For example here is how we would change the font properties on a cell using the cell variable we created above.

cell.Style.Font.Name = "Arial";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 12;

We can change the background color of a cell like this:

cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);

First we have to set the PatternType for the background, in this case we will make it solid. Setting the PatternType is required before you can change the color, the second line will throw an exception if the PatternType is not set. In the second line we set the color. If you are doing this from a console application you will need to add a reference to System.Drawing to be able to use System.Drawing.Color.

Another common Excel formatting task is to create borders around a cell. Here is how we would set a thick border around an entire cell:

cell.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);

You can also set each part of the border individually by using the Bottom, Top, Left, and Right properties of the Border. Each of these has a Color and Style property to set the appearance of the border.

So far we have been setting the style on a single cell, but we can also use these properties on a group of cells. For example this code will set the color and border on the block of cells between B1 and C10:

var cells = worksheet.Cells["B1:C10"];
cells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue);                cells.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.MediumDashed);

You will notice that each cell is set to blue, but the border is put around the whole block, not around each cell.

Besides setting properties on cells you can also work with the properties on columns and rows. For example here is how to set the width of a column:
worksheet.Column(1).Width = 100;

It’s important to remember that columns and rows start numbering at one no zero. EPPLus will allow you to use column and row index 0, but you will get an error when you try to open the workbook in Excel. To hide a column you can do this:

worksheet.Column(1).Hidden = true;

There are also similar properties for rows:

worksheet.Row(1).Height = 20;
worksheet.Row(1).Hidden = true;

These are some of the basic tasks you will do when creating an Excel file, but EPPLus lets you do almost anything with a workbook that you can do directly in Excel.