I'm importing data from an excel file, and need to know whether a cell is formatted as a percentage (so I know whether to multiply the value in the cell by 100 before I pass it on to my import routine)
I'm using GetFormatString(XF) to retrieve the format, and checking for a % symbol in the resulting string, but I was just wondering if there are any cases where there might be a % symbol in the format string which would cause excel to not treat the value as a percent.
Hi,
While checking for a % will work in most cases, if you want to be precise there are sadly many border cases. Out of the top of my head:
1)The number of % matters.
01. formatted with the string 0.0%% will display as
1000.0%%
So you need to count how many %, but with the caveats in 2)
2)A format string can have up to 4 sections, divided by semicolons, like this:
<format for positive numbers>;<format for negative numbers>;<format for 0>;<format for text>
So a format string like:
0.0%;(0.0)%;-
will actually have a single % not 2 since those are defined in different sections. In border cases you might have a % in one section and not in the other, and the section might not even apply to negative/positive numbers. For example:
[>0.45][blue]0.0%;[red]0.000
will display numbers more than 45% in red with a percent (like 46%) and numbers less or equal to 45% in blue as not percents (0.440)
3)The character "" in the format string means "repeat the next character until it fills the cell". So a format like:
0.00%
will display 0.1 as
0.1%%%%
and not 10%
4) "" means "a space the width of the next character"
So 0.0%
will display 0.1 as
0.1<space with the width of a % character> and not as 10%.
5)The charcter might be inside a quote. For example:
0.0"%"
will display 0.1 as
0.1% not as 10%
6)The character might be escaped:
0.0%
will display 0.1 as 0.1%
There might be more cases, those are the ones I can think out of the top of my head. So while in reality checking for a % will work in virtually all cases, to get strict there is a lot of extra stuff to consider, and you can't use an IndexOf or even a regex to get the right result: you have to parse the string because the string has state and the meaning of % depends on the context.
The good news is that we do care a lot about these kind of details so we already have the parser that takes in account all the border cases I am aware of. We use it when rendering or when you call GetCellFromString, so we show the same as Excel. For example, here is FlexCel's custom preview demo and Excel showing cases 1 to 6 above in rows 1 to 6:
The bad news is that we don't expose the "number of percents to apply" in a public method. We do expose if the format has a date or a number (which is similar to the percent case: you could check for M, D or Y in the format string, but again with lots of border cases).
I think exposing the number of percents wouldn't be complex, so I've added it to our todo list. Sadly this week is already full so I won't be able to do it, but for the next week we should have a version that has this functionality exposed.
Please email me on monday to adrian@tmssoftware.com and I'll send you an updated version with this functionality.
Adrian Gallero2017-10-03 23:04:11
Good morning Adrian, thanks for the very detailed reply. This isn't an urgent need on our part, but I will indeed email you next week for the update.
Kind Regards, Steve.
Hi,
The just released 6.17.3 adds a new method:
http://www.tmssoftware.biz/flexcel/doc/vcl/api/FlexCel.Core/TFlxNumberFormat/PercentCount.html
Which
will give you the number of not escaped % in a format string. Each
Percent multiplies the number by 100, so for example if you have 2 %,
you need to multiply by 10000.
You can call it like this:
numPercent := TFlxNumberFormat.PercentCount(value, formatstring);
Thanks, Adrian. Will download it now...