Using ClosedXML with F#

What is ClosedXML

ClosedXML is a popular open-source library for library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files in .NET applications. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API. In this blog post, we'll take a closer look at ClosedXML and provide some code samples to help you get started.

Git: https://github.com/ClosedXML/ClosedXML
Documentation: https://closedxml.readthedocs.io/en/latest/
Wiki: https://github.com/closedxml/closedxml/wiki

Getting Started with ClosedXML

Let's start with a basic example. Here's an example of how to create a new Excel file and add some data to it:

open ClosedXML.Excel

// Create a new workbook
let workbook = new XLWorkbook()

// Add a new worksheet
let worksheet = workbook.Worksheets.Add("Sheet1")

// Add some data to the worksheet
worksheet.Cell("A1").Value <- "Name"
worksheet.Cell("B1").Value <- "Age"
worksheet.Cell("A2").Value <- "John"
worksheet.Cell("B2").Value <- 30

// Save the workbook to a file
workbook.SaveAs("example.xlsx")

In this example, we're creating a new workbook, adding a new worksheet to it, and then adding some data to the worksheet. We're then saving the workbook to a file named "example.xlsx".

Formatting Cells with ClosedXML

ClosedXML also provides a wide range of formatting options, including font styles, background colours, borders, and more. Here's an example of how to format cells in a worksheet:

// Set the font style for the header row
worksheet.Range("A1:B1").Style.Font.Bold <- true

// Set the background colour for the header row
worksheet.Range("A1:B1").Style.Fill.BackgroundColor <- XLColor.LightBlue

// Add borders to the cells
let tableRangeBorderStyle = worksheet.Range("A1:B2").Style.Border

tableRangeBorderStyle.OutsideBorder <- XLBorderStyleValues.Thin
tableRangeBorderStyle.TopBorder <- XLBorderStyleValues.Thin
tableRangeBorderStyle.BottomBorder <- XLBorderStyleValues.Thin
tableRangeBorderStyle.LeftBorder <- XLBorderStyleValues.Thin
tableRangeBorderStyle.RightBorder <- XLBorderStyleValues.Thin

// Save the workbook to a file
workbook.SaveAs("example.xlsx");

In this example, we're setting the font style and background colour for the header row, and adding borders to the cells.

Working with Formulas in ClosedXML

ClosedXML also provides support for working with formulas in Excel. Here's an example of how to use formulas in a worksheet:

// Add some data to the worksheet
worksheet.Cell("A1").Value <- "Quantity"
worksheet.Cell("B1").Value <- "Price"
worksheet.Cell("C1").Value <- "Total"
worksheet.Cell("A2").Value <- 5
worksheet.Cell("B2").Value <- 10

// Add a formula to calculate the total
worksheet.Cell("C2").FormulaA1 <- "=A2*B2"

// Save the workbook to a file
workbook.SaveAs("example.xlsx")

In this example, we're adding some data to the worksheet and then adding a formula to calculate the total. The formula is using the values in cells A2 and B2 to calculate the product, which is then displayed in cell C2.

Making it functional

The downside of ClosedXML is that it is an OOP library that uses mutable types. However, there is a library Built on top of ClosedXML by Zaid Ajaj - ClosedXML.SimpleSheets.

Here's an example of using SimpleSheets:

open ClosedXML
open ClosedXML.Excel
open ClosedXML.SimpleSheets

type User = { Name: string; Age: int }

let users = [
    { Name = "Jane"; Age = 26 }
    { Name = "John"; Age = 25 }
]

let excelFile = Excel.createFrom(users, [
    Excel.field(fun user -> user.Name)
        .header("Name")
        .headerBackgroundColor(XLColor.DarkCyan)
        .headerFontColor(XLColor.White)

    Excel.field(fun user -> user.Age)
        .header("Age")
        .headerBackgroundColor(XLColor.DarkCyan)
        .headerFontColor(XLColor.White)
])

System.IO.File.WriteAllBytes("Users.xlsx", excelFile)

An alternative to that is creating your own helper functions:

let leftBorder color width (cell : IXLCell) =
    cell.Style.Border.LeftBorder <- width
    cell.Style.Border.LeftBorderColor <- color
    cell

let rightBorder color width (cell : IXLCell) =
    cell.Style.Border.RightBorder <- width
    cell.Style.Border.RightBorderColor <- color
    cell

let topBorder color width (cell : IXLCell) =
    cell.Style.Border.TopBorder <- width
    cell.Style.Border.TopBorderColor <- color
    cell

let bottomBorder color width (cell : IXLCell) =
    cell.Style.Border.BottomBorder <- width
    cell.Style.Border.BottomBorderColor <- color
    cell

let border color width =
    bottomBorder color width
    >> leftBorder color width
    >> topBorder color width
    >> rightBorder color width

This allows us to add a border by doing:

worksheet.Cell("C2")
|> border (XLColor.FromHtml "#BFBFBF") XLBorderStyleValues.Thin 
|> ignore

Conclusion

ClosedXML is a powerful and flexible library for creating and manipulating Excel files in .NET applications. Its simple and intuitive API, performance, and wide range of features make it a good choice for working with Excel files programmatically.