Excel cell data types

With GemBox.Spreadsheet, it's possible to set Excel cell values to various .NET objects such as String, Double, and DateTime. To check if an object of a specific type can be assigned to the Excel cell value, use the ExcelFile.SupportsType method.

To assign the cell value, you can use either the ExcelCell.Value property or one of the ExcelCell.SetValue methods. For performance reasons, the SetValue is the preferred way to set the cell value to a numerical or date type (value types) because it avoids boxing and unboxing .NET value types.

The following example shows how to set various Excel cell data types in C# and VB.NET.

Setting Excel cell value to various data types in C# and VB.NET
Screenshot of Excel cell data types
using System;
using System.IO;
using System.Text;
using GemBox.Spreadsheet;

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

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

        worksheet.Rows[0].Style = workbook.Styles[BuiltInCellStyleName.Heading1];
        worksheet.Columns[0].Width = 25 * 256;
        worksheet.Columns[1].Width = 25 * 256;
        worksheet.Columns[2].Width = 25 * 256;

        worksheet.Cells[0, 0].Value = "Value";
        worksheet.Cells[0, 1].Value = ".NET Value Type";
        worksheet.Cells[0, 2].Value = "Cell Value Type";

        // Sample data values.
        object[] values =
        {
            DBNull.Value,
            byte.MaxValue,
            sbyte.MinValue,
            short.MinValue,
            ushort.MaxValue,
            1000,
            (uint)2000,
            long.MinValue,
            ulong.MaxValue,
            float.MaxValue,
            double.MaxValue,
            3000.45m,
            true,
            DateTime.Now,
            'a',
            "Sample text.",
            new StringBuilder("Sample text."),
        };

        // Write data and data type to Excel cells.
        for (int i = 0; i < values.Length; i++)
        {
            object value = values[i];

            worksheet.Cells[i + 1, 0].Value = value;
            worksheet.Cells[i + 1, 1].Value = value.GetType().ToString();
        }

        // Save to Excel file and load it back as ExcelFile object.
        using (var stream = new MemoryStream())
        {
            workbook.Save(stream, SaveOptions.XlsxDefault);
            workbook = ExcelFile.Load(stream, LoadOptions.XlsxDefault);
            worksheet = workbook.Worksheets[0];
        }

        // Write cell type to Excel cells.
        for (int i = 0; i < values.Length; i++)
            worksheet.Cells[i + 1, 2].Value = worksheet.Cells[i + 1, 0].ValueType.ToString();

        workbook.Save("Data Types.%OutputFileType%");
    }
}
Imports System
Imports System.IO
Imports System.Text
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

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

        worksheet.Rows(0).Style = workbook.Styles(BuiltInCellStyleName.Heading1)
        worksheet.Columns(0).Width = 25 * 256
        worksheet.Columns(1).Width = 25 * 256
        worksheet.Columns(2).Width = 25 * 256

        worksheet.Cells(0, 0).Value = "Value"
        worksheet.Cells(0, 1).Value = ".NET Value Type"
        worksheet.Cells(0, 2).Value = "Cell Value Type"

        ' Sample data values.
        Dim values As Object() =
        {
            DBNull.Value,
            Byte.MaxValue,
            SByte.MinValue,
            Short.MinValue,
            UShort.MaxValue,
            1000,
            CUInt(2000),
            Long.MinValue,
            ULong.MaxValue,
            Single.MaxValue,
            Double.MaxValue,
            3000.45D,
            True,
            DateTime.Now,
            "a"c,
            "Sample text.",
            New StringBuilder("Sample text.")
        }

        ' Write data and data type to Excel cells.
        For i = 0 To values.Length - 1
            Dim value As Object = values(i)
            worksheet.Cells(i + 1, 0).Value = value
            worksheet.Cells(i + 1, 1).Value = value.GetType().ToString()
        Next

        ' Save to Excel file and load it back as ExcelFile object.
        Using stream As New MemoryStream()
            workbook.Save(stream, SaveOptions.XlsxDefault)
            workbook = ExcelFile.Load(stream, LoadOptions.XlsxDefault)
            worksheet = workbook.Worksheets(0)
        End Using

        ' Write cell type to Excel cells.
        For i = 0 To values.Length - 1
            worksheet.Cells(i + 1, 2).Value = worksheet.Cells(i + 1, 0).ValueType.ToString()
        Next

        workbook.Save("Data Types.%OutputFileType%")

    End Sub
End Module

When saving the ExcelFile object to an Excel file, GemBox.Spreadsheet will write the .NET objects from cell values in the appropriate Excel type. Microsoft Excel stores cell value as a number, text, logical value, error value, or as empty (see the description of the TYPE function).

So for instance, the StringBuilder object will be written as text, the Boolean object will be written as a logical value, and the DateTime object will be written as a number with a specific Number Format.

However, Int64 and UInt64 objects will be written as text values because Excel can't store all the digits for large integer numbers. If the least significant digits are not necessary, use floating-point for large numbers.

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.