Write Excel file in C# and VB.NET

GemBox.Spreadsheet can write many Excel file formats (including XLS, XLSX, CSV and ODS) in the same manner. Specifically, it uses a Save method to write to a physical Excel file or the Excel file's stream in C# and VB.NET.

The following sample demonstrates how to write typical table data to a new Excel file. It is also shows how to merge cells, specify column properties, and set the cell style, value, and borders.

Note that this sample creates an entire table from scratch. In most cases you would use a template Excel file with a predefined table to accomplish the same task by writing into that template.

Screenshot

Writing Screenshot

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


1using System;
2using System.Drawing;
3using System.Text;
4using GemBox.Spreadsheet;
5
6class Sample
7{
8    [STAThread]
9    static void Main(string[] args)
10    {
11        // If using Professional version, put your serial key below.
12        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
13
14        ExcelFile ef = new ExcelFile();
15        ExcelWorksheet ws = ef.Worksheets.Add("Writing");
16
17        object[,] skyscrapers = new object[21, 7]
18		    {
19			    {"Rank", "Building", "City", "Metric", "Imperial", "Floors", "Built (Year)"},
20			    { 1, "Taipei 101", "Taipei", 509, 1671, 101, 2004},
21			    { 2, "Petronas Tower 1", "Kuala Lumpur", 452, 1483, 88, 1998},
22			    { 3, "Petronas Tower 2", "Kuala Lumpur", 452, 1483, 88, 1998},
23			    { 4, "Sears Tower", "Chicago", 442, 1450, 108, 1974},
24			    { 5, "Jin Mao Tower", "Shanghai", 421, 1380, 88, 1998},
25			    { 6, "2 International Finance Centre", "Hong Kong", 415, 1362, 88, 2003},
26			    { 7, "CITIC Plaza", "Guangzhou", 391, 1283, 80, 1997},
27			    { 8, "Shun Hing Square", "Shenzhen", 384, 1260, 69, 1996},
28			    { 9, "Empire State Building", "New York City", 381, 1250, 102, 1931},
29			    {10, "Central Plaza", "Hong Kong", 374, 1227, 78, 1992},
30			    {11, "Bank of China Tower", "Hong Kong", 367, 1205, 72, 1990},
31			    {12, "Emirates Office Tower", "Dubai", 355, 1163, 54, 2000},
32			    {13, "Tuntex Sky Tower", "Kaohsiung", 348, 1140, 85, 1997},
33			    {14, "Aon Center", "Chicago", 346, 1136, 83, 1973},
34			    {15, "The Center", "Hong Kong", 346, 1135, 73, 1998},
35			    {16, "John Hancock Center", "Chicago", 344, 1127, 100, 1969},
36			    {17, "Ryugyong Hotel", "Pyongyang", 330, 1083, 105, 1992},
37			    {18, "Burj Al Arab", "Dubai", 321, 1053, 60, 1999},
38			    {19, "Chrysler Building", "New York City", 319, 1046, 77, 1930},
39			    {20, "Bank of America Plaza", "Atlanta", 312, 1023, 55, 1992}
40		    };
41
42        ws.Cells[0, 0].Value = "Example of writing typical table - tallest buildings in the world (2004):";
43
44        // Column width of 8, 30, 16, 9, 9, 9, 9, 4 and 5 characters.
45        ws.Columns[0].Width = 8 * 256;
46        ws.Columns[1].Width = 30 * 256;
47        ws.Columns[2].Width = 16 * 256;
48        ws.Columns[3].Width = 9 * 256;
49        ws.Columns[4].Width = 9 * 256;
50        ws.Columns[5].Width = 9 * 256;
51        ws.Columns[6].Width = 9 * 256;
52        ws.Columns[7].Width = 4 * 256;
53        ws.Columns[8].Width = 5 * 256;
54
55        int i, j;
56
57        for (j = 0; j < 7; j++)
58            ws.Cells[3, j].Value = skyscrapers[0, j];
59
60        ws.Cells.GetSubrangeAbsolute(2, 0, 3, 0).Merged = true;
61        ws.Cells.GetSubrangeAbsolute(2, 1, 3, 1).Merged = true;
62        ws.Cells.GetSubrangeAbsolute(2, 2, 3, 2).Merged = true;
63        ws.Cells.GetSubrangeAbsolute(2, 3, 2, 4).Merged = true;
64        ws.Cells[2, 3].Value = "Height";
65        ws.Cells.GetSubrangeAbsolute(2, 5, 3, 5).Merged = true;
66        ws.Cells.GetSubrangeAbsolute(2, 6, 3, 6).Merged = true;
67
68        CellStyle tmpStyle = new CellStyle();
69        tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
70        tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
71        tmpStyle.FillPattern.SetSolid(Color.Chocolate);
72        tmpStyle.Font.Weight = ExcelFont.BoldWeight;
73        tmpStyle.Font.Color = Color.White;
74        tmpStyle.WrapText = true;
75        tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
76
77        ws.Cells.GetSubrangeAbsolute(2, 0, 3, 6).Style = tmpStyle;
78
79        tmpStyle = new CellStyle();
80        tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
81        tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
82        tmpStyle.Font.Weight = ExcelFont.BoldWeight;
83
84        CellRange mergedRange = ws.Cells.GetSubrangeAbsolute(4, 7, 13, 7);
85        mergedRange.Merged = true;
86        mergedRange.Value = "T o p   1 0";
87        tmpStyle.Rotation = -90;
88        tmpStyle.FillPattern.SetSolid(Color.Lime);
89        mergedRange.Style = tmpStyle;
90
91        mergedRange = ws.Cells.GetSubrangeAbsolute(4, 8, 23, 8);
92        mergedRange.Merged = true;
93        mergedRange.Value = "T o p   2 0";
94        tmpStyle.IsTextVertical = true;
95        tmpStyle.FillPattern.SetSolid(Color.Gold);
96        mergedRange.Style = tmpStyle;
97
98        mergedRange = ws.Cells.GetSubrangeAbsolute(14, 7, 23, 7);
99        mergedRange.Merged = true;
100        mergedRange.Style = tmpStyle;
101
102        for (i = 0; i < 20; i++)
103            for (j = 0; j < 7; j++)
104            {
105                ExcelCell cell = ws.Cells[i + 4, j];
106
107                cell.Value = skyscrapers[i + 1, j];
108
109                if (i % 2 == 0)
110                    cell.Style.FillPattern.SetSolid(Color.LightSkyBlue);
111                else
112                    cell.Style.FillPattern.SetSolid(Color.FromArgb(210, 210, 230));
113
114                if (j == 3)
115                    cell.Style.NumberFormat = "#\" m\"";
116
117                if (j == 4)
118                    cell.Style.NumberFormat = "#\" ft\"";
119
120                if (j > 2)
121                    cell.Style.Font.Name = "Courier New";
122
123                cell.Style.Borders[IndividualBorder.Right].LineStyle = LineStyle.Thin;
124            }
125
126        ws.Cells.GetSubrange("A5", "I24").Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Double);
127        ws.Cells.GetSubrange("A3", "G4").Style.Borders.SetBorders(MultipleBorders.Vertical | MultipleBorders.Top, Color.Black, LineStyle.Double);
128        ws.Cells.GetSubrange("A5", "H14").Style.Borders.SetBorders(MultipleBorders.Bottom | MultipleBorders.Right, Color.Black, LineStyle.Double);
129
130        ws.Cells["A27"].Value = "Notes:";
131        ws.Cells["A28"].Value = "a) \"Metric\" and \"Imperial\" columns use custom number formatting.";
132        ws.Cells["A29"].Value = "b) All number columns use \"Courier New\" font for improved number readability.";
133        ws.Cells["A30"].Value = "c) Multiple merged ranges were used for table header and categories header.";
134
135        ws.PrintOptions.FitWorksheetWidthToPages = 1;
136
137        ef.Save("Writing.xls");
138    }
139}
1Imports System.Text
2Imports System.Drawing
3Imports GemBox.Spreadsheet
4
5Module Samples
6
7    Sub Main()
8
9        ' If using Professional version, put your serial key below.
10        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
11
12        Dim ef As ExcelFile = New ExcelFile
13        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Writing")
14
15        Dim skyscrapers(,) As Object = New Object(20, 6) _
16        {
17         {"Rank", "Building", "City", "Metric", "Imperial", "Floors", "Built (Year)"},
18         {1, "Taipei 101", "Taipei", 509, 1671, 101, 2004},
19         {2, "Petronas Tower 1", "Kuala Lumpur", 452, 1483, 88, 1998},
20         {3, "Petronas Tower 2", "Kuala Lumpur", 452, 1483, 88, 1998},
21         {4, "Sears Tower", "Chicago", 442, 1450, 108, 1974},
22         {5, "Jin Mao Tower", "Shanghai", 421, 1380, 88, 1998},
23         {6, "2 International Finance Centre", "Hong Kong", 415, 1362, 88, 2003},
24         {7, "CITIC Plaza", "Guangzhou", 391, 1283, 80, 1997},
25         {8, "Shun Hing Square", "Shenzhen", 384, 1260, 69, 1996},
26         {9, "Empire State Building", "New York City", 381, 1250, 102, 1931},
27         {10, "Central Plaza", "Hong Kong", 374, 1227, 78, 1992},
28         {11, "Bank of China Tower", "Hong Kong", 367, 1205, 72, 1990},
29         {12, "Emirates Office Tower", "Dubai", 355, 1163, 54, 2000},
30         {13, "Tuntex Sky Tower", "Kaohsiung", 348, 1140, 85, 1997},
31         {14, "Aon Center", "Chicago", 346, 1136, 83, 1973},
32         {15, "The Center", "Hong Kong", 346, 1135, 73, 1998},
33         {16, "John Hancock Center", "Chicago", 344, 1127, 100, 1969},
34         {17, "Ryugyong Hotel", "Pyongyang", 330, 1083, 105, 1992},
35         {18, "Burj Al Arab", "Dubai", 321, 1053, 60, 1999},
36         {19, "Chrysler Building", "New York City", 319, 1046, 77, 1930},
37         {20, "Bank of America Plaza", "Atlanta", 312, 1023, 55, 1992}
38        }
39
40        ws.Cells(0, 0).Value = "Example of writing typical table - tallest buildings in the world (2004):"
41
42        ' Column width of 8, 30, 16, 9, 9, 9, 9, 4 and 5 characters.
43        ws.Columns(0).Width = 8 * 256
44        ws.Columns(1).Width = 30 * 256
45        ws.Columns(2).Width = 16 * 256
46        ws.Columns(3).Width = 9 * 256
47        ws.Columns(4).Width = 9 * 256
48        ws.Columns(5).Width = 9 * 256
49        ws.Columns(6).Width = 9 * 256
50        ws.Columns(7).Width = 4 * 256
51        ws.Columns(8).Width = 5 * 256
52
53        Dim i As Integer, j As Integer
54
55        For j = 0 To 7 - 1 Step j + 1
56            ws.Cells(3, j).Value = skyscrapers(0, j)
57        Next
58
59        ws.Cells.GetSubrangeAbsolute(2, 0, 3, 0).Merged = True
60        ws.Cells.GetSubrangeAbsolute(2, 1, 3, 1).Merged = True
61        ws.Cells.GetSubrangeAbsolute(2, 2, 3, 2).Merged = True
62        ws.Cells.GetSubrangeAbsolute(2, 3, 2, 4).Merged = True
63        ws.Cells(2, 3).Value = "Height"
64        ws.Cells.GetSubrangeAbsolute(2, 5, 3, 5).Merged = True
65        ws.Cells.GetSubrangeAbsolute(2, 6, 3, 6).Merged = True
66
67        Dim tmpStyle As CellStyle = New CellStyle
68        tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center
69        tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center
70        tmpStyle.FillPattern.SetSolid(Color.Chocolate)
71        tmpStyle.Font.Weight = ExcelFont.BoldWeight
72        tmpStyle.Font.Color = Color.White
73        tmpStyle.WrapText = True
74        tmpStyle.Borders.SetBorders(MultipleBorders.Right Or MultipleBorders.Top, Color.Black, LineStyle.Thin)
75
76        ws.Cells.GetSubrangeAbsolute(2, 0, 3, 6).Style = tmpStyle
77
78        tmpStyle = New CellStyle
79
80        tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center
81        tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center
82        tmpStyle.Font.Weight = ExcelFont.BoldWeight
83
84        Dim mergedRange As CellRange = ws.Cells.GetSubrangeAbsolute(4, 7, 13, 7)
85        mergedRange.Merged = True
86        mergedRange.Value = "T o p   1 0"
87        tmpStyle.Rotation = -90
88        tmpStyle.FillPattern.SetSolid(Color.Lime)
89        mergedRange.Style = tmpStyle
90
91        mergedRange = ws.Cells.GetSubrangeAbsolute(4, 8, 23, 8)
92        mergedRange.Merged = True
93        mergedRange.Value = "T o p   2 0"
94        tmpStyle.IsTextVertical = True
95        tmpStyle.FillPattern.SetSolid(Color.Gold)
96        mergedRange.Style = tmpStyle
97
98        mergedRange = ws.Cells.GetSubrangeAbsolute(14, 7, 23, 7)
99        mergedRange.Merged = True
100        mergedRange.Style = tmpStyle
101
102        For i = 0 To 19
103            For j = 0 To 6
104                Dim cell As ExcelCell = ws.Cells(i + 4, j)
105
106                cell.Value = skyscrapers(i + 1, j)
107
108                If i Mod 2 = 0 Then
109                    cell.Style.FillPattern.SetSolid(Color.LightSkyBlue)
110                Else
111                    cell.Style.FillPattern.SetSolid(Color.FromArgb(210, 210, 230))
112                End If
113
114                If j = 3 Then
115                    cell.Style.NumberFormat = "#" + ControlChars.Quote + " m" + ControlChars.Quote
116                End If
117
118                If j = 4 Then
119                    cell.Style.NumberFormat = "#" + ControlChars.Quote + " ft" + ControlChars.Quote
120                End If
121
122                If j > 2 Then
123                    cell.Style.Font.Name = "Courier New"
124                End If
125
126                cell.Style.Borders(IndividualBorder.Right).LineStyle = LineStyle.Thin
127            Next j
128        Next i
129
130        ws.Cells.GetSubrange("A5", "I24").Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Double)
131        ws.Cells.GetSubrange("A3", "G4").Style.Borders.SetBorders(MultipleBorders.Vertical Or MultipleBorders.Top, Color.Black, LineStyle.Double)
132        ws.Cells.GetSubrange("A5", "H14").Style.Borders.SetBorders(MultipleBorders.Bottom Or MultipleBorders.Right, Color.Black, LineStyle.Double)
133
134        ws.Cells("A27").Value = "Notes:"
135        ws.Cells("A28").Value = "a) 'Metric' and 'Imperial' columns use custom number formatting."
136        ws.Cells("A29").Value = "b) All number columns use 'Courier New' font for improved number readability."
137        ws.Cells("A30").Value = "c) Multiple merged ranges were used for table header and categories header."
138
139        ws.PrintOptions.FitWorksheetWidthToPages = 1
140
141        ef.Save("Writing.xls")
142
143    End Sub
144
145End Module
Check alternative approaches as:

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