xlsgen, native Excel generator

xlsgen 2.9 build #54 : Fix for number formats

ARsT design — Wed, 17 Mar 2010 22:39:34 GMT

Yet another fix for number formats in xlsgen. This time the handling of * characters, which are special characters (unless escaped as in \*).

xlsgen 2.9 build #53 : Fix for advanced chart rendering

ARsT design — Tue, 16 Mar 2010 15:13:56 GMT

Build 2.9.0.53 of xlsgen fixes a problem when rendering charts, a case with multiple chart types in the same chart.

xlsgen 2.9 build #52 : Add worksheet before/after

ARsT design — Mon, 15 Mar 2010 10:39:07 GMT

Build 2.9.0.52 of xlsgen adds a forgotten and yet handy feature, the ability to decide where new worksheet tabs are created. So far, the only method for adding worksheet tabs to a spreadsheet was "AddWorksheet" which adds a new tab at the tail of existing tabs.

Sure, tabs can always be reordered, but why not let client applications choose where to put the new tabs in the first place?

Hence two new methods "AddWorksheetBefore" and "AddWorksheetAfter" which, as the names suggest, add new tabs respectively before and after the current worksheet. And that is the reason why the methods are exposed at the worksheet level, not at the workbook level.

xlsgen 2.9 build #51 : Fix for OpenFromMemory()

ARsT design — Mon, 8 Mar 2010 20:21:38 GMT

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.

xlsgen 2.9 build #50 : SQL query timeout

ARsT design — Thu, 4 Mar 2010 10:20:45 GMT

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.

xlsgen 2.9 build #49 : Fix for auto-filters

ARsT design — Thu, 18 Feb 2010 15:17:37 GMT

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.

xlsgen 2.9 build #48 : helpers for VBScript

ARsT design — Thu, 11 Feb 2010 13:44:54 GMT

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

xlsgen 2.9 build #47 : Auto-adjusting named ranges

ARsT design — Thu, 4 Feb 2010 14:56:07 GMT

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;

xlsgen 2.9 build #46 : Formula-based selection

ARsT design — Wed, 27 Jan 2010 12:33:53 GMT

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();

xlsgen 2.9 build #45 : Fix pack (Jan 2010)

ARsT design — Sat, 16 Jan 2010 07:29:42 GMT

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.

xlsgen 2.9 build #44 : Improved text boxes (II)

ARsT design — Sun, 10 Jan 2010 17:06:11 GMT

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.

xlsgen 2.9 build #43 : Improved text boxes

ARsT design — Sun, 3 Jan 2010 10:21:17 GMT

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.

Support for OpenOffice 3.2 RC1

ARsT design — Tue, 22 Dec 2009 14:36:56 GMT

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

Christmas time : 15% OFF the regular price

ARsT design — Thu, 17 Dec 2009 12:27:12 GMT

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

xlsgen 2.9 build #42 : Alternative text

ARsT design — Wed, 16 Dec 2009 08:32:48 GMT

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.

xlsgen 2.9 build #41 : Caching chart values

ARsT design — Mon, 14 Dec 2009 18:00:11 GMT

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.

xlsgen 2.9 build #40 : Improved chart duplication

ARsT design — Thu, 10 Dec 2009 18:13:24 GMT

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.

xlsgen 2.9 build #39 : Improved on-the-fly charts

ARsT design — Thu, 3 Dec 2009 07:47:06 GMT

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.

xlsgen 2.9 build #38 : Improved accuracy of font sizes

ARsT design — Mon, 30 Nov 2009 14:14:32 GMT

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.

xlsgen 2.9 build #37 : Fix for Excel 2010 charts

ARsT design — Tue, 24 Nov 2009 19:05:34 GMT

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.