I have written some vb code (in an aspx page) to query a database and output the results in an excel sheet (.xls). It works fine and generates a .xls sheet with my data in it. But now I want to be able to sort the sheet using the top headers.
I used DataTable.Column.Add(...) to add the headers and rows.add to add the rows.
But I want my generated xls sheet to have a dropdown on top of each column that filters the sheet to show only those that have the value that is selected.
I know I can do it manually but I want to know how to generate the xls sheet with this already in it.

for example lets say I have xls sheet like below:

aa | bb
--------
11 | 23
22 | 13
22 | 23
33 | 34
33 | 14
44 | 34

I want to have a dropdown in the top column cells aa and bb that let me chose any value of 11, 22, 33, and 44 and when chosen for example 22 the list would shrink to:

aa | bb
--------
22 | 13
22 | 23

How can I code this in my aspx page using vb to generate this xls sheet?

thanx

The XLS export is reading the HTML generated by the control output. Because that nifty Excel filtering functionality is part of Excel, I don't think there is a way to get the HTML to output enough stuff so the Excel will do that. You'd have to actually generate an XSL file with the cell fomulas/stuff in it to make the column header filters.

-Peter

How do I go about writing the xsl file? I've never done that!

I've never done it, but you should be able to reference the variables COM assemblies that excel uses and interact directly with excel functionality. Also, there are "Visual Studio Tools for Office". Something in that might give you a better way to generate excel worksheets.

-Peter