New project from FarawayTech: Zima Weather — Weather and calendar dashboard for busy professionals.
Excel Writing in C# with EPPlus
Here we cover the basics of writing Excel files in C#. This can also be useful when you only read Excel files from 3rd parties, for example, for unit testing.
To write an Excel file using EPPlus in C#, we first need to create an instance of the ExcelPackage
class.
using (var package = new ExcelPackage(new FileInfo(@"C:\temp\excel\myworkbook.xlsx")))
{
// package work
}
Next, we can add a worksheet to our Excel package and write data to the cells in this worksheet:
using (var package = new ExcelPackage(new FileInfo(@"C:\temp\excel\myworkbook.xlsx")))
{
var worksheet = package.Workbook.Worksheets.Add("Worksheet1");
worksheet.Cells[1, 1].Value = "Hello";
worksheet.Cells["B1"].Value = "world!";
package.Save();
}
Here are a few useful configuration options when writing to Excel files:
-
worksheet.Cells.AutoFitColumns()
— Automatically fits the column width to the maximum length of the cell value in the column. -
worksheet.Column(1).Width = 20
— Manually set the width of a column.
Unfortunately, unlike CSVHelper, EPPlus doesn't automatically convert Date/Time to the desired string format. You have to format the Date/Time values manually before writing to the Excel file. Here's how you can do it:
var dateTime = DateTime.Now;
worksheet.Cells[1, 1].Value = dateTime.ToString("MM/dd/yyyy");