Numeric formatting of a referenced property in a DataGridView bound to a DataTable

Question

Further to this question I am trying to implement a DataTable to use as a data source for a DataGridView. The original problem I am trying to get around is that the DGV presents a small subset of numbers from a sizeable object hierarchy and must reflect changes made to those objects. I cannot simply bind the DGV to the source data. I have therefore created an intermediate DataTable, populated it with the data to be displayed in the UI, and then bound that to the DGV.

The problem I am facing now is that although all of the dynamic values are numeric, I cannot apparently set the display format for those cells using the DataGridViewCellStyle's Format property. If I try to set numbers to three decimal places using "######0.000" for instance, they are unchanged. This doesn't particularly surprise me since the DataTable's data type is 'object'. I set this intentionally for two reasons: 1) It must hold references to the underlying data, not values, and 2) the contents are a mixture of text, which remains fixed, and numbers, which will change. Therefore I think the DGV is not treating the values as numeric.

Binding is done like this:

private void Test_Click(object sender, EventArgs e)
 {
     DataTable dt = populateDataTable();
     BindingSource SBind = new BindingSource();
     SBind.DataSource = dt;
     dataGridView1.DataSource = SBind;
     FormatDGV(dataGridView1);
}

DataTable is populated like this:

private DataTable populateDataTable(int cols, int rows)
    {

        DataTable custTable = new DataTable("UITable");
        for (int i = 0; i < cols; i++)
        {
            custTable.Columns.Add("", typeof(object));
        }
        for (int j = 0; j < rows; j++)
        {
            custTable.Rows.Add(new object[cols]);
        }

... then add specific info which needs to be displayed in the UI. Text is added as object:

custTable.Rows[1][0] = (object)"foo";
custTable.Rows[4][0] = (object)"bar";

I then try to set styles for the cells as I did previously (pre-binding):

dgv[2 + i, 10].Style = greenText;

DataGridViewCellStyle greenText = NewNumberFont(Color.Green, "######0.000");

private DataGridViewCellStyle NewNumberFont(Color fontColor, string numberFormat)
{
    DataGridViewCellStyle numberFont = new DataGridViewCellStyle(dataGridView1.DefaultCellStyle);
    numberFont.Alignment = DataGridViewContentAlignment.MiddleRight;
    Font newFont = new Font(dataGridView1.Font.Name, 15, FontStyle.Regular);
    numberFont.Font = newFont;
    numberFont.ForeColor = fontColor;
    numberFont.Format = numberFormat;
    return numberFont;
}

Is it possible to use this approach of an intermediate data source containing a mix of text and numbers, and still retain control over the number format that is displayed in the DataGridView?

Do you mean that the value of the cell can be a number or text, but you want the format to appear if it is a number? — محمد النعيمي, Jan 05 '20 at 17:06
No. The value of the cell can be a number or text. In the case of numbers, I need to be able to specify the display format. The text is fine as it is. — wotnot, Jan 05 '20 at 17:10
You can simply use the event CellFormatting And then examine the value of the cell if it is numerical applied to the format — محمد النعيمي, Jan 05 '20 at 17:16
In order to check the value if it is numeric, you can use the library "’Microsoft.VisualBaisc.Information" And then use .IsNumeric — محمد النعيمي, Jan 05 '20 at 17:20
I was wondering about using CellFormatting but I am not sure that would work. I cannot (apparently) specify how a number is displayed because the DGV is not treating it as numeric. I could control the formatting by making it into text, but I would not expect that to work since it is bound to a number. I need to control how it is displayed, without affecting the underlying source data. — wotnot, Jan 05 '20 at 17:25
Wait a little, I will write a simple code for you that will not affect the source data. For information, the source data will not be affected unless you confirm it. — محمد النعيمي, Jan 05 '20 at 17:29
Thanks. I have just been reading about DataGridView.CellFormatting. I agree that it may be a solution here. I will try it. — wotnot, Jan 05 '20 at 17:40

score 0 · Accepted Answer · answered Jan 05 '20 at 18:04

You can simply use the event CellFormatting And then examine the value of the cell if it is numerical applied to the format

public Form1()
    {
        InitializeComponent();
        this.dataGridView1.CellFormatting += DataGridView1_CellFormatting;
        this.dataGridView1.Rows.Add("five");
        this.dataGridView1.Rows.Add("235656.5477");
        this.dataGridView1.Rows.Add("24.54"); 
    }
private void DataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
    if (Microsoft.VisualBasic.Information.IsNumeric(e.Value))
    {
        DataGridViewCellStyle numberFont = new DataGridViewCellStyle(dataGridView1.DefaultCellStyle);
        numberFont.Alignment = DataGridViewContentAlignment.MiddleRight;
        Font newFont = new Font(dataGridView1.Font.Name, 15, FontStyle.Regular);
        numberFont.Font = newFont;
        numberFont.ForeColor = Color.Red;
        numberFont.Format = "{0:#,0.####}";
        e.CellStyle = numberFont;
        e.Value = string.Format("{0:#,0.####}", Decimal.Parse(e.Value.ToString()));
    }
}

Numeric formatting of a referenced property in a DataGridView bound to a DataTable

1 Answers1