C# Excel

last modified July 5, 2023

C# Excel tutorial shows how to work with Excel files in C# with ClosedXML library.

Excel xlsx

In this article we work with xlsx files. The xlsx is a file extension for an open XML spreadsheet file format used by Microsoft Excel. The xlsm files support macros. The xltm are macro-enabled template files. The xls format is a proprietary binary format while xlsx is based on Office Open XML format.

ClosedXML

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files.

$ dotnet add package closedxml

We add the package to the project.

C# Excel simple example

In the first example, we create a new xlsx file with ClosedXML.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook();

var ws = wbook.Worksheets.Add("Sheet1");
ws.Cell("A1").Value = "150";

wbook.SaveAs("simple.xlsx");

We create a new Excel file and write a value to a cell.

using var wbook = new XLWorkbook();

A new XLWorkbook is created.

var ws = wbook.Worksheets.Add("Sheet1");

We add a new sheet to the workbook.

ws.Cell("A1").Value = "150";

A value is written to cell with A1 address.

wbook.SaveAs("simple.xlsx");

The workbook is saved with the SaveAs method.

C# Excel cell

A cell is an intersection of a row and a column. Each cell has a unique address made up of its column letter and row number. For instance, the first cell located in the upper-left corner of a sheet has address A1.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook();

var ws = wbook.AddWorksheet("Sheet1");

ws.FirstCell().Value = 150;

ws.Cell(3, 2).Value = "Hello there!";
ws.Cell("A6").SetValue("falcon").SetActive();

ws.Column(2).AdjustToContents();

wbook.SaveAs("data.xlsx");

In the example, we work with cells.

ws.FirstCell().Value = 150;

The FirstCell retrieves the reference to the first cell in the sheet. We set its value to 150 using the Value property.

ws.Cell(3, 2).Value = "Hello there!";

Another way to reference a cell is used an overloaded Cell method, which takes the row and column numbers as parameters.

ws.Cell("A6").SetValue("falcon").SetActive();

Here, we get a cell via its address (A6), and use the SetValue method to write a string to the cell. The SetActive method makes the cell active.

ws.Column(2).AdjustToContents();

We adjust the width of the second column to the contents; the Hello there! value is fully visible.

C# read Excel file

In the next example, we read from the previously created Excel file.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook("simple.xlsx");

var ws1 = wbook.Worksheet(1); 
var data = ws1.Cell("A1").GetValue<string>();

Console.WriteLine(data);

The example reads a value from a cell.

using var wbook = new XLWorkbook("simple.xlsx");

We open a workbook.

var ws1 = wbook.Worksheet(1);

We navigate to the first sheet.

var data = ws1.Cell("A1").GetValue<string>();

Using the GetValue method, we read a value from cell A1.

C# Excel apply style

A style can be applied via the Style property.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook();

var ws = wbook.Worksheets.Add("Sheet1");

var c1 = ws.Column("A");
c1.Width = 25;

var c2 = ws.Column("B");
c2.Width = 15;

ws.Cell("A3").Value = "an old falcon";
ws.Cell("B2").Value = "150";
ws.Cell("B5").Value = "Sunny day";

ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell("A3").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell("A3").Style.Font.Italic = true;

ws.Cell("B2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Cell("B5").Style.Font.FontColor = XLColor.Red;

wbook.SaveAs("styled.xlsx");

In the example, apply styles to cells.

var c1 = ws.Column("A");
c1.Width = 25;

var c2 = ws.Column("B");
c2.Width = 15;

We set the widths of A and B columns.

ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell("A3").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Cell("A3").Style.Font.Italic = true;

In the A3 cell, we center the text horizontally and vertically and choose a cursive font.

ws.Cell("B2").Style.Border.OutsideBorder = XLBorderStyleValues.Thin;

We set a thin border for B2 cell.

ws.Cell("B5").Style.Font.FontColor = XLColor.Red;

In cell B5, the font color is set to red.

C# Excel ranges

A range is a group of one or more cells. A range address is specified with a upper-left cell address and a lower-bottom address, separated by a colon.

A single range is created with Range; multiple ranges are created with Ranges.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook();

var ws = wbook.Worksheets.Add("Sheet1");

ws.Range("D2:E2").Style.Fill.BackgroundColor = XLColor.Gray;
ws.Ranges("C5, F5:G8").Style.Fill.BackgroundColor = XLColor.Gray;

var rand = new Random();
var range = ws.Range("C10:E15");

foreach (var cell in range.Cells())
{
    cell.Value = rand.Next();
}

ws.Column("C").AdjustToContents();
ws.Column("D").AdjustToContents();
ws.Column("E").AdjustToContents();

wbook.SaveAs("ranges.xlsx");

In the example, we work with ranges.

ws.Range("D2:E2").Style.Fill.BackgroundColor = XLColor.Gray;

We change the background colour for D2:E2 range.

ws.Ranges("C5, F5:G8").Style.Fill.BackgroundColor = XLColor.Gray;

Here, we change the background colour for two ranges.

var rand = new Random();
var range = ws.Range("C10:E15");

foreach (var cell in range.Cells())
{
    cell.Value = rand.Next();
}

In each cell of the C10:E15 range, we set a random value. We use the Cells method to get the cells of the range.

ws.Column("C").AdjustToContents();

We adjust the width of the column C to the written contents so that the whole value is visible.

C# Excel merge cells

To merge cells, we use the Merge method.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook();

var ws = wbook.Worksheets.Add("Sheet1");

ws.Cell("A1").Value = "Sunny day";
ws.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell("A1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;

ws.Range("A1:B2").Merge();

wbook.SaveAs("merged.xlsx");

We merge four cells into one.

ws.Range("A1:B2").Merge();

We call the Merge method on a range of four cells; A1 through B2.

C# Excel sorting

A column can be sorted with the Sort method.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook();

var ws = wbook.Worksheets.Add("Sheet1");

var rand = new Random();
var range = ws.Range("A1:A15");

foreach (var cell in range.Cells())
{
    cell.Value = rand.Next(1, 100);
}

ws.Sort("A");

wbook.SaveAs("sorted.xlsx");

We add fifteen random values to the A column. Then we sort the column with Sort.

C# Excel CellsUsed

The CellsUsed method returns a collection of cells that have a value.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook();

var ws = wbook.Worksheets.Add("Sheet1");

ws.Cell("A1").Value = "sky";
ws.Cell("A2").Value = "cloud";
ws.Cell("A3").Value = "book";
ws.Cell("A4").Value = "cup";
ws.Cell("A5").Value = "snake";
ws.Cell("A6").Value = "falcon";
ws.Cell("B1").Value = "in";
ws.Cell("B2").Value = "tool";
ws.Cell("B3").Value = "war";
ws.Cell("B4").Value = "snow";
ws.Cell("B5").Value = "tree";
ws.Cell("B6").Value = "ten";

var n = ws.Range("A1:C10").CellsUsed().Count();
Console.WriteLine($"There are {n} words in the range");

Console.WriteLine("The following words have three latin letters:");

var words = ws.Range("A1:C10")
    .CellsUsed()
    .Select(c => c.Value.ToString())
    .Where(c => c?.Length == 3)
    .ToList();

words.ForEach(Console.WriteLine);

wbook.SaveAs("usedcells.xlsx");

In the example, we write twelve words to two columns of a sheet.

var n = ws.Range("A1:C10").CellsUsed().Count();
Console.WriteLine($"There are {n} words in the range");

We define a range that is larger than the cells we have written to. With the help of the CellsUsed method, we get all non-empty cells. The Count method counts the number of non-empty cells.

var words = ws.Range("A1:C10")
    .CellsUsed()
    .Select(c => c.Value.ToString())
    .Where(c => c?.Length == 3)
    .ToList();

Here we filter all words that have three latin letters.

C# Excel expression evaluation

With Evaluate, we can evaluate an expression.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook("data.xlsx");

var ws = wbook.Worksheet(1); 

var sum = ws.Evaluate("SUM(A1:A7)");
var max = ws.Evaluate("MAX(A1:A7)");

Console.WriteLine($"The sum is: {sum}");
Console.WriteLine($"The maximum valus is: {max}");

In the example, we have values in the A column. We evaluate SUM and MAX expressions on the values.

C# Excel formula

With the FormulaA1 property, we can insert a formula in the cell.

Program.cs

using ClosedXML.Excel;

using var wbook = new XLWorkbook("data.xlsx");

var ws = wbook.Worksheet(1); 

ws.Cell("A8").FormulaA1 = "SUM(A1:A7)";
ws.Cell("A8").Style.Font.Bold = true;

wbook.SaveAs("data2.xlsx");

In the example, we insert the SUM formula in the cell below the values.

Source

ClosedXML Github page

In this article we have demonstrated how to work with Excel files with C# using ClosedXMLlibrary.

Author

My name is Jan Bodnar and I am a passionate programmer with many years of programming experience. I have been writing programming articles since 2007. So far, I have written over 1400 articles and 8 e-books. I have over eight years of experience in teaching programming.

List all C# tutorials.

Ebooks

C# Excel

Excel xlsx

ClosedXML

C# Excel simple example

C# Excel cell

C# read Excel file

C# Excel apply style

C# Excel ranges

C# Excel merge cells

C# Excel sorting

C# Excel CellsUsed

C# Excel expression evaluation

C# Excel formula

Source

Author