Cell FormatString changes in Excel

Not Answered This post has 0 verified answers | 8 Replies | 5 Followers Thread's RSS feed.

Jiri
Points 100
Replied On: Thu, Mar 8 2012 3:41 AM Reply

In my code I use (v11.2)

worksheet.Rows(rowIndex).Cells(columnIndex).CellFormat.FormatString = "# ##0,00"

But when open the resulting file in Excel the cell is not formatted properly and when I look at the cell properties in Excel in the Format Cells dialog I see the following : "#\ ## 000"

What should I use as the format string to get the correct "# ##0,00" also in Excel?

 

  • Post Points: 20

All Replies

Replied On: Thu, Mar 8 2012 9:55 AM Reply

This looks like it might be a bug. You should be able to use this format string. I have forwarded this post to the Developer Support Manager and a DS engineer will be contacting you about this issue.

  • Post Points: 20
Michael S.
Points 9,218
Infragistics Employee
Replied On: Thu, Mar 8 2012 4:06 PM Reply

Jiri,

Are you sure that your keyboard is set to a Finnish (or European) keyboard when you type in that format string?  I tried this myself and I was able to get the formatting the way you want it in the Excel file.  However, I did not use the Worksheet.  I handled the InitializeColumn event of the UltraGridExcelExporter and set the format string like so:

if (e.Column.Index == 1)
         {
             e.ExcelFormatStr = "### ### ##0.00";
         }

But I found that the format string must be entered using a European keyboard, otherwise I did not get the decimal values.

So the numbers appear like this:  5 280,00

which I think is what you want. 

Sincerely,
  
Michael S.
Developer Support Engineer
Infragistics, Inc.
www.infragistics.com/support

  • Post Points: 5
Michael S.
Points 9,218
Infragistics Employee
Replied On: Thu, Mar 8 2012 4:14 PM Reply

I should also mention that it is also necessary to make sure that the Regional and Language settings on the machine where you are producing the Excel file are set to Finland or whatever country whose formats you want to use.

Sincerely,
  
Michael S.
Developer Support Engineer
Infragistics, Inc.
www.infragistics.com/support

  • Post Points: 20
Jiri
Points 100
Replied On: Fri, Mar 9 2012 4:19 AM Reply

When I set my locale to US English I can get the rounding right using the format string "# ##0.00", but I loose the thousands separator. It also turns out that the decimal separator (dot or comma) in the Excel format string depends on the locale, i.e. if the locale says comma and the format string has a dot, then the format string doesn't work.

The behavior I would expect is that the string I set as the format string for the cell becomes 1:1 the format string for the cell in Excel. After that it would be my responsibility to use the right thousands and decimal separators when I create the format string by looking at the current locale of the users computer. I would have to accept that if the resulting Excel file is opened on a computer with a different locale, then the format string might not work, but I can live with that.

Currently I really can't use the format string because the results on the client's computer are too unpredictable because I have no control over that setup.

  • Post Points: 20
Michael S.
Points 9,218
Infragistics Employee
Replied On: Thu, Mar 15 2012 1:18 PM Reply

Jiri,

I have subbmitted this issue to our development team for further review.  You will receive more information regarding this item through support case CAS-86377-GMNJ4Y.

Sincerely,
  
Michael S.
Developer Support Engineer
Infragistics, Inc.
www.infragistics.com/support

  • Post Points: 20
woojtii
Points 60
Replied On: Thu, Apr 12 2012 11:02 AM Reply

Hello,

 

i have similar issue

 

when i set StringFormat as "#,##0" then when i open excel file in the format dialog I see "# ##0".

 

another similar issue,

when i create a hyperlink this way: (using "," separator)

.ApplyFormula("=HYPERLINK(\"" + url + "\", \"" + item.FoundString + "\")");

it work on one machine, but generates exception on the other machine with different local settings

but when i create it using ";" separator it works fine o the another machine  (using ";" separator):

.ApplyFormula("=HYPERLINK(\"" + url + "\"; \"" + parsedItem.FoundString + "\")");

 

 

i see Excel generating depends on local settings, how can i get it working correctly?

regards

 

Wojtek

  • Post Points: 20
Replied On: Fri, Apr 13 2012 9:05 AM Reply

I am not sure what the problem is with the format string. I will ask Michael to look into this and see if it is the same issue reported above. As for parsing the formula, the argument separator is different depending on what the decimal separator is for the current culture. If you would like to always use a comma as the argument separator, specify the invariant culture when parsing the formula:

 Formula formula = Formula.Parse(
 "=HYPERLINK(\"" + url + "\", \"" + item.FoundString + "\")",
 CellReferenceMode.A1,
 CultureInfo.InvariantCulture);

formula.ApplyTo(cell);

  • Post Points: 20
Gijs Schouten
Points 65
Replied On: Wed, Aug 13 2014 2:29 AM Reply

Hello Mike,

Is this problem already solved because i created a ticket yesterday with the same issue but with the newest version of infragistics:
http://www.infragistics.com/community/forums/p/91506/452251.aspx#452251

The format string is still changed even if i change local culture settings.

Thanks in advantage.

  • Post Points: 5
Page 1 of 1 (9 items) | RSS