ClosedXML Nicely Formatted Header Row

In this article I’ll show a simple example of nicely formatting a header row in a generated Excel document (using the open source ClosedXML library).

If you would first like some background into setting up and calling functions using .Net and the ClosedXML library, have a look at a previous article I wrote, or visit my site (jwcooney.com) and browse the available categories.

Overview

As I mentioned above, I will be expanding on example code from an introductory article that I wrote a little while ago. This is written in VB.NET, but the code is very similar in C#.

Also,  if you don’t already have MS Office installed, then you’ll need a way to open the automatically generated Excel documents you will be creating. I find that a good free option is the Read-only version of Excel called Excel Mobile that Microsoft offers for free on the Microsoft Store site.

What we are Doing:

So for starters, we’ll generate a new Excel Workbook document from a VB.NET WebForms page. It will look like the example picture at the start of this article, where we have formatted the header cell to have large, bold, blue text. Also the cells in the first row are merged from A1 to D1, and the total row height has been made a lot larger than the default height.

How we do it:

ClosedXML makes formatting Excel documents quite easy. Here are the commands that make each of our formatting options possible:

1. Setting the header cell value: you can use the IXLCell object to address each cell in your Excel document. Then you can set the cell value using:

   Dim cellSheetHeading As IXLCell = workSheet.Cell(1, 1)
   cellSheetHeading.SetValue("THIS IS A TEST HEADER")

2. Increasing the font size to 18 point:

   cellSheetHeading.Style.Font.FontSize = 18

3. Bolding the Heading Text:

   cellSheetHeading.Style.Font.SetBold()

4. Changing the Heading Text Color to Blue:

   cellSheetHeading.Style.Font.FontColor = XLColor.Blue

5. Increasing the Row Height of the Header Row: Notice that from the IXLCell object we can address the cell’s row using the .WorkSheetRow method:

   cellSheetHeading.WorksheetRow.Height = 50

6. Merging Cells: You can merge multiple cells by the WorkSheet object’s Range.Merge method. You can merge cells horizontally as well as vertically. In this example we are simply merging a few cells horizontally in one row:

   workSheet.Range("A1:D1").Row(1).Merge()

The Example WebForm:

Now that we’ve reviewed the ways to update the formatting of the header of your Excel WorkSheet, here is a working example that you can run in your instance of Visual Studio. The page is written with code inline for simplicity. Also, the Excel is generated immediately on page load so you should see a prompt right away to save your new Excel document.

<%@ Page Language="vb"%>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="ClosedXML.Excel" %>
<script runat="server">
Protected Sub Page_Load(sender As Object, e As System.EventArgs)
Dim workbook As XLWorkbook = New XLWorkbook()
Dim workSheet As IXLWorksheet = workbook.Worksheets.Add("TEST WORKSHEET")

' Add the header row and format the cell contents to look like a nice header
Dim cellSheetHeading As IXLCell = workSheet.Cell(1, 1)
cellSheetHeading.SetValue("THIS IS A TEST HEADER")
cellSheetHeading.Style.Font.FontSize = 18
cellSheetHeading.Style.Font.SetBold()
cellSheetHeading.Style.Font.FontColor = XLColor.Blue
cellSheetHeading.WorksheetRow.Height = 50
workSheet.Range("A1:D1").Row(1).Merge()

' Add a second row
workSheet.Cell(2, 1).SetValue("here is some data in the second row")

' Below is the code to output the new Excel Workbook
Dim httpResponse = Response
httpResponse.Clear()
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
httpResponse.AddHeader("content-disposition", "attachment;filename=""TestDocument.xlsx""")
Using tmpMemoryStream As MemoryStream = New MemoryStream()
workbook.SaveAs(tmpMemoryStream)
tmpMemoryStream.WriteTo(httpResponse.OutputStream)
tmpMemoryStream.Close()
End Using
httpResponse.End()
End Sub
</script>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div></div>
</form>
</body>
</html>

Summary:

I hope this example has been useful to you and helps you get a feeling for formatting your Excel documents. The ClosedXML library has been a huge help in simplifying what it takes to create new Excel documents programmatically.

Let me know in the comments section below if you have any questions or comments.