Build 4.0.0.63 of xlsgen introduces the capability to match strings in a manner more useful than what is usually done.

Fuzzy string matching allows to compare content in cells and ignore not only the case, but also spaces as well as punctuation signs. The reason behind this is that real data may differ just by a few characters such as spaces or punctuations and it's sub-optimal that Excel regards such strings as different whereas they are really the same.

With fuzzy string matching, "AB" = "A B" = "A,B" = "a-b" and so on.

This mechanism for comparing strings is through out xlsgen, whether in cell formulas, or elsewhere such as conditional formatting formulas, autofilter expressions, sort and so on.

By default, string matching has the strict behavior, which means strings match if everything is the same except the case, so "AB" = "ab", but "AB" != "A B".

By activating the string matching algorithm with the new enumeration that is being introduced,

typedef enum
{
  [helpstring("String match, strict")]                                          stringmatch_strict       = 0,
  [helpstring("String match, ignore punctuation differences")]      stringmatch_punctuations = 1
} enumStringMatch;

workbook.FuzzyStringMatch = stringmatch_punctuations;

"AB" = "ab" and "AB" = "A B".

So once the FuzzyStringMatch property is set as above, calculations whether direct or indirect involving comparison operators such as =, <=, >=, <, > and != behave accordingly. Also when you are using functions doing string matching internally such as MATCH() and LOOKUP().

Fuzzy string matching example for spaces and punctuations

Considering the following variants of string "AB" :


Sample data 

Those variants differ in the addition of one or more space characters, case as well as punctuation characters as well as character substitutions which are arguably not variants of "AB".

If you are in Excel, select this range and create a conditional formatting on it where the condition to turn its background as yellow whenever the following condition applies : A1="AB", then only the first row gets highlighted because Excel does a strict match.


Strict string matching in conditional formattings : only one row is highlighted

If xlsgen is used to create such conditional formatting, that would yield :

xlsgen::IXlsConditionalFormattingPtr cf001s0 = worksheet->NewRange(L"R3C3:R18C3")->NewConditionalFormatting();
cf001s0->FormulaCondition->Formula = L"A1=\"AB\"";
xlsgen::IXlsStylePtr style001s0 = worksheet->NewStyle();
style001s0->Pattern->Pattern = xlsgen::pattern_solid;
style001s0->Pattern->BackgroundColor = 0xFFFF00;
cf001s0->Style = style001s0;

And the result would be the same than in Excel, for instance if you export the sheet as a PDF file to get the conditional formattings evaluated and rendered.

Now if all those variants are just regarded the same as "AB" itself, there is no reason to lose them at all. And if you are using Excel, you would have to write a custom VBA macro function whose calculation would implement fuzzy string matching. xlsgen does it for you. All you have to do is add the following line of code before the conditional formattings get evaluated :

workbook.FuzzyStringMatch = stringmatch_punctuations;

And then, something magic occurs :


Fuzzy string matching in conditional formattings : all variants are highlighted

All variants of "AB" get highlighted, but "AC" and other combinations aren't for not being variants after all : character "C" isn't character "B".

Since Excel does not support fuzzy string matching, the resulting file, next time it's opened in Excel, will not highlight those rows. It's therefore important to understand this context of calculations exists when xlsgen is used for calculations and rendering, not Excel.

Apple's media-sponsored and fabricated reputation got a little tainted today when they (Apple) issue a warning to stop a software update for the Apple watch from being downloaded and installed after reports began informing Apple watch users that there was a likelihood of rotting the Apple watch.

I think it's fair to say that any such product should never need any software update whatsoever and that any such software update is equivalent to a breach of contract with the person who purchased the product.

A software update should, at best, for those ok to pay the risk of doing so, be actively turn on by the user himself in order for the product to be updated and risk arbitrary malfunctions.

Microsoft, Apple and many others should be in jail for this practice. Being giant market players, they have made as if any one who purchased the product must also pay for the risk of arbitrary malfunction resulting from changing its internal software. This is the equivalent of getting knocked in the face by a complete stranger in the street and then be sued by this stranger.