Use the Excel Export API to Add Cell Borders

• Sep 19, 2023
• 3 minutes to read

To specify cell borders, use the XlBorder object. This object inherits from the XlBordersBase base class, which provides properties used to define border line style options.

To set a particular cell border, create an instance of the XlBorder class, and then use the corresponding properties of the XlBorder object to specify the border line style and color. The XlBorderLineStyle enumerator lists the available line styles. The border color is defined by the XlColor value.

You can also use static methods of the XlBorder class to set outside borders of a cell at once.

To set	Use API members
A bottom border	XlBordersBase.BottomLineStyle XlBorder.BottomColor
A top border	XlBordersBase.TopLineStyle XlBorder.TopColor
A left border	XlBordersBase.LeftLineStyle XlBorder.LeftColor
A right border	XlBordersBase.RightLineStyle XlBorder.RightColor
Diagonal borders	XlBorder.DiagonalColor XlBordersBase.DiagonalLineStyle XlBordersBase.DiagonalUp XlBordersBase.DiagonalDown
No border	XlBorder.NoBorders
Outside borders	XlBorder.OutlineBorders XlBorder.AllBorders

To apply border settings to a cell, pass the specified XlBorder object to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property.

Tip

To share border settings with multiple cells in a row at once, use the IXlRow.BlankCells or IXlRow.BulkCells method.

To set borders for the entire row or column, use the IXlRow.ApplyFormatting and IXlColumn.ApplyFormatting methods, or IXlRow.Formatting and IXlColumn.Formatting properties, respectively.

View Example

CellFormattingActions.cs

// Specify a two-dimensional array
// that stores possible line styles for a border.
XlBorderLineStyle[,] lineStyles = new XlBorderLineStyle[,]
{
    {
        XlBorderLineStyle.Thin,
        XlBorderLineStyle.Medium,
        XlBorderLineStyle.Thick,
        XlBorderLineStyle.Double
    },

    {
        XlBorderLineStyle.Dotted,
        XlBorderLineStyle.Dashed,
        XlBorderLineStyle.DashDot,
        XlBorderLineStyle.DashDotDot
    },

    {
        XlBorderLineStyle.SlantDashDot,
        XlBorderLineStyle.MediumDashed,
        XlBorderLineStyle.MediumDashDot,
        XlBorderLineStyle.MediumDashDotDot
    }
};

// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);

// Create a new document.
using(IXlDocument document = exporter.CreateDocument(stream))
{
  document.Options.Culture = CultureInfo.CurrentCulture;

  // Create a worksheet.
  using(IXlSheet sheet = document.CreateSheet())
  {
    for(int i = 0; i < 3; i++)
    {
        sheet.SkipRows(1);

        // Create a worksheet row.
        using(IXlRow row = sheet.CreateRow())
        {
            for(int j = 0; j < 4; j++)
            {
                row.SkipCells(1);

                // Create a new cell in the row.
                using(IXlCell cell = row.CreateCell())
                {
                  // Set outside borders for the created cell
                  // using a particular line style from the lineStyles array.
                  cell.ApplyFormatting(XlBorder.OutlineBorders(Color.SeaGreen, lineStyles[i, j]));
                }
            }
        }
    }
  }
}

CellFormattingActions.vb

' Specify a two-dimensional array that stores possible line styles for a border. 
Dim lineStyles(,) As XlBorderLineStyle = { _
    { XlBorderLineStyle.Thin, XlBorderLineStyle.Medium, XlBorderLineStyle.Thick, XlBorderLineStyle.Double }, _
    { XlBorderLineStyle.Dotted, XlBorderLineStyle.Dashed, XlBorderLineStyle.DashDot, XlBorderLineStyle.DashDotDot }, _
    { XlBorderLineStyle.SlantDashDot, XlBorderLineStyle.MediumDashed, XlBorderLineStyle.MediumDashDot, XlBorderLineStyle.MediumDashDotDot } _
}

' Create an exporter instance.
Dim exporter As IXlExporter = XlExport.CreateExporter(documentFormat)
' Create a new document.
Using document As IXlDocument = exporter.CreateDocument(stream)
    document.Options.Culture = CultureInfo.CurrentCulture
    ' Create a worksheet.
    Using sheet As IXlSheet = document.CreateSheet()
        For i As Integer = 0 To 2
            sheet.SkipRows(1)
            ' Create a worksheet row.
            Using row As IXlRow = sheet.CreateRow()
                For j As Integer = 0 To 3
                    row.SkipCells(1)
                    ' Create a new cell in the row.
                    Using cell As IXlCell = row.CreateCell()
                        ' Set outside borders for the created cell using a particular line style from the lineStyles array.
                        cell.ApplyFormatting(XlBorder.OutlineBorders(Color.SeaGreen, lineStyles(i, j)))
                    End Using
                Next j
            End Using
        Next i
    End Using
End Using

The image below shows the result (the workbook is opened in Microsoft® Excel®).