Build 4.0.0.72 of xlsgen is a fix pack including the following :
- custom theme correctly preserved in XLSB files
- sparklines correctly preserved in XLSB files
- font color in header/footer of XLSX /XLSB files : read/write and rendering

Build 4.0.0.71 of xlsgen adds form controls to our automatic source code generator which creates the source code for any Excel file in the programming language of your choice.

This addition to the source code generator is particularly useful because positioning form controls in the sheet may prove cumbersome and thanks to what we are making available, someone can now create a dialog-based user interface in Excel by hand, then pass the resulting file in the automatic source code generator in order to get the corresponding source code, and most notably the positions of each form control, complete with properties.

Build 4.0.0.70 of xlsgen makes it possible to compute the Gini() coefficient of a vector of values. The Gini() coefficient (Wikipedia) measures the dispersion of values, as a ratio between 0 and 1. For instance, if all values are the same, the Gini() coefficient is 0. The is a measure used in a number of markets.

The Gini() function is not an Excel function, which means it isn't part of any existing Excel version. It is an addition that only computes the value in the context of xlsgen.

Build 4.0.0.69 of xlsgen adds a VBA macro association property for a number of existing objects including :
- pictures
- text boxes
- vector shapes
- charts
- and also pictures, text boxes and vector shapes inside charts

This VBA macro association lets a user click on the object and trigger a macro. This was introduced in form controls earlier this year and has now been extended to all other objects in Excel.

Lets say you have an existing VBA macro called process in the spreadsheet in a VBA module. There is a property in the text box interface, called VBAMacro which you can invoke with mytextbox.VBAMacro = "process". And that's all it takes.

Build 4.0.0.68 of xlsgen includes several fixes :

- when processing XLSX/XLSM/XLSB files with VML streams (comments, ole objects, form controls, ...), there could be HTML fragments right within the XML stream which can cause havoc to parsers. This fix correctly handles <br> tags and so on.

- table copy maps internal formulas correctly. Internal formulas are formulas in tables referring to one or more of its columns by name (also called structured reference in Excel speak language).

Build 4.0.0.67 of xlsgen makes it possible to add Form controls to spreadsheets.


Form controls in xlsgen

Any of 11 form control types can be added :

• Buttons
• Check boxes
• Radio buttons
• Labels
• Edit boxes
• Spin controls
• Scroll bars
• List boxes
• Combo boxes
• Group boxes
• Dialog boxes

They can be added to XLS as well as XLSX/XLSM and XLSB files, so you do not have to worry about the underlying file format.

Each form control has a number of custom properties to it and xlsgen exposes them. One of the most powerful ones is the ability to attach to it a VBA macro by its name.

Here is how to do add a button control and a list box (C++) :

// create a button and attach it an existing VBA macro

xlsgen::IXlsWorksheetPtr worksheet = workbook->AddWorksheet( L"sheet1" );

xlsgen::IXlsFormButtonPtr button = worksheet->FormControls->Buttons->Add(L"my button",2/*row1*/,2/*col1*/,5/*row2*/,6/*col2*/,0,0,0,0);

button->VBAMacro = L"=myButton_Click";


xlsgen::IXlsWorksheetPtr worksheet2 = workbook->AddWorksheet( L"sheet2" );

worksheet2->Label[2][7] = L"item1";
worksheet2->Label[3][7] = L"item2";
worksheet2->Label[4][7] = L"item3";
worksheet2->Label[5][7] = L"item4";

// create a list box and preselect two items

xlsgen::IXlsFormListBoxPtr listbox = worksheet2->FormControls->ListBoxes->Add(2,2,9,6,0,0,0,0);
listbox->SelectionType = xlsgen::listboxtype_multipleselection;
listbox->InputRange = L"=$G$2:$G$5";
listbox->SelectItem(3);
listbox->SelectItem(1);
listbox->LinkedCell = L"=$G$10";  

Build 4.0.0.66 of xlsgen adds support for Web functions, namely =ENCODEURL(string) and =WEBSERVICE(string). Support is read, write and calculation.

WEBSERVICE() takes a regular internet url as parameter and fetchs content off the internet. Internally the fetched content is converted to Unicode-2 so xlsgen can expose the result as a string and let a client application parse it. The fetched content can be virtually anything, a web page, an XML stream, a RSS feed. The WEBSERVICE() function has been introduced in Excel 2013.

ENCODEURL() takes a string and returns a string with some of its characters replaced with ones compatible with regular internet urls. Anything which is not a number or a letter is replaced a hexadecimal encoded value prefixed with a percent sign, as in %35. ENCODEURL() is often used to encode an url before it is passed to the WEBSERVICE() function. The ENCODEURL() function has been introduced in Excel 2013.

Build 4.0.0.65 of xlsgen fixes the localization of all Excel functions supported by xlsgen, which means 500+ functions, in 6 languages including English, French, German, Spanish, Italian and Portuguese.

The formulas page in the documentation reflects how any function is called depending on the language of your choice (English by default).

As an example, a function such as AVERAGEIFS() in English was still called AVERAGEIFS() in German even though according to Microsoft Excel, it's MITTELWERTWENNS().

This did not affect how xlsgen stored formulas in Excel files, particularly XLSX/XLSM/... where formulas are stored in plain text, because formulas are supposed to be stored in English.

But for every other scenario, improper localization would add friction to someone used to Excel functions in their language. xlsgen fixes this with this build, at least for 6 languages.

Reminder : in the automatic source code generation tool which ships with xlsgen, you can choose which language to use for formulas.

Build 4.0.0.64 of xlsgen advances the previous build by adding even more capability to string matching in xlsgen.

What if we could match strings differing not only on spaces and punctuations but on some sort of letter combinations such as substitution, insertion and deletion? Let's see an example of that :

Sample data		Excel match		xlsgen Fuzzy punct		xlsgen Fuzzy letters

In this example, we start with a number of variations of string "abcdef" in which we have a number of spaces and punctuations and also swapped letters, missing letters and additional letters.

If in Excel we create a conditional formatting meant to highlight strings matching "abcdef", only the two first rows get highlighted in Excel. And for a reason, they differ only on the case. Add any space, punctuation let alone letters and Excel sees all of these as different strings.

If you are using xlsgen and the punctations mode for fuzzy string matching, then xlsgen highlights many rows but far from all rows. Specifically, xlsgen does not highlight rows where there is at least one letter combination such as swapping, insertion or deletion.

And if you are using xlsgen with the letters mode for fuzzy string matching, then xlsgen all of a sudden highlights almost all rows. In fact, the only which doesn't is because a letter appears three times which is regarded as too different.

Here is how you do this in xlsgen,

workbook.FuzzyStringMatch = stringmatch_letters;

xlsgen::IXlsConditionalFormattingPtr cf001s0 = worksheet->NewRange(L"R3C3:R16C3")->NewConditionalFormatting();
cf001s0->CellCondition->EqualTo(L"\"abcdef\"");
xlsgen::IXlsStylePtr style001s0 = worksheet->NewStyle();
style001s0->Pattern->Pattern = xlsgen::pattern_solid;
style001s0->Pattern->BackgroundColor = 0xFFFF00;
cf001s0->Style = style001s0;

What is this good for in practice? It turns out this is really useful to have such a versatile fuzzy string matching because letter substitutions are exactly what happens with manually entered data in the real world. Indeed, often letters are simply swapped, or they are missing letters or too many letters. Combined with supporting spaces, punctuations and case, this looks like the perfect tool for getting your job done as fast and accurately as possible.

This is indeed an additional tool in your arsenal and we are looking forward to seeing customers out there taking advantage of it for improving how they handle real world data, which is often full of errors.

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.

Build 4.0.0.62 of xlsgen updates the autofit column width computation by taking into account the width of arrows of auto-filters, wherever applicable.

Build 4.0.0.61 adds support for a number of math trigonometry functions in the calculation engine. The functions were already supported for read and write purposes. The list goes as follows :

ACOSH	Returns the inverse hyperbolic cosine of a number
ACOT	Returns the arccotangent of a number
ACOTH	Returns the hyperbolic arccotangent of a number
ASINH	Returns the inverse hyperbolic sine of a number
ATAN2	Returns the arctangent from x- and y-coordinates
ATANH	Returns the inverse hyperbolic tangent of a number
COSH	Returns the hyperbolic cosine of a number
COT	Returns the cotangent of an angle
COTH	Returns the hyperbolic cotangent of a number
SINH	Returns the hyperbolic sine of a number
TANH	Returns the hyperbolic tangent of a number
PI	Returns the value of PI

Build 4.0.0.60 of xlsgen adds support for wildcards (*, ? and ~) in the following database functions in the calculation engine : DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR and DVARP.

In other words, if you are using for instance a database function such as DSUM(A6:C10;"Sales";B1:B3), the criterias referenced in B1:B3 may use strings of the form Daliv*o which in turn will match strings in A6:C10 such as Dalivpo or Dalivio.

Build 4.0.0.59 of xlsgen fixes a problem related to the calculation of the SUBTOTAL() function when its first argument, a function identifier, is one of those special functions targeted for ignoring hidden values. Indeed, the function identifiers used in subtotals can be : average, count, counta, max, min, product, stdev, stdevp, sum, var and varp with an identifier within [1,11]. And then the special mode, for ignoring hidden values, whose identifiers are within [101,111].

Build 4.0.0.58 of xlsgen adds new capability which is to delete comments or hyperlinks on a mass basis. This capability is exposed both at the worksheet level (so all comments or all hyperlinks in that sheet tab get deleted) and at the range level (only comments or hyperlinks inside the range get deleted).

For an entire worksheet,

workbook->WorksheetByIndex[1]->DeleteHyperlinks();

For a range,

workbook->WorksheetByIndex[1]->NewRange(L"A1:G14")->DeleteComments();

Build 4.0.0.57 of xlsgen fixes how databars are preserved, in particular how one of its property (ShowBarOnly) is preserved in XLSX files. The problem did not occur with XLSB files.

Build 4.0.0.56 of xlsgen adds read/write and calculation support for the hidden function DATEDIF(). Support is added for integrity purposes.

The official Microsoft documentation is here.

Syntax : DATEDIF(start_date ; end_date ; mode)

Calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3.

modes :

• "y" : calculates the number of complete years
  
• "m" : calculates the number of complete months
  
• "d" : calculates the number of days
  
• "md" : calculates the number of days, ignoring months and years
  
• "ym" : calculates the number of months
  
• "yd" : calculates the number of days, ignoring months years
  

Build 4.0.0.55 of xlsgen adds /S and /SILENT parameters to the xlsgen.exe command line for registering COM components silently. This is useful, if you are using xlsgen.exe, which is our process isolated version of the engine, for registering it against COM either from a command-line window or from an installer.

So to register xlsgen.exe silently before using it, that would be :

<path>\xlsgen.exe /S /REGSERVER

And to unregister xlsgen.exe,

<path>\xlsgen.exe /S /UNREGSERVER

Build 4.0.0.54 of xlsgen fixes a problem in the calculation engine related to the MATCH() function. The second parameter, which is the range to look for a match, can be a non-area reference. Something that xlsgen did not expect and is now fixed.

Build 4.0.0.53 of xlsgen fixes a problem related to creating group outlines in XLSX and XLSB files. The problem did not reproduce with XLS files.

Group outlines is this mechanism for grouping and then expanding or collapsing a continuous group of rows, or group of columns. Setting the group as collapsed did not work in scenarios where XLSX or XLSB files were being created. This is now fixed.

Sample code (C++) :

xlsgen::IXlsRangePtr range1 = wksht->NewRange(L"R8C3:R10C3");
xlsgen::IXlsOutlinePtr outline = range1->NewOutline();
outline->VerticalOutlineLevel = xlsgen::outline_level1;
outline->VerticalOutlineUpsideDown = TRUE;
outline->VerticalOutlineExpand = FALSE;