Search code examples
vb.netexcelexportdecimaltransfer

Decimal separator lost after export from VB.Net to Excel

I have numbers and text stored in a 1-dimensional string array which i need to send to excel. For this operation i used a simple code:

    WPFtoExcel.Range("A1").Activate() 
    For item = 0 To 10 
    WPFtoExcel.ActiveCell.Value = Array(item)
    WPFtoExcel.ActiveCell.Offset(0, 1).Activate()
    Next

Some numbers that have >2 decimals in the array loose their decimal points during the transfer:

  • 5,65 appears in excel as 5,65 {OK}
  • 0,0152 appears in excel as 0,0152 {OK}

but:

  • 800,111 appears in excel as 800 111
  • 800,1114 appears in excel as 8 001 114

I would appreciate if someone could suggest how such numbers can be transfered as is.

Regards,

Solution

  • If you format the cell as Text before placing a value in it, Excel will refrain from changing it. Include:

    WPFtoExcel.ActiveCell.NumberFormat = "@"

    before:

    WPFtoExcel.ActiveCell.Value = Array(item)