NativeExcel for .NET Developer's Reference

Cell data types

Example

Demonstrates how to assign various data-type values to cells.

Output file: console-data.xls

[C#]
using System;
using System.Data;
using System.Drawing;
using NativeExcel;

namespace Console_Data
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-data.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create a new workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();

            //Add worksheet
            IWorksheet sheet = book.Worksheets.Add();

            // Column width
            sheet.Cells.Columns[2,5].ColumnWidth = 25;
            sheet.Cells.Columns[2].VerticalAlignment = XlVAlign.xlVAlignTop;

            //header 
            int row = 2;
            sheet.Cells[row, 2].Value = "Data type";
            sheet.Cells[row, 3].Value = "Min value";
            sheet.Cells[row, 4].Value = "Max value";
            sheet.Cells[row, 2, row, 4].Font.Bold = true;
             
            //Numberic values
            row += 2;
            IRange title = sheet.Cells[row, 2, row, 4];  
            title.Merge();
            title.Value = "Numeric values";
            title.Font.Italic = true;
            title.Interior.Color = Color.LightGray;

            //byte 
            row++; 
            sheet.Cells[row, 2].Value = "System.Byte (byte)";
            sheet.Cells[row, 3].Value = System.Byte.MinValue;
            sheet.Cells[row, 4].Value = System.Byte.MaxValue;

            //sbyte 
            row++; 
            sheet.Cells[row, 2].Value = "System.SByte";
            sheet.Cells[row, 3].Value = System.SByte.MinValue;
            sheet.Cells[row, 4].Value = System.SByte.MaxValue;

            //Int16
            row++;
            sheet.Cells[row, 2].Value = "System.Int16 (short)";
            sheet.Cells[row, 3].Value = System.Int16.MinValue;
            sheet.Cells[row, 4].Value = System.Int16.MaxValue;

            //UInt16
            row++;
            sheet.Cells[row, 2].Value = "System.UInt16 (ushort)";
            sheet.Cells[row, 3].Value = System.UInt16.MinValue;
            sheet.Cells[row, 4].Value = System.UInt16.MaxValue;

            //Int32
            row++;
            sheet.Cells[row, 2].Value = "System.Int32 (int)";
            sheet.Cells[row, 3].Value = System.Int32.MinValue;
            sheet.Cells[row, 4].Value = System.Int32.MaxValue;

            //UInt32
            row++;
            sheet.Cells[row, 2].Value = "System.UInt32 (uint)";
            sheet.Cells[row, 3].Value = System.UInt32.MinValue;
            sheet.Cells[row, 4].Value = System.UInt32.MaxValue;


            //Int64
            row++;
            sheet.Cells[row, 2].Value = "System.Int64 (long)";
            sheet.Cells[row, 3].Value = System.Int64.MinValue;
            sheet.Cells[row, 4].Value = System.Int64.MaxValue;

            //UInt64
            row++;
            sheet.Cells[row, 2].Value = "System.UInt64 (ulong)";
            sheet.Cells[row, 3].Value = System.UInt64.MinValue;
            sheet.Cells[row, 4].Value = System.UInt64.MaxValue;

            //Single
            row++;
            sheet.Cells[row, 2].Value = "System.Single (float)";
            sheet.Cells[row, 3].Value = System.Single.MinValue;
            sheet.Cells[row, 4].Value = System.Single.MaxValue;

            //Double
            row++;
            sheet.Cells[row, 2].Value = "System.Double (double)";
            sheet.Cells[row, 3].Value = System.Double.MinValue;
            sheet.Cells[row, 4].Value = System.Double.MaxValue;

            //Decimal
            row++;
            sheet.Cells[row, 2].Value = "System.Decimal (decimal)";
            sheet.Cells[row, 3].Value = System.Decimal.MinValue;
            sheet.Cells[row, 4].Value = System.Decimal.MaxValue;

            //Empty values
            row += 2;
            title = sheet.Cells[row, 2, row, 4];  
            title.Merge();
            title.Value = "Empty values";
            title.Font.Italic = true;
            title.Interior.Color = Color.LightGray;;

            //null
            row++;
            sheet.Cells[row, 2].Value = "null";
            sheet.Cells[row, 3].Value = null;
            sheet.Cells[row, 4].Value = null;

            //null
            row++;
            sheet.Cells[row, 2].Value = "System.DBNull";
            sheet.Cells[row, 3].Value = System.DBNull.Value;
            sheet.Cells[row, 4].Value = System.DBNull.Value;

            //Empty string
            row++;
            sheet.Cells[row, 2].Value = "Empty string";
            sheet.Cells[row, 3].Value = System.String.Empty;
            sheet.Cells[row, 4].Value = System.String.Empty;


            //Date and time
            row += 2;
            title = sheet.Cells[row, 2, row, 4];  
            title.Merge();
            title.Value = "Date and time values";
            title.Font.Italic = true;
            title.Interior.Color = Color.LightGray;;
               
            //DateTime with default format
            row++;
            sheet.Cells[row, 2].Value = "System.DateTime with default format";
            sheet.Cells[row, 3].Value = System.DateTime.Now;

            //DateTime with DD-MM-YYYY hh:mm:ss format
            row++;
            sheet.Cells[row, 2].Value = "System.DateTime";
            sheet.Cells[row, 3].Value = System.DateTime.Now;
            sheet.Cells[row, 3].NumberFormat = "DD-MM-YYYY hh:mm:ss";

            //Date
            row++;
            sheet.Cells[row, 2].Value = " - Date only";
            sheet.Cells[row, 3].Value = System.DateTime.Now;
            sheet.Cells[row, 3].NumberFormat = "DD-MM-YYYY";
            //Time
            row++;
            sheet.Cells[row, 2].Value = " - Time only";
            sheet.Cells[row, 3].Value = System.DateTime.Now;
            sheet.Cells[row, 3].NumberFormat = "hh:mm:ss";

            //bool
            row += 2;  
            sheet.Cells[row, 2].Value = "System.Boolean (bool)";
            sheet.Cells[row, 3].Value = false;
            sheet.Cells[row, 4].Value = true;

            //Text values
            row += 2;
            title = sheet.Cells[row, 2, row, 4];  
            title.Merge();
            title.Value = "Text values";
            title.Font.Italic = true;
            title.Interior.Color = Color.LightGray;;
                                                      
            //char                                    
            row++;  
            sheet.Cells[row, 2].Value = "System.Char (char)";
            sheet.Cells[row, 3].Value = 'A';

            //single line string                                    
            row++;  
            sheet.Cells[row, 2].Value = "System.String (string)";
            sheet.Cells[row, 3].Value = "Single line";

            //multi line string                                    
            row++;  
            sheet.Cells[row, 2].Value = "System.String (string)";
            sheet.Cells[row, 3].Value = "Multi line string\nFirst line\nSecond line";
            sheet.Cells[row, 3].WrapText = true;

            //StringBuilder                                    
            row++;  
            sheet.Cells[row, 2].Value = "System.Text.StringBuilder";
            sheet.Cells[row, 3].Value = new System.Text.StringBuilder("StringBuilder text");

            IRange range = sheet.Cells[2, 2, row, 4];
            range.Borders[XlBordersIndex.xlAround].ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            range.Borders[XlBordersIndex.xlInsideAll].ColorIndex = XlColorIndex.xlColorIndexAutomatic;


            //DataTable
            row = row + 2;  
            sheet.Cells[row, 2].Value = "System.Data.DataTable";
            int rowcnt;
            rowcnt = sheet.Cells[row, 3].SetDataTable(CreateDataTable(), true);
            range = sheet.Cells[row, 2, row + rowcnt - 1, 5];

//            DataTable dt = sheet.Cells[row, 3, row + rowcnt - 1, 5].GetDataTable(true);
            DataTable dt = sheet.UsedRange.GetDataTable(false, true);

            sheet.Cells[row + 100, 3].SetDataTable(dt, true);
   
            range.Borders[XlBordersIndex.xlAround].ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            range.Borders[XlBordersIndex.xlInsideAll].ColorIndex = XlColorIndex.xlColorIndexAutomatic;

            sheet.Zoom = 90;  
            sheet.View = XlWindowView.xlPageBreakPreview;
//            sheet.View = XlWindowView.xlNormalView;
            sheet.DisplayAutomaticPageBreaks = false; 
            sheet.Zoom = 100;  
            //Save workbook
            book.SaveAs(FileName);
        }


        static DataTable CreateDataTable() {
            DataTable dt = new DataTable();
            
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Date", typeof(DateTime));

            dt.Rows.Add(new object[]{1000, "Michael", new DateTime(1965,11,5)});
            dt.Rows.Add(new object[]{1001, "Andrew", new DateTime(1974,5,23)});
            dt.Rows.Add(new object[]{1003, "Maximilian", new DateTime(1981,10,13)});

            return dt;
        }


        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}
[Visual Basic]
imports System
imports System.Data
imports System.Drawing
imports NativeExcel
imports Microsoft.VisualBasic

Module Console_Data

    Sub Main()
        Dim FileName As String = "console-data.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)

        'Create workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()

        'Add sheet
        Dim sheet As IWorksheet = book.Worksheets.Add()

        ' Column width
        sheet.Cells.Columns(2,5).ColumnWidth = 25
        sheet.Cells.Columns(2).VerticalAlignment = XlVAlign.xlVAlignTop

        'header 
        Dim row As Integer = 2
        sheet.Cells(row, 2).Value = "Data type"
        sheet.Cells(row, 3).Value = "Min value"
        sheet.Cells(row, 4).Value = "Max value"
        sheet.Cells(row, 2, row, 4).Font.Bold = True
         
        'Numberic values
        row = row + 2
        Dim title As IRange = sheet.Cells(row, 2, row, 4)  
        title.Merge()
        title.Value = "Numeric values"
        title.Font.Italic = True
        title.Interior.Color = Color.LightGray

        'System.Byte 
        row = row + 1 
        sheet.Cells(row, 2).Value = "System.Byte (Byte)"
        sheet.Cells(row, 3).Value = System.Byte.MinValue
        sheet.Cells(row, 4).Value = System.Byte.MaxValue

        'System.SByte not supported by Visual Basic
        'row = row + 1 
        'sheet.Cells(row, 2).Value = "System.SByte"
        'sheet.Cells(row, 3).Value = System.SByte.MinValue
        'sheet.Cells(row, 4).Value = System.SByte.MaxValue

        'Int16
        row = row + 1
        sheet.Cells(row, 2).Value = "System.Int16 (Short)"
        sheet.Cells(row, 3).Value = System.Int16.MinValue
        sheet.Cells(row, 4).Value = System.Int16.MaxValue

        'UInt16 not supported by Visual Basic
        'row = row + 1
        'sheet.Cells(row, 2).Value = "System.UInt16 (ushort)"
        'sheet.Cells(row, 3).Value = System.UInt16.MinValue
        'sheet.Cells(row, 4).Value = System.UInt16.MaxValue

        'Int32
        row = row + 1
        sheet.Cells(row, 2).Value = "System.Int32 (Integer)"
        sheet.Cells(row, 3).Value = System.Int32.MinValue
        sheet.Cells(row, 4).Value = System.Int32.MaxValue

        'UInt32 not supported by Visual Basic
        'row = row + 1
        'sheet.Cells(row, 2).Value = "System.UInt32 (uint)"
        'sheet.Cells(row, 3).Value = System.UInt32.MinValue
        'sheet.Cells(row, 4).Value = System.UInt32.MaxValue

        'Int64
        row = row + 1
        sheet.Cells(row, 2).Value = "System.Int64 (Long)"
        sheet.Cells(row, 3).Value = System.Int64.MinValue
        sheet.Cells(row, 4).Value = System.Int64.MaxValue

        'UInt64 not supported by Visual Basic
        'row = row + 1
        'sheet.Cells(row, 2).Value = "System.UInt64 (ulong)"
        'sheet.Cells(row, 3).Value = System.UInt64.MinValue
        'sheet.Cells(row, 4).Value = System.UInt64.MaxValue

        'Single
        row = row + 1
        sheet.Cells(row, 2).Value = "System.Single (Single)"
        sheet.Cells(row, 3).Value = System.Single.MinValue
        sheet.Cells(row, 4).Value = System.Single.MaxValue

        'Double
        row = row + 1
        sheet.Cells(row, 2).Value = "System.Double (Double)"
        sheet.Cells(row, 3).Value = System.Double.MinValue
        sheet.Cells(row, 4).Value = System.Double.MaxValue

        'Decimal
        row = row + 1
        sheet.Cells(row, 2).Value = "System.Decimal (Decimal)"
        sheet.Cells(row, 3).Value = System.Decimal.MinValue
        sheet.Cells(row, 4).Value = System.Decimal.MaxValue

        'Empty values
        row = row + 2
        title = sheet.Cells(row, 2, row, 4)  
        title.Merge()
        title.Value = "Empty values"
        title.Font.Italic = True
        title.Interior.Color = Color.LightGray

        'System.DBNull
        row = row + 1
        sheet.Cells(row, 2).Value = "System.DBNull"
        sheet.Cells(row, 3).Value = System.DBNull.Value
        sheet.Cells(row, 4).Value = System.DBNull.Value

        'Empty string
        row = row + 1
        sheet.Cells(row, 2).Value = "Empty string"
        sheet.Cells(row, 3).Value = System.String.Empty
        sheet.Cells(row, 4).Value = System.String.Empty


        'Date and time
        row = row + 2
        title = sheet.Cells(row, 2, row, 4)  
        title.Merge()
        title.Value = "Date and time values"
        title.Font.Italic = True
        title.Interior.Color = Color.LightGray
           
        'DateTime with default format
        row = row + 1
        sheet.Cells(row, 2).Value = "System.DateTime with default format"
        sheet.Cells(row, 3).Value = System.DateTime.Now

        'DateTime with DD-MM-YYYY hh:mm:ss format
        row = row + 1
        sheet.Cells(row, 2).Value = "System.DateTime"
        sheet.Cells(row, 3).Value = System.DateTime.Now
        sheet.Cells(row, 3).NumberFormat = "DD-MM-YYYY hh:mm:ss"

        'Date
        row = row + 1
        sheet.Cells(row, 2).Value = " - Date only"
        sheet.Cells(row, 3).Value = System.DateTime.Now
        sheet.Cells(row, 3).NumberFormat = "DD-MM-YYYY"

        'Time
        row = row + 1
        sheet.Cells(row, 2).Value = " - Time only"
        sheet.Cells(row, 3).Value = System.DateTime.Now
        sheet.Cells(row, 3).NumberFormat = "hh:mm:ss"

        'bool
        row = row + 2  
        sheet.Cells(row, 2).Value = "System.Boolean (bool)"
        sheet.Cells(row, 3).Value = false
        sheet.Cells(row, 4).Value = true

        'Text values
        row = row + 2
        title = sheet.Cells(row, 2, row, 4)  
        title.Merge()
        title.Value = "Text values"
        title.Font.Italic = true
        title.Interior.Color = Color.LightGray
                                                  
        'char                                    
        row = row + 1  
        sheet.Cells(row, 2).Value = "System.Char (Char)"
        sheet.Cells(row, 3).Value = "A"C

        'single line string                                    
        row = row + 1  
        sheet.Cells(row, 2).Value = "System.String (String)"
        sheet.Cells(row, 3).Value = "Single line"

        'multi line string                                    
        row = row + 1  
        sheet.Cells(row, 2).Value = "System.String (String)"
        sheet.Cells(row, 3).Value = "Multi line string" + ControlChars.Lf + _
                                    "First line" + ControlChars.Lf + _
                                    "Second line"
        sheet.Cells(row, 3).WrapText = True

        'StringBuilder                                    
        row = row + 1  
        sheet.Cells(row, 2).Value = "System.Text.StringBuilder"
        sheet.Cells(row, 3).Value = new System.Text.StringBuilder("StringBuilder text")

        Dim range As IRange = sheet.Cells(2, 2, row, 4)
        range.Borders(XlBordersIndex.xlAround).ColorIndex = XlColorIndex.xlColorIndexAutomatic
        range.Borders(XlBordersIndex.xlInsideAll).ColorIndex = XlColorIndex.xlColorIndexAutomatic


        'DataTable
        row = row + 2
        sheet.Cells(row, 2).Value = "System.Data.DataTable"
        Dim rowcnt As Integer
        rowcnt = sheet.Cells(row, 3).SetDataTable(CreateDataTable(), True)
        range = sheet.Cells(row, 2, row + rowcnt - 1, 5)

        Dim dt As DataTable = sheet.UsedRange.GetDataTable(False, True)
        sheet.Cells(row + 100, 3).SetDataTable(dt, True)

        range.Borders(XlBordersIndex.xlAround).ColorIndex = XlColorIndex.xlColorIndexAutomatic
        range.Borders(XlBordersIndex.xlInsideAll).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        sheet.Zoom = 90  
        sheet.View = XlWindowView.xlPageBreakPreview
        sheet.DisplayAutomaticPageBreaks = False
        sheet.Zoom = 100

        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Function CreateDataTable() As DataTable 
        Dim dt As DataTable = New DataTable()
        
        dt.Columns.Add("Id",   System.Type.GetType("System.Int32"))
        dt.Columns.Add("Name", System.Type.GetType("System.String"))
        dt.Columns.Add("Date", System.Type.GetType("System.DateTime"))

        dt.Rows.Add(New Object() {1000, "Michael", New DateTime(1965,11,5)})
        dt.Rows.Add(New Object() {1001, "Andrew", New DateTime(1974,5,23)})
        dt.Rows.Add(New Object() {1003, "Maximilian", New DateTime(1981,10,13)})

        Return dt
    End Function


    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module