VB.NET中如何让DataGridView列编辑时不显示货币格式

我需要在VB.NET的DataGridView中实现编辑列值时不显示货币格式的效果——即单元格非编辑状态下显示带$符号的货币格式，编辑时只显示纯数字。目前代码运行时，编辑Price列会同时显示$符号，不符合需求。

当前代码 #

Public Class Form1
    Private BindingSource1 As BindingSource = Nothing
    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
        ' To reduce the flickering...
        DataGridView1.GetType().
    GetProperty("DoubleBuffered",
                BindingFlags.Instance Or BindingFlags.NonPublic).
    SetValue(DataGridView1, True)

    End Sub
    Private Sub CalculateTotal()
        Dim tot As Double = 0
        For Each item As DataGridViewRow In DataGridView1.Rows
            Dim quantity As Double = 0
            Dim price As Double = 0
            If Not IsDBNull(item.Cells("Qty").Value) Then
                Double.TryParse(CType(item.Cells("Qty").Value, String), quantity)
            End If
            If Not IsDBNull(item.Cells("PRICE").Value) Then
                Double.TryParse(CType(item.Cells("PRICE").Value, String), price)
            End If

            tot += (quantity * price)
        Next item
        lblTotal.Text = tot.ToString("N")
    End Sub
    Protected Sub updateDataSource()
        BindingSource1 = New BindingSource With {.DataSource = New BindingList(Of Products)}
        DataGridView1.DataSource = BindingSource1
        DataGridView1.AutoGenerateColumns = False
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.Columns("PRICE").DefaultCellStyle.Format = "c2"
        DataGridView1.Columns("PRICE").DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("en-US")
        DataGridView1.Columns("TOTAL").DefaultCellStyle.Format = "c2"
        DataGridView1.Columns("TOTAL").DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("en-US")
        BindingSource1.AddNew()
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        updateDataSource()
    End Sub
    Private Sub DataGridView1_CellEndEdit(
    sender As Object,
    e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Dim dgv = DirectCast(sender, DataGridView)
        Dim PRICE As Double = Nothing, QTY As Double = Nothing, TOTAL As Double = Nothing
        If DataGridView1.Rows(e.RowIndex).Cells("QTY").Value IsNot Nothing Then
            If Not Double.TryParse(DataGridView1.Rows(e.RowIndex).Cells("QTY").Value.ToString(), QTY) Then
                QTY = 0
            End If
        Else
            QTY = 0
        End If
        DataGridView1.Rows(e.RowIndex).Cells("QTY").Value = QTY
        If DataGridView1.Rows(e.RowIndex).Cells("PRICE").Value IsNot Nothing Then
            If Not Double.TryParse(DataGridView1.Rows(e.RowIndex).Cells("PRICE").Value.ToString(), PRICE) Then
                PRICE = 0
            End If
        Else
            PRICE = 0
        End If
        DataGridView1.Rows(e.RowIndex).Cells("PRICE").Value = PRICE
        TOTAL = (QTY * PRICE)
        DataGridView1.Rows(e.RowIndex).Cells("TOTAL").Value = TOTAL
        CalculateTotal()
    End Sub
End Class
Public Class Products
    Public Property PRODUCTNAME() As String
    Public Property QTY() As Double
    Public Property PRICE() As Double
    Public Property TOTAL() As Double
End Class

问题原因 #

当前代码给PRICE列设置了DefaultCellStyle.Format = "c2"，该格式会应用到单元格的所有状态（包括编辑状态），导致编辑时也会显示$货币符号。

解决方案 #

方案1：利用EditingControlShowing事件 #

通过监听编辑控件显示事件，在编辑Price列时替换为纯数字格式，编辑结束后自动恢复货币格式：

Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
    ' 仅处理PRICE列
    If DataGridView1.CurrentCell.OwningColumn.Name = "PRICE" Then
        Dim editTextBox As TextBox = TryCast(e.Control, TextBox)
        If editTextBox IsNot Nothing Then
            ' 将当前单元格的货币格式值转换为纯数字显示
            Dim currentValue As Double = 0
            If Double.TryParse(DataGridView1.CurrentCell.Value.ToString(), currentValue) Then
                editTextBox.Text = currentValue.ToString("N2") ' 保留两位小数的纯数字
            End If
        End If
    End If
End Sub

无需修改原有的DefaultCellStyle设置，编辑结束后单元格会自动恢复货币格式。

方案2：自定义单元格类型 #

创建自定义单元格，根据是否处于编辑状态返回不同格式的值，更灵活可控：

' 自定义货币编辑单元格
Public Class CurrencyEditableCell
    Inherits DataGridViewTextBoxCell

    Protected Overrides Function GetFormattedValue(value As Object, rowIndex As Integer, ByRef cellStyle As DataGridViewCellStyle, valueTypeConverter As TypeConverter, formattedValueTypeConverter As TypeConverter, context As DataGridViewDataErrorContexts) As Object
        ' 判断是否处于编辑状态，切换格式
        If Not Me.DataGridView.IsCurrentCellInEditMode Then
            ' 非编辑状态：货币格式
            cellStyle.Format = "c2"
            cellStyle.FormatProvider = CultureInfo.GetCultureInfo("en-US")
        Else
            ' 编辑状态：纯数字格式
            cellStyle.Format = "N2"
        End If
        Return MyBase.GetFormattedValue(value, rowIndex, cellStyle, valueTypeConverter, formattedValueTypeConverter, context)
    End Function
End Class

然后在updateDataSource方法中设置PRICE列的单元格模板：

' 替换PRICE列的单元格类型为自定义类型
DataGridView1.Columns("PRICE").CellTemplate = New CurrencyEditableCell()

代码优化建议 #

1. 利用数据绑定特性：避免直接操作DataGridView单元格，直接修改绑定的Products对象属性，UI会自动同步：

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    Dim currentProduct As Products = DirectCast(BindingSource1.Item(e.RowIndex), Products)
    
    ' 验证并更新QTY
    If Not Double.TryParse(currentProduct.QTY.ToString(), currentProduct.QTY) Then
        currentProduct.QTY = 0
    End If
    
    ' 验证并更新PRICE
    If Not Double.TryParse(currentProduct.PRICE.ToString(), currentProduct.PRICE) Then
        currentProduct.PRICE = 0
    End If
    
    ' 自动计算TOTAL
    currentProduct.TOTAL = currentProduct.QTY * currentProduct.PRICE
    CalculateTotal()
End Sub

2. 优化合计计算：直接遍历绑定数据源，效率更高：

Private Sub CalculateTotal()
    Dim total As Double = 0
    For Each product As Products In BindingSource1.List
        total += product.QTY * product.PRICE
    Next
    lblTotal.Text = total.ToString("c2", CultureInfo.GetCultureInfo("en-US"))
End Sub

内容的提问来源于stack exchange，提问作者dlaksmi