Using numberformats in Excel

The following code snippet shows how to use numberformats in excel. This tutorial use NetOffice but any way the syntax is exactly the same as interop.

using System;
using System.Reflection;
using System.Globalization;
using NetOfficeApi = LateBindingApi.Core.Factory;
using ApiSettings = LateBindingApi.Core.Settings;
using Excel = NetOffice.ExcelApi;
using NetOffice.ExcelApi.Enums;
namespace CSharpCornerExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Initialize Api
            NetOfficeApi.Initialize();
            // start excel and turn Application msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;
            // add a new workbook
            Excel.Workbook workBook = excelApplication.Workbooks.Add();
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
            /* some kind of numerics */
            // the given thread culture in all latebinding calls are stored in 
            //LateBindingApi.Core.Settings.
            // you can change the culture. default is en-us.
            CultureInfo cultureInfo = ApiSettings.ThreadCulture;
            string Pattern1 = string.Format("0{0}00",
            cultureInfo.NumberFormat.CurrencyDecimalSeparator);            
            string Pattern2 = string.Format("#{1}##0{0}00",
            cultureInfo.NumberFormat.CurrencyDecimalSeparator,              
            cultureInfo.NumberFormat.CurrencyGroupSeparator);
            workSheet.get_Range("A1").Value = "Type";
            workSheet.get_Range("B1").Value = "Value";
            workSheet.get_Range("C1").Value = "Formatted " + Pattern1;
            workSheet.get_Range("D1").Value = "Formatted " + Pattern2;
            int integerValue = 532234;
            workSheet.get_Range("A3").Value = "Integer";
            workSheet.get_Range("B3").Value = integerValue;
            workSheet.get_Range("C3").Value = integerValue;
            workSheet.get_Range("C3").NumberFormat = Pattern1;
            workSheet.get_Range("D3").Value = integerValue;
            workSheet.get_Range("D3").NumberFormat = Pattern2;
            double doubleValue = 23172.64;
            workSheet.get_Range("A4").Value = "double";
            workSheet.get_Range("B4").Value = doubleValue;
            workSheet.get_Range("C4").Value = doubleValue;
            workSheet.get_Range("C4").NumberFormat = Pattern1;
            workSheet.get_Range("D4").Value = doubleValue;
            workSheet.get_Range("D4").NumberFormat = Pattern2;
            float floatValue = 84345.9132f;
            workSheet.get_Range("A5").Value = "float";
            workSheet.get_Range("B5").Value = floatValue;
            workSheet.get_Range("C5").Value = floatValue;
            workSheet.get_Range("C5").NumberFormat = Pattern1;
            workSheet.get_Range("D5").Value = floatValue;
            workSheet.get_Range("D5").NumberFormat = Pattern2;
            Decimal decimalValue = 7251231.313367m;
            workSheet.get_Range("A6").Value = "Decimal";
            workSheet.get_Range("B6").Value = decimalValue;
            workSheet.get_Range("C6").Value = decimalValue;
            workSheet.get_Range("C6").NumberFormat = Pattern1;
            workSheet.get_Range("D6").Value = decimalValue;
            workSheet.get_Range("D6").NumberFormat = Pattern2;
            workSheet.get_Range("A9").Value = "DateTime";
            workSheet.get_Range("B10").Value = cultureInfo.DateTimeFormat.FullDateTimePattern;
            workSheet.get_Range("C10").Value = cultureInfo.DateTimeFormat.LongDatePattern;
            workSheet.get_Range("D10").Value = cultureInfo.DateTimeFormat.ShortDatePattern;
            workSheet.get_Range("E10").Value = cultureInfo.DateTimeFormat.LongTimePattern;
            workSheet.get_Range("F10").Value = cultureInfo.DateTimeFormat.ShortTimePattern;
            // DateTime
            DateTime dateTimeValue = DateTime.Now;
            workSheet.get_Range("B11").Value = dateTimeValue;
            workSheet.get_Range("B11").NumberFormat = 
            cultureInfo.DateTimeFormat.FullDateTimePattern;
            workSheet.get_Range("C11").Value = dateTimeValue;
            workSheet.get_Range("C11").NumberFormat = 
            cultureInfo.DateTimeFormat.LongDatePattern;
            workSheet.get_Range("D11").Value = dateTimeValue;
            workSheet.get_Range("D11").NumberFormat = 
            cultureInfo.DateTimeFormat.ShortDatePattern;
            workSheet.get_Range("E11").Value = dateTimeValue;
            workSheet.get_Range("E11").NumberFormat = 
            cultureInfo.DateTimeFormat.LongTimePattern;
            workSheet.get_Range("F11").Value = dateTimeValue;
            workSheet.get_Range("F11").NumberFormat = 
            cultureInfo.DateTimeFormat.ShortTimePattern;
            // string
            workSheet.get_Range("A14").Value = "String";
            workSheet.get_Range("B14").Value = "This is a sample String";
            workSheet.get_Range("B14").NumberFormat = "@";
            // number as string
            workSheet.get_Range("B15").Value = "513";
            workSheet.get_Range("B15").NumberFormat = "@";
            // set colums
            workSheet.Columns[1, Missing.Value].AutoFit();
            workSheet.Columns[2, Missing.Value].AutoFit();
            workSheet.Columns[3, Missing.Value].AutoFit();
            workSheet.Columns[4, Missing.Value].AutoFit();
            // save the book 
            string fileExtension = GetDefaultExtension(excelApplication);
            string workbookFile = string.Format("{0}\\ExcelExample{1}", Environment.CurrentDirectory,             fileExtension);
            workBook.SaveAs(workbookFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,             Missing.Value,
            XlSaveAsAccessMode.xlExclusive);
            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();
        } 
            #region Helper
            /// <summary>
            /// returns the valid file extension for the instance. for example ".xls" or ".xlsx"
            /// </summary>
            /// <param name="application">the instance</param>
            /// <returns>the extension</returns>
        private static string GetDefaultExtension(Excel.Application application)
        {
            double version = Convert.ToDouble(application.Version);
            if (version >= 120.00)
                return ".xlsx";
            else
                return ".xls";
        }
        #endregion

   }
}