Using numberformats in Excel

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

}
}