Thread: working with excel number format

In my program, I export datagridview results to an excel document, and the cells are formatted accordingly in the process
I've used the numberformat property with the value "$#,##.00" to format currency fields and "MM/DD/YYYY" to format short date fields. The formatting works, but in excel, the number format field in the ribbon reads 'Custom'. Instead, how do I make format it exactly to make it read 'Short Date' or 'Currency', instead of Custom?

How are you exporting to Excel?

Originally Posted by JohnH

How are you exporting to Excel?

JohnH, I am using the Excel.Application class and a reference to MS Excel Object 14.0 Library

Namespace import
Imports Excel = Microsoft.Office.Interop.Excel
Imports Word = Microsoft.Office.Interop.Word


heres some code

Code:

Dim oXL As Excel.Application
         Dim oWB As Excel.Workbook
         Dim oSheet As Excel.Worksheet
         Dim oRange As Excel.Range
 
         oXL = New Excel.Application
         'oXL.Visible = True '
         oXL.DisplayAlerts = False
         oWB = oXL.Workbooks.Add
         oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)
         oSheet.Name = "vInventory"
 
         Dim dt As New DataTable
         dt = (DirectCast(DataGridView1.DataSource, DataTable))
 
         Dim rowCount As Integer = 1
         For Each dr As DataRow In dt.Rows
             rowCount += 1
             For i As Integer = 1 To dt.Columns.Count
                 If rowCount = 2 Then
                     oSheet.Cells(1, i) = dt.Columns(i - 1).ColumnName
                 End If
                 oSheet.Cells(rowCount, i) = dr.Item(i - 1).ToString
             Next
         Next
 
 
             Dim cellRange As Excel.Range
             cellRange = oSheet.Range(oSheet.Cells(2, 1), oSheet.Cells(rowCount,1)) 'first column cells
             cellRange.NumberFormat = "MM/DD/YYYY" 'formatted as 'Short date', but in excel it reads 'Custom'

            cellRange = oSheet.Range(oSheet.Cells(2, 1), oSheet.Cells(rowCount,1)) 'second column cells
              cellRange.NumberFormat = "$#,##.00" 'formatted as 'Currency', but in excel it reads 'Custom', 


            oRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(rowCount, dt.Columns.Count))
             oRange.EntireColumn.AutoFit()

        Dim saveFileDialog1 As New SaveFileDialog
         saveFileDialog1.Filter = "Excel Workbook|*.xlsx"
         saveFileDialog1.Title = "Export to Excel"
         saveFileDialog1.FileName = fileSavePath("xlsx")
         saveFileDialog1.ShowDialog()
 
         If saveFileDialog1.FileName <> "" Then
             oWB.SaveAs(saveFileDialog1.FileName)
             oWB.Close() '
             oWB = Nothing
             oXL.Quit() '
 
             GC.WaitForPendingFinalizers()
             GC.Collect()
end if

The formatting works, but in excel it should read 'Short Date' or 'Currency' and not Custom
Maybe I should use a different numberformat property value?

My first though was that you were transferring strings instead of actual typed values, but I found that didn't matter much for dates. What I found is that several of the formats in the dialog actually has ";@" appended to them, try for yourself and record a macro and then set a cell format, look at the generated macro code to see the actual NumberFormat applied. For example in dialog I saw format d/m/yy, but the generated code set NumberFormat="d/m/yy;@". Once I did the same in my code the cell formats were correctly display in dialog afterwards. As for numberic values you should transfer those as numbers (Integer, Single etc) and not as strings, I see you do dr.Item(i - 1).ToString and that converts the value, if it is not already a numeric value, to a string.