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 GAny 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();
|
