Build 2.9.0.11 of xlsgen fixes a bug in the calculation engine when it computes formulas that return empty strings. This special value is encoded a different way, and xlsgen improves how it is stored. There is nothing special to do to take advantage of it.

Build 2.9.0.10 of xlsgen exposes the conditional formattings in scenarios where an Excel 2007 file is read. For some reason, it was not done until now (what was supported so far is the exposure of conditional formattings from Excel 97-2003 spreadsheets).

Of course, the immediate benefit is the improvement of the rendering of spreadsheets : print, preview, PDF, but also the export of spreadsheets : OpenOffice, HTML.

Auto-filling a range with data (two examples, one with formulas, another with numbers)

Auto-filling a range in xlsgen is the programmatic way by which it is possible to drag a selection in Excel vertically or horizontally and see the extension filled with data relevant to the data in the selection.

Formulas are replicated (the cell references are updated accordingly). Strings are replicated as is. Numbers are replicated by taking into account the difference between numbers in the data selection. The formatting is replicated.

The selection is defined by its depth. The depth means the height of the selection in case the fill is vertical. And the depth means the width in case the fill is horizontal. It is therefore possible to choose to auto-fill vertically or horizontally.

In the examples above, auto-fill is used for replicating formulas vertically, and numbers vertically. Since there is only one formula in the selection, the depth is set to 1. In the other example, numbers are replicated. The selection has three numbers, which implies the depth is set to 3. If the depth was set to 1 or 2, the result would be entirely different. Indeed the selection depth is used to infer the differences between numbers. The algorithm is always linear.

C++ code

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"data.xls", L"output.xls" ); xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1]; // auto-fill vertically our formula in the selection (see example above) wksht->NewRange(L"C3:C11")->AutoFill(1); wbk->Close();

Build 2.9.0.8 of xlsgen introduces the ability to transpose or turn a table.



Sometimes, data arranged as a cross table may be more useful with rows and columns turn upside down. Transpose is the mathematical name for such transform, and it is available in xlsgen. It transposes the data, formatting, formulas, merged cells and more. An optional parameter lets you choose to transpose values only, value and formatting, or everything.
The method also returns the transposed areas, for your convenience. For instance, if you transpose range D2:I10, a range 6-column wide and 9-row tall, the transposed area is D2:L7, 9-column wide and 6-row tall.
Transposing a range is a conservative function, therefore transposing a range twice returns to the initial situation.

C++ code

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"data.xls", L"output.xls" ); xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1]; wksht->NewRange(L"C2:H10")->Transpose(xlsgen::pasteoption_valuesandformattingandformulas); wbk->Close();

Build 2.9.0.7 of xlsgen introduces helpers at the range level.

A number of method calls are available at the cell level, or at the worksheet level. This new build introduces useful helpers at the range level, i.e. for all cells of one or more areas.

• erase : erases all data, formulas and formatting (including merged cells)
• autofit : autofits the data by column
• calculate : calculates all formulas inside the range
• delete formulas : delete all formulas inside the range (keeps the last value)

For more information on the actual method calls, see the IXlsRange interface.

Build 2.9.0.6 of xlsgen improves the Html generation introduced in V2.9. Usually the Html output is made of a number of files containing either the Html markup itself, or what is known as Html resources (images, CSS stylesheets, attachments, ...)

So far, xlsgen would write Html markup with direct links to those Html resources, but in case these Html views are generated off a server (presumably using a web front-end), then a mechanism must be made available to ensure that those links provide a backlink to the resources otherwise the front-end will not be able to access these.

In other words, when a client application (hosted by a web browser, or not) makes a first call to generate an Html view, xlsgen will return the Html markup for the view, but additional client calls are needed for gathering the resources so that in the end the spreadsheet appears on screen with its pictures, charts and so on.

So this is exactly what is exposed in this new build, thanks to the worksheet.Export.Options.UrlPrefix property. For instance, if you set up this property as "127.0.0.1/spreadsheet/docid=xyz?img=", assuming your web server front-end is hosted at address 127.0.0.1, this ensures that all resources links are prefixed with such string, enabling backlinks to work.

From now on, the xlsgen installer is a .exe, not a .msi file anymore.

It makes little change for developers in practice. Simply download xlsgen, then double-clik on the installer to start the install process.

But it improves the way limited user environments (which is the default on Windows Vista) can install xlsgen by having the possibility to right-click on the .exe file on choose "Run as administrator" in order to overcome install restrictions.

Beginning today, xlsgen does not ship native 64-bit images anymore (in the /x64 folder) thanks to the fact that they are not needed anymore. Indeed, the isolated server (xlsgen.exe) provides at least all the capabilities of the 64-bit images and works well on a 64-bit operating system as a 32-bit image.

Even better, the previous use of a native 64-bit image of xlsgen.dll could not use charts for extraction, printing and rendering purposes since xlsgen relies on Microsoft Office web components, and they are not available as native 64-bit images.

Since the isolated server (xlsgen.exe) is a 32-bit image, it turns out that charts work as usual on a 64-bit operating system, even when your client application or host is a 64-bit image.

For more information on the isolated server, read here.

Build 2.9.0.5 of xlsgen adds two new predefined conditional formattings to the arsenal, namely IsNumber and IsFormula.

The first predefined conditional formatting (IsNumber) highlights cells which store numbers. The second predefined conditional formatting (IsFormula) highlights cells which store formulas.

Why are they useful? Both are used for diagnosing and understanding the underlying logic of a spreadsheet.

More information on the API is available here.

Build 2.9.0.4 of xlsgen fixes a problem related to SQL queries, namely the handling of (NULL) values from databases. A blank is now inserted.

Last week, we mentioned that Office 2007 SP2 supports reading and writing ODF files from the Open Document File format. We also mentioned the fact that ODF files produced by xlsgen were not opening correctly in Office 2007 SP2.

After further investigation, we can say for sure that Microsoft is actually not reading and writing ODF files, but a proprietary variant that we shall call MS-ODF.

Indeed, formulas are not written using the ODF syntax but Excel own internal syntax. The consequence is obvious, none of the formulas can be read from any ODF-compatible Office software such as OpenOffice and so on.

Even worse, formulas are used in cells, charts, conditional formattings and so on, which really means most of spreadsheet objects are actually written in a proprietary file format !

Last but not least, Microsoft did not ship this module complete. Actually many ODF objects, such as defined names and VBA macros are not supported at all (the differences are listed here), which in and of itself makes most real-world business spreadsheets useless.

It's hard to overstate how lousy this situation is.

xlsgen will have to expose a flag for saving an ODF file as an MS-ODF file. When will it be exposed? In the coming months.

Earlier last month, Microsoft shipped Office 2007 SP2, a revision of Office 2007 which not only provided fixes for their product line (including many with Excel, charts notably), but also a native support for OpenOffice ODF files (actually .ods spreadsheets).

Although xlsgen successfully passes tests with Office 2007 SP2, as mentioned a few days ago, it is not that bright when one tries to open a .ods spreadsheet generated by xlsgen in Excel 2007 SP2.

By "not that bright", we mean the fact that Excel 2007 SP2 only implements a fraction of the file format, and that many important elements from the ISO specification seem to be incorrectly implemented, for things as simple as column-wide formatting, chart data sources, or even formulas.

We are reviewing this and will update accordingly. But suffice to say that it is less than ideal to have Open Office spreadsheets open perfectly in Open Office (2.0 and above) and be stripped down from many elements when viewed in Excel 2007 SP2. To add worries, the pervasiveness of Office 2007 in the marketplace is such that Microsoft's client product is now probably the defacto client worldwide for opening Open Office spreadsheets.

In this new build, xlsgen makes it possible to create a time-scale axis in charts as opposed to a category-based axis which in turn allows grouping of values by day, by month and by year.

A new enumeration is introduced : enumChartAxisDataType
chartaxistype_automatic = 0,
chartaxistype_date_auto = 1,
chartaxistype_date_days = 2,
chartaxistype_date_months = 3,
chartaxistype_date_years = 4

An example follows.

Let us assume that we have figures sorted by date. We have 2 figures for year 2000, 1 figure for year 2001 and 1 figure for year 2002. If we create a bar chart from those figures, we'll see 4 bars as in :


A bar chart without explicit time-scale axis settings


But if we write the following statement :

chart001s0->XAxis[xlsgen::chartaxis_primary]->Type = xlsgen::chartaxistype_date_years;

this time the chart only shows 3 bars. Indeed two bars are grouped and aggregated into one for year 2000, as in :


A bar chart and explicit time-scale axis settings


The full C++ source code of the example follows :


xlsgen::IXlsWorkbookPtr wbk = engine->New( L"chart_dateaxis.xls" );

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

wksht001->Cell[1][3]->HtmlDate = L"<span format=\"mmm-yy\"><font color=#000000 size=11 name=\"Calibri\">avr.-2000</font></span>";
wksht001->Cell[1][4]->HtmlDate = L"<span format=\"mmm-yy\"><font color=#000000 size=11 name=\"Calibri\">août-2000</font></span>";
wksht001->Cell[1][5]->HtmlDate = L"<span format=\"mmm-yy\"><font color=#000000 size=11 name=\"Calibri\">janv.-2001</font></span>";
wksht001->Cell[1][6]->HtmlDate = L"<span format=\"mmm-yy\"><font color=#000000 size=11 name=\"Calibri\">janv.-2002</font></span>";

wksht001->DefaultStyle = 1;
wksht001->Label[2][2] = L"cat1";
wksht001->Number[2][3] = 2;
wksht001->Number[2][4] = 3;
wksht001->Number[2][5] = 4;
wksht001->Number[2][6] = 5;

wksht001->Label[3][2] = L"cat2";
wksht001->Number[3][3] = 5;
wksht001->Number[3][4] = 6;
wksht001->Number[3][5] = 5;
wksht001->Number[3][6] = 6;

xlsgen::IXlsChartPtr chart001s0 = wksht001->NewChart(xlsgen::charttype_bar2D, 5, 2, 17, 7);

xlsgen::IXlsChartDynamicDataSourcePtr datasource001s0 = chart001s0->DynamicDataSource;

xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss0 = datasource001s0->AddSerie();
serie001s0ss0->SeriesTitlesFormula = L"Sheet1!$B$2";
serie001s0ss0->DataLabelsFormula = L"Sheet1!$C$1:$F$1";
serie001s0ss0->SeriesValuesFormula = L"Sheet1!$C$2:$F$2";
xlsgen::IXlsChartDynamicDataSourceSeriesPtr serie001s0ss1 = datasource001s0->AddSerie();
serie001s0ss1->SeriesTitlesFormula = L"Sheet1!$B$3";
serie001s0ss1->DataLabelsFormula = L"Sheet1!$C$1:$F$1";
serie001s0ss1->SeriesValuesFormula = L"Sheet1!$C$3:$F$3";
chart001s0->XAxis[xlsgen::chartaxis_primary]->Options->NumberFormat = L"mmm\\-yy";
chart001s0->CustomProperties->Stacked = TRUE;
chart001s0->Legend->Show = xlsgen::chartlegend_right;

// group by year
chart001s0->XAxis[xlsgen::chartaxis_primary]->Type = xlsgen::chartaxistype_date_years;

wbk->Close();

This build closes the gap of an ongoing work related to charts.

For Excel 97-2003 charts, it makes it possible to combine data labels, for instance value and category.

For Excel 2007 charts, it makes it possible to automatically read features such as : rich titles, legend placement, series markers, axis hidden flag, axis number format . As well as showing the secondary X axis whenever set as such.

Thanks to these improvements, the chart rendering gets automatically improved, which in turn improves the feature level of print, preview, HTML and PDF generation.

This short message to announce that xlsgen successfully passes Office 2007 service pack 2 tests, a service pack released on April 28.

Performance improvements in PDF generation

15% faster

50% less memory consumption

Today xlsgen 2.9 is shipping.

A number of important new features (HTML output, Open Office output, isolated server, ...) detailed here.

And a ton more coming.

If you are using xlsgen 2.5, now is the time to think about moving to xlsgen 2.9

It's been approximately two years since the last major revision of xlsgen was made available. xlsgen 2.5 has had the longest life of all major releases in the history of xlsgen, and with no less than 144 updates since it shipped.

In a few days, xlsgen 2.9 will ship, starting a new cycle.

Like any major release, it comes with plenty of new features :

- HTML output
    data, formatting (including conditionals), pictures, charts


xlsgen can generate HTML files

- Open Office output
    native ouput (cells, objects, pictures, charts, ...)


xlsgen can generate Open Office files

- Isolated server : xlsgen.exe
    this new deployment mode introduces a spreadsheet server back-end retaining the power
    of the dll component (calculation, rendering, ...)

- Ability to edit a chart
    for instance, change a pie to a column chart

- Picture insertion optimization
    checksum-based, so that the same picture is never inserted twice

- All Excel 2007 functions supported

- Chart serie sampling mechanism
    split large data sets according to a sampling rate

- Formula support in the CSV import
    data integration and data quality needs

- Numerous improvements related to currency and date handling



The obvious question, what happens if you already own a license of xlsgen 2.5. It is quite simple. If you purchased a license in February, March or April, you are eligible for a free license of xlsgen 2.9. Otherwise, you will have to purchase a license again.

Build 2.5.0.144 of xlsgen fixes how two internal flags are read, used and preserved in Excel 97-2003 spreadsheets, related to conditional formats.

Build 2.5.0.143 of xlsgen improves the exposure of data validations in the object model. Namely the following properties are added :

- ValidationType : now a read/write property instead of write-only
- PicklistItems : returns the values if the data validation is a manual pick-list
- InformationTitle, InformationBody : respectively the title and body of the information message (data validation hints)
- ErrorTitle, ErrorBody : respectively the title and body of the error message (data validation fails)