When exporting from DGV1 to Word, the currency format is lost

Question

I have 2 DataGridViews in Form1. DGV1 is data bound, where the data comes from an Access database; DGV2 is unbound.

I can copy the selected line from DGV1 to DGV2. When I do, everything is displayed correctly in DGV2. This includes the 2 formatted columns: E-Prices and Total, which should be show with currencies (€) and 2 decimal places.

Now, when I export the data from DGV2 to Word, the following happens:

1. In DGV2, if the E-Prices column has an amount like 133.60 €, Word will just how 133.6. The 0 is missing.
2. If I have a value like 10,00 € I see 10, but the comma and the two 00 are missing.

The zero behind it is always missing ....

I tried it with 2 different ways for testing to export to Word, but the result is always the same.

Here is one method:

Private Sub Button7_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
    ' Copy the data
    Dim r As Integer, c As Integer
    Dim oTable As Word.Table = objDoc.Tables.Add(objDoc.Bookmarks.Item("\endofdoc").Range,
        Datagridview2.RowCount, Datagridview2.ColumnCount, Datagridview2.ColumnHeadersVisible = True)
    For r = 0 To Datagridview2.RowCount - 2
        For c = 0 To Datagridview2.Columns.Count - 1
            If Datagridview2.Rows(r).Cells(4).Value IsNot DBNull.Value Then
                sum += Datagridview2.Rows(r).Cells(4).Value
            End If
            objDoc.Tables(1).Cell(r + 1, c + 1).Range.Text = Datagridview2(c, r).Value.ToString
        Next
    Next

    ' Format the cells
    Dim oCell As Cell
    oTable = objDoc.Tables(1)
    For Each oCell In oTable.Range.Cells
        If oCell.RowIndex > 0 Then
            Select Case oCell.ColumnIndex
                Case 1
                    oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter
                    oCell.Range.Font.Color = WdColor.wdColorBrown
                    oCell.Range.Font.Bold = False
                    oCell.Range.Columns.Width = 65
                Case 2
                    oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter
                    oCell.Range.Font.Color = WdColor.wdColorDarkYellow
                    oCell.Range.Font.Bold = False
                    oCell.Range.Columns.Width = 35
                Case 3
                    oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft
                    oCell.Range.Font.Color = WdColor.wdColorDarkTeal
                    oCell.Range.Font.Bold = False
                    oCell.Range.Columns.Width = 280
                Case 4
                    oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight
                    oCell.Range.Font.Color = WdColor.wdColorBrown
                    oCell.Range.Font.Bold = False
                    oCell.Range.Columns.Width = 70
                Case 5
                    oCell.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphRight
                    oCell.Range.Font.Color = WdColor.wdColorRed
                    oCell.Range.Font.Bold = False
                    oCell.Range.Columns.Width = 70
            End Select
        End If
    Next
End Sub

How can I fix this?

Answer 1

The fundamental issue here is one of data types, and understanding how the computer sees the difference between number types and strings.

The actual value supplied for your DataGridView is (hopefully) a Decimal (it could also be a double/float/etc, but only Decimal is correct when working with money).

When you see a nicely formatted value like 133.60 € on the screen, you're seeing a string rather than a Decimal, and you get this string because the DataGridView is configured to treat this column as a currency and format it appropriately. But the actual value in the cell is still a binary number, with no human-readable format at all.

However, MS Word does not know about your DataGridView formatting. It only sees the value. It doesn't know the value represents money, and so does only a basic conversion to string instead of a nice formatted conversion to a string-representing-currency.

To fix this, you want to think ahead about what kind of string you need to produce for each column. Then you can either call .ToString() on the original value with the appropriate format string. Or you can look at the FormattedValue property in the grid cell instead of the base Value property when exporting for Word.

So this line:

objDoc.Tables(1).Cell(r + 1, c + 1).Range.Text = Datagridview2(c, r).Value.ToString

might need to look like this:

objDoc.Tables(1).Cell(r + 1, c + 1).Range.Text = Datagridview2(c, r).FormattedValue