Write and save ODS/ODF files from your C#/VB.NET applications

The following example shows how you can write ODS (ODF/OpenDocument's spreadsheet file format) and other Excel files by using only the GemBox.Spreadsheet .NET component.

GemBox.Spreadsheet can create an Excel file in many formats (including XLSX, XLS, XLSB, ODS, CSV, and HTML) in the same manner. You can use it to quickly and easily export your data to the file format of your choice. Files can be saved to disk, or to a stream that can then be saved to a database or sent to a client's web browser.

Steps for writing ODS/ODF files

You can write ODS files with just a few lines of code. You only need an IDE like Visual Studio or JetBrains Rider and .NET Framework, .NET Core, or any other platform that implements .NET Standard.

Follow these steps to create an empty file, write data to it, and save it as a file from a disk:

Create or use an existing C# or VB.NET project.
Download and install GemBox.Spreadsheet.
Add a reference to GemBox.Spreadsheet.dll within your C# or VB.NET project.
Optionally, you can add GemBox.Spreadsheet Nuget package to your C# or VB.NET project.
Execute the C#/VB.NET code below.

Try writing ODS/ODF files online

You can test writing content to an ODS file with the interactive example below. After clicking on Run example, the C#/VB.NET code will be compiled, and the content will be written to an ODS file with only .NET framework and the GemBox.Spreadsheet component.

The following example shows how you can write typical table data to an excel file. It also shows how to merge cells, specify column properties, set cell values and style properties.

Screenshot of ODS file created with GemBox.Spreadsheet

C#
VB.NET

using GemBox.Spreadsheet;

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

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Writing");

        // Tabular sample data for writing into an Excel file.
        var skyscrapers = new object[21, 8]
        {
            { "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, 2018 },
            { 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, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2018 },
            { 14, "Landmark 81", "Ho Chi Minh City", "Vietnam", 461.2, 1513, 81, 2018 },
            { 15, "Changsha IFS Tower T1", "Changsha", "China", 452.1, 1483, 88, 2017 },
            { 16, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
            { 16, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
            { 16, "The Exchange 106", "Kuala Lumpur", "Malaysia", 451.9, 1483, 97, 2018 },
            { 19, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2010 },
            { 19, "Suzhou IFS", "Suzhou", "China", 450, 1476, 92, 2017 }
        };

        worksheet.Cells["A1"].Value = "Example of writing typical table - tallest buildings in the world (2019):";

        // Column width of 8, 30, 16, 20, 9, 11, 9, 9, 4 and 5 characters.
        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

        int i, j;
        // Write header data to Excel cells.
        for (j = 0; j < 8; j++)
            worksheet.Cells[3, j].Value = skyscrapers[0, j];
        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)

        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;

        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;

        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 and format sample data to Excel cells.
        for (i = 0; i < 20; i++)
            for (j = 0; j < 8; j++)
            {
                var cell = worksheet.Cells[i + 4, j];

                cell.Value = skyscrapers[i + 1, j];

                if (i % 2 == 0)
                    cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(221, 235, 247));
                else
                    cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(210, 210, 230));

                if (j == 4)
                    cell.Style.NumberFormat = "#\" m\"";

                if (j == 5)
                    cell.Style.NumberFormat = "#\" ft\"";

                if (j > 3)
                    cell.Style.Font.Name = "Courier New";

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

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

        worksheet.Cells["A27"].Value = "Notes:";
        worksheet.Cells["A28"].Value = "a) \"Metric\" and \"Imperial\" columns use custom number formatting.";
        worksheet.Cells["A29"].Value = "b) All number columns use \"Courier New\" font for improved number readability.";
        worksheet.Cells["A30"].Value = "c) Multiple merged ranges were used for table header and categories header.";

        worksheet.PrintOptions.FitWorksheetWidthToPages = 1;

        workbook.Save("Writing.%OutputFileType%");
    }
}

Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook = New ExcelFile
        Dim worksheet = workbook.Worksheets.Add("Writing")

        ' Tabular sample data for writing into an Excel file.
        Dim skyscrapers = New Object(20, 7) _
        {
            {"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, 2018},
            {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, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2018},
            {14, "Landmark 81", "Ho Chi Minh City", "Vietnam", 461.2, 1513, 81, 2018},
            {15, "Changsha IFS Tower T1", "Changsha", "China", 452.1, 1483, 88, 2017},
            {16, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998},
            {16, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998},
            {16, "The Exchange 106", "Kuala Lumpur", "Malaysia", 451.9, 1483, 97, 2018},
            {19, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2010},
            {19, "Suzhou IFS", "Suzhou", "China", 450, 1476, 92, 2017}
        }

        worksheet.Cells("A1").Value = "Example of writing typical table - tallest buildings in the world (2019):"

        ' Column width of 8, 30, 16, 20, 9, 11, 9, 9, 4 and 5 characters.
        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

        Dim i As Integer, j As Integer
        ' Write header data to Excel cells.
        For j = 0 To 8 - 1 Step j + 1
            worksheet.Cells(3, j).Value = skyscrapers(0, j)
        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)

        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

        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

        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 and format sample data to Excel cells.
        For i = 0 To 19
            For j = 0 To 7

                Dim cell = worksheet.Cells(i + 4, j)

                cell.Value = skyscrapers(i + 1, j)

                If i Mod 2 = 0 Then
                    cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(221, 235, 247))
                Else
                    cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(210, 210, 230))
                End If

                If j = 4 Then
                    cell.Style.NumberFormat = "#"" m"""
                End If

                If j = 5 Then
                    cell.Style.NumberFormat = "#"" ft"""
                End If

                If j > 3 Then
                    cell.Style.Font.Name = "Courier New"
                End If

                cell.Style.Borders(IndividualBorder.Right).LineStyle = LineStyle.Thin
            Next j
        Next i

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

        worksheet.Cells("A27").Value = "Notes:"
        worksheet.Cells("A28").Value = "a) 'Metric' and 'Imperial' columns use custom number formatting."
        worksheet.Cells("A29").Value = "b) All number columns use 'Courier New' font for improved number readability."
        worksheet.Cells("A30").Value = "c) Multiple merged ranges were used for table header and categories header."

        worksheet.PrintOptions.FitWorksheetWidthToPages = 1

        workbook.Save("Writing.%OutputFileType%")
    End Sub
End Module

Note that this example 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 cell values into that template.

Export DataTable to ODS/ODF

With GemBox.Spreadsheet you can also export data from a DataTable to an Excel sheet in C# and VB.NET. GemBox.Spreadsheet offers lots of options, such as the possibility to detect the columns data type, that can simplify your code.

You can use InsertDataTableOptions to set various options for exporting data from a DataTable to ExcelWorksheet such as:

ColumnHeaders - Defines whether to insert column headers.
StartRow and StartColumn - Define the position where to insert DataTable.
DataTableCellToExcelCellConverting - An event that fires when converting DataTable cell value to ExcelCell value which you can use to apply settings such as cell style.

Try exporting DataTable online

You can test inserting a DataTable to an empty file with the interactive example below. Just choose an output file format, click Run Example, and download the generated file.

The example creates a DataTable, fills it with data, inserts it into an Excel file and saves The Excel file to a file format of your choice. After clicking on Run example, the C#/VB.NET code will be compiled and executed with only .NET framework and the GemBox.Spreadsheet component.

C#
VB.NET

using System.Data;
using GemBox.Spreadsheet;

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

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("DataTable to Sheet");

        var dataTable = new DataTable();

        dataTable.Columns.Add("ID", typeof(int));
        dataTable.Columns.Add("FirstName", typeof(string));
        dataTable.Columns.Add("LastName", typeof(string));

        dataTable.Rows.Add(new object[] { 100, "John", "Doe" });
        dataTable.Rows.Add(new object[] { 101, "Fred", "Nurk" });
        dataTable.Rows.Add(new object[] { 103, "Hans", "Meier" });
        dataTable.Rows.Add(new object[] { 104, "Ivan", "Horvat" });
        dataTable.Rows.Add(new object[] { 105, "Jean", "Dupont" });
        dataTable.Rows.Add(new object[] { 106, "Mario", "Rossi" });

        worksheet.Cells[0, 0].Value = "DataTable insert example:";

        // Insert DataTable to an Excel worksheet.
        worksheet.InsertDataTable(dataTable,
            new InsertDataTableOptions()
            {
                ColumnHeaders = true,
                StartRow = 2
            });

        workbook.Save("DataTable to Sheet.%OutputFileType%");
    }
}

Imports System.Data
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook = New ExcelFile
        Dim worksheet = workbook.Worksheets.Add("DataTable to Sheet")

        Dim dataTable = New DataTable

        dataTable.Columns.Add("ID", Type.GetType("System.Int32"))
        dataTable.Columns.Add("FirstName", Type.GetType("System.String"))
        dataTable.Columns.Add("LastName", Type.GetType("System.String"))

        dataTable.Rows.Add(New Object() {100, "John", "Doe"})
        dataTable.Rows.Add(New Object() {101, "Fred", "Nurk"})
        dataTable.Rows.Add(New Object() {103, "Hans", "Meier"})
        dataTable.Rows.Add(New Object() {104, "Ivan", "Horvat"})
        dataTable.Rows.Add(New Object() {105, "Jean", "Dupont"})
        dataTable.Rows.Add(New Object() {106, "Mario", "Rossi"})

        worksheet.Cells(0, 0).Value = "DataTable insert example:"

        ' Insert DataTable to an Excel worksheet.
        worksheet.InsertDataTable(dataTable,
            New InsertDataTableOptions() With
            {
                .ColumnHeaders = True,
                .StartRow = 2
            })

        workbook.Save("DataTable to Sheet.%OutputFileType%")
    End Sub
End Module

Export DataTable performance

Since GemBox.Spreadsheet is completely written in C# and uses caching techniques, it offers both speed and memory optimizations. Your users will not need to wait for their reports to be generated any more. With GemBox.Spreadsheet you can read and write 1,000,000 rows with less than 256MB RAM and less than 4 seconds. You can try the performance yourself with the Performance example.

ODS/ODF file format

The Open Document Format for Office Applications (ODF), also known as OpenDocument, is a ZIP-compressed XML-based file format for spreadsheets, charts, presentations and word processing documents. It was developed with the aim of providing an open, XML-based file format specification for office applications. ODS is the filename extension used for OpenDocument spreadsheets.

Beside ODS, with GemBox.Spreadsheet you can also write the following file formats using the same API: XLSX, XLS, PDF, HTML, MHTML, CSV, TXT, XLSM, XLTX, XLTM, XPS, PNG, JPEG, GIF, BMP, TIFF, WMP, SVG.

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