Click here to Skip to main content
15,867,765 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all,

I've made a custom datagridview with Excel export functionality and stumbled upon a problem I can't seem to fix.

I'm trying to format the excel columns according to their datatype and giving an array to the Microsoft.Office.Interop.Excel object.
When my grid column is of any of the number-types (double, integer,...), I hand over the correct format based on my gridcolumns DefaultCellStyle.Format.
It works kind off, but this changes the cell type in Excel to "Custom" instead of keeping it as a Number but with the correct format.
This is annoying because when in Excel you press the "more/less decimals" buttons, it doesn't work.

My guess is that what I want to use for my formatting of number-columns is to tell excel that the column is of the "number" type and just tell it how many decimals it should show depending on the format i'm using in the grid.
I have no idea how to do this, however, as there doesn't seem to be a property of the interop that would allow me to do so.

What I have tried:

Currently I do the following before sending my data array to the interop:
columnRange.NumberFormatLocal = Me.Columns(i).DefaultCellStyle.Format
Tried using columnRange.NumberFormat too, but has the same effect.
It works visually, but gives me the problem i described above.

Cheers in advance for any tips or help :)
Posted
Updated 18-Jan-19 1:58am

1 solution

Check this:
Range.NumberFormat property (Excel) | Microsoft Docs[^]
and this:
MS Excel: How to use the FORMAT Function with Numbers (VBA)[^]

For decimals with 2 places after dot:
VB
columnRange.NumberFormat = "#,##0.00"
'keeps format:
'  .803  =>  0.80
' 1.224  =>  1.22
'55.556 => 55.56


For integers, you can use "General"
VB
columnRange.NumberFormat = "General"
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900