Build 4.0.0.42 of xlsgen adds read, write and calc support for two new functions : CONCAT() and TEXTJOIN().
Those functions are part of updates that are not made available to regular Excel 2016 purchasers. Microsoft has made them available for those purchasing Excel 2016 on a subscription basis (called Office 365), or using Excel online (desktop or mobile).
We thought there was a justification then for bringing those functions to ALL Excel users, including previous versions of Excel. This is made possible in xlsgen by natively supporting the calculation of those functions.
- CONCAT() is a variant of the existing CONCATENATE() function. What it supports is, in addition to literals such as numbers, strings and cell references, it supports cell range areas, and also the maximum buffer is 32KB not 8KB. The official documentation for the CONCAT() function is
here.
- TEXTJOIN() is a variant of CONCAT() and makes it possible to define a delimiter between concatenated strings as well as specify how empty cells should be handled. Again, the full documentation is
here.
worksheet.Formula[5][2] = L"=CONCAT(B1:B4)";
worksheet.CalculateFormula(5,2);
==> bb1bb2bb3bb4
worksheet.Formula[5][2] = L"=TEXTJOIN(\":\";TRUE;B1:B4;C1:C4)";
worksheet.CalculateFormula(5,2);
==> bb1:bb2:bb3:bb4:cc1:cc2:cc3:cc4