Build 4.0.0.33 of xlsgen fixes a problem in the calculation engine, related to the MOD() function. Support for non-integer values.

Build 4.0.0.32 of xlsgen adds exposure to gradient fills in cells in the object model.


Gradient fill in cells

This exposure makes it possible to add such gradient fills in XLSX files and their variants (XLSM, XLSB, ...), by adding the corresponding property in the IXlsPattern interface (regular style object). You can choose both colors, the gradient style (orientation, ...) and you can disable an existing gradient fill in a cell.

Of course, also xlsgen renders those gradient fills in print, preview, PDF, etc. But this has been the case for some time already, including the rendering of gradient fills of BIFF12 records in XLS files (written by Excel 2007 and above for compatibility reasons).

The automatic source code generator also exposes the corresponding source code so in the event you would like to use this feature, you can create it manually in Excel and then use the automatic source code generator to get the lines of code.

Here is an example source code :

xlsgen::IXlsStylePtr style = worksheet->NewStyle();
xlsgen::IXlsGradientPatternPtr gp = style->Pattern->Gradient;
gp->FirstColor = 0xFFFF00;
gp->SecondColor = 0xFF0000;
gp->GradientStyle = xlsgen::gradientstyle_verticall2;
style->Apply();

Build 4.0.0.31 of xlsgen is a fix pack made of the following :

- better preservation and setting of calculation properties (XLSX/XLSM/XLSB files)

- proper preservation of dialog sheet types (Form controls) (XLSX/XLSM/XLSB files)

- fixed mime type for international macrosheet sheet type (XLSX/XLSM/XLSB files)

- registerCOM.bat fixed (works in any folder) and added to both 32-bit and 64-bit setups because after all manual COM registration requires the .bat script technique for operating system such as Windows 7 and above.

Build 4.0.0.30 of xlsgen adds another option when importing CSV files, which is to override the automatic data type discovery in favor of a predefined data type.

In the sample below, the CSV content is imported as strings.

engine->Workbooks->CSVImportOptions->ForceDataType = xlsgen::datatype_string;

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"input.csv", L"output.xlsx" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

WCHAR sDimensions[128];
swprintf(sDimensions, L"R%dC%d:R%dC%d",
    wksht->Dimensions->FirstRow,
    wksht->Dimensions->FirstColumn,
    wksht->Dimensions->LastRow,
    wksht->Dimensions->LastColumn);
wksht->Columns[sDimensions]->AutoFit = TRUE;

wbk->Close();

Build 4.0.0.29 of xlsgen adds support for writing pivot tables in XLSB files, which brings this feature on par with other file formats (old Excel, XLSX/XLSM, ...).


xlsgen::IXlsWorkbookPtr wbk = engine->New( L"output.xlsb" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet( L"Sheet1" );

wksht->Cell[1][3]->HtmlLabel = L"<span width=10.554688 autofit=\"TRUE\"><font color=#000000 size=11 name=\"Calibri\">q1</font></span>";
wksht->Cell[1][4]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">q2</font>";
wksht->Cell[1][5]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">q3</font>";
wksht->Cell[1][6]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">q4</font>";
wksht->Cell[2][2]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">s1</font>";
wksht->Cell[2][3]->HtmlDate = L"<span format=\"dd/mm/yyyy\" width=10.554688 autofit=\"TRUE\"><font color=#000000 size=11 name=\"Calibri\">12/05/2015</font></span>";
wksht->Cell[2][4]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">1.2</font>";
wksht->Cell[2][5]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">2</font>";
wksht->Cell[2][6]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">1</font>";
wksht->Cell[3][2]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">s2</font>";
wksht->Cell[3][3]->HtmlDate = L"<span format=\"dd/mm/yyyy\" width=10.554688 autofit=\"TRUE\"><font color=#000000 size=11 name=\"Calibri\">13/05/2015</font></span>";
wksht->Cell[3][4]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">1.3</font>";
wksht->Cell[3][5]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">6</font>";
wksht->Cell[3][6]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">2</font>";
wksht->Cell[4][2]->HtmlLabel = L"<font color=#000000 size=11 name=\"Calibri\">s3</font>";
wksht->Cell[4][3]->HtmlDate = L"<span format=\"dd/mm/yyyy\" width=10.554688 autofit=\"TRUE\"><font color=#000000 size=11 name=\"Calibri\">14/05/2015</font></span>";
wksht->Cell[4][4]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">1.4</font>";
wksht->Cell[4][5]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">5</font>";
wksht->Cell[4][6]->HtmlFloat = L"<font color=#000000 size=11 name=\"Calibri\">0</font>";

xlsgen::IXlsWorksheetPtr wkshtPT = wbk->AddWorksheet( L"SheetPT" );

xlsgen::IXlsPivotTablePtr pt = wkshtPT->NewPivotTable();
pt->DataSource->Range = L"Sheet1!R1C3:R4C6";

pt->Rows->AddByName(L"q1");
pt->Data->AddByName(L"q2");
pt->Data->AddByName(L"q3");
pt->Data->AddByName(L"q4");

pt->InsertAt(3,1);

wbk->Close();


Build 4.0.0.28 of xlsgen adds XLSB template mode. This template mode means you can open an existing XLSB file, make a few changes and save it back preserving everything including objects that xlsgen does not support yet in the API, for instance smart art (also known as diagrams). XLSB is now a first class citizen. After support for importing XLSB files, and saving to XLSB files, it was the next logical step but the dots had to be connected, and it was no easy task to keep everything in context and sometimes re-map and re-contextualize objects, all of which cannot be done without a powerful core runtime.

This template mechanism also works for buffers in memory rather than files.

Scenarios such as migrating XLS or XLSX files (and their variants) to XLSB files also take advantage of this feature to avoid losing objects. And vice versa, from XLSB files to XLSX files.

Build 4.0.0.27 of xlsgen adds support for table parts in XLSB files. Table parts include column name and definition, style as well as custom formulas (i.e. calculated columns). This is to be in par with what xlsgen was doing so far for XLSX files.

Build 4.0.0.26 adds support for expressions such as Table1[[#This Row];[Y2013]:[Y2016]] in the formula parser, as well as for migrating to and from XLSX and XLSB files.

xlsgen 4.0.0.25 of xlsgen rearchitects itself in the area related to styles, named styles, their management (duplication, enforcing) in order to be as neutral as possible with Excel. This is a work for XLSX and XLSB files.

Build 4.0.0.24 of xlsgen adds round-trip support to font features such as the associated theme (minor or major) as well as the alpha channel for colors (transparency level in objects supporting this feature). This is for XLSX and XLSB files (and their variants).

Build 4.0.0.23 of xlsgen fixes the parser used in common expressions in formulas with COUNTIF()/SUMIF() and AVERAGEIF(). The expression to compare to was not supporting all operator cases, and all operand cases, which could return in the calculation engine unable to compute the value in such a cell.

Build 4.0.0.22 of xlsgen exposes the maximum number of rows and columns in each workbook, thanks to the MaxRows and MaxColumns properties available in the IXlsWorkbook interface.

This returns, for any XLS file (and variants such as XLA, XLM, ...), 65536 rows and 256 columns. And it returns 1048576 rows and 16384 columns for any XLSX file (and variants such as XLSM, XLSB, ...)

Beginning today, xlsgen 32-bit and 64-bit installers become .MSI files instead of .EXE files. It works the same. Double-click on .MSI files and it starts the install process.

Build 4.0.0.21 of xlsgen adds the following objects in the XLSB output (support for writing XLSB files is introduced in xlsgen 4.0, and is a binary version of XLSX files aimed at improving the speed for reading and writing large spreadsheets) :

- advanced conditional formattings
- hyperlinks
- comments
- data validations
- freeze/split panes
- auto filters and custom filter expressions

Build 4.0.0.20 of xlsgen improves the speed at which XLSX files (and their variants) are read. Half encoding calls removed, resulting in up to 10% imrpovements for reading files. Particularly noticeable with large files.

Build 4.0.0.19 of xlsgen has another fix for the calculation engine. This time it's a variant of MATCH() which is getting fixed.

Build 4.0.0.18 of xlsgen includes a fix for the calculation engine. A more dynamic boolean for handling pseudo-expressions sometimes found in Excel formulas. For instance whenever a bool, instead of being the result of a logical expression such as A1 < 3, is just the coercion of whatever content in a cell as a bool. Hence what Excel does by making any number equivalent to a boolean with the following rules : any non-zero value is equivalent to a TRUE, and zero is equivalent to a FALSE. A logic by which -13,45 is equivalent to TRUE. This is precisely what xlsgen does beginning with this build, doing exactly what Excel does.

Build 4.0.0.17 of xlsgen for charts improves how several chart properties are preserved, including invert color if negative as well as legend overlap.

This build also includes a better algorithm for computing LOOKUP(), HLOOKUP() and VLOOKUP() functions in the calculation engine, particularly when processing strings.

Build 4.0.0.16 of xlsgen is a security update related to a library dependency of xlsgen core called libPNG.

libPNG (PNG is short for Portable Network Graphics), is the official library for reading and writing PNG pictures.

We are using the latest version of libPNG, where a number of vulnerabilities have been closed compared to the version of libPNG we are using until now.

CVE 2015 7981 : potential out-of-bounds read in png_set_tIME()/png_convert_to_rfc1123() and an out-of-bounds write in png_get_PLTE()/png_set_PLTE().

CVE 2015 8126 : potential pointer overflow/underflow in png_handle_sPLT()/png_handle_pCAL() (and in png_handle_iTXt()/png_handle_zTXt() in the pre-1.6 branches), and all such versions likewise have a bug in their png_set_PLTE() implementations that left it open to the out-of-bounds write .

CVE 2015 8540 : potential out-of-bounds read in png_check_keyword().

Build 4.0.0.15 of xlsgen adds support for decrypting password-protected XLS files. It's been a while since we had support for XLSX files, and we are now introducing support for XLS files which means xlsgen can decrypt any Excel file.

How it works is, as expected, to simply pass the open password to the engine before trying to open a spreadsheet. For this there is the engine.Workbooks.OpenPassword property that takes a string. It does not matter if you set an open password even though the spreadsheet isn't encrypted. Also, xlsgen returns the error code 0x80072001 (which becomes an exception depending on your programming language) if the password is incorrect, or if no password is passed to open an encrypted spreadsheet.

The mechanism is the same for XLS and XLSX files (and their variants, XLSB, XLM, ...). Internally it is a different story. We are talking an entirely different story for "old" Excel files, basically MD5 for hashing, and XOR/RC4 for encrypting, and for "new" files, basically SHA-x for hashing and AES for encrypting.

engine->Workbooks->OpenPassword = L"abcd";

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"passwordprotected.xls", L"");

wbk->Close();