Use ClosedXML to write excel file

Office Open XML (also informally known as OOXML) is a zipped, XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents. Starting with the 2007 Microsoft Office system, Microsoft Office uses the XML-based file formats, such as .docx, .xlsx, and .pptx representing Microsoft Word, Microsoft Excel, and Microsoft PowerPoint repectively.

The OpenXML specification is a large and complicated model to implement. Trying to implement this model by your own involves a lot of time and effort. Another option is to use ClosedXML (an open source library created based on OpenXmlSdk) for manipulate OpenXML format files.

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. Having user-friendly model, it makes it easier to deal with OpenXML API.

To install ClosedXML, run the following command in the Package Manager Console:

Install-Package ClosedXML

The most important point about ClosedXML is:

ClosedXML allows you to create Excel files without the Excel application. The typical example is creating Excel reports on a web server.

First you have to import the library for Excel classes:

using ClosedXML.Excel;

In the following code snippet, we are creating a new worksheet Sheet1 and writing the text Hello World! on the cell A1, then save the file as HelloWorld.xlsx:

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sheet1");
    worksheet.Cell("A1").Value = "Hello World!";
    workbook.SaveAs("HelloWorld.xlsx");
}

Change the font for the whole sheet:

worksheet.Style.Font.FontName = "Calibri";

Change the font style and color for a particular cell:

worksheet.Cells("A1").Style.Font.FontSize = 14;
worksheet.Cells("A1").Style.Font.Bold = true;
worksheet.Cells("A1").Style.Font.FontColor = XLColor.White;

Select the range and fill background color:

var range1 = worksheet.Ranges("A1:A25,B1:B25");
range1.Style.Fill.BackgroundColor = XLColor.FromHtml("#556B2F");

Adjusts the width of all columns based on its contents.

worksheet.Columns().AdjustToContents();

These are few examples which demonstrates the basic functionality ClosedXML supports for Excel files.

For official project site and Nuget Package, please visit the github and nuget sites:

References(s):

• https://github.com/ClosedXML/ClosedXML
• https://www.nuget.org/packages/ClosedXML/