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
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using 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 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

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.