Build 4.5.0.33 of xlsgen has a fix related to named ranges with a global scope and how they are handled during a worksheet duplicate scenario, when there is already one with the same name and same scope.

Build 4.5.0.32 of xlsgen follows on a recent build and provides much more scalability for using merged cells. This is very noticeable if you are using a large amount of merged cells.

Build 4.5.0.31 of xlsgen adds support for rendering date-based category axis in charts.

Before this build, dates in categories (the X axis of the chart) would be taken as strings. In fact, dates are more interesting than strings because between 2 or more dates, the distance along the X axis shows how far they differ, so there is an opportunity to treat dates as a XY chart, i.e. the X axis has scale options exactly like the Y axis.

Here is an example of showing date-based category axis before build 4.5.0.31 :

You may notice the distance between categories is the same regardless the difference between dates


showing date-based category axis before build 4.5.0.31

And now, without changing a line of code, an actual date-based rendering :

showing date-based category axis now in xlsgen

Build 4.5.0.30 of xlsgen adds a pivot table rendering engine. So far, pivot tables were created (in XLS/XLSX/XLSB/ODS files) but their display was deferred until the corresponding file was opened in Excel. This is no longer true. If you create a pivot table, it is automatically calculated and rendered in any output such as PDF or HTML.

The rendering engine acts upon the pivot table field specifications and properties in order to compute the corresponding rows and columns.

Let's take an example, assuming you have the following data :

Sample pivot table data source for rendering purposes

And let's assume you would like pivot table fields arranged this way :

pivot table fields for rendering purposes

Here is the output, as calculated and rendered by xlsgen :

Pivot table rendering

Build 4.5.0.29 of xlsgen has a fix related to supporting form controls (buttons, radio buttons, check boxes, ...) in a worksheet duplicate scenario.

Build 4.5.0.28 of xlsgen brings improvements to speed. Whenever you are creating a large number of ranges, or a large number of strings in cells, xlsgen greatly reduces the memory fragmentation resulting from doing so. And your client source code needs not be updated to take advantage of it.

Build 4.5.0.27 of xlsgen adds automatic source code generation support for pivot tables. In other words, the automatic source code generation tool automatically creates the source code for existing pivot tables from XLS, XLSX and XLSB files, in the programming language of your choice. It makes it much simpler to use in practice : just create the pivot table in Excel, and ask the automatic source code generation tool for it.

Here is an example :


A pivot table in Excel

And here is the corresponding source code for it (VB) :

Dim pivotTable002s0 As IXlsPivotTable
Set pivotTable002s0 = wksht002.NewPivotTable
pivotTable002s0.DataSource.Range = "Sheet2!B2:F7"
pivotTable002s0.Options.Layout = enumPivotTableLayout.pivottablelayout_tabular
pivotTable002s0.Options.BuiltInPivotTableStyle = enumPivotTableStyle.pivottablestyle_light16
pivotTable002s0.Options.ShowRowHeaders = True
pivotTable002s0.Options.ShowColumnHeaders = True
pivotTable002s0.Options.ShowRowStripes = False
pivotTable002s0.Options.ShowColumnStripes = False
pivotTable002s0.Options.ShowGrandTotalsForRows = True
pivotTable002s0.Options.ShowGrandTotalsForColumns = True
Dim pf002s0r1 As IXlsPivotTableField
Set pf002s0r1 = pivotTable002s0.Rows.AddByName("s0")
pf002s0r1.AggregateFunction = enumPivotAggregateFunction.aggrpivotfunction_none
pf002s0r1.UnselectItemByName("aé")
Dim pf002s0r2 As IXlsPivotTableField
Set pf002s0r2 = pivotTable002s0.Rows.AddByName("s1")
Dim pf002s0d1 As IXlsPivotTableField
Set pf002s0d1 = pivotTable002s0.Data.AddByName("s2")
pivotTable002s0.InsertAt(13, 4)

It covers many of the pivot table features, i.e. data source, style, pivot fields (page/row/col/data), pivot field details (sorting, filtering, show values as, number format, ...)

Build 4.5.0.26 of xlsgen fixes a file migration problem related to ranges. When an XLS file is migrated to a XLSX file or a XLSB file, 256-column wide rows and 65536-row wide column are migrated to respectively 16384-column wide rows and 2^20-row wide columns.

Build 4.5.0.25 of xlsgen fixes a problem related to page fields in pivot tables.

When a single item is selected in a page field, Excel defaults to a single-selection UI instead of a multi-selection UI. Oddly enough, this is stored differently in the file too, whether it's a XLS, a XLSX or a XLSB file. xlsgen now does this too even though this is a design snafu.

What this build of xlsgen also brings to the table is perhaps more interesting to the client application developer : it's the ability to select items without unselecting them all first. Indeed, by default, all items in the field are selected by default. And if you'd like to select just one or two of them, before this build, you would have to unselect them all (there is a method that does this in a single call), and only then start selecting single items. With this build, the prior unselection is done for you so you don't have to.

Build 4.5.0.24 of xlsgen improves support for Unicode in CSV export and XML export.

For CSV export, the option to export as Unicode UTF8 was already available, but the CSV export did not include a Byte Order Mark right at the beginning of the file that would instruct a consumer application that the file is encoded with Unicode UTF8. This build adds it automatically.

If you need to support Unicode UCS2 (i.e. fix 2-byte encoding), this build adds the option in the worksheet export interface. Of course, the corresponding Byte Order Mark is added as well.

For XML export, it is exactly the same. Both Unicode UCS2 support is new (default was Unicode UTF8), and also this build adds a Byte Order Mark in the XML output, that lets a consuming application know in advance how the stream/file is encoded.

Here is an example (Unicode UCS2) :

workbook.WorksheetByIndex(2).Export.Options.UnicodeEncoding = True
workbook.WorksheetByIndex(2).Export.ExportAsCSV("filename.csv", True)

Build 4.5.0.23 of xlsgen has a fix for the calculation engine, for meeting a scenario where cells in error have to be ignored during the calculation of formulas using one or more of the following functions : IFS / SUMIF / SUMIFS / AVERAGEIF / AVERAGEIFS / MINIFS / MAXIFS / COUNTIF / COUNTIFS.

Build 4.5.0.22 of xlsgen adds support for shifting cells in a range.



Shift cells (before on the left, after on the right)

xlsgen shifts cells if a client application deletes entire rows or entire columns. But the client application may need such shift for an arbitrary cell range. When this occurs, the client application must choose to ask xlsgen to fill the blank by shifting cells to the left, or shifting cells up.

Here is how it works :

worksheet.NewRange("D5:E7").ShiftCellsLeft();

or

worksheet.NewRange("D5:E7").ShiftCellsUp();

Build 4.5.0.21 of xlsgen fixes a problem related to hyperlinks.

By default xlsgen url-encodes urls in hyperlinks before they are written to XLSX and XLSB files. Url-encoding, for instance, replaces # characters with %23 sequences (23 is the hexadecimal ascii-code of the # character).

It turns out that sometimes the client application passes urls that are already url-encoded to xlsgen. Url-encoding twice simply corrupts the url, so build 4.5.0.21 exposes a UrlEncode property to the IXlsHyperlink interface. Defaulting to true, the client application can set it to false to avoid url-encoding twice.

Also build 4.5.0.21 includes a fix for pivot tables in XLSX files where one of the pivot table field names may have appeared with an incorrect "Row Labels" label.

Build 4.5.0.20 of xlsgen makes it possible to convert dynamic ranges.

xlsgen exposes two kind of ranges : one is the most generic, actually holding a formula, and these are called dynamic ranges. They do not expose much features except an associated name (hence the name, named range) and the ability to enumerate and delete any of them. The other are regular ranges, basically holding one or more cell areas. Regular ranges expose a lot of features (almost 50) : from a regular range, you can create merged cells, outlines, tables, ...

Well it turns out, sometimes you need to use an existing dynamic range in order to create an object that typically is created from a regular range. So you need a way to convert a dynamic range to a regular range seamlessly. That's exactly what xlsgen 4.5.0.20 does. xlsgen does calculate the dynamic range and expands the output as cell areas as a regular range.

xlsgen::IXlsRangePtr regularRange = workbook->NamedRanges->DynamicRange[2]->Range;

Build 4.5.0.19 of xlsgen is a fix pack :

- chart rendering : the plot area is made smaller, should axis titles be rotated, to avoid some clipping

- hyperlinks : for named anchors, fix for writing such hyperlinks in XLSX files. It was working with XLS and XLSB files.

- CSV import : when a custom data type is specified for a column, it always takes precedence to what xlsgen infers automatically.

Build 4.5.0.18 fixes a number of chart rendering issues and one chart reading issue.

Fixed chart rendering issues :

• render optional drop lines to applicable 2D charts (mostly line charts and area charts)
• render optional drop lines for line 3D charts and area 3D charts
• render axis numbers with locale even when there is no number format (so "0,5" instead of "0.5" if the user country's locale for decimal separation is the comma)
• background gridlines thiner and less black in the PDF output
• Y axis repositioned when data has both negative and positive values
• render pie and doughnut leader lines (between data labels and chart elements)

And also custom data label positions from charts in XLSX and XLSB files were obscured from reading properly due to an artifact.

In build 4.5.0.17 of xlsgen, a number of issues are addressed :

- when rendering a chart with rotated text on axis tick marks, better positioning of the text to avoid clipping with the chart area.

- when reading a chart from XLSX and XLSB files, read drop lines, high-low lines and up-down bars and their associated formattings.

- for pivot tables, better coercing of mixed data types in a column in order to avoid file corruption (Excel super sensitive).

Build 4.5.0.16 of xlsgen improves the print/preview/PDF/XPS generation with an enhanced fit-to-page algorithm, that both splits scales in the two directions indepently, and ignores "blank" sizes due to columns with no content and formatting.

Build 4.5.0.15 of xlsgen improves pivot tables in a number of ways.

And this applies to XLS, XLSX, XLSB and ODS files.

- custom number format : pivot fields can have a custom number format. xlsgen carries the number format from the data source by default, but it is now possible to customize it in the pivot table itself.

- named range as datasource : before this build, the datasource needed to be a cell area. This still works, but it is now possible to pass a defined name, or a table name. The benefits of this is that by passing a name, the client application needs not know the boundaries of the data region.

- "show values as" setting : for any pivot field in the data area, it is now possible to show values as is, or as difference from another field, or a percent, etc. You can pick one of 8 choices.


And here is a piece of source code (C++) that shows the above in practice :

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

xlsgen::IXlsPivotTablePtr pt = wksht->NewPivotTable();
pt->DataSource->Range = L"Table4";

xlsgen::IXlsPivotTableFieldPtr pfSegment = pt->Rows->AddByName(L"Column1");
xlsgen::IXlsPivotTableFieldPtr pf1 = pt->Data->AddByName(L"q200");
pf1->NumberFormat = L"$###,###.00";
xlsgen::IXlsPivotTableFieldPtr pf2 = pt->Data->AddByName(L"q201");
xlsgen::IXlsPivotTableFieldPtr pf3 = pt->Data->AddByName(L"q202");

xlsgen::IXlsPivotTableFieldShowAsPtr pa2 = pf2->ShowValuesAs[xlsgen::pivotfieldshowas_differencefrom];
pa2->BasePivotField = pfSegment;
pa2->BasePivotItem = L"may";

pt->InsertAt(1,1);

Build 4.5.0.14 of xlsgen improves the PDF rendering in scenarios where the zoom level is small, either because it was set as a small value, or because it was indirectly set as a small value by using the fit page feature. When this happens, text chunks may be needlessly clipped a little bit. This build inflates the bounding area of the text chunk accordingly so this is not a problem.