Getting DecimalPlaces for formula cells

[Christian Muirhead]

Hi -

In this workbook A1 contains a constant number and is set to display 3 decimal places, while B1 has a formula (=1/3), and is set to display 5 decimal places.

I can get the decimal places for A1 using:

wb.ActiveSheet[1, 1].CellStyle.NumberFormatSettings.DecimalPlaces

but trying the same thing with B1 (wb.ActiveSheet[1, 2]...) fails with a NullReferenceException.

I'm reluctant to parse the CellStyle.NumberFormat string (especially given that I'm having trouble finding a detailed description of what it might contain).

Any ideas what might be going on here?

Thanks,
Christian


decimal-places.zip

[Sarathi D]

Hi Christian,

Thank you for posting your query to us.

We are able to reproduce the issue here. One way you can work-around this issue by using the below code snippet.

string s = sheet.Range["B1"].CellStyle.NumberFormatSettings.FormatString;
int l = s.Length - 2;
MessageBox.Show(l.ToString());

Please refer to the attached sample for implementation and let me know if this helps.

http://websamples.syncfusion.com/samples/XlsIO.Windows/67608/main.htm

Regards,
Sarathi

[Konrad Delong]

After spending some time on the issue, whe ended up parsing format string, as you suggest.

However, the solution doesn't work for cells with alternative formatting for negatives whose format string look like this:

0.000;[Red]0.00

Do you happen to have full formatString specification?

[Sarathi D]

Hi Christian,

I am afraid that I was not able to reproduce the issue again.I have applied alternate formatting to the cells but I am not able to see the issue.
I have attached the sample which I have worked and it is available here.

http://websamples.syncfusion.com/samples/XlsIO.Windows/F67608/main.htm

Please have a look at the above sample and if still the issue exists, could you please try reproducing it
in the above sample or send us the reproducing steps so that we could sort out the cause of the issue
and provide you a solution?

A sample from you will help a lot to reproduce the issue.

Sorry for the inconvenience.

Regards,
Sarathi

[Konrad Delong]

Whoops, seems like I answered to wrong thread...

sorry

[Konrad Delong]

OK, sorry once againg for mess on forum threads.

Format string may contain up to 4 sections defining formatting for positive, negative, zero and text data. What's more: they can describe date, time and fraction formatting. It's a legacy excel feature of course, but unfortunately many excel documents take advantage of this, which means that we cannot simply take the length of format string and subtract 2.

Some examples of valid format strings:

General <- yes, this one too
0.000
0.000;[Red]-0.00
0.000;[Red]-0.00;[Blue]"ZERO!"
0.000;[Red]-0.00;[Blue]"ZERO!";"text:"_@

Hope the attached example will be of some help.

DecimalRead.zip