JSON (Javascript Object Notation) is all the rage in modern web applications and has taken over XML for storing data in streams thanks to how much it fits with the Javascript programming language. JSON is highly hierarchical in nature and defines several data types so that a consumer application knows upfront the type of data it is dealing with (with the notable exception of dates which remain application-specific).

(sample JSON)

{
    "count":2,
    "cache":false,
    "environment":"live",
    "remainingAccesses":231,
    "data":[
        {"date":"2013-12-09", "service":"Be On", "campaign_id":17088, "campaign_name":"DE Peek & Cloppenburg Review Urban Karaoke", 
"project_id":119, "websites":3, "impressions":48, "views":1, "views_paid":0, "redirects":0, "playtoends":0},
        {"date":"2013-12-09", "service":"Be On", "campaign_id":17280, "campaign_name":"DE Telekom Technischer Support",  
"project_id":137, "websites":4, "impressions":189, "views":47, "views_paid":0, "redirects":2, "playtoends":9}
    ]
}

xlsgen 4.0 can import any JSON stream from a regular URL, a file or a memory buffer. A JSON stream can also be read by passing the URL directly to the workbook Open() method.

The import can be customized to provide styles on a per column basis.

xlsgen automatically infers hierarchies, cardinalities and what looks like a table set.

C/C++ code

xlsgen::IXlsJSONImportPtr json = worksheet->Import->JSON; xlsgen::IXlsStylePtr style = worksheet->NewStyle(); style->Pattern->BackgroundColor = 0xFFFF00; style->Font->Italic = TRUE; json->Options->ColumnStyleByName[L"City"] = style; json->ImportFile(L"input\\jsonfile3.json");

xlsgen 4.0 makes it possible to import web pages.

Many web pages include HTML-based table data that a business application may be interested in importing in a regular spreadsheet.

An arbitrary URL can be passed to the Open() method. It is also possible to customize the import (column styles including number formats, header style). And to import a web page stored in a memory buffer.

The internal xlsgen algorithm parses the HTML markup, finds tables, aggregates data in it, and imports the most consistent set.

Here is an example.

The URL is http://www.insee.fr/fr/themes/info-rapide.asp?id=10&date=20150807

A screenshot of which is :


xlsgen can import a web page

And the resulting data in the spreadsheet is :


xlsgen can import a web page in a spreadsheet

Creating pivot tables with xlsgen

xlsgen 4.0 makes it possible to create pivot tables in XLS files and in XLSX files (and their variants).

The pivot table above can be created with 6 lines of source code :

IXlsPivotTable pt = wkshtNew.NewPivotTable();
pt.DataSource.Range = "JUNCT!A1:S1000";

pt.Rows.AddByName("YR_INST");
pt.Rows.AddByName("DESCRIPT");
pt.Data.AddByName("ID");

pt.InsertAt(2,2);

Among custom options are : sorting/filtering, aggregate functions, pivot style, ...

We have been working on a number of major features for months and we are getting ready to ship xlsgen 4.0 soon. Features are going to be announced and we think these are important additions for the future of xlsgen and how it meets changing market demand. License pricing isn't going to change.

Build 3.5.0.134 of xlsgen adds support in the built-in chart renderer for shape types cylinder, cone and pyramid.

Truncated cones and truncated pyramids are supported as well.

These are variants for bar charts, and fully exposed in the object model.

Support is for XLS charts as well as XLSX charts.

Also for 2D charts as well as for 3D charts.

Build 3.5.0.133 of xlsgen introduces a new option for using the insert row/col feature.

When new rows are inserted in the middle of an existing spreadsheet, the rows actually propagate the formatting from the row above. This behavior mimics Excel.

In this new build, a new option called InsertFillOption is introduced. It is a boolean and its default value make the insert row/col feature work as explained above. If the option is set to False however, the new blanks remain blank, i.e. the formatting from the row above (or column on the left) isn't propagated.

Of course this option must be set accordingly and before using the insert row/col feature, as in :

workbook.InsertFillOption = False;

worksheet.Rows("5:5").Insert(10);

Build 3.5.0.132 of xlsgen provides support for table object in the calculation engine. For instance if you are using a function such as =VLOOKUP(C2, Table1, 2), where Table1 is the name of an existing table object in the current file, you can now get it supported by xlsgen in calculations.

Build 3.5.0.131 of xlsgen has another round of leaks fixed. It involves a GDI leak whenever pictures are being rendered ; another one is related to the fragmentation of memory for large files with many columns.

Build 3.5.0.130 of xlsgen fixes several file handle leaks in XLSX create/update scenarios (XLSX and their variants).

This is particularly useful in server scenarios where xlsgen is supposed to serve a number of requests over time.

Build 3.5.0.129 of xlsgen makes sure that custom properties in the metadata of XLS files are automatically and properly migrated to XLSX files whenever such a scenario is being run (XLS file as input, XLSX file as output). Regular non-custom metadata properties such as Author, Date, etc. were already migrated.

Build 3.5.0.128 of xlsgen fixes a limitation in 64-bit scenarios.

Some of what xlsgen used to have since last november (2014) for avoiding out of memory exceptions while loading large files when it crosses then 2GB limit, was actually also compiled for 64-bit scenarios even though in 64-bit cases a process can hold more than 2GB of memory (physical or virtual).

So this build undoes the unnecessary 2GB check in 64-bit, and as a result scenarios can eat all the memory you have (physical or virtual).

Build 3.5.0.127 of xlsgen improves memory performance in print, preview, PDF and XPS generation scenarios.

Earlier this year, improvements were made for speeding up such scenarios, unfortunately at the cost of making them (much) more memory intensive whenever big files would be generated (think 10000+ pages).

This build ensures that the memory consumption anymore is kept as low as possible (virtually non existent in some of the scenarios).

Build 3.5.0.126 of xlsgen adds OpenFromMemoryEx() to the in-memory scenarios.

OpenFromMemory(), which was available so far, was not allowing the Excel file format identifier or Output file format identifier be set independently at input and output.

OpenFromMemoryEx() lets you do that exactly. For instance you can open an XLSX Excel file from a memory buffer and create a PDF memory buffer as output.

Build 3.5.0.125 of xlsgen exposes databar Excel 2010 properties in the object model. So far those extended properties were supported internally for reading, writing and rendering purposes.

The properties are :

- GradientFill boolean : sets whether borders should have a gradient fill or a solid fill. Default is : gradient fill. Applies to Excel 2010 XLSX files only (and above).

- Direction enumTextReadingOrder : sets the bar reading order, among content, left to right and right to left. Default value : context. Applies to Excel 2010 XLSX files only (and above).

- AxisPosition enumDatabarAxisPosition : sets the axis position differentiating positive and negative values. Applies to Excel 2010 XLSX files only (and above).

Build 3.5.0.124 of xlsgen improves the automatic source code generator that is part of the install.

The automatic source code generator creates the source code corresponding to any XLS/XLSX file being given to it, and in the programming language of your choice.

The automatic source code generator just got even more interesting by generating the source code for all conditional formattings. This includes not only the usual suspects, cell based and formula based conditional formattings, but also all "advanced" ones such as data bars, color scales and icon sets. And again, in VB, VBScript, C#, C++ and Java.

What we want is make sure you can use xlsgen as fast as possible, and ideally without even learning the object model. And we think the automatic source code generator is a great start.

Build 3.5.0.123 of xlsgen exposes column widths in pixels, through a new property called WidthInPixels in the IXlsWorksheetColumns interface.

So far the width exposed the metrics used by Excel for column widths in XLS and XLSX files (and their variants). The metrics were not pixels, inches or anything of this.

A few months ago, we introduced pixel-based API in the object model for working with many objects such as charts, text boxes and so on.

This build exposes column widths so it is possible to get or set the width in pixels.

Build 3.5.0.122 of xlsgen fixes a problem related to the automatic removal of empty rows in XLSX files, for file size reduction purposes. Beginning with this build, if the height has been changed, the row isn't removed.

Build 3.5.0.121 of xlsgen ensures that in-memory scenarios preserve the internal XLSM mime type, avoiding a prompt whenever the files are opened in Excel.

When Excel 2007 introduced XLSM files for "macros", it actually introduced a subtle marking of the file, most probably for screening purposes, as such. In-memory scenarios in xlsgen lacked this capability so far because the algorithm was based on the file extension, XLSX versus XLSM, and there is no such thing as a file extension in in-memory scenarios.

So beginning with this build, xlsgen reads the internal markers of input buffers to figure out these are meant to be "macro enabled" output buffers. This is completely transparent for the client application, and for Excel.

Build 3.5.0.120 of xlsgen is a fix pack for charts.


It includes the following fixes :

- rotate x-axis text in transposed charts so it is easier to read (and mimics Excel)

- better rendering of plot area (actual wide setting)

- fill rendering : avoid partial deletion of borders

- custom data label rendering (font particularly)

- custom data labels : position is wrong in stacked chart case

- clipping case : data labels of type "outside" are repositioned to avoid clipping if they are too close to plot area boundaries

- chart rendering : custom data label positioning

Build 3.5.0.119 of xlsgen brings support of 6 gradient fill types to xlsgen for rendering charts.

Before that, only one horizontal type and one vertical type (both linear) were supported. Now 4 horizontal types and 4 vertical types are supported, which makes way for properly rendering some of the more advanced formatted charts.