xlsgen, native Excel generator

xlsgen 2.9 build #78 : Automatic re-scoping of local named ranges

ARsT design — Thu, 26 Aug 2010 10:34:25 GMT

Build 2.9.0.78 improves the handling of local named ranges, particularly during a worksheet Duplicate() method call.

Earlier builds improved the handling of local named ranges, but it was still up to the client application to make changes to formulas making references to local named ranges, after a worksheet Duplicate() for instance.

Beginning with this build, the re-scoping of affected local named ranges (which are therefore brought over from the source worksheet) is automatic. There is no need to manually updated formulas, be it in charts, conditional formattings, formulas in cells, ...

xlsgen 2.9 build #77 : Fix for local named ranges

ARsT design — Sun, 22 Aug 2010 15:47:21 GMT

Build 2.9.0.77 fixes two problems related to local named ranges, i.e. named ranges whose definition is limited to a single worksheet.

xlsgen improves the way it exposes local named ranges by making sure it's fully qualified, including the worksheet name, in appropriate scenarios. On the other hand, xlsgen also improves how fully qualified local named ranges are handled.

Overall this improves scenarios using local named ranges.

No source code change needed.

xlsgen 2.9 build #76 : Fix for XLSX conditional formattings

ARsT design — Mon, 16 Aug 2010 08:26:02 GMT

Build 2.9.0.76 of xlsgen fixes a minor glitch which prevented from writing back updated conditional formattings from existing ones in XLSX files.

xlsgen 2.9 build #75 : Re-inclusion of the native 64-bit build

ARsT design — Fri, 6 Aug 2010 12:52:57 GMT

Build 2.9.0.75 of xlsgen re-includes the native 64-bit build of xlsgen.

Even though our isolated server is a perfect solution for 64-bit scenarios, there is still sometimes a need for the native 64-bit version of our core dll engine.

This build provides the files in question in the /x64 folder of the install. To use the 64-bit build, simply replace the files in question in your install.

Why the native 64-bit build is worth having?

1) Runs direct, not through the WOW64 layer, which improves speed
2) The dll needs not marshall method calls back and forth, unlike our isolated server, which improves speed
3) The dll allows effective use of the 64-bit memory bandwidth, a big difference with the 2GB limit of the 32-bit world
4) More convenient activation (than the isolated server), results in easier deployments as well

Of course the 64-bit build has limits as well, which are the limits of xlsgen dependencies (not xlsgen itself) :
1) Chart rendering unavailable since Microsoft OWC components do not exist for 64-bit
2) ODBC data source querying unavailable since there is no 64-bit version of this either

xlsgen 2.9 build #74 : Column optimization

ARsT design — Wed, 28 Jul 2010 16:54:35 GMT

Build 2.9.0.74 of xlsgen makes internal changes related to how columns are managed, factored in, and stored in XLS and XLSX files. The changes result in a more compact representation which in turn reduces the processing time.

No source code change needed.

xlsgen 2.9 build #73 : Improved chart rendering

ARsT design — Fri, 16 Jul 2010 12:07:18 GMT

Build 2.9.0.73 of xlsgen improves the rendering of charts.

It benefits scenarios such as print/preview/PDF/HTML.

Chart rendering now supports borders (styles, width, color), area fill (solid fill, pattern fill, gradient fill, texutre fill and image fill). Plus a ton more formatting options related to fonts, etc.

Better chart rendering in xlsgen

No source code change needed.

xlsgen 2.9 build #72 : Java OpenFromMemory

ARsT design — Thu, 8 Jul 2010 09:27:01 GMT

Build 2.9.0.72 of xlsgen adds an implementation for the Java OpenFromMemory() method call, which was lacking so far. This scenario enables the reading/updating of Excel "files" in memory (XLS/XLSX). An example of using this method call can be found here.

xlsgen 2.9 build #71 : Exposure of table named ranges

ARsT design — Tue, 6 Jul 2010 11:08:30 GMT

Build 2.9.0.71 of xlsgen adds exposure of table named ranges. Table named ranges are defined names introduced by Excel 2007 (and available in Excel 2010 as well) which are automatically created when the Insert / Table menu function is used in Excel.

Exposure of table named ranges

Technically speaking, the Excel user interface shows the table named ranges in the list of defined names even though they are not stored there at all. Table named ranges are stored in their respective parts inside the file. But, this is convenient for the user. And as a result xlsgen does this as well.

To the developer, table named ranges appear as regular defined names. A new property however is introduced in order to differentiate real defined names from table named ranges (the IsTable property exposed in the IXlsName interface).

xlsgen 2.9 build #70 : Fix for sorting values

ARsT design — Fri, 2 Jul 2010 10:15:54 GMT

Build 2.9.0.70 of xlsgen improves the accuracy when sorting values (range-based).

No source code change needed.

xlsgen 2.9 build #69 : Inspecting local named ranges

ARsT design — Wed, 30 Jun 2010 12:25:42 GMT

Build 2.9.0.69 of xlsgen adds a Worksheet property to the IXlsDynamicRange interface making it possible, when enumerating existing named ranges, to know if the named range in question has a local scope or not (its scope is limited to a single worksheet), and accordingly retrieve the name of the worksheet in question. All non local named ranges have a global scope, in which case the worksheet property returns an empty string.

xlsgen 2.9 build #68 : Fixes for worksheet duplication

ARsT design — Fri, 25 Jun 2010 20:38:32 GMT

Build 2.9.0.68 of xlsgen fixes a number of problems related to worksheet duplication in XLSX files.

Among others,
- wrong "rId" in workbook part after deletion of worksheet
- improvement : actually delete the worksheet part relations in case of a worksheet deletion
- proper local sheet id reindexing
- default col width and row height in duplicated sheets
- outside col width
- copy row height from duplicated sheets
- missing pictures from duplicated sheets
- print paper size
- rich string with alternate formatting run
- distributed vertical alignment in cells
- better custom/autofit row height handling
- preservation of quarter-turn text
- duplication of local named ranges (applies to XLS files as well)

No source code change needed.

xlsgen 2.9 build #67 : Improved extraction of chart details (III)

ARsT design — Tue, 15 Jun 2010 08:41:11 GMT

Build 2.9.0.67 continues on the previous two builds and this time improves both XLS and XLSX chart formatting details extraction.

What is improved is the ability to read fills such as pattern, gradient and texture.

What is also improved is our automatic source code generation tool, where all of this is now exposed in the programming language of your choice.

xlsgen 2.9 build #66 : Improved extraction of chart details (II)

ARsT design — Wed, 9 Jun 2010 07:42:40 GMT

Build 2.9.0.66 of xlsgen adds the same improvements than the previous build, 2.9.0.65, but this time applied to XLSX files (i.e. Excel 2007 and Excel 2010).

Among others, hidden axis, hidden legend, legend placement, maximum per axis, interval ticks between units, color (of area), round corners, border type, width, style and color, series-level color and border, tick marks (inside/outside/none), ticks hide independent of axis hide, major/minor gridlines border, copy of font settings.

This is an interesting improvement for the following scenarios : introspection, edit, copy, conversion and rendering.

No source code change needed.

xlsgen 2.9 build #65 : Improved extraction of chart details

ARsT design — Thu, 3 Jun 2010 14:13:38 GMT

Build 2.9.0.65 of xlsgen improves the extraction of chart formatting details from XLS files.

Among others, hidden axis, hidden legend, legend placement, maximum per axis, interval ticks between units, color (of area), round corners, border type, width, style and color, series-level color and border, tick marks (inside/outside/none), ticks hide independent of axis hide, major/minor gridlines border, copy of font settings.

This is an interesting improvement for the following scenarios : introspection, edit, copy, conversion and rendering.

No source code change needed.

xlsgen 2.9 build #64 : Pictures in headers/footers

ARsT design — Thu, 27 May 2010 15:06:54 GMT

Build 2.9.0.64 of xlsgen adds support for pictures in headers/footers. Works with XLS and XLSX files.

It works as follows : the &G token can be used (G is for Graphics), immediately followed by the full filepath of the picture in question, enclosed in double quotes.

An example : centered picture = &C&G"c:\tmp\seasight.gif"

(warning : escaping quotes and/or backslashes may be needed depending on your programming language).

For more information on how to setup headers/footers, take a look here.

Pictures in headers/footers

Obviously, removing existing pictures is just as simple : remove the &G token.

xlsgen 2.9 build #63 : Improved ODF output

ARsT design — Wed, 19 May 2010 09:56:26 GMT

Build 2.9.0.63 of xlsgen improves the OpenOffice export capabilities by adding comments and text boxes to the output. More information here.

xlsgen 2.9 build #62 : Improved chart data labels

ARsT design — Mon, 10 May 2010 09:32:30 GMT

Build 2.9.0.62 of xlsgen makes two improvements to chart data labels.

First, a quick recap of what are data labels,

Improved chart data labels

The two improvements are :
- formula-governed data labels
- rendering of data labels (print, preview, PDF, HTML)

The first improvement makes it possible to use formulas as opposed to literal values in order to govern the content of chart data labels. It requires specific code, see below for an example of that.

The second improvement automatically renders chart data labels. No code change needed.

An example of formula-governed data labels,

xlsgen::IXlsChartPtr chart41 = wksht->NewChart(xlsgen::charttype_pie2D,
        17, //row1
        2, //col1
        25, //row2
        6); //col2

xlsgen::IXlsChartDataSourcePtr datasource41 = chart41->DataSource;
datasource41->SeriesInRows = TRUE;
datasource41->Range = L"Sheet1!R1C2:R2C6";
chart41->SeriesByIndex[1]->SeriesDataLabels->Show = xlsgen::datalabels_valuepercent;
chart41->SeriesByIndex[1]->SeriesDataLabels->DataLabelElements[1]->Options->Patterns->Area->Type =
xlsgen::chartareatype_custom;
chart41->SeriesByIndex[1]->SeriesDataLabels->DataLabelElements[1]->Formula = L"Sheet1!$B$3";
chart41->SeriesByIndex[1]->SeriesDataLabels->DataLabelElements[1]->Options->Patterns->Area->Gradient->SingleColor->GradientStyle =
xlsgen::gradientstyle_diagonalUp0;
chart41->SeriesByIndex[1]->SeriesDataLabels->DataLabelElements[1]->Options->Patterns->Area->Gradient->SingleColor->Color =
0x0000DD;
chart41->SeriesByIndex[1]->SeriesDataLabels->DataLabelElements[1]->Options->Patterns->Area->Gradient->SingleColor->LightThreshold =
100;

xlsgen 2.9 build #61 : Calculated chart axis min/max

ARsT design — Tue, 4 May 2010 14:46:16 GMT

Build 2.9.0.61 of xlsgen enables the calculation of chart axis min/max values when introspecting charts in a spreadsheet.

The min/max values can be set or get manually, but when the option is set as automatic, xlsgen makes it possible to query for the underlying min/max values of a given axis (Y for instance), which in turn are calculated based on the chart data source.

This feature allows better rendering (better layout) of charts in client applications.

Example :

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"inputfile.xls", L"" );
xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];
xlsgen::IXlsChartPtr chart = wksht->Charts->Item[1];
double ymin = chart->YAxis[xlsgen::chartaxis_primary]->Scale->MinimumValue;
double ymax = chart->YAxis[xlsgen::chartaxis_primary]->Scale->MaximumValue;

xlsgen 2.9 build #60 : Improved HTML output

ARsT design — Thu, 29 Apr 2010 19:20:49 GMT

Build 2.9.0.60 of xlsgen adds support for hyperlinks and cell anchors to the HTML output.

This improvement enables far richer online/navigation scenarios.

By writing hyperlinks, xlsgen enables sheet to sheet links as well as sheet to everything links.

By writing cell anchors, xlsgen enables cell bookmarking so that creating when clicking a link to Sheet2!A120 for instance, the web browser will indeed navigate to Sheet2 and scroll down to row 120 and column A. Cell anchors are materialized by non-visible <a name="A120">...</a> markup in the HTML output, in every cell.

Office 2010 final : successful tests!

ARsT design — Tue, 27 Apr 2010 16:12:09 GMT

xlsgen already passed successful tests related to early Office 2010 builds. Now it passes the final Office 2010 build as well.

Office 2010 was released to the world last week. And xlsgen has to ensure to work with it seamlessly.

Early tests were passed : Office 2010 technical preview (back in July 2009), then Office 2010 beta (later in 2009).

What did xlsgen pass?
- full read/write XLS file compatibility : 100% open success, 100% feature retaining

- full read/write XLSX file compatibility : 100% open success, 100% feature retaining

- ability to handle future XLSX (ISO 29500) standard variants : for instance, xlsgen handles date-type cells, a feature introduced in an earlier build of Office 2010, then removed by Microsoft in the final build.

Achieving this success required effort over time. No less than 3 builds were needed in the 2.9 development codebase. Which means if you are using 2.9, you'd better download the latest version for free (tests were done with xlsgen 2.9.0.59). Which also means if you are using xlsgen 2.5, 2.1 or even older releases, you will have to upgrade or you will face issues (50+ issues related to Office 2010 compatibility were fixed since the early tests). Of course, upgrading to 2.9 from 2.5, 2.1 or an even older version requires a new license.