' using Numberformats in Excel
' taken from Example03.vb
'
Private Sub Example03Main()

  ' start excel and turn off msg boxes
  Dim excelApplication As New Excel.Application()
  excelApplication.DisplayAlerts = False

  ' add a new workbook
  Dim workBook As Excel.Workbook = excelApplication.Workbooks.Add()
  Dim workSheet As Excel.Worksheet = workBook.Worksheets(1)

  ' /* some kind of numerics */

  '  the given thread culture in all latebinding calls are stored 
' in NetOffice.Settings. you can change the culture. default is en-us
Dim cultureInfo As CultureInfo = NetOffice.Settings.ThreadCulture Dim Pattern1 As String = String.Format("0{0}00", _
cultureInfo.NumberFormat.CurrencyDecimalSeparator) Dim Pattern2 As String = String.Format("#{1}##0{0}00", _
cultureInfo.NumberFormat.CurrencyDecimalSeparator, _
cultureInfo.NumberFormat.CurrencyGroupSeparator) workSheet.Range("A1").Value = "Type" workSheet.Range("B1").Value = "Value" workSheet.Range("C1").Value = "Formatted " + Pattern1 workSheet.Range("D1").Value = "Formatted " + Pattern2 Dim integerValue As Integer = 532234 workSheet.Range("A3").Value = "Integer" workSheet.Range("B3").Value = integerValue workSheet.Range("C3").Value = integerValue workSheet.Range("C3").NumberFormat = Pattern1 workSheet.Range("D3").Value = integerValue workSheet.Range("D3").NumberFormat = Pattern2 Dim doubleValue As Double = 23172.64 workSheet.Range("A4").Value = "double" workSheet.Range("B4").Value = doubleValue workSheet.Range("C4").Value = doubleValue workSheet.Range("C4").NumberFormat = Pattern1 workSheet.Range("D4").Value = doubleValue workSheet.Range("D4").NumberFormat = Pattern2 Dim floatValue As Single = 84345.9141F workSheet.Range("A5").Value = "float" workSheet.Range("B5").Value = floatValue workSheet.Range("C5").Value = floatValue workSheet.Range("C5").NumberFormat = Pattern1 workSheet.Range("D5").Value = floatValue workSheet.Range("D5").NumberFormat = Pattern2 Dim decimalValue As Decimal = 7251231.313367 workSheet.Range("A6").Value = "Decimal" workSheet.Range("B6").Value = decimalValue workSheet.Range("C6").Value = decimalValue workSheet.Range("C6").NumberFormat = Pattern1 workSheet.Range("D6").Value = decimalValue workSheet.Range("D6").NumberFormat = Pattern2 workSheet.Range("A9").Value = "DateTime" workSheet.Range("B10").Value = cultureInfo.DateTimeFormat.FullDateTimePattern workSheet.Range("C10").Value = cultureInfo.DateTimeFormat.LongDatePattern workSheet.Range("D10").Value = cultureInfo.DateTimeFormat.ShortDatePattern workSheet.Range("E10").Value = cultureInfo.DateTimeFormat.LongTimePattern workSheet.Range("F10").Value = cultureInfo.DateTimeFormat.ShortTimePattern ' DateTime Dim dateTimeValue As DateTime = DateTime.Now workSheet.Range("B11").Value = dateTimeValue workSheet.Range("B11").NumberFormat = cultureInfo.DateTimeFormat.FullDateTimePattern workSheet.Range("C11").Value = dateTimeValue workSheet.Range("C11").NumberFormat = cultureInfo.DateTimeFormat.LongDatePattern workSheet.Range("D11").Value = dateTimeValue workSheet.Range("D11").NumberFormat = cultureInfo.DateTimeFormat.ShortDatePattern workSheet.Range("E11").Value = dateTimeValue workSheet.Range("E11").NumberFormat = cultureInfo.DateTimeFormat.LongTimePattern workSheet.Range("F11").Value = dateTimeValue workSheet.Range("F11").NumberFormat = cultureInfo.DateTimeFormat.ShortTimePattern ' string workSheet.Range("A14").Value = "String" workSheet.Range("B14").Value = "This is a sample String" workSheet.Range("B14").NumberFormat = "@" ' number as string workSheet.Range("B15").Value = "42" ' :-) workSheet.Range("B15").NumberFormat = "@" ' set colums workSheet.Columns(1).AutoFit() workSheet.Columns(2).AutoFit() workSheet.Columns(3).AutoFit() workSheet.Columns(4).AutoFit() ' save the book Dim fileExtension As String = GetDefaultExtension(excelApplication) Dim workbookFile As String = String.Format("{0}\Example03{1}", _
Application.StartupPath, 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() Dim fDialog As New FinishDialog("Workbook saved.", workbookFile) fDialog.ShowDialog(Me) End Sub ''' <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> ''' <remarks></remarks> Private Function GetDefaultExtension(ByVal app As Excel.Application) As String Dim version As Double = Convert.ToDouble(app.Version, CultureInfo.InvariantCulture)
If (version >= 12.0) Then Return ".xlsx" Else Return ".xls" End If End Function

Last edited May 14, 2012 at 4:45 AM by SebastianDotNet, version 7