OpenFromMemory(), the mechanism for creating and updating Excel spreadsheets entirely in memory was working well with .XLS memory buffers, but less well with .XLSX memory buffers. In fact, it lacked implementation at all regarding .XLSX memory buffers.

This is what is addressed by build 2.9.0.51 of xlsgen.

Build 2.9.0.50 of xlsgen makes it possible to set timeout for SQL queries. Command and connection timeouts are respectively 30 seconds and 15 seconds by default, so there was a need to be able to change these. Zero is a special value which means indefinite wait.

The settings are to be done before the connection string.

The two new properties, CommandTimeout and ConnectionTimeout are exposed in the IXlsTableDataSource interface.

Build 2.9.0.49 fixes 3 problems with auto-filters in XLS files.

1) better reading of existing auto-filters (namely, auto-filters without custom expressions).
2) better deletion of existing auto-filters (namely, auto-filters without custom expressions)
3) better handling of sheet tab reordering with auto-filters

No code change needed to take advantage of these.

Build 2.9.0.48 of xlsgen adds a couple helpers for the VBScript language. These are necessary because the VBScript language does not lend itself too much to data manipulations. A new helpers interface is available. Particularly these are useful for wrapping ILockBytes in VARIANTs (VBScript's native data type) and vice versa.

Here is a code sample :

' xlsgen_clientside_read.vbs
' to run this code, double-click on the .vbs file
'
' demonstrates how to update an existing .xls file in memory using VBScript
' for the purpose of the sample, the output buffer is written back to a file

Dim szProcessingFolder
szProcessingFolder = "c:\"

Dim engine
Set engine = CreateObject("ExcelGenerator.ARsTdesign")

' create a memory buffer host
Dim lb
lb = engine.helpers.ILockBytes_New

' open an existing Excel file, and retrieve a workbook
Dim wbk
Set wbk = engine.OpenInMemory(szProcessingFolder & "input.xls", lb, 3)

' create a new worksheet
Dim wksht
Set wksht = wbk.AddWorksheet("sheet1")

' sample code
wksht.Label(9,1) = "hello world!"
' your code begins here
' ...

wbk.Close

' convert the output to a regular memory buffer (byte array)
Dim byteArrayBuffer
byteArrayBuffer = engine.helpers.ILockBytes_Write(lb)

' for the purpose of the sample, write back to a regular file
engine.helpers.WriteFile szProcessingFolder & "myfile.xls", byteArrayBuffer

' free the memory
Set engine = Nothing

Build 2.9.0.47 of xlsgen adds auto-adjusting capabilities.

Automatically adjusting named ranges is an optional mechanism in xlsgen which automatically updates named ranges according to the update of the underlying data being referenced.

Here is a simple example below.


Auto-adjusting named ranges

On the left side, we initially have a bunch of rows and a named range myrange. Then when the data is updated, a new row is added (Product13). Thanks to xlsgen's built-in auto-adjusting named ranges enabled, the named range myrange gets automatically updated, on the right side of the capture below.

Here is how to do it, in C++ :

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

// will return areaBeforeUpdate = R2C1:R14C5
_bstr_t areaBeforeUpdate = wksht->Range[L"myrange"]->Range;

// enable auto-adjusting of named ranges
wbk->AutoAdjustDefinedNames = TRUE;

// insert a new row of data
wksht->Label[15][1] = L"Product13";

// will return areaAfterUpdate = R2C1:R15C5, notice the change
_bstr_t areaAfterUpdate = wksht->Range[L"myrange"]->Range;

Build 2.9.0.46 of xlsgen introduces a capability that is not available in Excel. It's the ability to build ranges based on formulas.

One of the obvious uses for this is the style formatting, for instance change the color of all cells whose value is greater than a given threshold, but obviously any functionality exposed at the range-level is available as well.

In the code sample below, what is shown is how to change the color of all cells greater than 20.


Formula-based selection

And the corresponding source code (C++) is :

// build a range of cells governed by value > 20
// notice the use of relative cell references in : F2 > 20
xlsgen::IXlsRangePtr r = wksht->NewRange(L"F2:F14")->SelectByFormula(L"F2 > 20");

// returns s = R2C6:R3C6;R9C6:R11C6
_bstr_t s = r->Range;

// apply color to those cells
r->Style->Pattern->Pattern = xlsgen::pattern_solid;
r->Style->Pattern->BackgroundColor = 0xFF0000;

r->Apply();

Build 2.9.0.45 includes no less than 15 fixes and improvements.

In details,

- support formulas of the form "=#REF! $B$1:$B$2" (i.e. including error tokens)
- support formulas of the form "=$A$1:$B$2 $B$1:$B$2" (i.e. intersections)
- support formulas of the form "=$A2:INDIRECT()..." (i.e. formula-based range construction)
- support formulas of the form "=ADDRESS(INDIRECT():INDIRECT())..." (i.e. left and right hand formula-based range construction)
- support multiple conditions for rendering conditional formattings. So far, the first condition evaluated to true would cancel evaluation (and therefore inclusion of styles) of other conditions.
- read existing XLSX comments
- combine vumeter and sparklines into the same component. No more vumeter.ocx in the installer.
- support CORREL and PEARSON functions in the calc engine.
- fix a GPF when resaving a XLSX file generated by xlsgen. The problem was a missing fragment for custom data labels in charts.
- write a default theme part in all new XLSX files. Necessary for improving the compatibility with OpenOffice, most notably charts.
- better preserve the print gridlines flag from XLSX files.
- better compute the bounding area of conditional formattings in XLS files.
- better handling of HYPERLINK records in XLS files.
- preserve the localSheetId attribute from defined names in XLSX files. This caused file errors in Excel.
- support GetSerieByName for charts using dynamic sources.

No source code changed are needed to take advantage of these.

Continuing on previous build, build 2.9.0.44 of xlsgen improves text boxes.

All of it is for XLS and XLSX files.

With this new build, text boxes can be managed, and they are now rendered.

To manage text boxes means to be able to enumerate, introspect, extract as picture and delete text boxes.

To render text boxes means that text boxes are now part of print, preview, PDF and even HTML.

For more information, see the page on the documentation.

Build 2.9.0.43 of xlsgen improves text boxes in XLS and XLSX files.

It applies to both floating text boxes and chart text boxes.

Text boxes now support formulas and formatting styles.

Supporting formulas means that the content of the text box can be governed by the content of another cell, in the same spreadsheet, or in another spreadsheet.

Supporting styles means that the content of the text box can be customized to support background color, a border and the alignment. Note that regarding the font formatting, it is already supported by using rich labels as opposed to regular labels.

For more information and a code sample, see the documentation page related to text boxes.

xlsgen successfully passes .ods, .xls and .xlsx spreadsheet generation tests for OpenOffice 3.2 RC1

Christmas time : the deployment license of xlsgen is 15% OFF the regular price

Build 2.9.0.42 of xlsgen adds alternative text to drawing objects such as text boxes, chart text boxes, comments and charts.

The alternative text is what appears in the Web tab of a drawing object, when you right-click on its Properties in Excel.

In xlsgen, so far alternative text was only supported for pictures (IXlsPicture).

Now it's added to the following interfaces : IXlsTextBox, IXlsChartTextBox, IXlsComment, IXlsChart.

Build 2.9.0.41 of xlsgen introduces caching of chart values in XLS and XLSX files. For users, this changes nothing. For applications expecting XLS and XLSX files to contain cached values of charts, this build enables the scenario. It is made possible by the always improved chart engine in xlsgen, which does read/write and rendering among other things.

Build 2.9.0.40 of xlsgen improves how charts are handled in xlsgen.

So far when a client application wanted to duplicate the content of a worksheet onto another, charts would be also duplicated as long as they were from the same workbook. With build 2.9.0.40, charts are now duplicated even if they are from another workbook. And that works for XLS and XLSX files.

There is no change expected in the client application.

Build 2.9.0.39 improves on-the-fly charts. For the record, on-the-fly charts are charts whose data source is an array of constants, not cell references.

This build improves read/write and rendering scenarios. For both XLS and XLSX files.

For more information on on-the-fly charts, see click.

Beginning with build 2.9.0.38 of xlsgen, font sizes are expressed using floating-point values, not integers, improving the accuracy not only the desired intent, but also of processing such as rendering.

This is an interface change. Both IXlsFont and IXlsChartFont interfaces have been updated to reflect this.

If you are using an interop language such as .NET, Delphi and so on, make sure to recompile your project in order to resync your interop library.

Build 2.9.0.37 improves the layout of charts in Excel 2010 for files generated by xlsgen. This build was announced a few days ago and allows to ensure that Excel 2010 is neutral.

Excel 2010 beta, which is now publically available (alpha bits were available since last July) has just been tested with xlsgen. Results are 100% successful. Files generated by xlsgen open and work well with Excel 2010 beta. Not to mention that xlsgen already supports the new functions added in Excel 2010 (more about this here).

What needs improvements though is the charting layout. Obviously the Excel 2010 chart engine does not render XLS or XLSX charts the way it used to do in previous Excel versions (including Excel 2007). Which means xlsgen has to add the little details here and there to ensure that the layout renders correctly. It will be the subject of next build, and possibly a few more in the future.

Build 2.9.0.36 of xlsgen continues a recent build improving the print/preview/PDf processing. This time it improves the accuracy of rendering by internally using floating-point values instead of integers for anything related to the layout.

Build 2.9.0.35 of xlsgen is a performance pack. It improves speed, memory use and file size of XLS and XLSX files.

There are three performance improvements.

The first one is for reading large XLS files. It is a continued work. Some transient buffers are removed, resulting in a lot less memory used, not to mention fragmentation. To take advantage of this improvement, simply use the updated version of xlsgen.

The second one is for reducing the file sizes by taking advantage of the built-in shared formulas. This implies an active will to use a mechanism exposed by xlsgen, in the name of range-based formulas.

For instance, by running the following :

worksheet.NewRange("A4:A20").Formula = "=...";

the formula is actually only written litterally in the first cell (A4), and then for all other cells (A5 to A20), only a reference to cell A4 is stored, saving the size of the formula byte codes for each cell. This results in dramatic reduction to any XLS and XLSX spreadsheet making intense use of such formulas.

The third one improves the bulk insertion (merged cells, conditional formattings, ...) and the speed is improved by using internal clipping structures that reduce the time to figure out if existing objects need to be updated, etc. Again, to take advantage of that, simply use the updated version the xlsgen.