Build 4.5.0.53 of xlsgen introduces a special mode, the keep mode, for the isolated server (xlsgen.exe).

When used, the keep mode avoids that the working process kills itself after the running engine instance is out of scope (the scope is under the control of the client application). This mode also enforces strict apartment related to COM object method call marshalling.

How to activate this mode ?

Just register the isolated server as a COM object using a command-line script, adding the /K option to it, as in :

<path>\xlsgen.exe /K /REGSERVER

Put this in a .BAT file, then right-click on it and choose Run as Administrator.

Upon registration, a message box appears to confirm completion.

Build 4.5.0.52 of xlsgen improves the import of all 4 input file formats namely CSV, XML, HTML and JSON.

When custom data mapping was introduced in build 4.5.0.4, meaning that you can pass a number format for any given column of data being imported, we said that chances are that we would, in the long run, end up having some kind of AI or intelligent automatic inference that would figure it the data it is importing, and would therefore infer the number format itself. That's what build 4.5.0.52 brings to the table. Not a single line of code is needed.

Let's say in the data you import, there is a "length" column with a number of kilometers to it, ie :

12 kms
15 kms
50 kms
75 kms
...

Since build 4.5.0.4, if you want this data to be imported as numbers and retain this "kms" suffix, you would pass it like this to the import engine : options.ColumnDataMappingFormatByName[L"length"] = "0 \"kms\"";

With build 4.5.0.52, this line of code is not needed anymore. It is magically inferred. And it works the same for all CSV, XML, HTML and JSON files.

Build 4.5.0.51 of xlsgen brings a number of improvements to all of the 4 input file formats, i.e. CSV, XML, HTML and JSON.

The improvements are :

- improved usage of # and 0 when automatically inferring the type and number format of data that appear to be numbers (integer or float)

- improved currency detection when automatically inferring the type and number format of data that appear to be currencies, that is, numbers with an accompanying currency symbol

- improved disambiguation of date, time and date+time when automatically inferring the type and number format of data that appear to be date or time related

- better import of different data types inside the same column


Overall, when you are importing a CSV file, an XML file, an HTML file or a JSON file, the automatic import engine works better at finding the actual data types and tries a lot inferring an appropriate number format, resulting in data that are imported correctly and are therefore ready for calculations and charting. Of course, any custom option that may be set before the Import statement takes precedence over what is automatically inferred, letting a client application really control how data is imported.

Build 4.5.0.50 of xlsgen adds support for calculating table formulas, that is, formula expressions including one or more table structured references, such as :

- Table1[Sales] // Sales column
- Table1[[Sales]:[Income]] // Column range : Sales, ..., Income
- Table1[[#Headers];[Sales]] // Header rows from the Sales column

Table formulas can appear anywhere : in cells, in objects (conditional formattings, ...), in defined names, ...

Build 4.5.0.49 of xlsgen takes into account the "hide zero cells" option which is supposed to show empty cells every time a cell stores a 0, entered as a zero or the result of a function. This impacts print, preview and PDF output.

Build 4.5.0.48 of xlsgen adds calculation support for Excel 365 functions related to the recently announced (by Microsoft) dynamic array functions, namely :

• =RANDARRAY([rows],[columns]) : returns an array of random numbers between 0 and 1.
• =SEQUENCE(rows,[columns],[start],[step]) : generates a list of sequential numbers in an array, such as 1, 2, 3, 4.
• =FILTER(array,include,[if_empty]) : filters a range of data based on criteria you define.
• =UNIQUE(array,[by_col],[occurs_once]) : returns a list of unique values in a list or range.
• =SORT(array,[sort_index],[sort_order],[by_col]) : sorts the contents of a range or array.
• =SORTBY(array, by_array1, [sort_order1], [[by_array2], [sort_order2]],…) : sorts the contents of a range or array based on the values in a corresponding range or array.
• =SINGLE(value) : returns a single value using logic known as implicit intersection.

This build follows build 4.5.0.44 in which read and write support for those functions was added.

Build 4.5.0.47 of xlsgen fixes a design problem in the calculation engine, namely the recursive calls to function calculations by reverse finding formula dependencies, reverse calculating them and putting them in the cache in order to avoid any additional recursion.

Build 4.5.0.46 of xlsgen fixes a problem related to parsing minuses and substracts with extended functions.

Build 4.5.0.45 of xlsgen adds read, write and calculation support for spill ranges.


Spill range example (notice =SUM(A2#) in the formula bar)

Spill range is a special cell range where the beginning of the range is specified, but not the end. Spill range have been announced on September 2018 by Microsoft and they will be avalaible in the course of 2019 in Office 365. It's important to realize that this feature is not going to be made available in any regular Excel version included Excel 2019 (released on September 2018).

xlsgen provides read, write and calculation support for spill ranges, so as long as your scenario does not imply opening the resulting Excel file in a version of Excel that does not support them, which is many of them, xlsgen can get the scenario up and running, regardless of your Excel version (in fact xlsgen does not expect any Excel version installed on the computer where xlsgen runs).

Spill range is of interest whenever the cell area being referenced changes over time. Indeed, every time a new row is added, or removed, a fix cell area would break the integrity of calculations, whereas a dynamic cell area would include both new rows and removed rows. This is exactly what spill range is.

The notation is subtle. An anchor sign is added to a cell reference. This anchor sign means the bottom of the range isn't specified by the user, which in turn means it's up to xlsgen to calculate it based on what's in that range, that is whether that's raw data or an existing array formula, i.e. a formula that spans across multiple rows.

In the screen shot above, it's very easy to understand the result of suming the spill range A2#, as in =SUM(A2#). It's the equivalent of =SUM(A2:A11).

Build 4.5.0.44 of xlsgen adds read and write support for Excel 365 functions related to the recently announced (by Microsoft) dynamic array functions, namely :

• =RANDARRAY([rows],[columns]) : returns an array of random numbers between 0 and 1.
• =SEQUENCE(rows,[columns],[start],[step]) : generates a list of sequential numbers in an array, such as 1, 2, 3, 4.
• =FILTER(array,include,[if_empty]) : filters a range of data based on criteria you define.
• =UNIQUE(array,[by_col],[occurs_once]) : returns a list of unique values in a list or range.
• =SORT(array,[sort_index],[sort_order],[by_col]) : sorts the contents of a range or array.
• =SORTBY(array, by_array1, [sort_order1], [[by_array2], [sort_order2]],…) : sorts the contents of a range or array based on the values in a corresponding range or array.
• =SINGLE(value) : returns a single value using logic known as implicit intersection.

Build 4.5.0.43 of xlsgen has a fix for the calculation engine, for disambiguating parenthesis in criterias used in the following functions : IFS / SUMIF / SUMIFS / AVERAGEIF / AVERAGEIFS / MINIFS / MAXIFS / COUNTIF / COUNTIFS.

Build 4.5.0.42 of xlsgen introduces a new chart : overlay line chart.


Overlay line chart example

The overlay line chart makes it possible to, as the name implies, overlay more than one time series one on top of another.

What's of interest is that whenever a data series spans across multiple years, this chart splits it into multiple series, one per year, sorts the data points according to the timeline, and makes it possible to overlay the series on the same chart in order to compare time periods year over year. xlsgen hides the complexity of this : this chart cannot be built in Excel without a number of step calculations and sorting that xlsgen does.

C/C++ code

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"EURUSD1.csv", L"output.xls" ); xlsgen::IXlsChartPtr chart1 = wbk->WorksheetByIndex[1]->NewChart(xlsgen::charttype_overlayline, row1, col1, row2, col2); xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie1 = chart1->DynamicDataSource->AddSerie(); serie1->SeriesValuesFormula = L"=R1C3:R100C3";

Build 4.5.0.41 of xlsgen fixes a problem related to calculations, and in particular to the not equal operator (<>). Its behavior is improved for dealing with arguments of different data types.

Build 4.5.0.40 of xlsgen fixes a problem related to setting an active pane by default (bottom right) whenever both panes are frozen or split.

Build 4.5.0.39 of xlsgen fixes a problem related to writing extended formulas in XLSX files, by adding a prefix to it whenever the functions were introduced in Excel 2010 or a more recent version of Excel. Nothing changes in XLS and XLSB files since in both files the functions are written as bytecodes instead of strings.

Build 4.5.0.38 of xlsgen fixes the reading of HTML entities of the form &#XY; inside XLSX files, by properly decoding them.

Build 4.5.0.37 of xlsgen improves the update of data and formulas for shifting cells up or to the left. First of all, references in formulas are updated if it hits the shifted cell area. Second, there is a performance improvement when shifting cells applies to full rows or full columns.

Build 4.5.0.36 of xlsgen fixes a number of internal I/O calls that were still non-Unicode and could, in the advent of a custom codepage usage, be unable to, for instance, open arbitrary files (Excel files and others).

Build 4.5.0.35 of xlsgen has a fix related to creating pivot tables in XLSX/XLSB/XLS files. This fixes the internal metadata stored for pivot tables, an area in which Excel is super extra sensitive and may show error messages.

Build 4.5.0.34 of xlsgen adds a boolean for switching on and off the xlsgen pivot table rendering engine introduced in 4.5.0.30. By default, it's on, which means xlsgen automatically calculates and renders the pivot table on save and on print/preview renderings. And should your application need to disable it, here is what you would do :

pivotTable.Options.AutoRendering = false;

before inserting the pivot table in a sheet.