Custom lists

xlsgen > overview > Custom lists

With custom lists, xlsgen 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

Java code

// build a calendar list meant to allow proper date sorting
XlsList 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");
...

VB code

' build a calendar list meant to allow proper date sorting
Dim list As xlsgen.IXlsList
Set 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")
...

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");
...

C++ code

// build a calendar list meant to allow proper date sorting
xlsgen::IXlsListPtr list = worksheet->NewList();
list->Add(L"january 2011");
  list->AddAlternate(L"january");
list->Add(L"feb 2011");
  list->AddAlternate(L"february 2011");
  list->AddAlternate(L"february");
list->Add(L"march 2011");
list->AddAlternate(L"march");
...

Using custom lists

Using custom lists for sorting (1)

With the regular sort API, it goes like this :

Java code

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

VB code

' sort (ascending)
Dim sort As xlsgen.IXlsSort
Set sort = worksheet.NewRange("C5:E8").NewSort
sort.PrimaryCustomKey(0, list, True)
sort.Apply

C# code

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

C++ code

// sort (ascending)
xlsgen::IXlsSortPtr sort = worksheet->NewRange(L"C5:E8")->NewSort();
sort->PrimaryCustomKey(0, list, TRUE);
sort->Apply();

Using custom lists for sorting (2)

With the business intelligence API, it goes like this :

Java code

// same sort (except descending versus ascending) using business intelligence APIs
worksheet.getTables().InferFromWorksheet();
XlsTableSort s1 = worksheet.getTables().getItem(1).getDataResults().getColumns().getItemByName("Date").getSorting();
s1.putOrder(false);
s1.putCustomList(list);
s1.Apply();
worksheet.InsertTableAt(worksheet.getTables().getItem(1), 4, 7);

VB code

' same sort (except descending versus ascending) using business intelligence APIs
worksheet.Tables.InferFromWorksheet()
Dim s1 As xlsgen.IXlsTableSort
Set s1 = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Date").Sorting
s1.Order = False
s1.CustomList = list
s1.Apply
worksheet.InsertTableAt(worksheet.Tables.Item(1), 4, 7)

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

C++ code

// same sort (except descending versus ascending) using business intelligence APIs
worksheet->Tables->InferFromWorksheet();
xlsgen::IXlsTableSortPtr s1 = worksheet->Tables->Item[1]->DataResults->Columns->ItemByName[L"Date"]->Sorting;
s1->Order = FALSE;
s1->CustomList = list;
s1->Apply();
worksheet->InsertTableAt(worksheet->Tables->Item[1], 4, 7);

Using custom lists for removing duplicates

Java code

XlsList 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);

VB code

Dim listIndicators As IXlsList 
Set 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)

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

C++ code

xlsgen::IXlsListPtr 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(L"R11C3:R15C5")->RemoveDuplicatesUsingList(listIndicators);