xlsgen, native Excel generator

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.

xlsgen 2.9 build #59 : Hide/Show zeros option

ARsT design — Mon, 26 Apr 2010 09:09:06 GMT

Build 2.9.0.59 of xlsgen exposes, by customer request, the show/hide zeros option. This option sets whether cells containing a zero (entered or calculated) are displayed as blank cells or not. Default is : zeros are displayed.

This is obviously supported in both XLS and XLSX files.

Here is how to change the behavior : workbook.ShowZeros = FALSE

xlsgen 2.9 build #58 : Image in chart

ARsT design — Sun, 11 Apr 2010 16:12:55 GMT

Build 2.9.0.58 of xlsgen makes it possible to insert pictures in charts, so that pictures move when charts move, etc. This is supported for both XLS and XLSX files. For more information, see this.

Image in chart

Here is a sample code in C++ in order to use the feature :



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

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

    wksht->Label[2][2] = L"Janvier";
    wksht->Label[3][2] = L"Février";
    wksht->Label[4][2] = L"Mars";
    wksht->Label[1][3] = L"2000";
    wksht->Number[2][3] = 2;
    wksht->Number[3][3] = 6;
    wksht->Number[4][3] = 5;
    wksht->Label[1][4] = L"2001";
    wksht->Number[2][4] = 3;
    wksht->Number[3][4] = 7;
    wksht->Number[4][4] = 6;
    wksht->Label[1][5] = L"2002";
    wksht->Number[2][5] = 4;
    wksht->Number[3][5] = 8;
    wksht->Number[4][5] = 7;
    wksht->Label[1][6] = L"2003";
    wksht->Number[2][6] = 5;
    wksht->Number[3][6] = 9;
    wksht->Number[4][6] = 5;

    xlsgen::IXlsChartPtr chart = wksht->NewChart(xlsgen::charttype_bar2D, 
                                                     8, //row1
                                                     1, //col1 
                                                     16, //row2
                                                     5 //col2
                                                     );
    xlsgen::IXlsChartDataSourcePtr datasource = chart->DataSource;
    datasource->SeriesInRows = TRUE;
    datasource->Range = L"R1C2:R4C6";

    xlsgen::IXlsPicturePtr pic = chart->Shapes->Pictures->Add(L"input\\pic300x200.png");
    pic->LeftColumn = 3000; // 1/4000-th of the width of the chart
    pic->RightColumn = 4000; // 1/4000-th of the width of the chart
    pic->TopCell = 3; // 1/4000-th of the height of the chart
    pic->BottomCell = 700; // 1/4000-th of the height of the chart
    pic->AlternativeText = L"my alternative text";

   wbk->Close();

xlsgen 2.9 build #57 : Feature: group by

ARsT design — Wed, 31 Mar 2010 11:02:20 GMT

Build 2.9.0.57 of xlsgen adds a Group by capability to xlsgen. Rows of data can be consolidated into groups and visually represented as such.

Here is an example, before and after :

An example of Group by, a new capability of xlsgen

And here is how to set it up :


define a style for the "background" rows
xlsgen::IXlsStylePtr styleGray = wksht001->NewStyle();
styleGray->Font->Color = 0x888888;

define the range where the group by will apply
xlsgen::IXlsRangePtr r1 = wksht001->NewRange(L"C4:E20");

group by (1st column is used as sorting clue)
xlsgen::IXlsConditionalFormattingPtr cf1 = r1->NewConditionalFormatting();
cf1->Style = styleGray;
cf1->PredefinedCondition->GroupBy(1);

Since this Group by is actually a predefined conditional formatting (a two-part conditional formatting to be accurate), it is dynamically updated as the underlying data changes. And it is also rendered in print/preview/PDF.

xlsgen 2.9 build #56 : Fix for advanced chart rendering (II)

ARsT design — Sat, 27 Mar 2010 15:40:36 GMT

Build 2.9.0.56 of xlsgen includes another attempt (see build 2.9.0.53) to fix advanced chart rendering issues involving multiple chart types.