Create and write Excel files

The following example shows how you can write data to an ExcelFile object and create a new Excel file using GemBox.Spreadsheet in your C# or VB.NET applications. It also shows how to merge cells, specify column properties, and set the ExcelCell object's Value and Style.

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Create new empty workbook.
        var workbook = new ExcelFile();

        // Add new sheet.
        var worksheet = workbook.Worksheets.Add("Skyscrapers");

        // Write title to Excel cell.
        worksheet.Cells["A1"].Value = "List of tallest buildings (2021):";

        // Tabular sample data for writing into an Excel file.
        var skyscrapers = new object[,]
        {
             { "Rank", "Building", "City", "Country", "Metric", "Imperial", "Floors", "Built (Year)" },
             { 1, "Burj Khalifa", "Dubai", "United Arab Emirates", 828, 2717, 163, 2010 },
             { 2, "Shanghai Tower", "Shanghai", "China", 632, 2073, 128, 2015 },
             { 3, "Abraj Al-Bait Clock Tower", "Mecca", "Saudi Arabia", 601, 1971, 120, 2012 },
             { 4, "Ping An Finance Centre", "Shenzhen", "China", 599, 1965, 115, 2017 },
             { 5, "Lotte World Tower", "Seoul", "South Korea", 554.5, 1819, 123, 2016 },
             { 6, "One World Trade Center", "New York City", "United States", 541.3, 1776, 104, 2014 },
             { 7, "Guangzhou CTF Finance Centre", "Guangzhou", "China", 530, 1739, 111, 2016 },
             { 7, "Tianjin CTF Finance Centre", "Tianjin", "China", 530, 1739, 98, 2019 },
             { 9, "China Zun", "Beijing", "China", 528, 1732, 108, 2018 },
             { 10, "Taipei 101", "Taipei", "Taiwan", 508, 1667, 101, 2004 },
             { 11, "Shanghai World Financial Center", "Shanghai", "China", 492, 1614, 101, 2008 },
             { 12, "International Commerce Centre", "Hong Kong", "China", 484, 1588, 118, 2010 },
             { 13, "Central Park Tower", "New York City", "United States", 472, 1550, 98, 2020 },
             { 14, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2019 },
             { 15, "Landmark 81", "Ho Chi Minh City", "Vietnam", 461.2, 1513, 81, 2018 },
             { 16, "Changsha IFS Tower T1", "Changsha", "China", 452.1, 1483, 88, 2018 },
             { 17, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
             { 17, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
             { 19, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2010 },
             { 19, "Suzhou IFS", "Suzhou", "China", 450, 1476, 98, 2019 }
        };

        // Set row formatting.
        worksheet.Rows["1"].Style = workbook.Styles[BuiltInCellStyleName.Heading1];

        // Set columns width.
        worksheet.Columns["A"].SetWidth(8, LengthUnit.CharacterWidth);  // Rank
        worksheet.Columns["B"].SetWidth(30, LengthUnit.CharacterWidth); // Building
        worksheet.Columns["C"].SetWidth(16, LengthUnit.CharacterWidth); // City
        worksheet.Columns["D"].SetWidth(20, LengthUnit.CharacterWidth); // Country
        worksheet.Columns["E"].SetWidth(9, LengthUnit.CharacterWidth);  // Metric
        worksheet.Columns["F"].SetWidth(11, LengthUnit.CharacterWidth); // Imperial
        worksheet.Columns["G"].SetWidth(9, LengthUnit.CharacterWidth);  // Floors
        worksheet.Columns["H"].SetWidth(9, LengthUnit.CharacterWidth);  // Built (Year)
        worksheet.Columns["I"].SetWidth(4, LengthUnit.CharacterWidth);  // Top 10
        worksheet.Columns["J"].SetWidth(5, LengthUnit.CharacterWidth);  // Top 20

        // Write header data to Excel cells.
        for (int col = 0; col < skyscrapers.GetLength(1); col++)
            worksheet.Cells[3, col].Value = skyscrapers[0, col];
        worksheet.Cells["E3"].Value = "Height";

        worksheet.Cells.GetSubrange("A3:A4").Merged = true;  // Rank
        worksheet.Cells.GetSubrange("B3:B4").Merged = true;  // Building
        worksheet.Cells.GetSubrange("C3:C4").Merged = true;  // City
        worksheet.Cells.GetSubrange("D3:D4").Merged = true;  // Country
        worksheet.Cells.GetSubrange("E3:F3").Merged = true;  // Height
        worksheet.Cells.GetSubrange("G3:G4").Merged = true;  // Floors
        worksheet.Cells.GetSubrange("H3:H4").Merged = true;  // Built (Year)

        // Set header cells formatting.
        var style = new CellStyle();
        style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        style.VerticalAlignment = VerticalAlignmentStyle.Center;
        style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(237, 125, 49));
        style.Font.Weight = ExcelFont.BoldWeight;
        style.Font.Color = SpreadsheetColor.FromName(ColorName.White);
        style.WrapText = true;
        style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin);
        worksheet.Cells.GetSubrange("A3:H4").Style = style;

        // Write "Top 10" cells.
        style = new CellStyle();
        style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
        style.VerticalAlignment = VerticalAlignmentStyle.Center;
        style.Font.Weight = ExcelFont.BoldWeight;
        var mergedRange = worksheet.Cells.GetSubrange("I5:I14");
        mergedRange.Merged = true;
        mergedRange.Value = "T o p   1 0";
        style.Rotation = -90;
        style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(198, 239, 206));
        mergedRange.Style = style;

        // Write "Top 20" cells.
        mergedRange = worksheet.Cells.GetSubrange("J5:J24");
        mergedRange.Merged = true;
        mergedRange.Value = "T o p   2 0";
        style.IsTextVertical = true;
        style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 235, 156));
        mergedRange.Style = style;
        mergedRange = worksheet.Cells.GetSubrange("I15:I24");
        mergedRange.Merged = true;
        mergedRange.Style = style;

        // Write sample data and formatting to Excel cells.
        for (int row = 0; row < skyscrapers.GetLength(0) - 1; row++)
        {
            for (int col = 0; col < skyscrapers.GetLength(1); col++)
            {
                var cell = worksheet.Cells[row + 4, col];
                cell.Value = skyscrapers[row + 1, col];

                cell.Style.Borders[IndividualBorder.Right].LineStyle = LineStyle.Thin;

                if (row % 2 == 0)
                    cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(221, 235, 247));

                if (col == 0)
                    cell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
                if (col > 3)
                    cell.Style.Font.Name = "Courier New";
                if (col == 4)
                    cell.Style.NumberFormat = "#\" m\"";
                if (col == 5)
                    cell.Style.NumberFormat = "#\" ft\"";
            }
        }

        worksheet.Cells.GetSubrange("A5", "J24").Style.Borders.SetBorders(
            MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium);
        worksheet.Cells.GetSubrange("A3", "H4").Style.Borders.SetBorders(
            MultipleBorders.Vertical | MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium);
        worksheet.Cells.GetSubrange("A5", "I14").Style.Borders.SetBorders(
            MultipleBorders.Bottom | MultipleBorders.Right, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium);

        worksheet.PrintOptions.FitWorksheetWidthToPages = 1;

        // Save workbook as an Excel file.
        workbook.Save("Writing.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        ' Create new empty workbook.
        Dim workbook As New ExcelFile()

        ' Add new sheet.
        Dim worksheet = workbook.Worksheets.Add("Skyscrapers")

        ' Write title to Excel cell.
        worksheet.Cells("A1").Value = "List of tallest buildings (2021):"

        ' Tabular sample data for writing into an Excel file.
        Dim skyscrapers = New Object(,) _
        {
            {"Rank", "Building", "City", "Country", "Metric", "Imperial", "Floors", "Built (Year)"},
            {1, "Burj Khalifa", "Dubai", "United Arab Emirates", 828, 2717, 163, 2010},
            {2, "Shanghai Tower", "Shanghai", "China", 632, 2073, 128, 2015},
            {3, "Abraj Al-Bait Clock Tower", "Mecca", "Saudi Arabia", 601, 1971, 120, 2012},
            {4, "Ping An Finance Centre", "Shenzhen", "China", 599, 1965, 115, 2017},
            {5, "Lotte World Tower", "Seoul", "South Korea", 554.5, 1819, 123, 2016},
            {6, "One World Trade Center", "New York City", "United States", 541.3, 1776, 104, 2014},
            {7, "Guangzhou CTF Finance Centre", "Guangzhou", "China", 530, 1739, 111, 2016},
            {7, "Tianjin CTF Finance Centre", "Tianjin", "China", 530, 1739, 98, 2019},
            {9, "China Zun", "Beijing", "China", 528, 1732, 108, 2018},
            {10, "Taipei 101", "Taipei", "Taiwan", 508, 1667, 101, 2004},
            {11, "Shanghai World Financial Center", "Shanghai", "China", 492, 1614, 101, 2008},
            {12, "International Commerce Centre", "Hong Kong", "China", 484, 1588, 118, 2010},
            {13, "Central Park Tower", "New York City", "United States", 472, 1550, 98, 2020},
            {14, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2019},
            {15, "Landmark 81", "Ho Chi Minh City", "Vietnam", 461.2, 1513, 81, 2018},
            {16, "Changsha IFS Tower T1", "Changsha", "China", 452.1, 1483, 88, 2018},
            {17, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998},
            {17, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998},
            {19, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2010},
            {19, "Suzhou IFS", "Suzhou", "China", 450, 1476, 98, 2019}
        }

        ' Set row formatting.
        worksheet.Rows("1").Style = workbook.Styles(BuiltInCellStyleName.Heading1)

        ' Set columns width.
        worksheet.Columns("A").SetWidth(8, LengthUnit.CharacterWidth)  ' Rank
        worksheet.Columns("B").SetWidth(30, LengthUnit.CharacterWidth) ' Building
        worksheet.Columns("C").SetWidth(16, LengthUnit.CharacterWidth) ' City
        worksheet.Columns("D").SetWidth(20, LengthUnit.CharacterWidth) ' Country
        worksheet.Columns("E").SetWidth(9, LengthUnit.CharacterWidth)  ' Metric
        worksheet.Columns("F").SetWidth(11, LengthUnit.CharacterWidth) ' Imperial
        worksheet.Columns("G").SetWidth(9, LengthUnit.CharacterWidth)  ' Floors
        worksheet.Columns("H").SetWidth(9, LengthUnit.CharacterWidth)  ' Built (Year)
        worksheet.Columns("I").SetWidth(4, LengthUnit.CharacterWidth)  ' Top 10
        worksheet.Columns("J").SetWidth(5, LengthUnit.CharacterWidth)  ' Top 20

        ' Write header data to Excel cells.
        For col As Integer = 0 To skyscrapers.GetLength(1) - 1
            worksheet.Cells(3, col).Value = skyscrapers(0, col)
        Next
        worksheet.Cells("E3").Value = "Height"

        worksheet.Cells.GetSubrange("A3:A4").Merged = True  ' Rank
        worksheet.Cells.GetSubrange("B3:B4").Merged = True  ' Building
        worksheet.Cells.GetSubrange("C3:C4").Merged = True  ' City
        worksheet.Cells.GetSubrange("D3:D4").Merged = True  ' Country
        worksheet.Cells.GetSubrange("E3:F3").Merged = True  ' Height
        worksheet.Cells.GetSubrange("G3:G4").Merged = True  ' Floors
        worksheet.Cells.GetSubrange("H3:H4").Merged = True  ' Built (Year)

        ' Set header cells formatting.
        Dim style = New CellStyle()
        style.HorizontalAlignment = HorizontalAlignmentStyle.Center
        style.VerticalAlignment = VerticalAlignmentStyle.Center
        style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(237, 125, 49))
        style.Font.Weight = ExcelFont.BoldWeight
        style.Font.Color = SpreadsheetColor.FromName(ColorName.White)
        style.WrapText = True
        style.Borders.SetBorders(MultipleBorders.Right Or MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin)
        worksheet.Cells.GetSubrange("A3:H4").Style = style

        ' Write "Top 10" cells.
        style = New CellStyle()
        style.HorizontalAlignment = HorizontalAlignmentStyle.Center
        style.VerticalAlignment = VerticalAlignmentStyle.Center
        style.Font.Weight = ExcelFont.BoldWeight
        Dim mergedRange = worksheet.Cells.GetSubrange("I5:I14")
        mergedRange.Merged = True
        mergedRange.Value = "T o p   1 0"
        style.Rotation = -90
        style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(198, 239, 206))
        mergedRange.Style = style

        ' Write "Top 20" cells.
        mergedRange = worksheet.Cells.GetSubrange("J5:J24")
        mergedRange.Merged = True
        mergedRange.Value = "T o p   2 0"
        style.IsTextVertical = True
        style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 235, 156))
        mergedRange.Style = style
        mergedRange = worksheet.Cells.GetSubrange("I15:I24")
        mergedRange.Merged = True
        mergedRange.Style = style

        ' Write sample data and formatting to Excel cells.
        For row As Integer = 0 To skyscrapers.GetLength(0) - 1 - 1
            For col As Integer = 0 To skyscrapers.GetLength(1) - 1

                Dim cell = worksheet.Cells(row + 4, col)
                cell.Value = skyscrapers(row + 1, col)

                cell.Style.Borders(IndividualBorder.Right).LineStyle = LineStyle.Thin

                If row Mod 2 = 0 Then cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(221, 235, 247))

                If col = 0 Then cell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center
                If col > 3 Then cell.Style.Font.Name = "Courier New"
                If col = 4 Then cell.Style.NumberFormat = "#"" m"""
                If col = 5 Then cell.Style.NumberFormat = "#"" ft"""

            Next
        Next

        worksheet.Cells.GetSubrange("A5", "J24").Style.Borders.SetBorders(
            MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium)
        worksheet.Cells.GetSubrange("A3", "H4").Style.Borders.SetBorders(
            MultipleBorders.Vertical Or MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium)
        worksheet.Cells.GetSubrange("A5", "I14").Style.Borders.SetBorders(
            MultipleBorders.Bottom Or MultipleBorders.Right, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium)

        worksheet.PrintOptions.FitWorksheetWidthToPages = 1

        ' Save workbook as an Excel file.
        workbook.Save("Writing.%OutputFileType%")

    End Sub

End Module
Creating and writing Excel workbook's cell values and formatting in C# and VB.NET
Screenshot of written cell data in output Excel workbook

The Excel file can be saved using one of the ExcelFile.Save methods. These methods enable you to save data to a physical file (when providing the file's path) or an in-memory file (when providing the file's Stream).

You can specify the format of your Excel file by providing an object from the SaveOptions derived class (XlsxSaveOptions, XlsSaveOptions, XlsbSaveOptions, OdsSaveOptions, CsvSaveOptions, and HtmlSaveOptions). Or you can let GemBox.Spreadsheet choose the appropriate options based on the file name extension by omitting the SaveOptions parameter.

Create Excel Files using Rich text

The following example shows how you can create a new Excel file with rich formatted text in individual cells.

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        // Create new empty workbook.
        var workbook = new ExcelFile();

        // Add new sheet.
        var worksheet = workbook.Worksheets.Add("Sheet1");

        worksheet.Columns["B"].SetWidth(400, LengthUnit.Pixel);

        // Add plain text to cell.
        worksheet.Cells["B2"].Value = "This is a plain text.";

        // Add rich formatted text to cell.
        worksheet.Cells["B4"].Value = "This is a rich formatted text.";
        worksheet.Cells["B4"].Style.Font.Color = SpreadsheetColor.FromArgb(255, 128, 64);
        worksheet.Cells["B4"].GetCharacters(10, 19).Font.Name = "Arial Black";
        worksheet.Cells["B4"].GetCharacters(15, 9).Font.Size = 14 * 20;
        worksheet.Cells["B4"].GetCharacters(25, 5).Font.Size = 18 * 20;

        // Add HTML formatted text to cell.
        string html = @"<td style='
            font-family: Arial Narrow;
            color: royalblue;
            border: solid black;
            background: #FFF2CC'>This is another rich formatted text.</p>";
        worksheet.Cells["B6"].SetValue(html, LoadOptions.HtmlDefault);

        // Save workbook as an Excel file.
        workbook.Save("Writing.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        ' Create new empty workbook.
        Dim workbook As New ExcelFile()

        ' Add new sheet.
        Dim worksheet = workbook.Worksheets.Add("Sheet1")
        
        worksheet.Columns("B").SetWidth(400, LengthUnit.Pixel)

        ' Add plain text to cell.
        worksheet.Cells("B2").Value = "This is a plain text."

        ' Add rich formatted text to cell.
        worksheet.Cells("B4").Value = "This is a rich formatted text."
        worksheet.Cells("B4").Style.Font.Color = SpreadsheetColor.FromArgb(255, 128, 64)
        worksheet.Cells("B4").GetCharacters(10, 19).Font.Name = "Arial Black"
        worksheet.Cells("B4").GetCharacters(15, 9).Font.Size = 14 * 20
        worksheet.Cells("B4").GetCharacters(25, 5).Font.Size = 18 * 20

        ' Add HTML formatted text to cells.
        Dim html As String = "<td style='
            font-family: Arial Narrow;
            color: royalblue;
            border: solid black;
            background: #FFF2CC'>This is another rich formatted text.</p>"
        worksheet.Cells("B6").SetValue(html, LoadOptions.HtmlDefault)

        ' Save workbook as an Excel file.
        workbook.Save("Writing.%OutputFileType%")
    End Sub
End Module
Creating and writing Excel workbook's text with rich formatting in C# and VB.NET
Screenshot of written formatted text in output Excel workbook

You can find a complete element hierarchy (parent / child relationships) of GemBox.Spreadsheet's in-memory representation of an Excel file on the Content Model help page.

The following is a list of examples that show how you can create different workbook elements:

The following is a list of examples that show how you can add different worksheet options:

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