Build 4.0.0.52 of xlsgen adds support for rendering histograms created by Excel 2016 (and by xlsgen).


Histogram rendered by xlsgen (number-based histogram with 4 bins including an overflow bin)

Histograms as defined by Excel 2016 are in fact two histograms depending on whether the data is string-based or number-based.

For strings, histograms group them in categories (strict string match with case sensitivity, etc.), sum up their associated values and show them. It's the equivalent of a stacked bar chart except the fact with stacked charts the visual representation keeps the summed up values separated.

For numbers, histograms automatically or programmatically creates groups known as bins, and associate it with the total of numbers for each bin, i.e. frequency, thereby showing the frequency of each bin. The bin width can be customized, the bin count as well as two special bins, which are the overflow bin and the underflow bin. All of those parameters are optional.

This build also introduced the HistogramByCategory boolean property which allows to manually tell if the histogram is to be written/read/rendered as a string-based histogram or a number-based histogram.

By providing an API for creating and rendering histogram Excel 2016 charts, xlsgen allows such scenarios without Excel 2016 installed on the computer.

Build 4.0.0.51 of xlsgen makes an important performance gain for processing large spreadsheets.

Those Excel files can be either legacy (XLS, ...) or modern (XLSX, ...). The class of large spreadsheets being adressed by this build is one made of many rows with columns not on the left of the spreadsheet. For instance, if many columns filled with data/formulas begin at 800 (or ADT in terms of column reference), previous builds of xlsgen would pre-allocate 800 columns in memory for this row alone. With this build, only column 800 and a few more are pre-allocated. This, as is obvious, has an important consequence for the memory consumption for loading, processing and writing the files. And you can expect up to 90% less memory consumption in such scenario. Many scenarios that would have required a 64-bit version of xlsgen would simply work fine on the regular 32-bit version of xlsgen (the 32-bit version of xlsgen can hold only 2GB of working memory due to the 32-bit per running process Windows restrictions).

Build 4.0.0.50 of xlsgen makes it possible to correctly process Excel files generated by some unknown third-party tool in which the cell structures are not the ones that Excel would create when saving such a file. Even though, a quick fix was to resave the file in question in Excel, it was deemed not practical in automated scenarios, hence the fix.

For some time, xlsgen is available at 250 euros instead of 349 euros, or 30% off.

Build 4.0.0.49 of xlsgen adds support for custom print paper sizes.

Currently, the PaperSize property of the page setup interface in the xlsgen object model lets you select a paper size among roughly 40. But should one not be listed, you would be out of luck.

Thanks to a new property being introduced with this build, called PaperSizeForPrinter, a custom paper print size can be passed, used and stored in the Excel file. This property combines a printer (name) and a paper print size (such as "A8"). The reason of the association is because it does not make sense to try to use a custom paper print size if the printer does not support it. Indeed, when a printer is installed on a computer, it lists the paper print sizes it supports. Also xlsgen must map the user-friendly paper print size to the internal ID of the paper print size known by the printer.

Here is how to set the "A8" size to the printer "PDFCreator", a virtual printer for creating PDF files :

workbook->WorksheetByName[L"Sheet1"]->PageSetup->PaperSizeForPrinter[L"PDFCreator"] = L"A8";

If you'd rather not name the printer, and use the implicit name of the default printer, use :

workbook->WorksheetByName[L"Sheet1"]->PageSetup->PaperSizeForPrinter[L""] = L"A8";

Build 4.0.0.48 of xlsgen adds more rendering capabilities related to headers and footers in spreadsheets.

In the previous build, what was added were even header/footer, first page header/footer as well as the corresponding rendering capabilities. What's added now is the support in rendering scenarios (print, preview, PDF, ...) for font tags in those headers and footers.

To get a list of existing font tags, take a look at xlsgen documentation.

Font tags include font name, size, color, bold, italic, strikethrough, underline, superscript, subscript. Font tags are hidden to the Excel user, he/she just uses the font dialog while in the header/footer settings. Of course they are very real when it comes to supporting them. Next time a spreadsheet is rendered with xlsgen, the header/footer will look nicer.

Build 4.0.0.47 of xlsgen improves the handling as well as the exposure of header/footer in spreadsheets.

Namely,

- XLSB files correctly read even header/footer as well as first page header/footer. In Excel 2007, two additional header/footers were introduced on top of the regular header/footer : even header/footer for specifying a header/footer for even pages (2, 4, ...), and a first page header/footer which is a header/footer applied only to the first page of a sheet.

- rendering of even header/footer as well as first page header/footer in all rendering scenarios (print, preview, PDF, XPS, ...)

- exposure of even header/footer and first page header/footer in the xlsgen object model. See the IXlsPageSetup interface, namely PageEvenHeader, PageEvenFooter, PageFirstHeader and PageFirstFooter properties.

- fix for default gridlines when writing XLSB files. That was defaulting to a yes and it was inconsistent with XLSX files as well as what Excel does.

- parsing of font color tags (&K + rgb color and &K + theme color).

Build 4.0.0.46 of xlsgen improves the CSV import to support edge cases :
- files with an arbitrary number of empty column names
- files with an arbitrary number of empty columns
- files with an arbitrary number of columns expected to be imported as smoothly as possible in XLS files (XLS only has 256 columns).

Also, the CSV import stops inserting zeroes in a column whose datatype has been inferred as a number. For a zero to show in the Excel file, it must be present in the input CSV file.

Build 4.0.0.45 makes deployment of xlsgen simpler by not shipping Sparklines.ocx anymore.

Sparklines.ocx included 4 visual controls, namely the vumeter, sparklines, google charts and google geomaps. As depicted :


The 4 visual controls are now native in xlsgen

Those controls are still available in this build and if you were using them, there is no breaking change. What is changed is the fact that these are native built-in controls in xlsgen.dll, so there is no external exposure in sparklines.ocx anymore (there was a /visualcomponents folder in the install).

The reason those controls are now built-in is that not only the hassles of deploying the additional file is now gone, but more importantly the visual controls were implementing ActiveX interfaces and as such could prove hard to enable in a number of server-side environments depending on your corporate policy.

Thanks to the controls being now built-in, the deployment problems related to it are no longer a subject of discussion.

Build 4.0.0.44 of xlsgen introduces support for funnel charts.


Funnel charts in xlsgen

Funnel charts display values as progressively decreasing proportions. A typical example of a funnel chart starts with the sales leads on top, then down to the qualified leads, the hot leads and the closed deals. A business is bound to lose some number of potential deals at each step in the sales process and this is represented by the narrowing sections as you move from the top section (the widest) to the bottom section (the narrowest.)

Funnel charts were introduced by Microsoft earlier this year in Excel 2016. More accurately, funnel charts are available to Excel users who purchased Excel 2016 on a subscription basis (Office 365) as well as Excel online and Excel and Android and iOS.

xlsgen can read and create such charts regardless of the Excel version you are using. Moreover, xlsgen actually renders funnel charts anywhere applicable, in print, preview, PDF/XPS/HTML scenarios.

It's very easy to create a funnel chart :

xlsgen::IXlsChartPtr chart = worksheet->NewChart(xlsgen::charttype_funnel,
                                                     10, //row1
                                                     2, //col1 
                                                     25, //row2
                                                     10 //col2
                                                     );

chart->MainTitle->Label = L"Funnel chart";

xlsgen::IXlsChartDynamicDataSourceSeriesPtr s1 = chart->DynamicDataSource->AddSerie();
s1->SeriesValuesFormula = L"={2;13;5;4;10;1;0;2}";
s1->DataLabelsFormula   = L"={\"cat1\";\"cat2\";\"cat3\";\"cat4\";\"cat5\";\"cat6\";\"cat7\";\"cat8\"}";

Build 4.0.0.43 of xlsgen continues and finishes what was begun with the earlier build, by adding the 4 last functions (out of 6) newly introduced by Microsoft in their Office 365 offerings as well as the online version of Excel, namely :

- IFS(expression1, result_if_true_1 [, expression2, result_if_true_2, ...]) : combine multiple conditions in a single statement. This function is useful for avoiding IF() nesting. The official documentation is here.

- SWITCH(expression, value1, result1, [,value2, result2, ...] [default value]) : switch statement for selecting a discrete value out of n values. Optional use of a default value (last parameter). The official documentation is here.

- MAXIFS(range, range1, expression1, [, range2, expression2]) : combine multiple conditions for calculating a max value. The official documentation is here.

- MINIFS(range, range1, expression1, [, range2, expression2]) : combine multiple conditions for calculating a min value. The official documentation is here.

Complete with TEXTJOIN() and CONCAT(), these are the 6 new functions that xlsgen makes available to all Excel users, not just a fraction like Microsoft would like to do.

Build 4.0.0.42 of xlsgen adds read, write and calc support for two new functions : CONCAT() and TEXTJOIN().

Those functions are part of updates that are not made available to regular Excel 2016 purchasers. Microsoft has made them available for those purchasing Excel 2016 on a subscription basis (called Office 365), or using Excel online (desktop or mobile).

We thought there was a justification then for bringing those functions to ALL Excel users, including previous versions of Excel. This is made possible in xlsgen by natively supporting the calculation of those functions.

- CONCAT() is a variant of the existing CONCATENATE() function. What it supports is, in addition to literals such as numbers, strings and cell references, it supports cell range areas, and also the maximum buffer is 32KB not 8KB. The official documentation for the CONCAT() function is here.

- TEXTJOIN() is a variant of CONCAT() and makes it possible to define a delimiter between concatenated strings as well as specify how empty cells should be handled. Again, the full documentation is here.


worksheet.Formula[5][2] = L"=CONCAT(B1:B4)";
worksheet.CalculateFormula(5,2);

==> bb1bb2bb3bb4


worksheet.Formula[5][2] = L"=TEXTJOIN(\":\";TRUE;B1:B4;C1:C4)";
worksheet.CalculateFormula(5,2);

==> bb1:bb2:bb3:bb4:cc1:cc2:cc3:cc4

Build 4.0.0.41 of xlsgen ignores the DPI (dots per inch) at which your screen is set in order to render or print/preview sheets, making it possible to create such objects predictably from any computer.

Build 4.0.0.40 of xlsgen fixes problems related to rendering content in cells (as in PDF, XPS, ...) :

- improved clipping of right-aligned numbers

- boolean values expressed in human friendly terms (i.e. TRUE/FALSE) instead of 1 and 0. Also affected by the current rendering locale (i.e. if the formula language is chosen to be French, the boolean values will be VRAI/FAUX, respectively).

Build 4.0.0.39 of xlsgen adds support for Excel files generated by third-party tools, in which :
- there is a usage of local namespaces across XML streams
- there is a usage of absolute paths to internal parts as opposed to relative paths
- there is dysfonctional usage of style attributes

It has been chosen to read those files rather than flag them as invalid, for the reason that Excel does it.

Build 4.0.0.38 of xlsgen makes sure that encrypted XLS and XLSX files (and their variants) are re-encrypted automatically when the file is written back after an update. The re-encryption uses the same algorithms and parameters than the decryption, which means xlsgen is neutral. This comes as an expectation since there is no ground for xlsgen removing the protection in a file without the client program asking for it (by resetting the password).

Build 4.0.0.37 of xlsgen supports all cryptographic providers that may be used in order to decrypt XLS files (and their variants), namely :

- Weak Encryption (XOR)
- Office 97/2000 Compatible (RC4 + MD5)
- RC4, Microsoft Base Cryptographic Provider v1.0
- RC4, Microsoft Base DSS and Diffie-Hellman Cryptographic Provider
- RC4, Microsoft DH SChannel Cryptographic Provider
- RC4, Microsoft Enhanced Cryptographic Provider v1.0
- RC4, Microsoft Enhanced DSS and Diffie-Hellman Cryptographic Provider
- RC4, Microsoft Enhanced RSA and AES Cryptographic Provider (Prototype) (Windows XP only)
- RC4, Microsoft Enhanced RSA and AES Cryptographic Provider
- RC4, Microsoft RSA SChannel Cryptographic Provider
- RC4, Microsoft Strong Cryptographic Provider

Build 4.0.0.36 of xlsgen makes it possible to read XLSX files generated by third parties that don't quite conform to the specs, where rows and columns in sheets are not specified on a per cell basis.

Build 4.0.0.35 of xlsgen fixes three problems related to the calculation engine :

- SMALL() function ignores empty cells. Before this build it would take them as zeros and it could affect the result of the function.

- DGET() function (and other database functions) correctly handles empty fields in the third parameter where a range of field names are provided. Before this build, the function in this case would always return the #NUM! error.

- MATCH() function returns correct cell index when processing numbers.

Build 4.0.0.34 of xlsgen adds support for decrypting XLS files encrypted using the Microsoft Enhanced Cryptopgraphic provider, which uses SHA1 and RC4 algorithms. This comes in addition to the two other encryption modes which were already supported by xlsgen (XOR and regular RC4).

To decrypt and read such file, just pass the password :

xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

engine->Workbooks->OpenPassword = L"knownpassword";
xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"input\\protected.xls", L"output\\unprotected.xls" );
wbk->WorksheetByIndex[1]->Cell[1][1]->HtmlLabel = L"unprotected by <b>xlsgen</b>";
wbk->Close();