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 in C# and VB.NET, as shown in the example below.
using GemBox.Spreadsheet;
using System;
using System.IO;
using System.Text;
class Program
{
static void Main()
{
// If using the 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 GemBox.Spreadsheet
Imports System
Imports System.IO
Imports System.Text
Module Program
Sub Main()
' If using the 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
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.
To check if an object of a specific type can be assigned to the Excel cell value, use the ExcelFile.SupportsType method.
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.
