ExcelCell Data Types

Following sample demonstrates support for various cell data types: integer and floating point numbers, strings, dates etc. It also shows how to set value of specific type to a cell.

Notice that large integers (Int64) are converted to Excel strings because Excel can't store all the digits for Int64 number. If least significant digits are not important, use floating point numbers for large values.

Notice the usage of SetValue methods when setting the cell value to numerical or date type, this is done in order to avoid boxing of value types.

Screenshot

Data Types Screenshot

See the full code below, use RUN EXAMPLE to execute.


1using GemBox.Spreadsheet;
2using System;
3using System.IO;
4
5class Sample
6{
7    [STAThread]
8    static void Main(string[] args)
9    {
10        // If using Professional version, put your serial key below.
11        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
12
13        ExcelFile ef = new ExcelFile();
14        ExcelWorksheet ws = ef.Worksheets.Add("Data Types");
15
16        ws.Cells[0, 0].Value = "Cell value examples:";
17
18        // Column width of 25 and 40 characters.
19        ws.Columns[0].Width = 25 * 256;
20        ws.Columns[1].Width = 40 * 256;
21
22        // Print gridlines (and show them in PDF, XPS, etc.)
23        ws.PrintOptions.PrintGridlines = true;
24
25        int row = 1;
26
27        ws.Cells[++row, 0].Value = "Type";
28        ws.Cells[row, 1].Value = "Value";
29
30        ws.Cells[++row, 0].Value = "System.DBNull:";
31        ws.Cells[row, 1].Value = System.DBNull.Value;
32
33        ws.Cells[++row, 0].Value = "System.Byte:";
34        ws.Cells[row, 1].SetValue(byte.MaxValue);
35
36        ws.Cells[++row, 0].Value = "System.SByte:";
37        ws.Cells[row, 1].SetValue(sbyte.MinValue);
38
39        ws.Cells[++row, 0].Value = "System.Int16:";
40        ws.Cells[row, 1].SetValue(short.MinValue);
41
42        ws.Cells[++row, 0].Value = "System.UInt16:";
43        ws.Cells[row, 1].SetValue(ushort.MaxValue);
44
45        ws.Cells[++row, 0].Value = "System.Int64:";
46        ws.Cells[row, 1].Value = long.MinValue;
47
48        ws.Cells[++row, 0].Value = "System.UInt64:";
49        ws.Cells[row, 1].Value = ulong.MaxValue;
50
51        ws.Cells[++row, 0].Value = "System.UInt32:";
52        ws.Cells[row, 1].SetValue((uint)1234);
53
54        ws.Cells[++row, 0].Value = "System.Int32:";
55        ws.Cells[row, 1].SetValue(-5678);
56
57        ws.Cells[++row, 0].Value = "System.Single:";
58        ws.Cells[row, 1].SetValue(float.MaxValue);
59
60        ws.Cells[++row, 0].Value = "System.Double:";
61        ws.Cells[row, 1].SetValue(double.MaxValue);
62
63        ws.Cells[++row, 0].Value = "System.Boolean:";
64        ws.Cells[row, 1].SetValue(true);
65
66        ws.Cells[++row, 0].Value = "System.Char:";
67        ws.Cells[row, 1].Value = 'a';
68
69        ws.Cells[++row, 0].Value = "System.Text.StringBuilder:";
70        ws.Cells[row, 1].Value = new System.Text.StringBuilder("StringBuilder text.");
71
72        ws.Cells[++row, 0].Value = "System.Decimal:";
73        ws.Cells[row, 1].Value = 50000m;
74
75        ws.Cells[++row, 0].Value = "System.DateTime:";
76        ws.Cells[row, 1].SetValue(DateTime.Now);
77
78        ws.Cells[++row, 0].Value = "System.String:";
79        ws.Cells[row++, 1].Value = "Microsoft Excel is a spreadsheet program written and distributed by Microsoft for computers using the Microsoft Windows operating system and Apple Macintosh computers. It is overwhelmingly the dominant spreadsheet application available for these platforms and has been so since version 5 1993 and its bundling as part of Microsoft Office.\n"
80            + "Microsoft originally marketed a spreadsheet program called Multiplan in 1982, which was very popular on CP/M systems, but on MS-DOS systems it lost popularity to Lotus 1-2-3. This promoted development of a new spreadsheet called Excel which started with the intention to, in the words of Doug Klunder, 'do everything 1-2-3 does and do it better' . The first version of Excel was released for the Mac in 1985 and the first Windows version (numbered 2.0 to line-up with the Mac and bundled with a run-time Windows environment) was released in November 1987. Lotus was slow to bring 1-2-3 to Windows and by 1988 Excel had started to outsell 1-2-3 and helped Microsoft achieve the position of leading PC software developer. This accomplishment, dethroning the king of the software world, solidified Microsoft as a valid competitor and showed its future of developing graphical software. Microsoft pushed its advantage with regular new releases, every two years or so. The current version is Excel 11, also called Microsoft Office Excel 2003.\n"
81            + "Early in its life Excel became the target of a trademark lawsuit by another company already selling a software package named \"Excel.\" As the result of the dispute Microsoft was required to refer to the program as \"Microsoft Excel\" in all of its formal press releases and legal documents. However, over time this practice has slipped.\n"
82            + "Excel offers a large number of user interface tweaks, however the essence of UI remains the same as in the original spreadsheet, VisiCalc: the cells are organized in rows and columns, and contain data or formulas with relative or absolute references to other cells.\n"
83            + "Excel was the first spreadsheet that allowed the user to define the appearance of spreadsheets (fonts, character attributes and cell appearance). It also introduced intelligent cell recomputation, where only cells dependent on the cell being modified are updated, while previously spreadsheets recomputed everything all the time or waited for a specific user command. Excel has extensive graphing capabilities.\n"
84            + "When first bundled into Microsoft Office in 1993, Microsoft Word and Microsoft PowerPoint had their GUIs redesigned for consistency with Excel, the killer app on the PC at the time.\n"
85            + "Since 1993 Excel includes support for Visual Basic for Applications (VBA) as a scripting language. VBA is a powerful tool that makes Excel a complete programming environment. VBA and macro recording allow automating routines that otherwise take several manual steps. VBA allows creating forms to handle user input. Automation functionality of VBA exposed Excel as a target for macro viruses.\n"
86            + "Excel versions from 5.0 to 9.0 contain various Easter eggs.\n\nFor more information see: http://en.wikipedia.org/wiki/Microsoft_Excel";
87
88        ef.Save("Data Types.xls");
89    }
90}
1Imports GemBox.Spreadsheet
2Imports System.IO
3
4Module Samples
5
6    Sub Main()
7
8        ' If using Professional version, put your serial key below.
9        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
10
11        Dim ef As ExcelFile = New ExcelFile
12        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Data Types")
13
14        ws.Cells(0, 0).Value = "Cell value examples:"
15
16        ' Column width of 25 and 40 characters.
17        ws.Columns(0).Width = 25 * 256
18        ws.Columns(1).Width = 40 * 256
19
20        ' Print gridlines (and show them in PDF, XPS, etc.)
21        ws.PrintOptions.PrintGridlines = True
22
23        Dim row As Integer = 1
24
25        row = row + 1
26        ws.Cells(row, 0).Value = "Type"
27        ws.Cells(row, 1).Value = "Value"
28
29        row = row + 1
30        ws.Cells(row, 0).Value = "System.DBNull:"
31        ws.Cells(row, 1).Value = System.DBNull.Value
32
33        row = row + 1
34        ws.Cells(row, 0).Value = "System.Byte:"
35        ws.Cells(row, 1).SetValue(Byte.MaxValue)
36
37        row = row + 1
38        ws.Cells(row, 0).Value = "System.SByte:"
39        ws.Cells(row, 1).SetValue(SByte.MinValue)
40
41        row = row + 1
42        ws.Cells(row, 0).Value = "System.Int16:"
43        ws.Cells(row, 1).SetValue(Short.MinValue)
44
45        row = row + 1
46        ws.Cells(row, 0).Value = "System.UInt16:"
47        ws.Cells(row, 1).SetValue(UShort.MaxValue)
48
49        row = row + 1
50        ws.Cells(row, 0).Value = "System.Int64:"
51        ws.Cells(row, 1).Value = Long.MinValue
52
53        row = row + 1
54        ws.Cells(row, 0).Value = "System.UInt64:"
55        ws.Cells(row, 1).Value = ULong.MaxValue
56
57        row = row + 1
58        ws.Cells(row, 0).Value = "System.UInt32:"
59        ws.Cells(row, 1).SetValue(CType(1234, UInteger))
60
61        row = row + 1
62        ws.Cells(row, 0).Value = "System.Int32:"
63        ws.Cells(row, 1).SetValue(-5678)
64
65        row = row + 1
66        ws.Cells(row, 0).Value = "System.Single:"
67        ws.Cells(row, 1).SetValue(Single.MaxValue)
68
69        row = row + 1
70        ws.Cells(row, 0).Value = "System.Double:"
71        ws.Cells(row, 1).SetValue(Double.MaxValue)
72
73        row = row + 1
74        ws.Cells(row, 0).Value = "System.Boolean:"
75        ws.Cells(row, 1).SetValue(True)
76
77        row = row + 1
78        ws.Cells(row, 0).Value = "System.Char:"
79        ws.Cells(row, 1).Value = "a"c
80
81        row = row + 1
82        ws.Cells(row, 0).Value = "System.Text.StringBuilder:"
83        ws.Cells(row, 1).Value = New System.Text.StringBuilder("StringBuilder text.")
84
85        row = row + 1
86        ws.Cells(row, 0).Value = "System.Decimal:"
87        ws.Cells(row, 1).Value = 50000D
88
89        row = row + 1
90        ws.Cells(row, 0).Value = "System.DateTime:"
91        ws.Cells(row, 1).SetValue(DateTime.Now)
92
93        row = row + 1
94        ws.Cells(row, 0).Value = "System.String:"
95        ws.Cells(row, 1).Value = "Microsoft Excel is a spreadsheet program written and distributed by Microsoft for computers using the Microsoft Windows operating system and Apple Macintosh computers. It is overwhelmingly the dominant spreadsheet application available for these platforms and has been so since version 5 1993 and its bundling as part of Microsoft Office." + vbLf _
96         + "Microsoft originally marketed a spreadsheet program called Multiplan in 1982, which was very popular on CP/M systems, but on MS-DOS systems it lost popularity to Lotus 1-2-3. This promoted development of a new spreadsheet called Excel which started with the intention to, in the words of Doug Klunder, 'do everything 1-2-3 does and do it better' . The first version of Excel was released for the Mac in 1985 and the first Windows version (numbered 2.0 to line-up with the Mac and bundled with a run-time Windows environment) was released in November 1987. Lotus was slow to bring 1-2-3 to Windows and by 1988 Excel had started to outsell 1-2-3 and helped Microsoft achieve the position of leading PC software developer. This accomplishment, dethroning the king of the software world, solidified Microsoft as a valid competitor and showed its future of developing graphical software. Microsoft pushed its advantage with regular new releases, every two years or so. The current version is Excel 11, also called Microsoft Office Excel 2003." + vbLf _
97         + "Early in its life Excel became the target of a trademark lawsuit by another company already selling a software package named 'Excel.' As the result of the dispute Microsoft was required to refer to the program as 'Microsoft Excel' in all of its formal press releases and legal documents. However, over time this practice has slipped." + vbLf _
98         + "Excel offers a large number of user interface tweaks, however the essence of UI remains the same as in the original spreadsheet, VisiCalc: the cells are organized in rows and columns, and contain data or formulas with relative or absolute references to other cells." + vbLf _
99         + "Excel was the first spreadsheet that allowed the user to define the appearance of spreadsheets (fonts, character attributes and cell appearance). It also introduced intelligent cell recomputation, where only cells dependent on the cell being modified are updated, while previously spreadsheets recomputed everything all the time or waited for a specific user command. Excel has extensive graphing capabilities." + vbLf _
100         + "When first bundled into Microsoft Office in 1993, Microsoft Word and Microsoft PowerPoint had their GUIs redesigned for consistency with Excel, the killer app on the PC at the time." + vbLf _
101         + "Since 1993 Excel includes support for Visual Basic for Applications (VBA) as a scripting language. VBA is a powerful tool that makes Excel a complete programming environment. VBA and macro recording allow automating routines that otherwise take several manual steps. VBA allows creating forms to handle user input. Automation functionality of VBA exposed Excel as a target for macro viruses." + vbLf _
102         + "Excel versions from 5.0 to 9.0 contain various Easter eggs." + vbLf + vbLf + "For more information see: http://en.wikipedia.org/wiki/Microsoft_Excel"
103
104        ef.Save("Data Types.xls")
105
106    End Sub
107
108End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.