Build 4.9.0.31 of xlsgen fixes a problem related to updating pivot table's data source worksheet names, when such renaming event occurs.

Build 4.9.0.30 of xlsgen has support for adding pictures in sheets using the WebP file format standard.

WebP has recently become mainstream enough so Microsoft added support for this file format in Excel in 2021 in subscription-based Excel 365, and also the licensed version Excel 2021.

WebP is both lossy and lossless, depending on the client application generating it, and can have additional features which actually make it better than anything else out there from a feature standpoint and from a file size standpoint. In short, it is better than JPEG, PNG, GIF combined.

worksheet.NewPicture(".\\pic.webp", 1, 1, 36, 16, 0, 0, 0, 0);

Build 4.9.0.29 of xlsgen adds support for reading and displaying electrocardiograms.




Electrocardiograms (data)

 

Electrocardiograms (charts)


xlsgen can import electrocardiograms for data and visual analysis purposes. This is expected to streamline data transfer in health related corporations.

Electrocardiograms are the result of placing electrodes on someone's body (simple electrodes, holter, ...), measuring the tension in electrodes over a period of time, then analysing the peaks and lows to identify a potential pathology.

The standard electrode names (called leads) are imported as well : lead I, lead II, lead aVR, ...

The health practitioner saves electrocardiograms in binary files, whose file format are standardized (ISO). xlsgen can read SCP-ECG files, which are files with a .SCP suffix.

From a programming standpoint, importing an electrocardiogram in xlsgen is really simple :

C++ code

xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) ); xlsgen::IXlsWorkbookPtr wbk = engine->New( L"electro_cardiogram.xlsx"); xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet(L"Data"); wksht->Import->ElectroCardiogram->ImportFile(L"sample.scp"); wbk->Close();

Doing so imports the table of electrode data over time in the current worksheet, and creates another worksheet for the associated charts.

Build 4.9.0.28 of xlsgen adds support for AutoIt, a scripting language that is very much like VBScript, supports COM/Automation.

AutoIt code

Local $engine = ObjCreate("ExcelGenerator.ARsTdesign") $workbook = $engine.New("sample.xlsx") $worksheet = $workbook.AddWorksheet("Sheet1") $worksheet.Label(2, 3) = "Hello world!" $workbook.Close $engine = 0 Exit

Build 4.9.0.27 of xlsgen adds read and write support for 14 new functions, introduced in the subscription-based Excel version earlier in May 2022 by Microsoft.

TEXTBEFORE	Returns text that’s before delimiting characters
TEXTAFTER	Returns text that’s after delimiting characters
TEXTSPLIT	Splits text into rows or columns using delimiters
VSTACK	Stacks arrays vertically
HSTACK	Stacks arrays horizontally
TOROW	Returns the array as one row
TOCOL	Returns the array as one column
WRAPROWS	Wraps a row array into a 2D array
WRAPCOLS	Wraps a column array into a 2D array
TAKE	Returns rows or columns from array start or end
DROP	Drops rows or columns from array start or end
CHOOSEROWS	Returns the specified rows from an array
CHOOSECOLS	Returns the specified columns from an array
EXPAND	Expands an array to the specified dimensions

Build 4.9.0.26 of xlsgen fixes a problem related to committing changes to a table object under certain circumstances.

Build 4.9.0.25 of xlsgen introduces support for dynamic arrays.

Dynamic arrays is a formula mode meant to replace the array formula mode. Microsoft calls the old array formula mode, the one with {...} curly braces (obtained by hitting Ctrl+Shift+Enter on the keyboard), legacy array formulas. Legacy array formulas are still available for compatibility reasons in Excel but dynamic arrays are now the preferred mode.

Dynamic arrays are much easier to use in practice because there is no surrounding or escape symbol. Dynamic arrays are automatically created whenever the calculation engine encounters a formula result holding more than one value, i.e. a vector of values or a matrix of values. When this occurs the resulting values are spilled in other cells near the current cell. In Excel the formula bar shows the formula greyed out in all cells but the first one, making it possible to understand where the values in cells come from.

Dynamic arrays were introduced in fall 2018 in the subscription-based Excel version. And got ported to a licensed version, namely Excel 2021. xlsgen adds it too.

Example : =SORT(A2:A10), this SORT() formula returns the a range of same size than the input range, and as such is a perfect example of dynamic array formula. Dynamic array formula may appear just about everywhere where a formula result in a cell is more than one literal value.

Dynamic array formulas are not compatible with Excel version earlier than Excel 2021 so in those previous versions they appear as legacy array formulas but with a potential breaking behavior because calculating a dynamic array formula and a legacy array formula isn't the same : the first is cell-based, the other is range-based. In either case, Microsoft chose not to support dynamic array formulas in XLS files. In other words this is for XLSX, XLSB and their variants (XLSM, XLTM, ...)

Because dynamic array formulas may not be suitable in some scenarios, xlsgen provides a property (at the workbook level called DynamicArray) which can disable it altogether. Dynamic arrays are on by default because they provide an inherently better mechanism for working with formulas than legacy array formulas.

Build 4.9.0.24 of xlsgen is a fix pack including the following :

- preservation of cell/value metadata in XLSX and XLSB files

- better writing of array formulas in XLSB files. Under certain circumstances, the writing is incorrect and it corrupts the file.

- preservation of font single/double accounting underline option in XLSB files.

Build 4.9.0.23 of xlsgen has a fix for a number of calculated functions (SORT, SORTBY, UNIQUE, FILTER) which is needed to address dynamic array support.

Build 4.9.0.22 of xlsgen adds support for rendering pie charts with 3D volume.

Until now, the 3D volume type for pie charts was ignored and any such chart would be rendered as 2D. This "limitation" is over. This is for all outputs (PDF, SVG, ...)



Rendering 3D pie charts in xlsgen

Build 4.9.0.21 of xlsgen is a fix pack related to importing data from CSV, JSON, HTML and XML files.

• the behavior of the automatic type inference is improved. It tries to find patterns when numbers are associated to a string inside a cell, but it does not do so anymore at the expense of growing too much the dictionary of number formats and styles. In other words, it does well, wherever applicable. This is a welcome improvement since this automatic type inference was introduced more than a year ago (in xlsgen 4.5, actually).
  
• autofitting columns is now performed automatically right after the import phase, so the imported data looks cleaner in Excel.
  
• autofitting columns was already performed silently for JSON, HTML and XML data streams. It was not performed for CSV data at all.
  
• an auto-fit property is introduced in all 4 data import options (i.e. for CSV, JSON, HTML and XML). This property lets an application control whether or not auto-fit is wanted. For instance, in case auto-fit is not wanted, just pass FALSE to the boolean :
  
  worksheet.Import.CSV.Options.AutoFit = FALSE
  

Build 4.9.0.20 of xlsgen adds a couple code samples in C# and VB.NET for .NET 6.0, and actually Visual Studio 2022 projects (recently released by Microsoft).

Regarding .NET 6.0 itself, xlsgen works flawlessly without modification. Of course, it assumes your project is targeting Windows, not Linux or Android or even Mac OS.

Code samples use either the regular COM instantiation or the COM-free technique. As a reminder, make sure to always link your project with the correct version of xlsgen.dll, that is, 32-bit if you are targeting a x86 build, 64-bit if you are targeting a x64 or Any CPU build.

Build 4.9.0.19 of xlsgen adds direct support for data connections such as SQL to pivot tables in XLSX, XLSB and XLS files.

Until now, a pivot table created in xlsgen could be attached only to a worksheet-type data source. Making it possible to attach a pivot table to a SQL data connection without any clutter (such as a temporary table object) makes it both useful and clean for applications.

Here is how it works (below is C++ code) :

xlsgen::IXlsWorkbookPtr workbook = engine->New( L"pivotTableSQL.xlsx" );

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

xlsgen::IXlsPivotTablePtr pt = worksheet->NewPivotTable();

// create the SQL data connection (Access MDB database)
pt->DataSource->DataConnection->CommandTimeout = 40;
pt->DataSource->DataConnection->ConnectionTimeout = 120;
pt->DataSource->DataConnection->ConnectionString = L"Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\\input\\db1.mdb;uid=;pwd=;";

pt->DataSource->DataConnection->SQLStatement = L"select * from Table1";

// fetch the data
pt->DataSource->DataConnection->Run();

// create the pivot table itself
pt->Rows->AddByName(L"firstname");
pt->Rows->AddByName(L"ID");

pt->InsertAt(11,2);

workbook->Close();

Build 4.9.0.18 of xlsgen fixes a problem related to handling pivot table descriptors in files where both regular and extended descriptors are present, avoiding a file corruption problem.

Build 4.9.0.17 of xlsgen is a fix pack for issues related to XLSB files.

- under certain circumstances, array formulas were not written correctly and would corrupt the Excel file (XLSB).

- incorrect external reference index when reading from a XLSB file and converting to a XLSX file

- generation of external reference cache when converting a XLSX file to a XLSB file

- internal formula building for a number of conditional formattings when converting a XLSB file to a XLSX file

Build 4.9.0.16 adds all other lambda functions introduced in Office 365 (reminder : xlsgen supports them, and they are not available in any installable Excel version other than the one associated to an Office 365 subscription).

BYROW()	Applies a Lambda function to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
BYCOL()	Applies a Lambda function to each column and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 3 columns by 1 row.
MAKEARRAY()	Returns a calculated array of a specified row and column size, by applying a Lambda function.
SCAN()	Scans an array by applying a Lambda function to each value and returns an array that has each intermediate value.
MAP()	Returns an array formed by mapping each value in the array(s) to a new value by applying a Lambda function to create a new value.
REDUCE()	Reduces an array to an accumulated value by applying a Lambda function to each value and returning the total value in the accumulator.
ISOMITTED()	Checks whether the value in a Lambda function is missing and returns TRUE or FALSE.

Those functions basically iterate over the cells in a particular way, either cell by cell, row by row, and so on, and what they do is apply the lambda function on each cell value. The result is either written back to a new cell, with most functions above, or accumulated, as in functions SCAN() and REDUCE().

Example : =BYROW(A1:C2; LAMBDA(x;MAX(x)) )

Example : =BYCOL(A1:C2; LAMBDA(x;MAX(x)) )

Example : =MAKEARRAY(2; 3; LAMBDA(r; c; r*c) )

Example : =SCAN(""; A1:C2; LAMBDA(t; u; t&u) ). t is an accumulator with an initial value of "".

Example : =MAP(A1:C2; LAMBDA(a; IF(a>4;a*a;+a) ))

Example : =REDUCE(0; A1:C2; LAMBDA(t; u; IF(u>4; t+u; 0)) ). t is an accumulator with an initial value of 0.

As for the ISOMITTED() function, it works like this :

C++ code

xlsgen::IXlsWorksheetPtr worksheet = workbook->WorksheetByIndex[1]; // create a lambda function xlsgen::IXlsDynamicRangePtr dynrange_xy = worksheet->NewDynamicRange(L"MyFuncXY"); dynrange_xy->Formula = L"=LAMBDA(x;y;IF(ISOMITTED(y);\"y param is omitted\";x&y))"; dynrange_xy->UserDefinedFunction = TRUE; dynrange_xy->Description = L"(x,y) function"; worksheet->Formula[10][6] = L"=MyFuncXY(4;2)"; _bstr_t s106 = wksht->Label[10][6]; // returns "42" worksheet->Formula[11][6] = L"=MyFuncXY(4)"; _bstr_t s116 = wksht->Label[11][6]; // returns "y param is omitted"

Build 4.9.0.15 of xlsgen introduces Lambda functions, a mechanism in Excel that is meant to reduce the complexity of spreadsheets. It was introduced in 2021 as part of Office 365 subscriptions, is not available in any downloadable Excel version, but xlsgen supports it.

Usually, cell with calculations end up with multiple functions nested at multiple levels, making it hard to understand and maintain over time. The fix for this is usually to either factorize a static piece of it into a defined name, or to externalize it to a VBA macro function, both of which have limitations. The limitation of static defined names is that parameters can't be passed to the defined name so a dynamic call is impossible. A limitation which isn't in VBA macros, except that VBA is a different thing than Excel functions, a different language and that it's been years that running VBA macros has become a security problem.

So this lays down the path for something else, and that's lambda functions, that's how it is named in Excel. Lambda functions can be seen as a mechanism for overcoming the limit of not being able to pass arguments to defined names, while remaining in the Excel function context.

Introducing lambda functions is actually straight forward. Let's assume we have a mathematical function y = x + 3. We could specify this function like this as well : x ==> x + 3, or rather function(x; x+3). Back to Excel context, if we create a defined name that we shall call y, and whose definition is =lambda(x;x+3), that's all we need then to make a call anywhere in the spreadsheet of the form y(1) and it would return 4.

When we create that defined name, we can use the description to explain further details about what the function accomplishes, what the parameters are, etc. which carries a meaning to anyone who will be tasked to understand and maintain the spreadsheet over time.

Anywhere in the spreadsheet where this x + 3 calculation is stored in formulas, it can be replaced by the y(x) function call and doing so this removes complexity to formulas across the entire spreadsheet.

Here is another example. Assuming we have data layed down like this :



In C12, we have a calculation whose only point is to obtain the price of a coffee type of a given size. Allegedly, all what we want for this is a handy function of the form CalcCoffeePriceForSize(coffee_type; coffee_size), but in Excel we usually end up with a string of functions like this :

=VLOOKUP(A12;Table1_data;MATCH(B12;Table1_headers;0);FALSE)

where A12 is the coffee type and B12 the coffee size. The MATCH function is there to find the appropriate coffee size column, and the VLOOKUP finds the coffee price for the coffee type given its coffee size.
A string of Excel functions like this isn't particularly self-describing, and it would get far worse if we added error handling, etc.
And that's where the lambda function mechanism comes handy. Let's create a new defined name, called CalcCoffeePriceForSize, then paste the formula above in there.
Then edit this definition and replace it by :

=VLOOKUP(coffee_type;Table1_data;MATCH(coffee_size;Table1_headers;0);FALSE)

Then edit again and prefix the above with the =LAMBDA function call along with parameters :

=LAMBDA(coffee_type;coffee_size;VLOOKUP(coffee_type;Table1_data;MATCH(coffee_size;Table1_headers;0);FALSE))

Don't forget to describe this function and parameters in the Comment area. And click OK. Of course if you are doing this in Excel, this won't work unless a subscription-based Excel product is being used. In other words, it does not work with Excel 2016, Excel 2019 or Excel 2021.
What is being done here is pass the list of all function parameters, and then we have the calculation itself.



Now back in cell C12 where the calculation occurs, replace the previous string of formulas with :

=CalcCoffeePriceForSize(A12;B12)

That's it. Cell C12 is a calculated formula and the intention is self-describing. If the user needs to know more about the actual calculation, it's available, but not shown unless needed. Complexity has been lowered without compromising the maintainability of the spreadsheet.
So that's a brief introduction to lambda functions.

in xlsgen, creating the lambda function is done like this :

C++ code

xlsgen::IXlsWorksheetPtr worksheet = workbook->WorksheetByIndex[1]; // create a lambda function xlsgen::IXlsDynamicRangePtr dynrange = worksheet->NewDynamicRange(L"CalcCoffeePriceForSize"); dynrange->Formula = L"=LAMBDA(coffee_type;coffee_size;VLOOKUP(coffee_type;Table1_data;MATCH(coffee_size;Table1_headers;0);FALSE))"; dynrange->UserDefinedFunction = TRUE; dynrange->Description = L"CalcCoffeePriceForSize computes the price of a coffee for a given coffee type and a given coffee size."; // use the lambda function worksheet->Formula[12][3] = L"=CalcCoffeePriceForSize(A12;B12)";

Build 4.9.0.14 of xlsgen improves the positioning and sizing of sheet headers and footers in rendering scenarios (print, PDF, ...). This has particular effect when the content of such blocks is large.

Build 4.9.0.13 of xlsgen is a fix pack :

- sheet header/footer : check that the length is no more than 255 characters (Excel limit).

- HTML import : fix for parser

- HTML import : import   cells differently, assumes no content

- HTML import : import XML-type markup in HTML streams

- HTML import : uninitialized pointer.

- HTML import : improved number mapping resolutions. For instance, a number candidate cannot be seen as an integer in Excel if it has more than 11 digits : imported as a string.

Build 4.9.0.12 of xlsgen adds to JSON data import, HTML data import and XML data import the same properties added to CSV data import in the previous build. Namely,

- AutoFormatMapping (boolean) : enable or disable the automatic pattern matching algorithm. It needs to be disabled in cases where the algorithm tries hard too much finding patterns and ends up creating more number formats than Excel actually accepts (200).

- FirstRow : as the name implies this allows to tell at which first row the CSV content should be inserted. Before this, row could be specified on a per column basis, but this property is for all columns in a single statement.

- FirstColumn : same than FirstRow, except for columns.


Sample :

// importing a sample JSON file

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

xlsgen::IXlsJSONImportPtr json = worksheet->Import->JSON;

json->Options->AutoFormatMapping = FALSE;
json->Options->FirstRow = worksheet->HorizontalPane->SplitLimit; // insert the data right after the split

json->ImportFile(L"input\\jsonfile3.json");