App_Code/DataTypesDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <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 static class DataTypesDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "DataTypes";
// Excel number format strings, applied to the same number
private static readonly string[] NumberFormats = { "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 static readonly string[] DateFormats = { "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 static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
ExcelWorkbook workbook = 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";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);
ExcelWorksheet worksheet = 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);
double number = 1234567.809;
worksheet["A12"].Value = number;
worksheet["C12"].Value = "General (the default)";
int row = 13;
foreach (string numberFormat in NumberFormats)
{
worksheet[row, 1].Value = number;
worksheet[row, 1].Style.Number.NumberFormatString = numberFormat;
worksheet[row, 3].Text = numberFormat;
row++;
}
// 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;
DateTime date = new DateTime(2026, 10, 17, 14, 35, 20);
worksheet[row, 1].Value = date;
worksheet[row, 3].Value = "Default date format";
row++;
foreach (string dateFormat in DateFormats)
{
worksheet[row, 1].Value = date;
worksheet[row, 1].Style.Number.NumberFormatString = dateFormat;
worksheet[row, 3].Text = dateFormat;
row++;
}
row++;
// 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 = worksheet["A12"].IsNumber ? "A12 holds a number" : "A12 does not hold a number";
worksheet[row + 1, 3].Value = worksheet["A8"].IsString ? "A8 holds a string" : "A8 does not hold a string";
workbook.Save(output);
}
finally
{
workbook.Close();
logo.Dispose();
}
}
}
}