XEROF

xlsgen 5.0.0.15 : Advanced data sorting

Build 5.0.0.15 of xlsgen adds advanced data sorting. Before this build, sorting on values was already available. This build adds the following :

on values
on cell color : background cell color or font color
on associated icon (related to icon-based conditional formatting)

This is illustrated as follows :

Sort on values (ascending) by column C

Sort on background cell color (descending) by column D

Sort on cell font color (ascending) by column E

Sort on icons (ascending) by column G

Any number of sort conditions can be added (up to 3 if you target XLS files) so if you'd like to sort on column C then on column D, the corresponding conditions can be added to the tree of conditions, accordingly.

Here is an example for sorting on background cell color :

C++ code



xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"data.xls", L"output.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

xlsgen::IXlsRangePtr r = wksht->NewRange(L"C4:F7");
xlsgen::IXlsSortPtr sort = r->NewSort();

xlsgen::IXlsSortConditionPtr sc = sort->NewCondition();

sc->SortOnWhat = xlsgen::sortcondition_oncellbkgndcolor; // cell background color is used as sort criteria
sc->columnIndex = 2; // 2nd column of the range, i.e. column D
sc->SortColor = 0xFFFF00; // 0xFFFF00 = yellow

sort->Apply();

And another one for sorting on icons (related to existing conditional formatting of type icon) :

C++ code



xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"data.xls", L"output.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

xlsgen::IXlsRangePtr r = wksht->NewRange(L"C4:F7");
xlsgen::IXlsSortPtr sort = r->NewSort();

xlsgen::IXlsSortConditionPtr sc = sort->NewCondition();

sc->SortOnWhat = xlsgen::sortcondition_onicon; // cell icon is used as sort criteria
sc->columnIndex = 4; // 4th column of the range i.e. column F
sc->SortIconset = xlsgen::iconset_3trafficlights_unrimmed; // icon set to match
sc->SortIconsetIndex = 1; // which icon in iconset to match (1-based)

sort->Apply();