Hi, i need to export a query result to excel.
I did it using a few methods but it seems that it is taking to long if loading large amount of data like 1000 rows.
I did looping a datatable and write the values to excel via excel object 11.0. - 20 secs for 1000 rows
exporting the data to CVS but i will have problems formatting the data.
using copyfromrecordset using adodb. But they want it to be done in .net
Are there any better and faster ways Must be done programmatically using vb.net.
thanks
exporting data to excel
Cologne Claret
I recently had the same problem, although i could have used the adodb option, i ended up going with the csv file.
Formatting is a problem i had as well. In the end it didn't need to be formatted so i just used the csv and let the user sort it out afterwards. They didn't mind.
There is a free .c# component available on the gotdotnet.com site called excelexporter.
It does all the lovely formatting and such, but its slower than a dead tortoise. Only does about 100 rows per minute so is effectively next to useless for large amounts of data.
I only just had a thought and i'm not sure its possilbe, but could you create an excel worksheet programatically, format the header section etc, then create a csv file of your data and use ..net to import it to the excel sheet, starting at a specified row\column
The other option of course, is to write it out to xml and set all your formatting in that manner. I've never tried it, but im pretty sure it can be done. There should be an XML guru around here somewhere.
Probably cant but i just thought of it. :)
can you just tell them adodb is part of .net :)
BlackGrouse
Actually what i am trying to do is to let the user to export data from a datagrid to an excel spreadsheet so performing it directly through sql is not an option in this case. Opening up the excel app and looping through all the cells to input the value is too slow.
Haha why isn't that a CopyfromDatatable method similar to CopyfromRecordset.
Anyway is it possible to use the select into statement on excel like Select Into [excel8.0 .......From some select statement here
Michael Koltachev - MSFT
Well yes you can export using SQL but not from a WinForm DataGrid. I'm not aware of anything that will export directly from a DataGrid to Excel and unfortunately the automation method is going to be slow when you're working with that many rows.
There is a method that uses XML but I don't know how feasible the implementation would be for you:
HOW TO: Transfer XML Data to Microsoft Excel 2002 by Using Visual Basic .NET
Roy Chastain
Sometimes the export can be performed directly through SQL but it depends upon your data source. Is the data coming from another database