xlsgen 5.0 has shipped.

The first and most impacting feature is its licensing model, where we are introducing upgrades on a per year basis, and at a competitive price. In other words, we are done with our 2-year major version licensing model.

Of course, xlsgen is still royalty-free.



Long life xlsgen 5.0 !!!!!

Build 4.9.0.34 of xlsgen adds feature improvement to pivot table row grouping.

Before this build, only data interval was made available from the GroupBy interface.

Now MinimalValue and MaximumValue can be manually set.

And here is an example of this (C++) :

xlsgen::IXlsPivotTablePtr pt = worksheet->NewPivotTable();
pt->DataSource->Range = L"Sheet1!D4:F14";

xlsgen::IXlsPivotTableFieldPtr pf1 = pt->Rows->AddByName(L"s2");
pf1->AggregateFunction = xlsgen::aggrpivotfunction_none;
pf1->SortAscending = TRUE;

xlsgen::IXlsPivotTableFieldGroupByPtr pf1g = pf1->GroupBy;
pf1g->Interval = 2;
pf1g->MaximumValue = 40;

xlsgen::IXlsPivotTableFieldPtr pf_d1 = pt->Data->AddByName(L"s3");

pt->Options->Layout = xlsgen::pivottablelayout_tabular;
pt->Options->BuiltInPivotTableStyle = xlsgen::pivottablestyle_medium14;
pt->Options->ShowRowStripes = TRUE;

pt->InsertAt(1,1);

Build 4.9.0.33 of xlsgen fixes a problem related to colliding names when creating a pivot table.

Thre data source of a pivot table is an arbitrary cell range therefore its columns can have same names and collide. To solve this for Excel, and to avoid an error message, xlsgen disambiguates pivot field names.

Build 4.9.0.32 of xlsgen adds an XLS conversion pack. It is made of the following :

- XLSX to XLS file conversion (also XLSM to XLS)

- XLSB to XLS file conversion

- XLSX to XLSB : VBA macros preserving

- XLSB to XLSX : VBA macros preserving


The conversion from XLSX or XLSB files back to XLS files is the bigger piece of the pie. In 2022, it sounds a little odd to hear that we are still providing tools to leverage XLS files, but for legacy systems out there, it may be the only option available. So there it is. Of course, converting to a XLS file comes with a baggage : the sheet rows and columns are much smaller (64K rows per sheet instead of one million, ...), a number of objects are not supported at all (such as the data model), and a number of objects are partially supported (such as conditional formattings where databars simply do not exist). Those wanting to leverage XLS files when it's known to be such a subset of XLSX/XLSB better know what they are doing.


Here is how XLSX ==> XLS conversion works :

workbook = engine.Open("inputfile.xlsx", "outputfile.xls");
workbook.Close();

Open() in-memory variants also work (OpenInMemory, OpenFromMemory).

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"