App_Code/DataTypesDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Shows the cell value types (text, numbers, dates, booleans) and the Excel number formats that control how a
''' value is displayed. The value stays the same; only its display changes.
''' </summary>
Public Module DataTypesDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "DataTypes"
' Excel number format strings, applied to the same number
Private ReadOnly NumberFormats As String() = {"0", "0.00", "#,##0", "#,##0.00", "0%", "0.00%",
"0.00E+00", "# ?/?", "# ??/??", "_( #,##0_);\( #,##0\ )", "_( #,##0_);[Red]\( #,##0\ )",
"_( #,##0.00_);\( #,##0.00\ )", "_( #,##0.00_);[Red]\( #,##0.00\ )",
"_(* #,##0_);_(* \( #,##0\ );_(* ""-""_);_( @_ )",
"_(* #,##0.00_);_(* \( #,##0.00\ );_(* ""-""??_);_( @_ )"}
' Excel date and time format strings, applied to the same date
Private ReadOnly DateFormats As String() = {"m/d/yyyy", "d\-mmm\-yy", "d\-mmm", "mmm\-yy",
"dddd, mmmm d, yyyy", "h:mm AM/PM", "h:mm:ss AM/PM", "h:mm", "h:mm:ss", "m/d/yy h:mm"}
''' <summary>
''' Creates the workbook in the given format and saves it to the output stream.
''' </summary>
''' <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
''' <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
''' <param name="output">The stream that receives the workbook.</param>
Public Sub CreateWorkbook(format As ExcelWorkbookFormat, inputFolder As String, output As Stream)
Dim workbook As New ExcelWorkbook(format)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Data types and number formats"
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"
Dim logo As Image = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"))
Try
Dim titleStyle As ExcelCellStyle = DemoLayout.AddTitleStyle(workbook)
Dim sectionStyle As ExcelCellStyle = DemoLayout.AddSectionStyle(workbook)
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Data Types"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Portrait)
DemoLayout.WriteTitle(worksheet, "A2:G3", "Data Types and Data Formatting Demo", titleStyle)
worksheet("A1").ColumnWidthInChars = 22
worksheet("C1").ColumnWidthInChars = 18
' TEXT: Value accepts any type, Text always stores a string (also for text that looks like a number)
DemoLayout.WriteSection(worksheet, "A5:E5", "Text", sectionStyle)
worksheet("A7").Value = "A text set with the Value property"
worksheet("A8").Text = "00123"
worksheet("C8").Value = "Text keeps the leading zeros"
' NUMBERS
DemoLayout.WriteSection(worksheet, "A10:E10", "Numbers", sectionStyle)
Dim number As Double = 1234567.809
worksheet("A12").Value = number
worksheet("C12").Value = "General (the default)"
Dim row As Integer = 13
For Each numberFormat As String In NumberFormats
worksheet(row, 1).Value = number
worksheet(row, 1).Style.Number.NumberFormatString = numberFormat
worksheet(row, 3).Text = numberFormat
row += 1
Next
' a negative number shows the second section of a format, here in red
worksheet(row, 1).Value = -number
worksheet(row, 1).Style.Number.NumberFormatString = NumberFormats(10)
worksheet(row, 3).Text = NumberFormats(10) & " (negative)"
row += 2
' DATES: a DateTime value is stored as a date serial number and displayed with a date format
DemoLayout.WriteSection(worksheet, "A" & row & ":E" & row, "Dates and times", sectionStyle)
row += 2
Dim sampleDate As New DateTime(2026, 10, 17, 14, 35, 20)
worksheet(row, 1).Value = sampleDate
worksheet(row, 3).Value = "Default date format"
row += 1
For Each dateFormat As String In DateFormats
worksheet(row, 1).Value = sampleDate
worksheet(row, 1).Style.Number.NumberFormatString = dateFormat
worksheet(row, 3).Text = dateFormat
row += 1
Next
row += 1
' BOOLEANS
DemoLayout.WriteSection(worksheet, "A" & row & ":E" & row, "Booleans", sectionStyle)
row += 2
worksheet(row, 1).Value = True
worksheet(row + 1, 1).Value = False
' the value types can be read back from the cells
worksheet(row, 3).Value = If(worksheet("A12").IsNumber, "A12 holds a number", "A12 does not hold a number")
worksheet(row + 1, 3).Value = If(worksheet("A8").IsString, "A8 holds a string", "A8 does not hold a string")
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module