Update: if you use the formatter as 'n' for number, it works fine...!
The data I am getting from the database is in the RAW format. For example, if I am tracking the price of the item, it will be 23.45343 . I need to display in the form of 23.45.
I also need to take care of the rounding the values. Also, adding ',' for every thousands e.g. 1,000,000.01 But I do not need currency specifier along with it. e.g. $. I can add formats such as c, d. But I need the currency specifier removed.
I also need to handle percentages, to show, 2.1 as 2.1%
Now, I can use 'p' to format it as %, but the problem here is the data I am getting from the DB is already in %. But the number doesnt have the % sign, which I need to add to the cells.
Is it possible to do that? If yes, how?
Another Update:
The datetime data I am getting from the database is like this: "Fri Sep 05 00:00:00 EDT 2008"
I need to convert it to MM/dd/yyyy format. I have tried using format strings like d,g,etc. But I am not able to format it. Is there any way where we can manually format the data of the column?
I'll really appreciate your help in this regards.
Thanks :)
I already had a post on this. But had many updates after that and hence posting it again.
I am using UltraGrid for my application. One of the columns' data which I am getting from the DB contains a % value. And, I need to display that as '% ' in the grid. e.g. 2.1 --> 2.1%. So, I can not use the formatter 'p' for this.
Moreover, for another column, I am getting the date as, 'Fri Sep 05 00:00:00 EDT 2008'. I tried using the formatters like 'd', 'g', but it is not working. Is there any way to format it such that, I can format the value in MM/dd/yyyy format?
What the grid does is take the Value of the cell and call ToString on it and pass in the Format you specified. So the formats you can use are not related to the grid, they are the format used by the data type you are using. For documentation on the formats, check out Microsoft's documentation:
Standard Numeric Format Strings
Custom Numeric Format Strings
If you can't get what you want using those, then the next option would be to format the values yourself. You could do this by hiding the real column and showing an unbound column. Or you could use a DataFilter.
The unbound column approach is easier, especially if the field is not editable - since you do not have to un-format it.
Note: Assume .Format is prefaced with GridName.DisplayLayout.Bands(0).Columns(ColName)
Apparently you can also use excel-like formatting: .Format = "#,##0.00;-#,##0.00;-"
Which gives you: Positive Numbers: 1,000.00 Negative Numbers: -1,000.00 Zero: -
You could also modify it to do the () for negative numbers so -5 would display as (5.00) .Format = "#,##0.00;(#,##0.00);-"
Which Gives You: Positive Numbers: 1,000.00 Negative Numbers: (1,000.00) Zero: -
So the order is: "Positive_Number_Format;Negative_Number_Format;Zero_Format"
Question:In excel, you can put a color indicator for positive and negative, ie [Red](#,##0.00)Is that also possible here? I tried and it didn't work, but is there a different format? Or would I just do it in initialize row?
I have attached screenshots of the results of the examples I gave above.
Another question:
The following is the Accounting format in excel: "_($* #,##0.00_);_($* -#,##0.00;_($* ""-""??_);_(@_)"
I tried translating that to this: "$* #,##0.00;$* -#,##0.00;-" but it displayed the *
This get's me close, but not with the $: "$ #,##0.00;$ -#,##0.00;-"
Is there a way to make it work so that the $ is left aligned and the amount is right aligned?
In C# you can do the fomatting like this:
"_(#,##0.0%_);_((#,##0.0%);_(\"-\"??_);_(@_)"
Note the "\" before the quotes inside the format string. That solves the problem of the double "".
I'd stripped out the dollar signs, was mostly looking for the "-" for zero amounts. Here's the corrected example.
"_($* #,##0.00_);_($* -#,##0.00;_($* \"-\"??_);_(@_)"