Generating Excel with sorting/filtering headers using ASP.Net

[AndyH1]

I'm generating Excel using the Excel 11 Object Library as in:

http://www.beansoftware.com/ASP.NET-Tutorials/Generating-Excel-Reports.aspx

It works fine and I can generate an Excel report with the titles formatted as black on grey.

The customer however wants a more complicated Excel sheet generated to mirror what they have already. Basically on a worksheet of data they currently have

Event Entry date Sitename
----- ---------- ---------
1423 12/11/2007 Schenzen
1234 11/12/2007 Nanjing
1254 11/01/2008 Beijing
...

On the headings; Event, Entry Date and Sitename they have a small dropdown arrow button next to each heading which when clicked on gives a dropdown list, for example with Sitename the drop down list would be

Sort Ascending
Sort Descending
----------------
(All)
(Top 10)
(Custom...)
Schenzen
Nanjing
Beijing

If you selected say Nanjing it would then only show the rows with Nanjing in them (filter).

I'm not proficient in Excel and do not know how to do this manually, let alone add it to the generation code, but Iv'e no idea where to look to find out how to do this. Is there a good reference someone can recommend to show me how to do things like this. Iv'e done charts so assume this should also be possible someway.

Thanks
Andy

 

[jmeckley]

there is probably a property to set autofilter to true on any given row/column. this is a feature of excel, not anything to do with asp.net. For more help I would check out either an office programming forum, or the language forum you are using.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[AndyH1]

Iv'e tried 'xlWorkSheet.AutoFilter = True' but I get an error saying this is read only. I tried 'xlWorkSheet.AutoFilterMode = true' but this too seems to cause an error.

Andy

 

[AndyH1]

Also tried xlWorkSheet.Range("$A1").AutoFilter() but this gives System.Runtime.InteropServices.COMException: AutoFilter method of Range class failed

Can anyone advise how I set the property please?
Thanks
Andy