Excel cell data types

With GemBox.Spreadsheet you can set Excel cell values to various .NET objects like String, Double, and DateTime. To check if the 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 method is the preferred way for setting the cell value to numerical or date type because it avoids boxing .NET value types.

The following example shows how you can 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 into an Excel file, GemBox.Spreadsheet will write the .NET objects from cell values in the appropriate Excel type. Microsoft Excel stores any 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, notice that Int64 and UInt64 objects are written as text values. That is because Excel can't store all the digits for large integer numbers. If the least significant digits are not important, use floating-point numbers 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.