How to format datatable column

Question

1.00/5 (1 vote)

See more:

I have a datatable with following values.

OA OA_Date OA_currency OA_Balance_Amount OA_remarks
39392 08-Feb-16 USD -573518.00 Twice paid-US410-0001067399
39402 08-Feb-16 USD -416612.30 Excess paid-US410-0001123424
39385 08-Feb-16 EUR -303531.76 Details required

My requirement is i need amount column number to be in commas, that is -573518.00 to -573,518.00.

So my data table must be like this

OA OA_Date OA_currency OA_Balance_Amount OA_remarks
39392 08-Feb-16 USD -573,518.00 Twice paid-US410-0001067399
39402 08-Feb-16 USD -416,612.30 Excess paid-US410-0001123424
39385 08-Feb-16 EUR -303,531.76 Details required

Is it possible to achieve this. Please help.
Thanks in advance.

What I have tried:

i had checked many sites but i didn't found anything that fulfills my criteria.

Posted 1-Jun-16 7:45am

Member 8478234

Updated 1-Jun-16 8:37am

Add a Solution

Comments

CHill60 1-Jun-16 14:02pm

Values in a datatable don't have format - it's at the point of displaying the data that you would introduce format. How are you displaying this data?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-06-01T08:11:00

You don't format a DataTable - you format the source (normally a DB) or more usually you format the presentation control: the GridView, DataGridView, or similar that display the values.
The commas you mention aren't "real" - they are an artifact of the presentation in the users culture, and may not be commas at all, or may be in a different digit position.
For example, UK numbers can be split into thousands: 1,234,567,890.1 with a comma between each three digits. However the Indian equivalent would be based on the lakh, and separate the first three digits, then each two: 1,23,45,67,890.1
Other cultures swap the comma and dot. Some users don't want comma separation at all!
But trying to format the DataTable, you throw away the number itself, and replace it with a string - which means that local formatting can no longer be applied on a user-by-user basis.

Leave it to the presentation layer, and "fix" the formatting there if you really must (but I don't recommend it!)

CHill60 · Answer 2 · 2016-06-01T08:33:00

In addition to Solution 1

When you format the number you can ensure it picks up the local culture display as follows:

C#

Console.WriteLine(aNumber.ToString("N"));

Or to a specific culture (I repeat OriginalGriff's words - I don't recommend it) e.g. for Gujarati

C#

Console.WriteLine(aNumber.ToString("N", CultureInfo.CreateSpecificCulture("gu-IN")));

Similarly if you are displaying the data in a DataGridView then you can format the appropriate column as follows

C#

dataGridView1.DataSource = dt;
dataGridView1.Columns[3].DefaultCellStyle.Format = "N";

And if you want to force the display culture (Not recommended) then add this line

C#

dataGridView1.Columns[3].DefaultCellStyle.FormatProvider = new CultureInfo("gu-IN");

George Jonsson · Answer 3 · 2016-06-01T08:37:00

Example to format a number as you want it:

C#

decimal d = 199999.99M;
string s = d.ToString("C2", new System.Globalization.CultureInfo("en-US"));

This will give the result

$199,999.99

So in your data table you store the value as a decimal or maybe a double and when you present the data in a report or data grid, you format it there.
This also give the possibility to present the data in different ways for different cultures.

In a DataGridView (Windows Forms) you manipulate the DataGridViewCellStyle, see DataGridViewCellStyle.Format Property (System.Windows.Forms)[^], but I think you also need to set the culture for your UI thread first to get the wanted result.

C#

System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");

System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle1 = new System.Windows.Forms.DataGridViewCellStyle();
dataGridViewCellStyle1.Format = "C2";
dataGridView.DefaultCellStyle = dataGridViewCellStyle1;

How to format datatable column

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0