Build 5.0.0.10 of xlsgen makes a change in behavior when a worksheet is deleted.

Before this build, when a worksheet was deleted, its content would be internally erased, but the worksheet itself would still appear as part of the worksheet collection of the current workbook. And only when closing the workbook would the worksheet be actually not written back in the file, hence the worksheet deletion.

Beginning with this build, a worksheet deleted by a statement such as worksheet.Delete() immediately disappear from the worksheet collection, making it more natural to program worksheets.

Build 5.0.0.9 of xlsgen adds up on the previous build and improves both speed and memory performance of xlsgen :

- faster reading of files with many shapes

- better scaling of memory when processing many hyperlinks and shapes

Build 5.0.0.8 of xlsgen is a performance fix pack, and also contains a fix for cell metadata :

- better handling of XLSX files with many internal relationships (hyperlinks) to avoid GPF case.

- removed most middle layer API calls to traverse relationships, avoiding bottleneck in files with many internal relationships

- refactored code in tables and pivot tables that were indirect bottlenecks as well


Also a fix for cell metadata to better return hit test for cells containing hyperlinks.

Build 5.0.0.7 of xlsgen is a fix pack for pivot table rendering :

- introduction of Outline and Compact rendering algorithms

- better reading of existing pivot table layout setting from XLSX fles

- cell indent is now 3 space chars instead of 2 space chars, used notably in Compact rendering algorithm


Pivot table rendering has now 3 rendering layout algorithms depending on your choosing : Tabular, Outline and Compact.

Differences is that Outline and Compact don't have subtotals and grandtotals.

Compact has all row fields in a single column (hierarchically), as opposed to one column per row field.

Build 5.0.0.6 of xlsgen is a fix pack.

- Systematic crash during pivot table generation in XLSX files. This is now fixed.

- Unable to read item groupings from existing pivot tables in XLSX files due to creative usage of XML standard in Excel. This is now fixed too.

- Crash when reading complex pivot tables from XLSB files. Fixed.

Build 5.0.0.5 of xlsgen ensures that color details are preserved as is during read/write and file conversion (such as XLSX to XLSB for instance). This preserves palette indexes, color themes, rgbs, ... along with color effects such as tint, alpha, shade, luminosity modulation. This is for colors in cells and in shapes.

Build 5.0.0.4 of xlsgen adds support for rendering row and column headings in scenarios such as Print, export to PDF, XPS, ...


row and column headings in xlsgen

The row and col headings in Excel is in the print settings dialog, sheet tab. And is on a per worksheet basis.

Build 5.0.0.3 of xlsgen fixes a problem related to rendering charts with horizontal bars that happen to also be stacked and stretched. Rendering was incorrect and some of the bars could be clipped entirely.

Build 5.0.0.2 of xlsgen adds support for preserving custom sheet views. This was already supported in XLS files, but is now for XLSX and XLSB files.

Custom sheet views are views that have their own zoom level, hidden rows, frozen panes and so on. Custom sheet views can be created in Excel by going to the View menu and clicking on New Window to create a new custom sheet view. All other views are open simultaneously. And to switch which view goes in the foreground, click on Switch windows.

Build 5.0.0.1 of xlsgen introduces read and write support for the strict version of XLSX files known as ISO 29500.

This variant is used in corporations out there for compliance purposes. The strict version of XLSX poses less security risk because it does not carry VBA macros. Internally, the XML is also more standard. And there is no pseudo-XML such as VML either.

How to you create such file ? Just like a regular XLSX file, except a property must be toggled before the file is created :

IXlsWorkbook wbk = engine.New("file.xlsx")
wbk.StrictISO29500 = true

IXlsWorksheet wksht = wbk.AddWorksheet("Sheet1)
...

wbk.Close()

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.