Learn C#

Creating Excel Files in C#

Tim Corey

55m 42s

Creating and working with Excel files is a crucial skill for any C# developer building reporting tools, data exports, or user input interfaces. In this article, we dive deep into a detailed walkthrough provided by Tim Corey in his video "Creating Excel files in C#", as he demonstrates how to create an Excel file using C#, format it, and even read data back from it - all using the EPPlus library.

Whether you're looking to create a new Excel workbook from a List<T>, format Excel worksheet cells, or read structured data back into your application, Tim's example serves as a practical tutorial to cover the essentials of Excel file generation in C#.

Let's break it down step-by-step with reference to Tim's video.

Introduction: Why Use Excel in C#?

Tim begins by explaining that Microsoft Excel is one of the most important tools in business. Excel files are user-friendly, highly versatile, and perfect for representing data. He highlights how creating Excel files in C# can be used as a substitute for complex reporting solutions - making Excel your default reporting tool.

The goal: create an Excel application using C#, populate it with data, apply some formatting, and then read from that Excel file.

Setting Up the Project in Solution Explorer

Tim opens Visual Studio 2019, selects a Console Application (.NET Core), and names the solution ExcelDemoApp. Using a console app provides a clean and distraction-free environment to focus purely on Excel-related code.

He upgrades the Target Framework to .NET 5.0 to leverage C# 9 features like simplified object instantiation.

Adding EPPlus from NuGet Packages

To create an Excel workbook, Tim installs the EPPlus package via NuGet Package Manager. EPPlus is an Excel library that works with .xlsx files (Open XML) and supports full Excel functionality such as charts, tables, styles, and formulas - without needing MS Office or Excel Interop.

Note: EPPlus is free for non-commercial use but requires a license for commercial applications.

He copies the required non-commercial license acceptance line into his code to avoid runtime errors and adds the necessary using OfficeOpenXml; directive.

Defining the Excel File Path

Using System.IO.FileInfo, Tim sets a hardcoded file path:

var file = new FileInfo(@"C:\demos\youtubedemo.xlsx");

var file = new FileInfo(@"C:\demos\youtubedemo.xlsx");

Dim file As New FileInfo("C:\demos\youtubedemo.xlsx")

Dim file As New FileInfo("C:\demos\youtubedemo.xlsx")

This file does not exist initially - it will be created dynamically by the program.

Creating a Data Model

To populate the Excel worksheet, Tim defines a simple PersonModel class with the following properties:

int Id
string FirstName
string LastName

He uses a helper method GetSetupData() to return a List<PersonModel> populated with sample data like Tim Corey, Sue Storm, and Jane Smith.

Saving Data to Excel File Asynchronously

In modern development, blocking the UI thread is unacceptable. That's why Tim defines an asynchronous method:

static async Task SaveExcelFile(List<PersonModel> people, FileInfo file)

static async Task SaveExcelFile(List<PersonModel> people, FileInfo file)

Private Shared Async Function SaveExcelFile(people As List(Of PersonModel), file As FileInfo) As Task

Private Shared Async Function SaveExcelFile(people As List(Of PersonModel), file As FileInfo) As Task

Before writing to Excel, he checks if the file exists and deletes it to avoid naming conflicts.

if (file.Exists) file.Delete();

if (file.Exists) file.Delete();

If file.Exists Then file.Delete()

If file.Exists Then file.Delete()

Creating and Formatting the Excel Worksheet

Tim uses the EPPlus ExcelPackage object inside a using block to ensure proper disposal:

using var package = new ExcelPackage(file);

using var package = new ExcelPackage(file);

Using package As New ExcelPackage(file)
End Using

Using package As New ExcelPackage(file)
End Using

He adds a new worksheet to the workbook:

var ws = package.Workbook.Worksheets.Add("MainReport");

var ws = package.Workbook.Worksheets.Add("MainReport");

Dim ws = package.Workbook.Worksheets.Add("MainReport")

Dim ws = package.Workbook.Worksheets.Add("MainReport")

Loading Data

Using LoadFromCollection, he inserts the list of people starting at cell "A2":

ws.Cells["A2"].LoadFromCollection(people, true);

ws.Cells["A2"].LoadFromCollection(people, true);

ws.Cells("A2").LoadFromCollection(people, True)

ws.Cells("A2").LoadFromCollection(people, True)

true ensures headers are included (like "Id", "FirstName", "LastName").

Then, AutoFitColumns() adjusts column widths automatically:

ws.Cells[ws.Dimension.Address].AutoFitColumns();

ws.Cells[ws.Dimension.Address].AutoFitColumns();

ws.Cells(ws.Dimension.Address).AutoFitColumns()

ws.Cells(ws.Dimension.Address).AutoFitColumns()

Finally, he saves the Excel file:

await package.SaveAsync();

await package.SaveAsync();

Await package.SaveAsync()

Await package.SaveAsync()

Styling Excel Cells and Rows

To make the Excel file more readable, Tim shows how to apply styles:

Title Row

ws.Cells["A1"].Value = "Our Cool Report";
ws.Cells["A1:C1"].Merge = true; ws.Row(1).Style.Font.Size = 24;
ws.Row(1).Style.Font.Color.SetColor(Color.Blue);
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

ws.Cells["A1"].Value = "Our Cool Report";
ws.Cells["A1:C1"].Merge = true; ws.Row(1).Style.Font.Size = 24;
ws.Row(1).Style.Font.Color.SetColor(Color.Blue);
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

ws.Cells("A1").Value = "Our Cool Report"
ws.Cells("A1:C1").Merge = True
ws.Row(1).Style.Font.Size = 24
ws.Row(1).Style.Font.Color.SetColor(Color.Blue)
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center

ws.Cells("A1").Value = "Our Cool Report"
ws.Cells("A1:C1").Merge = True
ws.Row(1).Style.Font.Size = 24
ws.Row(1).Style.Font.Color.SetColor(Color.Blue)
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center

Header Row

ws.Row(2).Style.Font.Bold = true;
ws.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

ws.Row(2).Style.Font.Bold = true;
ws.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

ws.Row(2).Style.Font.Bold = True
ws.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center

ws.Row(2).Style.Font.Bold = True
ws.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center

Custom Column Width

ws.Column(3).Width = 20;

ws.Column(3).Width = 20;

ws.Column(3).Width = 20

ws.Column(3).Width = 20

This section demonstrates advanced formatting such as:

Merged cells
Font styling
Background colors
Row/column alignment
Width adjustments

These mimic typical MS Office Excel formatting features.

Reading Data from an Excel File in C#

Now that we've written data to Excel, it's time to read it back into C#.

Tim introduces the method:

static async Task<List<PersonModel>> LoadExcelFile(FileInfo file)

static async Task<List<PersonModel>> LoadExcelFile(FileInfo file)

Private Shared Async Function LoadExcelFile(file As FileInfo) As Task(Of List(Of PersonModel))

Private Shared Async Function LoadExcelFile(file As FileInfo) As Task(Of List(Of PersonModel))

He sets up row and column counters:

int row = 3; // Skip title and header rows int col = 1;

int row = 3; // Skip title and header rows int col = 1;

Dim row As Integer = 3 ' Skip title and header rows
Dim col As Integer = 1

Dim row As Integer = 3 ' Skip title and header rows
Dim col As Integer = 1

Looping Through Rows

Using a while loop, he checks for non-empty cells and reads values:

while (!string.IsNullOrWhiteSpace(ws.Cells[row, col].Value?.ToString()))

while (!string.IsNullOrWhiteSpace(ws.Cells[row, col].Value?.ToString()))

While Not String.IsNullOrWhiteSpace(ws.Cells(row, col).Value?.ToString())

While Not String.IsNullOrWhiteSpace(ws.Cells(row, col).Value?.ToString())

Inside the loop, values are read and parsed:

p.Id = int.Parse(ws.Cells[row, col].Value.ToString());
p.FirstName = ws.Cells[row, col + 1].Value.ToString();
p.LastName = ws.Cells[row, col + 2].Value.ToString();

p.Id = int.Parse(ws.Cells[row, col].Value.ToString());
p.FirstName = ws.Cells[row, col + 1].Value.ToString();
p.LastName = ws.Cells[row, col + 2].Value.ToString();

p.Id = Integer.Parse(ws.Cells(row, col).Value.ToString())
p.FirstName = ws.Cells(row, col + 1).Value.ToString()
p.LastName = ws.Cells(row, col + 2).Value.ToString()

p.Id = Integer.Parse(ws.Cells(row, col).Value.ToString())
p.FirstName = ws.Cells(row, col + 1).Value.ToString()
p.LastName = ws.Cells(row, col + 2).Value.ToString()

The List<PersonModel> is populated and returned.

Verifying Imported Data

Tim loops through the returned list and writes each entry to the console:

foreach (var p in peopleFromExcel)
{
     Console.WriteLine($"{p.Id} {p.FirstName} {p.LastName}");
}

foreach (var p in peopleFromExcel)
{
     Console.WriteLine($"{p.Id} {p.FirstName} {p.LastName}");
}

For Each p In peopleFromExcel
    Console.WriteLine($"{p.Id} {p.FirstName} {p.LastName}")
Next

For Each p In peopleFromExcel
    Console.WriteLine($"{p.Id} {p.FirstName} {p.LastName}")
Next

To demonstrate the flexibility of Excel as a user input tool, Tim manually adds new records ("Bill Smith", "Mary White") directly in the Excel file. When re-running the app, these entries are imported without changing the code - proving Excel's potential as a data input interface.

Practical Use Cases

Tim highlights use cases for the C# Excel solution:

Exporting database tables to Excel
Importing data entered by end users
Using Excel as a reporting dashboard
Creating structured workbooks for data processing

And all of this without relying on Microsoft.Office.Interop.Excel, which requires Excel to be installed.

Final Notes on EPPlus and Excel Files

Tim explains that .xlsx files created programmatically don't contain all metadata until opened and saved in Excel. This is why the file size increases after opening it in the real Excel application.

He also notes that EPPlus is a polished, robust library with support for:

Charts
Images
Sparklines
Formulas
Page setup
Borders
Cell formatting

Conclusion

Creating Excel files in C# doesn't have to be difficult or expensive. Thanks to the EPPlus Excel library, developers can build feature-rich Excel documents programmatically without needing MS Office. As Tim Corey demonstrates, with just a few lines of code you can:

Create an Excel file
Populate rows and columns
Apply styling
Read data back
Use Excel as a user-facing UI

This makes it a fantastic choice for lightweight reporting tools, data import/export, or simply providing your users with an interface they already know.

So next time you're building a project and need to output or consume Excel data, try implementing the sample code from Tim Corey's video - and bring the power of Excel into your .NET Framework or .NET Core applications.