Create Excel Files in C#

April 23, 2017 

Creating report files in Excel format is a feature that many users need out of their software applications.  

In this article. we'll cover creating an Excel spreadsheet in C# using a third-party library called EPPlus.

EPPlus is a .NET library that reads and writes Excel files using the Open Office XML format (*.xlsx).  It is licensed under GNU Library General Public License (LGPL) so it can be freely used in commercial software.

Installation

In Visual Studio 2017, go to Tools -> NuGet Package Manager -> Package Manager Console

In the console window that appears, type the following at the prompt to install it into the current project:

PM > Install-Package EPPlus

After it is finished, check the References under Solution Explorer and verify that the EPPlus assembly is listed.

Add to Project

We add line 7 below so we can use the types inside the namespace:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using OfficeOpenXml;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }
}

Create a Workbook

We call the ExcelPackage class constructor to create a workbook.   In the below code, we do this within a using block so we don't have to explicitly dispose of the object.

We add 3 blank worksheets to the workbook by using the Add() method in the Worksheets class.   Finally, we save the file to a path using the SaveAs() method.

using (ExcelPackage excel = new ExcelPackage())
{
  excel.Workbook.Worksheets.Add("Worksheet1");
  excel.Workbook.Worksheets.Add("Worksheet2");
  excel.Workbook.Worksheets.Add("Worksheet3");

  FileInfo excelFile = new FileInfo(@"C:\Users\amir\Desktop\test.xlsx");
  excel.SaveAs(excelFile);
}

The SaveAs method will throw an exception if there are no worksheets defined. 

Select a Worksheet

Let's say we want to add some data to Worksheet1.   How can we target it?

var excelWorksheet = excel.Workbook.Worksheets["Worksheet1"];

Add a Row

Spreadsheets typically have header rows.  Let's add a header row to Worksheet1.  

For this, we create a List containing an array of strings:

List<string[]> headerRow = new List<string[]>()
{
  new string[] { "ID", "First Name", "Last Name", "DOB" }
};

Before we can pass this object into our worksheet, we must figure out the cell range for the header row:

// Determine the header range (e.g. A1:E1)
string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";

Here's how we load the data into the worksheet:

// Popular header row data
excelWorksheet.Cells[headerRange].LoadFromArrays(headerRow);

Putting it all together here is the code block:

using (ExcelPackage excel = new ExcelPackage())
{
  excel.Workbook.Worksheets.Add("Worksheet1");
  excel.Workbook.Worksheets.Add("Worksheet2");
  excel.Workbook.Worksheets.Add("Worksheet3");
  
  var headerRow = new List<string[]>()
  {
    new string[] { "ID", "First Name", "Last Name", "DOB" }
  };
  
  // Determine the header range (e.g. A1:D1)
  string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";

  // Target a worksheet
  var worksheet = excel.Workbook.Worksheets["Worksheet1"];
  
  // Popular header row data
  worksheet.Cells[headerRange].LoadFromArrays(headerRow);
  
  FileInfo excelFile = new FileInfo(@"C:\Users\amir\Desktop\test.xlsx");
  excel.SaveAs(excelFile);
}

If we open the file in Excel, we see that the header row appears in the first worksheet:


Row Styling

We can easily change the font size, color, and weight of any row.

worksheet.Cells[headerRange].Style.Font.Bold = true;
worksheet.Cells[headerRange].Style.Font.Size = 14;
worksheet.Cells[headerRange].Style.Font.Color.SetColor(System.Drawing.Color.Blue);

 

Add Data to Specific Cell

We can easily add data to a cell by setting the Value property of the Cell element.

worksheet.Cells["A1"].Value = "Hello World!";

Add Data to Multiple Cells

Inserting data into multiple cells is also straightforward.   We can populate a list containing an array that corresponds to each row.    

var cellData= new List()
{
  new object[] {0, 0, 0, 1},
  new object[] {10,7.32,7.42,1},
  new object[] {20,5.01,5.24,1},
  new object[] {30,3.97,4.16,1},
  new object[] {40,3.97,4.16,1},
  new object[] {50,3.97,4.16,1},
  new object[] {60,3.97,4.16,1},
  new object[] {70,3.97,4.16,1},
  new object[] {80,3.97,4.16,1},
  new object[] {90,3.97,4.16,1},
  new object[] {100,3.97,4.16,1}
};

excelWorksheet.Cells[2, 1].LoadFromArrays(cellData);

Here we add the data from cellData into the worksheet starting on row 2, column 1.   Remember that we already inserted a header row on row 1.

Check if Excel is installed

We can use this line of code to determine if Excel is installed:

bool isExcelInstalled = Type.GetTypeFromProgID("Excel.Application") != null ? true : false;

If the expression evaluates to true, then we can safely open the file using the below code:

if (isExcelInstalled) 
{
  System.Diagnostics.Process.Start(excelFile.ToString());
}

What about Microsoft.Office.Interop.Excel?

If the user has Excel installed on their machine, we could tap into the Microsoft.Office.Interop.Excel assembly which instructs .NET on how to call the Excel COM libraries.

This approach has a couple flaws though.   Excel file generation would fail if the user didn't have Excel installed.  Also, what happens when the version of the assembly we're using is incompatible with the latest version of Excel?

A software application shouldn't rely on an Excel installation in order to create a spreadsheet which is why I discourage using Microsoft.Office.Interop.Excel.


+ Recent posts