asp.net - Open DataTable in Excel VB -

ok, interesting issue. have been tasked modifying existing vb project. user selected series of dropdowns select sql query , run query. user selects , environment dropdown, results of dropdown populates category dropdown. once category selected, dropdown of available queries. once select query , hit "run" button, gridview results of query. of query results huge. query i'm running test has 40 columns , 20,000 records. query runs in less 5 seconds takes on minute render gridview. once gridview done rendering, user has option export results excel. , this, mean code opens instance of excel through gridview.rendercontrol , displays results in excel. user doesn't want save excel file , navigate file, want open right webform using code currently.

however, user doesn't care gridview. don't care if see @ all. want open excel. instead of using gridview.rendercontrol, want open excel , populate datatable (or dataset) in memory. thoughts on best way that?

here's how populating gridview: dim myconnection sqlconnection dim mycommand sqlcommand dim mydatatable datatable dim myreader sqldatareader

        myconnection = new sqlconnection()         myconnection.connectionstring = configurationmanager.connectionstrings(connection).connectionstring          mycommand = new sqlcommand()         mycommand.commandtext = sqlquery         mycommand.commandtype = commandtype.text         mycommand.connection = myconnection          mycommand.connection.open()         myreader = mycommand.executereader(commandbehavior.closeconnection)          mydatatable = new datatable()         mydatatable.load(myreader)          if (mydatatable.rows.count > 0)             queryresultpanel.visible = true             gvlineitems.datasource = mydatatable             gvlineitems.databind()         end if          mydatatable.dispose()         mycommand.dispose()         myconnection.dispose() 

here's how they're opening , populating instance of excel:

     protected sub btnexporttoexcel_click(byval sender object, byval e system.eventargs) handles btnexporttoexcel.click         response.clear()         response.buffer = true         '         ' set content type excel.         '          response.addheader("content-disposition", "attachment;filename=gridviewexport.xls")         response.charset = ""         response.contenttype = "application/vnd.ms-excel"         '          ' turn off view state.         '          me.enableviewstate = false         dim ostringwriter new system.io.stringwriter()         dim ohtmltextwriter new system.web.ui.htmltextwriter(ostringwriter)         '          ' html control.         '         gvlineitems.rendercontrol(ohtmltextwriter)         '          ' write html browser.         '         response.write(ostringwriter.tostring())         response.[end]()     end sub 

obviously, there's no rendercontrol datatable or dataset , can't figure out how record set render in instance of excel without saving file first.

alright, here's solution found (in case interested). it's pretty simple actually. looped through datatable , used stringwriter.

    protected sub writetoexcelfile(dt datatable)     dim sw stringwriter      each datacol datacolumn in dt.columns         sw.write(datacol.columnname + vbtab)     next      dim row datarow     each row in dt.rows         sw.write(vbnewline)         dim column datacolumn         each column in dt.columns             if not row(column.columnname) nothing                 sw.write(row(column).tostring() + vbtab)             else                 sw.write(string.empty + vbtab)             end if         next column     next row      response.clear()     response.contenttype = "application/vnd.ms-excel"     response.addheader("content-disposition", "attachment;filename=datatable.xls")     response.output.write(sw.tostring())     response.flush()     system.web.httpcontext.current.response.flush()     system.web.httpcontext.current.response.suppresscontent = true     system.web.httpcontext.current.applicationinstance.completerequest() end sub