asp.net - Open DataTable in Excel VB -

OK, this is an interesting issue. I have been assigned the task of revising an existing VB project. At present, the user is selected to select a SQL query from the series of dropdowns and then that query runs, the user chooses and the Environment dropdown displays the results category dropdown of that dropdown. Once the category is selected, they get a dropdown of available questions. Once they select a query and press the "Run" button, they receive a gridview with the result of the query. Some query results are too big I have 40 columns and 20,000 records in a query running as a test. The query runs in less than 5 seconds but gridview renders a minute to render. Once gridview is rendered, the user has the option to export the results in Excel. And by this, I mean that the code opens an example of Excel through gridview.RenderControl and displays results in Excel. The user does not want to save the excel file and then navigate to the file, they want to open it right from the webform, which is currently in the code.

However, users do not care about Gridview, they do not care that they are seeing it at all. Instead of using gridview.RenderControl, I want to open Excel and populate it with DataTable (or DataSet) in memory, the best way to do it is to have an idea on how to do this.

It is currently reported that they are currently browsing the gridview: Dual MyConnection SqlConnection Dual MyCommand SqlCommand Dynamic MyDataTable DataTable Dynamic MyReader as SqlDataReader as

  My Connection = New SQA Connection () MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings (connection) .ConsensString 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) then QueryresultPanel.Visible = True gvLineItems.DataSource = MyDataTable gvLineItems.DataBind () end if MyDataTable.Dispose () MyCommand.Dispose () MyConnection.Dispose ()  

Here are how they are opening and extending the examples of Excel:

  protected Sub btnExportToExcel_Click (ByVal this type As the scheme, ByVal e System.EventArgs as) btnExportToExcel.Click Response.Clear (in Excel) Response.Buffer = true 'conduct' content type. Response.AddHeader ("Content-Properties", "Attachment; File Name = GridViewExport.xls") Response.Charset = "" Response.ContentType = "application / vnd.ms-excel" '' View state off. Get 'HTML' for 'control' in the form of 'Me.EnableViewState = False Slow oStringWriter New System.IO.StringWriter () as the dim oHtmlTextWriter New System.Web.UI.HtmlTextWriter (oStringWriter). 'GvLineItems.RenderControl (oHtmlTextWriter)' Write back the HTML in the browser. Answer:  

Obviously, there is no reader control for any datatable or dataset and can not know how to achieve this record in an example of Excel to save the file without any files Sets to submit for.

OK, the solution I found here (in case anyone is interested) it is actually very simple I just stopped through the data and for the use of string wighter.

  Protected sub-writeAxcelFile (DataTable as DT) DIT. For each datacol in the form of a datacol in the form of a dolphin in the form of a slow sw. In the columns (Datacol.Collman + VBTib) the next slow line DataRow form dt.Rows sw.Write (vbNewLine) column of each row of the DataColumn For each column in dt.Columns, then row no (column.ColumnName) is anything else then sw.Write (row is column) ToString () + vbTab) Other. Swit ends (string.apti + vbTab) if next column responds next line. Clear Response Contact Type = "app / vnd.ms-excel" Response.AddHeader ("Content Dishes", "Attachment; File Name = DataTable.xls") Response.Output.Write (sw.ToString ()) Response.Flush () System .Web.HttpContext.Current.Response.Flush () System.Web.HttpContext.Current.Response.SuppressContent = True System.Web.HttpCOnTextCount.Application Instance. Complimentary () and subscriptions