How to convert column datagridview values to strings manually?

Question

How to convert column datagridview values to strings manually in VB.NET?

I tried to convert the values manually it didn't work because I wanted to use it in the print preview library but if I use another example (shown further down) it works. Is there an error in my code?

Public Function getData() As DataTable
    Dim dt As New DataTable()
    dt.Columns.Add("UserId", GetType(Int32))
    dt.Columns.Add("UserName", GetType(String))
    dt.Columns.Add("Education", GetType(String))
    dt.Columns.Add("Location", GetType(String))
    dt.Columns.Add("Price", GetType(Double))
    dt.Rows.Add(1, "Satinder Singh", "Bsc Com Sci", "Mumbai", 25000)
    dt.Rows.Add(2, "Amit Sarna", "Mstr Com Sci", "Mumbai", 21000)
    dt.Rows.Add(3, "Andrea Ely", "Bsc Bio-Chemistry", "Queensland", 35000)
    dt.Rows.Add(4, "Leslie Mac", "MSC", "Town-ville", 22000)
    dt.Rows.Add(5, "Vaibhav Adhyapak", "MBA", "New Delhi", 27000)
    Return dt
End Function

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    DataGridView1.DataSource = getData()

End Sub
    Private Sub DataGridView1_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting

        If (e.ColumnIndex = 4) Then

            e.Value = String.Format("{0:N2}", e.Value)

        End If

    End Sub
Private Sub GenerateReport()
    KtReport1.Clear()
    KtReport1.AddString("<h2>Product List</h2>")
    KtReport1.AddHorizontalRule()
    ktReport1.AddDatagridView(DataGridView1)
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    GenerateReport()
    KtReport1.ShowPrintPreviewDialog()
End Sub

datagridview in column price:- datagridview in column price in print preview report:-

If I use another example it works:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim random As New Random()
    For i As Integer = 0 To 4
        Dim productName = "Product" & (i + 1)
        Dim productPrice = random.Next(5, 5000)
        DataGridView1.Rows.Add(New Object() {productName, productPrice.ToString("N2")})
    Next i
End Sub

Private Sub GenerateReport()
    KtReport1.Clear()
    KtReport1.AddString("<h2>Product List</h2>")
    KtReport1.AddHorizontalRule()
    ktReport1.AddDatagridView(DataGridView1)
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    GenerateReport()
    KtReport1.ShowPrintPreviewDialog()
End Sub

another example result datagridview in column price:-

another example result datagridview in column price in print preview report:-

Answer results from user09938

Result in datagridview

Result in Print Preview (Ktreport)

code kimtoo report (ktreport)

Answer 1

It's not clear what KtReport1 is, but it seems that you'd like to format the Price column in the DataGridView. The following shows how to use a DataTable with a DataGridView and how to set the DataGridViewColumn properties.

According to Decimal Data Type (Visual Basic):

It is particularly suitable for calculations, such as financial, that require a large number of digits but cannot tolerate rounding errors.

In the code below, you'll notice that if the data type of the DataTable column is Decimal, the Format is set to N2. Otherwise, the Format isn't set.

Form1.vb

Public Class Form1

    'create new instance
    Private _dt As DataTable = New DataTable()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'initialize DataTable
        InitializeDataTable(_dt)

        'initialize DataGridView
        InitializeDataGridView(_dt)

        'bind to DataTable
        DataGridView1.DataSource = _dt

        'add test data
        AddTestData(_dt)
    End Sub

    Public Sub InitializeDataTable(dt As DataTable)
        'add columns to DataTable
        dt.Columns.Add(New DataColumn() With {.ColumnName = "UserId", .Caption = "User Id", .DataType = GetType(Int32)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "Username", .Caption = "Username", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "Education", .Caption = "Education", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "Location", .Caption = "Location", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "Price", .Caption = "Price", .DataType = GetType(Decimal)})
    End Sub

    Public Sub InitializeDataGridView(dt As DataTable)
        'set property
        DataGridView1.AutoGenerateColumns = False

        'remove existing columns
        DataGridView1.Columns.Clear()

        'add each column that exists in the DataTable to the DataGridView
        'ToDo: change/set properties as desired
        For Each dataColumn As DataColumn In dt.Columns
            Dim dgvCol As DataGridViewTextBoxColumn = New DataGridViewTextBoxColumn() With
            {
                .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill,
                .DataPropertyName = dataColumn.ColumnName,
                .DefaultCellStyle = If(dataColumn.DataType = GetType(Decimal), New DataGridViewCellStyle() With {.Alignment = DataGridViewContentAlignment.MiddleCenter, .Format = "N2"}, New DataGridViewCellStyle() With {.Alignment = DataGridViewContentAlignment.MiddleCenter}),
                .FillWeight = 10,
                .HeaderCell = New DataGridViewColumnHeaderCell() With {.Style = New DataGridViewCellStyle() With {.Alignment = DataGridViewContentAlignment.MiddleCenter}},
                .HeaderText = If(Not String.IsNullOrEmpty(dataColumn.Caption), dataColumn.Caption, dataColumn.ColumnName),
                .MinimumWidth = 5,
                .Name = dataColumn.ColumnName,
                .Width = 100
            }

            'add column to DataGridView
            DataGridView1.Columns.Add(dgvCol)
        Next
    End Sub

    Public Sub AddTestData(dt As DataTable)
        'add rows to DataTable
        dt.Rows.Add(1, "Satinder Singh", "Bsc Com Sci", "Mumbai", 25000)
        dt.Rows.Add(2, "Amit Sarna", "Mstr Com Sci", "Mumbai", 21000)
        dt.Rows.Add(3, "Andrea Ely", "Bsc Bio-Chemistry", "Queensland", 35000)
        dt.Rows.Add(4, "Leslie Mac", "MSC", "Town-ville", 22000)
        dt.Rows.Add(5, "Vaibhav Adhyapak", "MBA", "New Delhi", 27000)
    End Sub
End Class

Additional Resources

• If operator
• Standard numeric format strings