BanTim BanTim - 5 months ago 54
Vb.net Question

Convert Cell Values of DataTable to Percentages

I have a dynamically generated

DataTable
with bunch of different values:

Column1 | Column2 | Column3
----------------------------
230 | 265 | 272
270 | 235 | 235
250 | 235 | 254
250 | 225 | 259


I want to show data of each cell this way:


  • Each cell value in a column should be represented as percentage value of the column total.



For example instead of 230 in first cell of
Column1
in above table, I want to show
23.00%
. And it should calculate this way:

23.00% = (230 / (230 + 270 + 250 + 250)) * 100 (For Column 1...cell 1)


For example, for above table, the result should be:

Column1 | Column2 | Column3
-----------------------------
23.00% | 27.60% | 26.67%
27.00% | 24.48% | 23.04%
25.00% | 24.48% | 24.90%
25.00% | 23.44% | 25.39%

Answer

You can add computed columns to your DataTable by setting Expression property of DataColumn.

Then you can format the data in your DataGridView using DefaultCellStyle.Format property of the column.

For example suppose you have a Table1 which contains Column1, then to add a computed column Column1Percent you can do this:

table1.Columns.Add("Column1Percent", double);
table1.Columns["Column1Percent"].Expression = "(Column1/SUM(Column1)) * 100";
this.DataGridView1.Columns["Column1Percent"].DefaultCellStyle.Format = "0.00'%'";

And then the result would be:

Column1 | Column1Percent
------------------------
1       | 20.00%
2       | 40.00%
2       | 40.00%