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 ExcelFile object in order to create a new Excel file. It is also shows how to merge cells, specify column properties, and set the cell Value and CellStyle.

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

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