genespos genespos - 9 days ago 5
Vb.net Question

Excel file (from DataGridView) not showing 'Zero' for values < 1

I'm exporting a DataGridView to an Excel file.

The DGV contains a column with currency (€) and so I'm using this code to format the Excel Sheet Column:

With xlSheet
.Range(.Cells(1, C + 1), .Cells(1, C + 1)).EntireColumn.NumberFormat = "€ #,###,###.00"
End With


But, when I open the saved excel file, if the value is < 1 (i.e. 0,25) I get:


€ ,25


instead of


€ 0,25


How can I fix?

Answer

Add a zero before the decimal point into your number format:

With xlSheet
    .Range(.Cells(1, C + 1), .Cells(1, C + 1)).EntireColumn.NumberFormat = "€ #,###,##0.00"
End With

From this link - https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4

0 (zero) -- This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.