Search List and Exporting to Excel

Original post : SJdali 15/03/2011

"Hi Guys
I have written some script to spit out the results of a search list into Excel. I have this working, however I can only seem to return the data table for the search list. I have looked through the various properties and can only seem to pull out the same data at various levels.
I was hoping you guys could point me in the direction to pull out the data as displayed via code i.e. column headings etc all finished as the user would see it? I know this is essentially controlled by the business layer, but thought I would try asking anyway.

I have tried:
SearchList.DataTable.DefaultView.ToTable(); SearchControl.SearchList.DataTable;

and variations on the above but no luck yet. Any help much appreciated.
Cheers!"

Responses:
RichardA 15/03/2011
"Hi Simon,
There is a build in mechanism for creating a CSV from a searchlist.
SearchControl.CreateCSV() with various overloads for which columns to include and headers etc.

If you can't use the CreateCSV method then SearchList.ListView is a data table that has the display columns information in it. You can use this to know which columns from SearchControl.SearchList.DataTable you need to output.
Regards Richard"

Graham Shaw (BJ) 18/03/2011
"For quick export we convert the datatable to an ADO recordset and then use the Excel Range.CopyFromRecordset function which is much quicker than iterating rows / columns, especially if the set is large."

Mike Walker CTO 18/03/2011
"internally we do this for word table export service as well as the ADO Recordset has some helper functions as Graham said"

SJdali 21/03/2011

"Thanks Graham,
I will look into this "

SJdali 21/03/2011

"Hi Richard,
Thanks for that. I now successfully have the Header Row created
It's looking like I am going to have to create a new data table using the data from the header row and the SearchList.DataTable to only have the displayed rows? Cheers
Simon"

RichardA wrote:
Hi Simon,
There is a build in mechanism for creating a CSV from a searchlist.
SearchControl.CreateCSV() with various overloads for which columns to include and headers etc.

If you can't use the CreateCSV method then SearchList.ListView is a data table that has the display columns information in it. You can use this to know which columns from SearchControl.SearchList.DataTable you need to output.
Regards Richard"