Friday, November 08, 2013

Sorting in Excel via Delphi automation

I was asked to prepare a table for the OP's flagship database, which shows for each question how many people have answered 'yes' and how many 'no'. Getting the data itself was fairly easy but displaying it was something else again.

Basically, I had an array with 480 rows and seven columns, where I had to sort the array by the fourth column; the spreadsheet has a header row. I thought that it would be easy to output the data to Excel then let Excel do the sorting. As it turns out,  I have never had cause to sort in Excel via automation before now, so first I recorded an Excel macro which would sort. This is the result
Range("A1:G481").Sort Key1:= Range("D2"), Order1:= xlAscending, Header:= _
  xlGuess, OrderCustom:= 1, MatchCase:= True, Orientation:= xlTopToBottom, _
  DataOption1:= xlSortNormal
While Excel might be able to understand that, it wasn't at all clear how I was going to translate that into a form that Delphi could understand. So I started googling, finding several solutions, none of which worked. Eventually I found the solution which I am documenting here.
 xlAscending = 1;
 xlNo = 0;
 xlTopToBottom = 1;
 xlSyllabary = 1;

 sheet.Range['A1', 'G481'].Sort (sheet.Range['D2'], xlAscending,
                                 EmptyParam, EmptyParam,  // key2
                                 EmptyParam, EmptyParam,  // key3
                                 xlAscending, xlNo, EmptyParam, True,
                                 xlTopToBottom, xlSyllabary);

Let me explain the 'G481' term: there are seven columns, and Excel labels them alphabetically. So 'A1' refers to the cell at the intersection of the first column and the first row, its neighbour is 'B1', etc. So 'G1' refers to the cell at the intersection of the seventh column and the first row. As there are 480 rows in the array along with a header row, the final cell is G481.

The first parameter to the 'sort' procedure is the column by which the spreadsheet will be sorted; the second parameter presumably states that the sort will be in ascending order. In this case there is no second nor third key, but if there were, they would be the third and fifth parameters. The eighth parameter (xlNo) would appear to relate to the existence of the header. Don't ask me about the rest (what does syllabary mean? Now I know).

Unfortunately, for the time being,  I am going to have to relate to this code as a magical spell as I don't really understand what each parameter does. All I know is that it does what is required.

Note to myself: following is the definition of the 'sort' procedure from MSDN:
Object Sort(
 Object Key1,
 XlSortOrder Order1,
 Object Key2,
 Object Type,
 XlSortOrder Order2,
 Object Key3,
 XlSortOrder Order3,
 XlYesNoGuess Header,
 Object OrderCustom,
 Object MatchCase,
 XlSortOrientation Orientation,
 XlSortMethod SortMethod,
 XlSortDataOption DataOption1,
 XlSortDataOption DataOption2,
 XlSortDataOption DataOption3


Anonymous said...

With this small example you really made my day! Thank you!

Lacroix Luc said...

+1 !