Excel styles and formatting in C# and VB.NET

With GemBox.Spreadsheet you can style and format an Excel file from C# and VB.NET. You can set cell styles like alignment, indentation, borders, shading, protection, number format and more. You can also set font formatting like name, size, color, italic, boldness, underlining, subscript and superscript.

Besides styling the whole cell, you can also style individually different parts of the cell's content. For more information see the Inline Text Formatting example.

You can format one or more cells through the Style property on ExcelCell.Style, CellRange.Style, ExcelRow.Style, and ExcelColumn.Style. When you're modifying the style of multiple cells, we recommend that you use a memory-optimized approach of applying the formatting on the cell range, entire row or entire column instead of formatting each cell individually.

The following example shows how to set different style-specific properties on Excel rows, columns and cells.

Excel styles and formatting on cells, rows, and columns — Screenshot of various Excel styles and formatting applied to cells, rows, and columns

C#
VB.NET

View on GitHub

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Styles");

        worksheet.Rows[0].Style = workbook.Styles[BuiltInCellStyleName.Heading1];
        worksheet.Columns[0].Width = 30 * 256;
        worksheet.Columns[1].Width = 35 * 256;

        worksheet.Cells[0, 0].Value = "Property";
        worksheet.Cells[0, 1].Value = "Result";

        int row = 0;
        worksheet.Cells[row += 2, 0].Value = "Borders";
        worksheet.Cells[row, 1].Style.Borders.SetBorders(
            MultipleBorders.All | MultipleBorders.Diagonal,
            SpreadsheetColor.FromArgb(252, 1, 1),
            LineStyle.Thin);

        worksheet.Cells[row += 2, 0].Value = "FillPattern";
        worksheet.Cells[row, 1].Style.FillPattern.SetPattern(
            FillPatternStyle.ThinHorizontalCrosshatch,
            SpreadsheetColor.FromName(ColorName.Green),
            SpreadsheetColor.FromName(ColorName.Yellow));

        worksheet.Cells[row += 2, 0].Value = "Font.Color";
        worksheet.Cells[row, 1].Value = "Color.Blue";
        worksheet.Cells[row, 1].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue);

        worksheet.Cells[row += 2, 0].Value = "Font.Italic";
        worksheet.Cells[row, 1].Value = "true";
        worksheet.Cells[row, 1].Style.Font.Italic = true;

        worksheet.Cells[row += 2, 0].Value = "Font.Name";
        worksheet.Cells[row, 1].Value = "Comic Sans MS";
        worksheet.Cells[row, 1].Style.Font.Name = "Comic Sans MS";

        worksheet.Cells[row += 2, 0].Value = "Font.ScriptPosition";
        worksheet.Cells[row, 1].Value = "ScriptPosition.Superscript";
        worksheet.Cells[row, 1].Style.Font.ScriptPosition = ScriptPosition.Superscript;

        worksheet.Cells[row += 2, 0].Value = "Font.Size";
        worksheet.Cells[row, 1].Value = "18 * 20";
        worksheet.Cells[row, 1].Style.Font.Size = 18 * 20;

        worksheet.Cells[row += 2, 0].Value = "Font.Strikeout";
        worksheet.Cells[row, 1].Value = "true";
        worksheet.Cells[row, 1].Style.Font.Strikeout = true;

        worksheet.Cells[row += 2, 0].Value = "Font.UnderlineStyle";
        worksheet.Cells[row, 1].Value = "UnderlineStyle.Double";
        worksheet.Cells[row, 1].Style.Font.UnderlineStyle = UnderlineStyle.Double;

        worksheet.Cells[row += 2, 0].Value = "Font.Weight";
        worksheet.Cells[row, 1].Value = "ExcelFont.BoldWeight";
        worksheet.Cells[row, 1].Style.Font.Weight = ExcelFont.BoldWeight;

        worksheet.Cells[row += 2, 0].Value = "HorizontalAlignment";
        worksheet.Cells[row, 1].Value = "HorizontalAlignmentStyle.Center";
        worksheet.Cells[row, 1].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;

        worksheet.Cells[row += 2, 0].Value = "Indent";
        worksheet.Cells[row, 1].Value = "five";
        worksheet.Cells[row, 1].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
        worksheet.Cells[row, 1].Style.Indent = 5;

        worksheet.Cells[row += 2, 0].Value = "IsTextVertical";
        worksheet.Cells[row, 1].Value = "true";
        worksheet.Rows[row].Height = 60 * 20;
        worksheet.Cells[row, 1].Style.IsTextVertical = true;

        worksheet.Cells[row += 2, 0].Value = "NumberFormat";
        worksheet.Cells[row, 1].Value = 1234;
        worksheet.Cells[row, 1].Style.NumberFormat = "#.##0,00 [$Krakozhian Money Units]";

        worksheet.Cells[row += 2, 0].Value = "Rotation";
        worksheet.Cells[row, 1].Value = "35 degrees up";
        worksheet.Cells[row, 1].Style.Rotation = 35;

        worksheet.Cells[row += 2, 0].Value = "ShrinkToFit";
        worksheet.Cells[row, 1].Value = "This property is set to true so this text appears shrunk.";
        worksheet.Cells[row, 1].Style.ShrinkToFit = true;

        worksheet.Cells[row += 2, 0].Value = "VerticalAlignment";
        worksheet.Cells[row, 1].Value = "VerticalAlignmentStyle.Top";
        worksheet.Rows[row].Height = 30 * 20;
        worksheet.Cells[row, 1].Style.VerticalAlignment = VerticalAlignmentStyle.Top;

        worksheet.Cells[row += 2, 0].Value = "WrapText";
        worksheet.Cells[row, 1].Value = "This property is set to true so this text appears broken into multiple lines.";
        worksheet.Cells[row, 1].Style.WrapText = true;

        workbook.Save("Styles and Formatting.%OutputFileType%");
    }
}

Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Styles")

        worksheet.Rows(0).Style = workbook.Styles(BuiltInCellStyleName.Heading1)
        worksheet.Columns(0).Width = 30 * 256
        worksheet.Columns(1).Width = 35 * 256

        worksheet.Cells(0, 0).Value = "Property"
        worksheet.Cells(0, 1).Value = "Result"

        Dim row As Integer = 2
        worksheet.Cells(row, 0).Value = "Borders"
        worksheet.Cells(row, 1).Style.Borders.SetBorders(
            MultipleBorders.All Or MultipleBorders.Diagonal,
            SpreadsheetColor.FromArgb(252, 1, 1),
            LineStyle.Thin)

        row = row + 2
        worksheet.Cells(row, 0).Value = "FillPattern"
        worksheet.Cells(row, 1).Style.FillPattern.SetPattern(
            FillPatternStyle.ThinHorizontalCrosshatch,
            SpreadsheetColor.FromName(ColorName.Green),
            SpreadsheetColor.FromName(ColorName.Yellow))

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.Color"
        worksheet.Cells(row, 1).Value = "Color.Blue"
        worksheet.Cells(row, 1).Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue)

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.Italic"
        worksheet.Cells(row, 1).Value = "true"
        worksheet.Cells(row, 1).Style.Font.Italic = True

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.Name"
        worksheet.Cells(row, 1).Value = "Comic Sans MS"
        worksheet.Cells(row, 1).Style.Font.Name = "Comic Sans MS"

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.ScriptPosition"
        worksheet.Cells(row, 1).Value = "ScriptPosition.Superscript"
        worksheet.Cells(row, 1).Style.Font.ScriptPosition = ScriptPosition.Superscript

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.Size"
        worksheet.Cells(row, 1).Value = "18 * 20"
        worksheet.Cells(row, 1).Style.Font.Size = 18 * 20

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.Strikeout"
        worksheet.Cells(row, 1).Value = "true"
        worksheet.Cells(row, 1).Style.Font.Strikeout = True

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.UnderlineStyle"
        worksheet.Cells(row, 1).Value = "UnderlineStyle.Double"
        worksheet.Cells(row, 1).Style.Font.UnderlineStyle = UnderlineStyle.Double

        row = row + 2
        worksheet.Cells(row, 0).Value = "Font.Weight"
        worksheet.Cells(row, 1).Value = "ExcelFont.BoldWeight"
        worksheet.Cells(row, 1).Style.Font.Weight = ExcelFont.BoldWeight

        row = row + 2
        worksheet.Cells(row, 0).Value = "HorizontalAlignment"
        worksheet.Cells(row, 1).Value = "HorizontalAlignmentStyle.Center"
        worksheet.Cells(row, 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

        row = row + 2
        worksheet.Cells(row, 0).Value = "Indent"
        worksheet.Cells(row, 1).Value = "five"
        worksheet.Cells(row, 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left
        worksheet.Cells(row, 1).Style.Indent = 5

        row = row + 2
        worksheet.Cells(row, 0).Value = "IsTextVertical"
        worksheet.Cells(row, 1).Value = "true"
        worksheet.Rows(row).Height = 60 * 20
        worksheet.Cells(row, 1).Style.IsTextVertical = True

        row = row + 2
        worksheet.Cells(row, 0).Value = "NumberFormat"
        worksheet.Cells(row, 1).Value = 1234
        worksheet.Cells(row, 1).Style.NumberFormat = "#.##0,00 ($Krakozhian Money Units)"

        row = row + 2
        worksheet.Cells(row, 0).Value = "Rotation"
        worksheet.Cells(row, 1).Value = "35 degrees up"
        worksheet.Cells(row, 1).Style.Rotation = 35

        row = row + 2
        worksheet.Cells(row, 0).Value = "ShrinkToFit"
        worksheet.Cells(row, 1).Value = "This property is set to true so this text appears shrunk."
        worksheet.Cells(row, 1).Style.ShrinkToFit = True

        row = row + 2
        worksheet.Cells(row, 0).Value = "VerticalAlignment"
        worksheet.Cells(row, 1).Value = "VerticalAlignmentStyle.Top"
        worksheet.Rows(row).Height = 30 * 20
        worksheet.Cells(row, 1).Style.VerticalAlignment = VerticalAlignmentStyle.Top

        row = row + 2
        worksheet.Cells(row, 0).Value = "WrapText"
        worksheet.Cells(row, 1).Value = "This property is set to true so this text appears broken into multiple lines."
        worksheet.Cells(row, 1).Style.WrapText = True

        workbook.Save("Styles and Formatting.%OutputFileType%")
    End Sub
End Module

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy

Published: October 15, 2018 | Modified: December 19, 2022 | Author: Mario Zorica

Excel styles and formatting in C# and VB.NET

See also

Next steps