xlsgen, native Excel generator

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.

xlsgen 3.0 build #13 : Fix for chart axis crossing

ARsT design — Thu, 5 Jan 2012 10:01:32 GMT

Build 3.0.0.13 of xlsgen has a fix related to chart axis crossing values in XLSX files (Excel 2007/2010).

xlsgen did not write the value at which an axis could cross the alternate axis in the case of XY charts (scatter, bubbles).

This fix also reads the value from existing charts (all charts) from XLSX files. And also exposes the value in the automatic source code generation tool.

No source code change needed.

xlsgen 3.0 build #12 : Fix for overlapping shared formulas

ARsT design — Tue, 20 Dec 2011 21:59:48 GMT

Build 3.0.0.12 of xlsgen fixes a problem to shared formulas overlapping one onto others.

The shared formulas is a mechanism in Excel meant to optimize the storing of formulas. A use case that was never seen before, considered valid by Excel, is the overlapping of two shared formulas areas. xlsgen could be disturbed by this and corrupt the formulas in the overlap.

This build fixes the problem in question.

No source code change needed.

xlsgen 3.0 build #11 : Fix for wrapped cells in print/preview/PDF

ARsT design — Thu, 15 Dec 2011 14:40:46 GMT

Build 3.0.0.11 of xlsgen has a fix related to the handling of vertical alignment in cells when doing a print/preview/PDF.

The change is related to the ignoring of the vertical alignment setting whenever the cell is both marked as wrapped and left aligned horizontally.

xlsgen does not ignore the vertical alignment anymore.

No source code changes needed.

xlsgen 3.0 build #10 : Table subtotals

ARsT design — Sat, 10 Dec 2011 23:44:16 GMT

Build 3.0.0.10 of xlsgen introduces another business intelligence feature : table subtotals.

Continuing last build, which introduced table breaks, this time xlsgen adds the ability to compute subtotals.

It is a straight-forward feature. The ability to aggregate data along a numeric axis.

The aggregate function lets one customize the process. Among the functions are : sum, min, max, average and count.

And here is how it looks like :

Table subtotals

Internally, xlsgen does build actual Excel formulas, computes them and put their results in cells in the footer of each break.

Here is how to produce the subtotals :


worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("City").Breaks
pb.DistinctValues()

IXlsTableColumn tcSales = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Sales index")

IXlsTableSubtotal sts = pb.Subtotals.Add(tcSales)
sts.BuiltinFunction = xlsgen.enumSubtotalFunction.subtotalfunc_min
sts.Label = "min = "

IXlsTableSubtotal stm = pb.Subtotals.Add(tcSales)
stm.BuiltinFunction = xlsgen.enumSubtotalFunction.subtotalfunc_max
stm.Label = "max = "

pb.Apply()

xlsgen 3.0 build #9 : Table breaks

ARsT design — Thu, 1 Dec 2011 08:29:00 GMT

Build 3.0.0.9 of xlsgen introduces table breaks, a new capability.

xlsgen business intelligence features include the ability to break tables in sections according to criterias.

The most obvious criteria is distinct values, which is straight forward on columns whose data type is a string. This mechanism allows to break on every other value, and create a section accordingly making it easier to understand the actual data.

The other criteria is a generic formula.

The capabilities are exposed in the IXlsTableBreaks interface.

Breaking on distinct values

Here is the raw data we are working with :

Data before applying breaks

The City column has a set of two distinct values : {Rockwell, Marver}. By breaking on distinct values, xlsgen creates the corresponding sections.


worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("City").Breaks
pb.DistinctValues()
pb.Apply()

And the following result is obtained :

Breaking on distinct values

Breaking on a generic formula

Whenever the criteria to break the table is more complex than just distinct values, xlsgen exposes a generic formula mechanism, which can use one or more column names and any Excel formula. An example follows :

In the first example, the data we have is a capture from For(eign)Ex(change) currency data, which is a time series capturing forex data from 3:00 PM to 5:30 PM on a particular day, on a per minute basis.

Data before applying breaks

We may be interested in breaking all transactions per hour, therefore break on the Time column according to a criteria involving the HOUR() function :


worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Time").Breaks
pb.Formula = "=HOUR([Time])"
pb.Apply()

Doing so we obtain 3 breaks, one for the time period [3:00-3:59], one for [4:00-4:59] and one for [5:00-5:59].

Breaking on a generic formula, =HOUR([Time])

In the second example, the generic formula mechanism can be used to enforce predefined value ranges. For instance, we have a Sales index column where the data is between 0 and 100, and we would like to put all values in the [0-80] range in a particular bucket and everything above 80 in another bucket.

Data before applying breaks


worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Sales index").Breaks
pb.Formula = "=IF([Sales index] > 80; 2; 1)"
pb.Apply()

And the following result is obtained :

Breaking on distinct value ranges (inside [0-80], and above 80)

xlsgen 3.0 build #8 : Fix pack

ARsT design — Mon, 21 Nov 2011 19:29:49 GMT

Build 3.0.0.8 of xlsgen brings a fix pack with the following relevant changes :

- sheet names to be enclosed in quotes in XLSX files whenever the names look like cell references, as in 'T1'!A1
- do not write alignment properties in conditional formattings in XLSX files since Excel 2007/2010 complains about it (there is no Alignment tab in Excel for conditional formattings)
- unary -- in formulas (i.e. better handling of case with multiple unary operators)
- table inference internal headers better inferred (cas with date versus time)

No source code change needed.

xlsgen 3.0 build #7 : Fix for IEEE values in formulas

ARsT design — Thu, 17 Nov 2011 08:48:13 GMT

Build 3.0.0.7 of xlsgen has a fix for IEEE values in exponential form in formulas from XLSX files.

IEEE values in exponential form are : +-dddd(.)dddddE+-cccc. An example is : 2.5E-12

No source code change needed.

xlsgen 3.0 build #6 : Fix pack

ARsT design — Fri, 11 Nov 2011 10:51:19 GMT

Build 3.0.0.6 of xlsgen is a fix pack. It contains the following :

- CSV import : better auto-discovery feature of headers
- XLSX writing : handling of custom part names (written by tools other than Excel)
- Additional Page setup properties duplicated with worksheet duplication
- New sample code : "business intelligence"
- Fix for table inference
- Support for secondary Y chart axis in XLSX reading scenarios
- Support for alternate chart types in XLSX reading scenarios
- Print preview scale now create a better PDF (no flatten print area)
- Fix for reading XLS files created by Crystal Reports
- Preserve the scrollbar position per sheet when reading/writing XLSX files
- Fix for nested sorting (business intelligence API)
- Workaround for the bogus DrawText() WIN32 API calls, used for rendering text

xlsgen 3.0 build #5 : Advanced CSV import (II)

ARsT design — Wed, 2 Nov 2011 09:42:00 GMT

Build 3.0.0.5 of xlsgen adds more capabilities and continues what was introduced in build 3.0.0.4

This time the new feature is the conditional import formula.

It's the ability to set up a formula that governs if rows will be imported or not.

For instance, let's say we have the following CSV file :


---------- datasource.csv ----------
ID;DESCRIPTION;DATE;PRICE
45;description1;10/06/2006;10,2
12;description2;11/06/2006;5,25
32;description3;12/06/2006;6,3
86;description4;13/06/2006;9,99
74;description5;14/06/2006;4,45
29;description6;15/06/2006;8,5
31;description7;16/06/2006;7
56;description8;17/06/2006;11,5
52;description9;18/06/2006;11
---------- datasource.csv ----------

Assuming we would like to import only the rows where the ID is greater than 50, then having identified that the ID column is the first column, we can do this (C++ below) :


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

wksht->Import->CSV->Options->ColumnConditionalImportFormulaByIndex[1] = L"=CELL(\"contents\") > 50";

wksht->Import->CSV->ImportFile(L"input\\datasource.csv");

And the resulting sheet contains the following :

ID	DESCRIPTION	DATE	PRICE
86	description4	38881	9,99
74	description5	38882	4,45
56	description8	38885	11,5
52	description9	38886	11

xlsgen 3.0 build #4 : Advanced CSV import

ARsT design — Tue, 25 Oct 2011 10:02:26 GMT

Build 3.0.0.4 of xlsgen adds advanced CSV import capabilities. What is introduced is the ability to specify custom data import mapping in order to ease the import of data. An example speaks best.

Assuming we would like to import the following data in an Excel file using xlsgen (the EUR/USD ticks are taken from a trading tool) :


---- EURUSD_ticks.csv excerpt -----------------------------------------------
2011.10.25,03:00,1.39076,1.39092,1.39060,1.39061,41
2011.10.25,03:01,1.39062,1.39070,1.39029,1.39033,37
2011.10.25,03:02,1.39031,1.39044,1.39015,1.39042,55
2011.10.25,03:03,1.39040,1.39040,1.38997,1.38997,51
2011.10.25,03:04,1.38997,1.39028,1.38997,1.39018,48
2011.10.25,03:05,1.39017,1.39034,1.39009,1.39016,41
2011.10.25,03:06,1.39020,1.39025,1.39001,1.39009,55
2011.10.25,03:07,1.39017,1.39041,1.39014,1.39037,40
2011.10.25,03:08,1.39037,1.39078,1.39037,1.39077,40
2011.10.25,03:09,1.39079,1.39112,1.39079,1.39094,94
2011.10.25,03:10,1.39095,1.39107,1.39086,1.39091,71
2011.10.25,03:11,1.39093,1.39105,1.39087,1.39102,34
2011.10.25,03:12,1.39101,1.39102,1.39075,1.39098,57
2011.10.25,03:13,1.39098,1.39102,1.39077,1.39077,21
2011.10.25,03:14,1.39076,1.39078,1.39055,1.39061,72
---- EURUSD_ticks.csv excerpt -----------------------------------------------

If we import EURUSD_ticks.csv as is, without custom data mapping, the resulting will not read the first column as a date, but as a raw string (notice the left alignment), therefore unable to be play its role in Excel calculations and display. The second column will be read as a time which is correct but not displayed in a human-friendly manner. Here is a screen capture of what it would look like in Excel :

Importing EURUSD_ticks.csv in xlsgen with default mappings results in incorrect data

By applying custom data mapping for both first and second columns, the dates are correctly imported and time is correctly displayed :

Importing EURUSD_ticks.csv in xlsgen with custom mappings results in correct data

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


xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

// we create a regular Excel file, and use the import interfaces
//  data types are custom bound
//  since we know the incoming data uses a particular data format

xlsgen::IXlsWorkbookPtr workbook = engine->New(L"ResultingFile.xls");
xlsgen::IXlsWorksheetPtr wksht = workbook->AddWorksheet(L"Sheet1");

xlsgen::IXlsCSVImportOptionsPtr options = wksht->Import->CSV->Options;
options->ColumnDataMappingFormatByIndex[1] = L"YYYY.MM.DD";
options->ColumnDataMappingFormatByIndex[2] = L"HH:MM";

wksht->Import->CSV->ImportFile(L"EURUSD_ticks.csv");

workbook->Close();

xlsgen 3.0 build #3 : Fix for macro 4.0 sheets

ARsT design — Mon, 17 Oct 2011 09:51:11 GMT

Build 3.0.0.3 of xlsgen fixes a problem in XLSM files embedding Macro 4.0 sheets, particularly International Macro 4.0 sheets. The corresponding markup was not supported so far.

No source code change needed.

xlsgen 3.0 build #2 : Performance improvements (II)

ARsT design — Tue, 11 Oct 2011 11:36:32 GMT

Build 3.0.0.2 of xlsgen continues on 3.0.0.1 and finds more performance improvements to make. No less than 40% faster in speed for reading XLSX files (the larger the files, the greater the speed change). And up to 15% in speed for writing XLS files.

If you are making intense use of xlsgen, and were using an old version (2.9, 2.5, ...), it's an opportunity to move forward.

xlsgen 3.0 build #1 : Performance improvements

ARsT design — Fri, 7 Oct 2011 17:45:48 GMT

Build 3.0.0.1 makes great strides in performance. 35% less memory used for processing XLSX files. Up to 15% speed up improvement for processing XLSX files.

xlsgen 3.0 has shipped!

ARsT design — Wed, 7 Sep 2011 14:56:04 GMT

xlsgen 3.0 has shipped!

A wealth of new capabilities in a number of areas. Whether you are interested in generating raw Excel files, analyzing, introspecting, calculating, reporting and now introducing Business intelligence, xlsgen comes with extreme versatility, speed on so many development platforms all at once.

If you are using an older version of xlsgen, such as 2.9, a new license is needed.

What's new in 3.0?

Business intelligence
Vector shapes (all basic types) and their associated formattings (lines, fills, ...)
Data bars, color scales and icon sets. See here for more information
Read and extract VBA macros (i.e. plain text)
Histograms
File encryption
Support for 21 new formulas in the calculation engine : ABS, ACOS, ADDRESS, AND, ASIN, ATAN, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CLEAN, CHAR, CHOOSE, CODE, COLUMN, COLUMNS, CONCATENATE, CORREL, COS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEVALUE, DAY, DAYS360, DEGREES, DELTA, DEVSQ, ERROR.TYPE, EVEN, EXACT, EXP, EXPONDIST, FACT, FACTDOUBLE, FALSE, FIND, FIXED, FLOOR, FREQUENCY, GCD, GEOMEAN, HLOOKUP, HOUR, IF, IFERROR, INDEX, INDIRECT, INT, INTERCEPT, INTERSECT, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, KURT, LARGE, LCM, LEFT, LEN, LINEST, LN, LOG, LOG10, LOGEST, LOGNORMDIST, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MOD, MODE, MONTH, MROUND, N, NA, NORMDIST, NORMSDIST, NOT, NOW, ODD, OFFSET, OR, PEARSON, PERCENTILE, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANK, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SLOPE, SMALL, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, T, TAN, TEXT, TIME, TIMEVALUE, TODAY, TRIM, TRIMMEAN, TRUE, TYPE, UNION, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, YEAR
Google charts
Export as XPS
Custom lists
Range helper : search and replace
A few new code samples (visual_component, vector_shapes, databar, histogram, read_VBA_macros, querySQLite_datasource, custom_lists)

xlsgen 3.0 feature #9 : Custom lists

ARsT design — Wed, 7 Sep 2011 12:27:53 GMT

With custom lists, xlsgen 3.0 offers a versatile mechanism for processing data in an order that is not predetermined. The first and most explicit example for custom lists is sorting dates, particularly months : january, february, ...If months are to be sorted alphabetically, january will not come first, so the sorting will be seen as flawed. In fact it takes a custom list to define the expected order.

While sorting dates is trivial, sorting other objects is as easy to implement. And there is no reason to limit this to sorting either. For instance, one could use it in the Remove duplicates feature that we've been exposing for some time in order to define a particular relational order between objects. And to help matters, the list has a few important features :

it can have a name
it can have an arbitrary number of entries (for instance, january, february, ...)
each entry can have any number of alternate names (for instance jan, Jan, ...)
each entry is selectable, and the selection is passed around

While two uses of custom lists have already been mentioned, Sorting and Remove duplicates, we'll mention a third use, which is Prompting. Prompting comes when the user has to pick one or more choice in a list. The custom list perfectly fits that model. But it can go well beyond with nested prompting which means entries a list have references to other lists, for instance a country has a number of regions and each region has a number of cities. The corresponding custom lists let define and implement applications where the user will be able to zoom in or out the desired level of detail. That is exactly what is available in the business intelligence prompting features.

A code sample using custom lists in different ways is available in the /samples folder, custom_lists.

Creating custom lists

C# code


// build a calendar list meant to allow proper date sorting
IXlsList list = worksheet.NewList();
list.Add("january 2011");
  list.AddAlternate("january");
list.Add("feb 2011");
  list.AddAlternate("february 2011");
  list.AddAlternate("february");
list.Add("march 2011");
list.AddAlternate("march");
...

Using custom lists

Using custom lists for sorting (1)

With the regular sort API, it goes like this :

C# code


// sort (ascending)
IXlsSort sort = worksheet.NewRange("C5:E8").NewSort();
sort.PrimaryCustomKey(0, list, 1);
sort.Apply();

Using custom lists for sorting (2)

With the business intelligence API, it goes like this :

C# code


// same sort (except descending versus ascending) using business intelligence APIs
worksheet.Tables.InferFromWorksheet();
IXlsTableSort s1 = worksheet.Tables.get_Item(1).DataResults.Columns.get_ItemByName("Date").Sorting;
s1.Order = 0;
s1.CustomList = list;
s1.Apply();
worksheet.InsertTableAt(worksheet.Tables.get_Item(1), 4, 7);

Using custom lists for removing duplicates

C# code


IXlsList listIndicators = worksheet.NewList();
listIndicators.Add("L1");
  listIndicators.AddAlternate("L2"); // that is to say : L2 should be seen as equal to L1

// remove duplicates (custom list)
worksheet.NewRange("R11C3:R15C5").RemoveDuplicatesUsingList(listIndicators);

xlsgen 3.0 feature #8 : 21 additional calc functions, Search/Replace

ARsT design — Tue, 6 Sep 2011 13:43:57 GMT

xlsgen 3.0 adds 21 functions to the calculation engine (in bold below) :

ABS, ACOS, ADDRESS, AND, ASIN, ATAN, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CLEAN, CHAR, CHOOSE, CODE, COLUMN, COLUMNS, CONCATENATE, CORREL, COS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEVALUE, DAY, DAYS360, DEGREES, DELTA, DEVSQ, ERROR.TYPE, EVEN, EXACT, EXP, EXPONDIST, FACT, FACTDOUBLE, FALSE, FIND, FIXED, FLOOR, FREQUENCY, GCD, GEOMEAN, HLOOKUP, HOUR, IF, IFERROR, INDEX, INDIRECT, INT, INTERCEPT, INTERSECT, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, KURT, LARGE, LCM, LEFT, LEN, LINEST, LN, LOG, LOG10, LOGEST, LOGNORMDIST, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MOD, MODE, MONTH, MROUND, N, NA, NORMDIST, NORMSDIST, NOT, NOW, ODD, OFFSET, OR, PEARSON, PERCENTILE, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANK, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SIN, SLOPE, SMALL, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, T, TAN, TEXT, TIME, TIMEVALUE, TODAY, TRIM, TRIMMEAN, TRUE, TYPE, UNION, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, YEAR

xlsgen 3.0 also adds search/replace capabilities :

Trivial to use : worksheet.NewRange("C3:I9").SearchAndReplace("word", "ppt");

The method call actually also returns how many replacements have occured making it easy to track the evolution of the contents of the worksheet.