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:
E-Prices
column has an amount like 133.60 €
, Word will just how 133.6
. The 0 is missing.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?
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