Excel styles and formatting

The following example shows how to use GemBox.Spreadsheet to set various style and format options like alignment, indentation, borders, and font related properties on Excel rows, columns, and cells.

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
Excel styles and formatting on cells, rows, and columns
Screenshot of various Excel styles and formatting applied to cells, rows, and columns

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 using a memory-optimized approach of applying the formatting on acell range, entire row, or entire column instead of formatting each cell individually.

See also


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