Styles and Formatting

Using GemBox.Spreadsheet you can set custom number formatting, cell styles (alignment, indentation, rotation, borders, shading, protection, text wrapping, shrinking, etc.) and font formatting (size, color, font type, italic and strikeout properties, different levels of boldness, underlining, subscript and superscript).

Following sample shows how to set different style-specific properties on rows, columns and cells.

Screenshot

Styles and Formatting Screenshot

See the full code below, use RUN EXAMPLE to execute.


1using GemBox.Spreadsheet;
2using System;
3using System.IO;
4
5class Sample
6{
7    [STAThread]
8    static void Main(string[] args)
9    {
10        // If using Professional version, put your serial key below.
11        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
12
13        ExcelFile ef = new ExcelFile();
14        ExcelWorksheet ws = ef.Worksheets.Add("Styles and Formatting");
15
16        ws.Cells[0, 1].Value = "Cell style examples:";
17        ws.PrintOptions.PrintGridlines = true;
18
19        int row = 0;
20
21        // Column width of 4, 30 and 36 characters.
22        ws.Columns[0].Width = 4 * 256;
23        ws.Columns[1].Width = 30 * 256;
24        ws.Columns[2].Width = 36 * 256;
25
26        ws.Cells[row += 2, 1].Value = ".Style.Borders.SetBorders(...)";
27        ws.Cells[row, 2].Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(252, 1, 1), LineStyle.Thin);
28
29        ws.Cells[row += 2, 1].Value = ".Style.FillPattern.SetPattern(...)";
30        ws.Cells[row, 2].Style.FillPattern.SetPattern(FillPatternStyle.ThinHorizontalCrosshatch, SpreadsheetColor.FromName(ColorName.Green), SpreadsheetColor.FromName(ColorName.Yellow));
31
32        ws.Cells[row += 2, 1].Value = ".Style.Font.Color =";
33        ws.Cells[row, 2].Value = "Color.Blue";
34        ws.Cells[row, 2].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue);
35
36        ws.Cells[row += 2, 1].Value = ".Style.Font.Italic =";
37        ws.Cells[row, 2].Value = "true";
38        ws.Cells[row, 2].Style.Font.Italic = true;
39
40        ws.Cells[row += 2, 1].Value = ".Style.Font.Name =";
41        ws.Cells[row, 2].Value = "Comic Sans MS";
42        ws.Cells[row, 2].Style.Font.Name = "Comic Sans MS";
43
44        ws.Cells[row += 2, 1].Value = ".Style.Font.ScriptPosition =";
45        ws.Cells[row, 2].Value = "ScriptPosition.Superscript";
46        ws.Cells[row, 2].Style.Font.ScriptPosition = ScriptPosition.Superscript;
47
48        ws.Cells[row += 2, 1].Value = ".Style.Font.Size =";
49        ws.Cells[row, 2].Value = "18 * 20";
50        ws.Cells[row, 2].Style.Font.Size = 18 * 20;
51
52        ws.Cells[row += 2, 1].Value = ".Style.Font.Strikeout =";
53        ws.Cells[row, 2].Value = "true";
54        ws.Cells[row, 2].Style.Font.Strikeout = true;
55
56        ws.Cells[row += 2, 1].Value = ".Style.Font.UnderlineStyle =";
57        ws.Cells[row, 2].Value = "UnderlineStyle.Double";
58        ws.Cells[row, 2].Style.Font.UnderlineStyle = UnderlineStyle.Double;
59
60        ws.Cells[row += 2, 1].Value = ".Style.Font.Weight =";
61        ws.Cells[row, 2].Value = "ExcelFont.BoldWeight";
62        ws.Cells[row, 2].Style.Font.Weight = ExcelFont.BoldWeight;
63
64        ws.Cells[row += 2, 1].Value = ".Style.HorizontalAlignment =";
65        ws.Cells[row, 2].Value = "HorizontalAlignmentStyle.Center";
66        ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
67
68        ws.Cells[row += 2, 1].Value = ".Style.Indent";
69        ws.Cells[row, 2].Value = "five";
70        ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
71        ws.Cells[row, 2].Style.Indent = 5;
72
73        ws.Cells[row += 2, 1].Value = ".Style.IsTextVertical = ";
74        ws.Cells[row, 2].Value = "true";
75        // Set row height to 60 points.
76        ws.Rows[row].Height = 60 * 20;
77        ws.Cells[row, 2].Style.IsTextVertical = true;
78
79        ws.Cells[row += 2, 1].Value = ".Style.NumberFormat";
80        ws.Cells[row, 2].Value = 1234;
81        ws.Cells[row, 2].Style.NumberFormat = "#.##0,00 [$Krakozhian Money Units]";
82
83        ws.Cells[row += 2, 1].Value = ".Style.Rotation";
84        ws.Cells[row, 2].Value = "35 degrees up";
85        ws.Cells[row, 2].Style.Rotation = 35;
86
87        ws.Cells[row += 2, 1].Value = ".Style.ShrinkToFit";
88        ws.Cells[row, 2].Value = "This property is set to true so this text appears shrunk.";
89        ws.Cells[row, 2].Style.ShrinkToFit = true;
90
91        ws.Cells[row += 2, 1].Value = ".Style.VerticalAlignment =";
92        ws.Cells[row, 2].Value = "VerticalAlignmentStyle.Top";
93        // Set row height to 30 points.
94        ws.Rows[row].Height = 30 * 20;
95        ws.Cells[row, 2].Style.VerticalAlignment = VerticalAlignmentStyle.Top;
96
97        ws.Cells[row += 2, 1].Value = ".Style.WrapText";
98        ws.Cells[row, 2].Value = "This property is set to true so this text appears broken into multiple lines.";
99        ws.Cells[row, 2].Style.WrapText = true;
100
101        ef.Save("Styles and Formatting.xls");
102    }
103}
1Imports GemBox.Spreadsheet
2Imports System.IO
3
4Module Samples
5
6    Sub Main()
7
8        ' If using Professional version, put your serial key below.
9        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
10
11        Dim ef As ExcelFile = New ExcelFile
12        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Styles and Formatting")
13
14        ws.Cells(0, 1).Value = "Cell style examples:"
15        ws.PrintOptions.PrintGridlines = True
16
17        Dim row As Integer = 0
18
19        ' Column width of 4, 30 and 36 characters.
20        ws.Columns(0).Width = 4 * 256
21        ws.Columns(1).Width = 30 * 256
22        ws.Columns(2).Width = 36 * 256
23
24        row = row + 2
25        ws.Cells(row, 1).Value = ".Style.Borders.SetBorders(...)"
26        ws.Cells(row, 2).Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(252, 1, 1), LineStyle.Thin)
27
28        row = row + 2
29        ws.Cells(row, 1).Value = ".Style.FillPattern.SetPattern(...)"
30        ws.Cells(row, 2).Style.FillPattern.SetPattern(FillPatternStyle.ThinHorizontalCrosshatch, SpreadsheetColor.FromName(ColorName.Green), SpreadsheetColor.FromName(ColorName.Yellow))
31
32        row = row + 2
33        ws.Cells(row, 1).Value = ".Style.Font.Color ="
34        ws.Cells(row, 2).Value = "Color.Blue"
35        ws.Cells(row, 2).Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue)
36
37        row = row + 2
38        ws.Cells(row, 1).Value = ".Style.Font.Italic ="
39        ws.Cells(row, 2).Value = "true"
40        ws.Cells(row, 2).Style.Font.Italic = True
41
42        row = row + 2
43        ws.Cells(row, 1).Value = ".Style.Font.Name ="
44        ws.Cells(row, 2).Value = "Comic Sans MS"
45        ws.Cells(row, 2).Style.Font.Name = "Comic Sans MS"
46
47        row = row + 2
48        ws.Cells(row, 1).Value = ".Style.Font.ScriptPosition ="
49        ws.Cells(row, 2).Value = "ScriptPosition.Superscript"
50        ws.Cells(row, 2).Style.Font.ScriptPosition = ScriptPosition.Superscript
51
52        row = row + 2
53        ws.Cells(row, 1).Value = ".Style.Font.Size ="
54        ws.Cells(row, 2).Value = "18 * 20"
55        ws.Cells(row, 2).Style.Font.Size = 18 * 20
56
57        row = row + 2
58        ws.Cells(row, 1).Value = ".Style.Font.Strikeout ="
59        ws.Cells(row, 2).Value = "true"
60        ws.Cells(row, 2).Style.Font.Strikeout = True
61
62        row = row + 2
63        ws.Cells(row, 1).Value = ".Style.Font.UnderlineStyle ="
64        ws.Cells(row, 2).Value = "UnderlineStyle.Double"
65        ws.Cells(row, 2).Style.Font.UnderlineStyle = UnderlineStyle.Double
66
67        row = row + 2
68        ws.Cells(row, 1).Value = ".Style.Font.Weight ="
69        ws.Cells(row, 2).Value = "ExcelFont.BoldWeight"
70        ws.Cells(row, 2).Style.Font.Weight = ExcelFont.BoldWeight
71
72        row = row + 2
73        ws.Cells(row, 1).Value = ".Style.HorizontalAlignment ="
74        ws.Cells(row, 2).Value = "HorizontalAlignmentStyle.Center"
75        ws.Cells(row, 2).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center
76
77        row = row + 2
78        ws.Cells(row, 1).Value = ".Style.Indent"
79        ws.Cells(row, 2).Value = "five"
80        ws.Cells(row, 2).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left
81        ws.Cells(row, 2).Style.Indent = 5
82
83        row = row + 2
84        ws.Cells(row, 1).Value = ".Style.IsTextVertical = "
85        ws.Cells(row, 2).Value = "true"
86        ' Set row height to 60 points.
87        ws.Rows(row).Height = 60 * 20
88        ws.Cells(row, 2).Style.IsTextVertical = True
89
90        row = row + 2
91        ws.Cells(row, 1).Value = ".Style.NumberFormat"
92        ws.Cells(row, 2).Value = 1234
93        ws.Cells(row, 2).Style.NumberFormat = "#.##0,00 [$Krakozhian Money Units]"
94
95        row = row + 2
96        ws.Cells(row, 1).Value = ".Style.Rotation"
97        ws.Cells(row, 2).Value = "35 degrees up"
98        ws.Cells(row, 2).Style.Rotation = 35
99
100        row = row + 2
101        ws.Cells(row, 1).Value = ".Style.ShrinkToFit"
102        ws.Cells(row, 2).Value = "This property is set to true so this text appears shrunk."
103        ws.Cells(row, 2).Style.ShrinkToFit = True
104
105        row = row + 2
106        ws.Cells(row, 1).Value = ".Style.VerticalAlignment ="
107        ws.Cells(row, 2).Value = "VerticalAlignmentStyle.Top"
108        ' Set row height to 30 points.
109        ws.Rows(row).Height = 30 * 20
110        ws.Cells(row, 2).Style.VerticalAlignment = VerticalAlignmentStyle.Top
111
112        row = row + 2
113        ws.Cells(row, 1).Value = ".Style.WrapText"
114        ws.Cells(row, 2).Value = "This property is set to true so this text appears broken into multiple lines."
115        ws.Cells(row, 2).Style.WrapText = True
116
117        ef.Save("Styles and Formatting.xls")
118
119    End Sub
120
121End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.