xlsgen 4.9 introduces charts using the SVG file format. It is now possible to export one or more charts as SVG, or Scalable Vector Graphics. This W3C norm is now very popular on the web thanks to the nature of SVG, i.e. vector-based drawing along with its effectiveness when specifying complex combinations (clipping, gradients, ...). It is also more efficient in size.

Here is a sample chart in PNG,


PNG chart

and then the same in SVG,


SVG chart


To get this, just write :

currentWorksheet.Charts.Item(2).ExtractAsSVG("c:\\tmp\\export.svg");


Of course, all chart types and their formatting details can be exported as SVG, including the new charts we are introducing in xlsgen 4.9

When SVG charts are part of an HTML page, the beauty of it is that instead of having to worry about retrieving secondary parts (images are binary elements that are loaded after the HTML page itself is loaded), SVG charts can now be part of the HTML page itself, right within the HTML markup, and so it's much simpler to handle if you are serving HTML pages on your server.

By default, HTML export in xlsgen uses bitmap charts (PNG files), but all it takes is the following line of code to replace PNG charts with SVG charts during HTML export :

workbook.ChartEngine = enumChartEngine.chartengine_svg;

xlsgen 4.9 improves PDF output in the following ways :

• Unicode fonts : prior to this, only ANSI characters in Excel files would be encoded correctly in PDF files. That would leave much of eastern languages unsupported. With Unicode fonts, PDF files use whatever character from whatever encoding.

• Font embedding : the embedding of the subset of fonts actually used in the Excel file ensures that PDF readers can accurately display characters for viewing and printing purposes. This is activated by default, meaning that such PDF files are bigger in size from those not embedding the fonts. For this reason, font embedding can be disabled by setting the boolean value to the corresponding export option :
  
  

  C/C++ code

  worksheet->Export->Options->FontEmbedding = FALSE;

• improved character spacing : even though PDF is character-based, fonts are not. Accurate spacing depends on actual glyph bounding boxes and this is taken care of to improve the reading of words in PDF files.

xlsgen 4.9 introduces a multithreaded calculation engine.

xlsgen multithreaded calculation engine can be taken advantage of to accelerate calculations or just avoid having many CPU cores staying idle. Here is a snapshot of a calculation scenario using single thread calculations :

Single thread calculations (notice the 24% overall usage and the bottom right chart)

And here is the same calculation scenario with all of the 6 CPU cores of that i5-9400 taken to handle the calculations :

Mlutithread calculations (notice the 100% overall usage and all charts)

The amount of worker threads is up to the client application. Of course, if you are running a CPU with n cores, it makes sense to create no more than n worker threads otherwise threads will be waiting one another, not doing work in parallel.

If you don't know how many cores the CPU where xlsgen runs has, it can be queried very easily :

C/C++ code

int nbCores = workbook->CalculationOptions->MultithreadCores;

And then, creating n worker threads for next calculations is just as simple :

C/C++ code

workbook->CalculationOptions->MultithreadCores = n; workbook->CalculateFormulas();

xlsgen 4.9 is coming and the theme for this next major release is developer friendliness.

More than half of the features are actually meant to make existing tasks easier to write and use, such as PDF engine improvements, chart rendering improvements, HTML markup improvements, and so on. Over the next few days, we'll be previewing details.

And also new features such as the introduction of multithreaded calculations, SVG chart export and so on.

Build 4.5.0.99 of xlsgen fixes a problem related to content with multiple formatting runs in cells, particularly for the calculation of the height of autofit.

Build 4.5.0.98 of xlsgen has two fixes related to rendering :

- some merged cells incorrectly (by Excel!) storing a different style (particularly background color) across the cells, still have to be rendered using only the style referenced in the top left corner of the merged cells area

- in XLSX files, some HTML encoded characters were not parsed correctly to Unicode

Build 4.5.0.97 of xlsgen does not ignore multiple EOL characters when it occurs, especially for the calculation of row autofit.

Build 4.5.0.96 of xlsgen is a fix related to HTML in cells. It improves how non-font HTML tags relate to font HTML tags.

Build 4.5.0.95 of xlsgen fixes a problem related to calculating the INDEX() function. Under certain circumstances, the function would be unable to calculate at all.

Build 4.5.0.94 of xlsgen is a fix pack. It has the following :

- fixed "fit to page" print scenarios in real and virtual printing

- fixed rendering of merged cells at edges of pages

- improved row height computation (autofit) in presence of merged cells

- improved row height computation (autofit) scenarios

Build 4.5.0.93 of xlsgen fixes a problem related to rendering large pieces of strings in PDF, in the native PDF rendering. The clip region could become too small if the print scale was causing great shrink (fit to x page scenarios).

Build 4.5.0.92 of xlsgen adds read, write and calc support for the new LET() function.

Microsoft announced earlier in march that they would add the LET() function to the arsenal of Excel functions. Announcement is here.

The point of the LET() function is to able to name expressions that are often used multiple times in the formula and use the name instead of the expressions, improving the readability of the formula not just for the person who wrote the formula, but also for everyone else who will be tasked to update the Excel spreadsheet.

Microsoft made clear that this LET() function will only be made available on a subscription basis to Office 365 and none of the existing Excel versions out there will obtain it (we are talking about Excel 2019, 2016, 2013, ... none of which will ever get it).

xlsgen ships with support for the LET() function so you can write and calculate those formulas.

Here is an example :

LET( name1, expression1,
name2, expression2,
...
expression)

this function calculates expression where a number of smaller expressions appear, namely expression1, expression2, ... Instead of using the expression, this function lets the formula writer use name1, name2, ...

The ability to use names instead of literal expressions may improve the readability of the formula. And in addition to this, if a smaller expression is used multiple times in the global expression, using names instead improves the readability as well. And it improves the performance of calculations by caching (internally) smaller expression evaluations.

Build 4.5.0.91 of xlsgen adds the option related to updating formula specs whenever there is a row or column insertion or deletion.

By default, formulas in the workbook are updated to reflect the new rows or columns, and doing this keeps the integrity of the spreadsheet. But sometimes, depending on your scenario, it may be needed to keep the formula specs intact. That's what you can obtain by setting False to a new property we are making available at the workbook level, i.e.

workbook.FormulaDefinitionUpdate = False

Build 4.5.0.90 of xlsgen adds a new property to pictures, the ability to set a RGB-based chroma key in order to make transparent all pixels in that picture with that RGB color. This works with all Excel files.

Here is how it works. Assuming you have a picture to be inserted, where you know in advance that the red color is used as chroma key :

(C++ code follows)

xlsgen::IXlsPicturePtr picture1 = worksheet->Pictures->Add( L"input\\a.gif" );
picture1->LeftColumn = 1;
picture1->TopCell = 1;
picture1->ChromaKeyColor = 0xFF0000; // RED chroma key



Setting the chroma key color lets one see through the picture

Build 4.5.0.89 of xlsgen fixes a problem related to the calculation of a special operand used in the TEXT() function. It's the case where the operand uses a percent-formatted number.

Build 4.5.0.88 of xlsgen improves how linking behaves in duplicate scenarios involving external workbooks.

Before this build, any link from an external workbook, for instance a chart series formula, would be expanded to literals and copied over to the destination sheet. Beginning with this build, links are brought as is, as long as the links depend only on the sheet being duplicated. When the link depends on a sheet not being copied over, the link is expanded as literals and brought over as literals.

This build also exposes the text boxes collection inside charts. So far only pictures and vector shapes were exposed (for querying purposes).

Build 4.5.0.87 of xlsgen adds support for copying hyperlinks (url, file, sheet location) stored in text boxes, vector shapes and pictures in duplicate scenarios.

Build 4.5.0.86 of xlsgen is a fix pack for charts :

- when duplicating charts, improved accuracy of chart formatting details such as chart XY type combination, axis type, font details

- also when duplicating charts, make sure to copy text boxes, vector shapes, pictures and charts (both inside charts)

- when reading existing charts, better read font details and better read of leader lines in the case of a bar/column chart

Build 4.5.0.85 of xlsgen fixes a problem related to moving auto-filters accordingly, if present, when inserting a row or a column. This works for all Excel files and avoids corrupting the file in question.

Build 4.5.0.84 of xlsgen ships with the latest stable version of libPNG, a third party library that allows to read and write pictures in the PNG file format, which includes a number of security fixes. We are using libpng 1.6.37. libPNG is the only third party dependency of xlsgen and is used at a number of end points, for instance for PNG picture rendering purposes.