xlsgen, native Excel generator

xlsgen 3.0 build #32 : Improved CSV export

ARsT design — Thu, 17 May 2012 12:41:17 GMT

Build 3.0.0.32 of xlsgen improves the CSV export by making it possible to render numbers according to their associated number formats.

For instance without the option, 8.506 with an associated 0.00 number format will be exported as 8.506. When the number formatting option is set, see the options interface, 8.506 is exported as 8.51, assuming the user's regional settings for decimal separators is the dot character (i.e. 8,51 if the user's regional settings for decimal separators is the comma character).

xlsgen 3.0 build #31 : Rendering of vector shapes (II)

ARsT design — Wed, 9 May 2012 22:25:35 GMT

Build 3.0.0.31 of xlsgen improves the rendering of vector shapes in two ways :

- support for rendering vector shapes inside charts

- support for 14 additional basic shape types, pictured below :

14 additional basic shape types rendered by xlsgen

xlsgen 3.0 build #30 : Column and Row move

ARsT design — Fri, 4 May 2012 12:50:53 GMT

Build 3.0.0.30 of xlsgen adds the ability to move around a group of columns or rows.

Moving columns is one of the column/rows helping tools to move data consistently.

It's very simple to use : worksheet.Columns("A:A").Move(2) moves the content of column A, two steps to the right. The parameter can be negative or positive depending on which direction you want it to go.

And it works the same for rows.

xlsgen 3.0 build #29 : Rendering of vector shapes

ARsT design — Fri, 20 Apr 2012 19:00:47 GMT

Build 3.0.0.29 of xlsgen adds rendering of vector shapes from XLS and XLSX files.

Rendering is used in print/preview/PDF/XPS generation.

The basic shape types supported are lines, arrows, rectangles and ovals :

Rendering vector shapes in xlsgen

Regarding arrows, all begin and end cap styles are supported.

And also basic arrows :

Rendering vector shapes in xlsgen (bis)

Associated text is rendered as well. And of course a number of formatting styles are supported (border width, fill color, horizontal alignment, ...).

xlsgen 3.0 build #28 : Fix for XLSX strings

ARsT design — Mon, 16 Apr 2012 22:53:57 GMT

Build 3.0.0.28 of xlsgen has a fix for particular strings in XLSX files beginning or ending with non-printable characters.

xlsgen 3.0 build #27 : Fix for DDE links

ARsT design — Fri, 13 Apr 2012 08:53:35 GMT

Build 3.0.0.37 of xlsgen fixes a problem for reading DDE (and OLE) links in formulas.

New pricing scheme

ARsT design — Sun, 8 Apr 2012 13:32:42 GMT

Beginning today, xlsgen gets its price back to normal, that is, 390 euros. It had been discounted 15% for two months or so.

In addition to this, an upgrade price is finally introduced. For all new purchases, if you are an existing xlsgen customer, owning a license of a previous major version such as 2.9, 2.5, etc., you can now purchase the upgrade license for as little as 250 euros, a 35% discount on the regular price.

Once purchased, send us by email the current license file (xlsgen.license.lic) as well as your previous purchase info, and we will send your 3.0 registration number.

xlsgen 3.0 build #26 : Improved text boxes

ARsT design — Thu, 5 Apr 2012 10:03:53 GMT

Build 3.0.0.26 of xlsgen improves text boxes formattings.

Before that build text boxes only supported a fill color and a border. Beginning with this build, text boxes can support ALL border formattings (style, width, color) and all area formattings (fill, gradients, texture, pattern and images).

This support is for XLS and XLSX files. And it supports both floating text boxes and chart text boxes.

Here is a preview of what can be created with build 3.0.0.26 with a few lines of code :

Border and area formattings for text boxes

And here is the corresponding source code :

xlsgen::IXlsTextBoxPtr textbox = worksheet->NewTextBox(2,3, 5,6, 0,200, 80,300);
textbox->Label = L"text box formattings";

textbox->Patterns->Borders->Type = xlsgen::chartbordertype_custom;
textbox->Patterns->Borders->Weight = xlsgen::chartborderweight_hairline;
textbox->Patterns->Borders->Style = xlsgen::chartborderstyle_dashdot;
textbox->Patterns->Borders->Color = xlsgen::colorRed;

textbox->Patterns->Area->Type = xlsgen::chartareatype_custom;
textbox->Patterns->Area->Gradient->SingleColor->GradientStyle = xlsgen::gradientstyle_diagonalUp1;
textbox->Patterns->Area->Gradient->SingleColor->Color = 0x0000DD;
textbox->Patterns->Area->Gradient->SingleColor->LightThreshold = 100;

xlsgen 3.0 build #25 : Time-based conditional formattings

ARsT design — Thu, 29 Mar 2012 13:58:01 GMT

Build 3.0.0.25 of xlsgen adds support for time-based conditional formattings for XLS and XLSX files.

With this build, xlsgen completes support for all conditional formattings (some were recently introduced in Excel 2007 and Excel 2010).

Time-based conditions are, as the name suggests, a mechanism for highlighting cells whenever a date matches the condition. For instance a condition may be "tomorrow", in turn if we are say thursday March 29, 2012, then all cells having a date such as March 30, 2012 will be highlighted.

Time-based conditions are presented in the xlsgen object model as predefined conditions, and they are internally translated into Excel formulas. Therefore, they are also evaluated accordingly (for instance, when rendering a sheet).

A new enumeration type makes its appearance :

typedef enum
{
conditionaltimeperiod_today = 0;
conditionaltimeperiod_yesterday = 1;
conditionaltimeperiod_tomorrow = 2;
conditionaltimeperiod_last7days = 3;
conditionaltimeperiod_thismonth = 4;
conditionaltimeperiod_lastmonth = 5;
conditionaltimeperiod_nextmonth = 6;
conditionaltimeperiod_thisweek = 7;
conditionaltimeperiod_lastweek = 8;
conditionaltimeperiod_nextweek = 9;
} enumConditionalTimePeriod;

Here is an example showing how to use the new time-based conditional formattings :

xlsgen::IXlsConditionalFormattingPtr cf = wksht001->NewRange(L"C2:C8")->NewConditionalFormatting();
cf->PredefinedCondition->DatesMatchingPeriod(xlsgen::conditionaltimeperiod_nextmonth);
cf->Style = style0002;

xlsgen 3.0 build #24 : Text-based conditional formattings

ARsT design — Wed, 21 Mar 2012 10:46:36 GMT

Build 3.0.0.24 of xlsgen adds 4 predefined conditional formattings to XLS and XLSX files.

These are text-based conditional formattings, i.e. conditional formattings which highlight cells based on a rule governed by a text condition.

The 4 text conditions are :
- beginningWith
- endingWith
- containing
- notContaining

Internally, the 4 predefined conditional formattings are replaced by Excel formulas and stored in XLS and XLSX files as such.

Here is an example :

xlsgen::IXlsConditionalFormattingPtr cf = wksht->NewRange(L"R2C1:R10C1")->NewConditionalFormatting();
cf->PredefinedCondition->BeginsWith(L"apple");
cf->Style = style2;

xlsgen 3.0 build #23 : Faster PDF generation

ARsT design — Tue, 13 Mar 2012 15:16:29 GMT

Build 3.0.0.23 of xlsgen improves the speed at which PDF files are generated.

In fact, by streamlining the two-pass process to just one, PDF files are now faster to generate and it is proportional to the number of pages.

Expected figures are 5% for single pages PDF, 10% for 10 pages PDF and so on.

xlsgen 3.0 build #22 : Fix pack

ARsT design — Wed, 29 Feb 2012 18:07:14 GMT

Build 3.0.0.22 of xlsgen has 3 fixes :

- support for center across selection formatting style in rendering (print/preview/PDF)

- support the + and - operators in page number syntax used in headers/footers : for instance &[Page]+10 will evaluate correspondly now instead of understanding +10 as a string

- support for zig-zag ranges in formulas as in A1:D1:D4:G4

No source code change needed

xlsgen 3.0 build #21 : Database functions

ARsT design — Wed, 22 Feb 2012 11:31:35 GMT

Build 3.0.0.21 of xlsgen adds support for database functions in the calculation engine.

So far, the 12 database functions could be read and written, but now they can be evaluated as well.

DAVERAGE, Averages the values in a column in a list or a database that match conditions you specify
DCOUNT, Counts the cells containing numbers in the field (column) of records in the database that match the conditions you specify
DCOUNTA, Counts nonblank cells in the field (column) of records in the database that match conditions you specify
DGET, Extracts from a database a single record that matches the conditions you specify
DMAX, Returns the largest number in the field (column) of records in the database that match the conditions you specify
DMIN, Returns the smallest number in the field (column) of records in the database that match the conditions you specify
DPRODUCT, Multiplies the values in the field (column) of records in the database that match the conditions you specify
DSTDEV, Estimates the standard deviation based on a sample from selected database entries
DSTDEVP, Calculates the standard deviation based on the entire population of selected database entries
DSUM, Adds the numbers in the field (column) of records in the database that match the conditions you specify
DVAR, Estimates variance based on a sample from selected database entries
DVARP, Calculates variance based on the entire population of selected database entries

Each database function takes 3 parameters. The first is the cell area including the database itself. The second is the column title where the calculations will occur. The third is one or more criterias for doing the calculations.

Note that xlsgen has another mechanism, table subtotals, which both does the same calculations and creates visuals of this (table sections according to criterias).

xlsgen 3.0 build #20 : Fix for rendering floating-point values

ARsT design — Mon, 20 Feb 2012 11:26:42 GMT

Build 3.0.0.20 of xlsgen makes an important improvement to rendering floating-point values.

Before that build, when a number such as 0,746357 would be rendered in a cell where the formatting is set to Number with 2 decimals, xlsgen would store it as 0,746357 but render it (display, print) as 0,74, therefore truncating the value correspondingly. This was a convention.

Turns out Excel has another convention which is to round up the last decimal to the next value. In the example above, this would become 0,75, because the next decimal 6 is greater or equal than 5.

Neither convention is good or bad, simply a convention.

Beginning with this build, xlsgen adopts Excel convention.

For rendering and display, xlsgen now rounds up values as in Excel.

This obviously causes rendering and display changes. Customers may review this before considering there is a fault in the program.

xlsgen 3.0 build #19 : Improved print/preview/PDF/XPS

ARsT design — Mon, 20 Feb 2012 11:20:51 GMT

Build 3.0.0.19 of xlsgen improves the print/preview/PDF/XPS rendering in several ways.

First of all, it improves how extremely large pictures are rendered. Before that build, the pixels on borders could be left not rendered due to rounding errors in the picture rendering layer (OLE32). xlsgen anticipates the rounding error now.

Second, xlsgen now supports cell indent rendering which is a style formatting used in Left and Right alignment.

No source code change needed.

xlsgen 3.0 build #18 : Fix for OpenFromMemory()

ARsT design — Thu, 16 Feb 2012 08:47:03 GMT

Build 3.0.0.18 of xlsgen has a fix related to the OpenFromMemory() scenarios.

This build enables support for .XLSX files/memory buffers in OpenFromMemory().

So far only .XLS files/memory buffers were allowed.

No source code change needed.

xlsgen 3.0 build #17 : Validation against Microsoft Office BFF file validator

ARsT design — Tue, 7 Feb 2012 17:07:00 GMT

Build 3.0.0.17 of xlsgen adds 4 fixes for validating XLS files against the Microsoft Office BFF file validator.

In addition to Excel 2010 file check on open, also recently added to previous Excel versions as part of service packs, Microsoft has been working on another deep file format validator for XLS files, which is here.

The important changes to ensure that xlsgen generated XLS files are successfully passing the test are included in this build.

So whenever this validator becomes part of Excel, xlsgen is already ready for it.

xlsgen 3.0 build #16 : Text reading-order

ARsT design — Fri, 3 Feb 2012 08:59:37 GMT

Build 3.0.0.16 of xlsgen adds read/write support for text reading-order in XLS and XLSX files.

Text reading-order is relevant in far east languages.

A new enumeration is created :

typedef enum
{
[helpstring("Text reading order, context")] textreadingorder_context = 0,
[helpstring("Text reading order, left to right")] textreadingorder_left_to_right = 1,
[helpstring("Text reading order, right to left")] textreadingorder_right_to_left = 2
} enumTextReadingOrder;

And the property is exposed at several levels :

- style : Alignment.ReadingOrder property (IXlsAlignment interface)
- chart text elements : Options.Alignment.ReadingOrder property (IXlsChartAlignment interface)

Also the property is exposed in html cells, as in <b rtl=true>data</b>.

Also the automatic source code generator outputs the corresponding source code, when used.

xlsgen 3.0 build #15 : More conditional formattings

ARsT design — Mon, 16 Jan 2012 09:44:12 GMT

Build 3.0.0.15 of xlsgen adds more conditional formattings to XLSX files.

They are as follows :
- top values : for instance highlights the top 5 values
- bottom values
- top values in percent : for instance highlights the top 10% values
- bottom values in percent
- above or below average : with several average variants
- duplicate values : for instance highlights values that are duplicates
- unique values : for instance highlights values that are unique

Trying to create such conditional formattings in XLS files will return error messages. The reason why is that they are new in the XLSX file format.

Here is how you get to highlight unique values in a set :

// create a style
IXlsStyle s = worksheet.NewStyle();
s.Pattern.BackgroundColor = 0xCCFFCC;

// highlights unique values in column B using style s
IXlsConditionalFormatting cf = worksheet.NewRange("B:B").NewConditionalFormatting();
cf.PredefinedCondition.UniqueValues();
cf.Style = s;

xlsgen 3.0 build #14 : Fix for editing hyperlinks

ARsT design — Tue, 10 Jan 2012 22:03:10 GMT

Build 3.0.0.14 of xlsgen has a fix related to editing existing hyperlinks in XLSX files.

Before that build, changes to existing hyperlinks would be ignored when it caming to writing the XLSX files.

No source code change needed.