I'm calling a stored procedure, filling a datatable with the results, then binding the webdatagrid to the datatable. When I export the webdatagrid to Excel, the column headers are exported but no data. Here are the relevant pieces of code:
Dim selectionString As String selectionString = "EXEC NamePhraseSearch '" + BuildParameter() + "'" SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.Text SqlDataSource1.SelectCommand = selectionString 'SqlDataSource1.Select(DataSourceSelectArguments.Empty) Dim connectionstring As String = SqlDataSource1.ConnectionString Using cnn As New Data.SqlClient.SqlConnection(connectionstring) cnn.Open() Using dad As New Data.SqlClient.SqlDataAdapter(selectionString, cnn) dad.SelectCommand.CommandTimeout = 0 dad.Fill(dtb) End Using cnn.Close() End Using webGridResults.DataSource = dtb webGridResults.DataBind()
--This results in the grid being populated with the results from the stored procedure in the form of the datatable.
--Then to export to excel:
WebExcelExporter1.DataExportMode = DataExportMode.AllDataInDataSource WebExcelExporter1.DownloadName = "ListingsSummary" WebExcelExporter1.Export(webGridResults)
When I open the excel file that is downloaded there are only Column headers, no data.
Please assist me with this issue.
Hello Dan,Thank you for posting in the community!
In this particular case, I believe the DataSource should be set in the exporting event. In order not to duplicate the forum threads, please refer to the following forum posts, where this issue has being discussed.
The first one is regarding the use of SqlDataSource, and the other one is more like in general and it contains a simple code sample.
WebExcelExporter export only contains column headers not dataExport to excel only show column headers not the data
Please let me know if I could further help you!
Hello,
I am just checking the progress of this issue. Have you being able to resolve it, based on my suggestions?
I am using a datatable to populate the grid. When I checked the contents of the datatable at the time I was exporting the grid, the datatable was empty, thus the empty excel workbook. Since the grid is not editable, and therefore the contents of the datatable would be the same when I export it as when I originally filled the datatable, my solution was to create a session variable to hold the datatable at the time the datatable was created/filled. Just prior to exporting the grid, I set the value of the datatable to the session variable
My code looks like this at the time I fill the datatable/bind the grid:
Dim dtb As New Data.DataTable
Dim connectionstring As String = SqlDataSource1.ConnectionString Using cnn As New Data.SqlClient.SqlConnection(connectionstring) cnn.Open() Using dad As New Data.SqlClient.SqlDataAdapter(selectionString, cnn) dad.SelectCommand.CommandTimeout = 0 dad.Fill(dtb) End Using cnn.Close() End Using
webGridResults.DataSource = dtb webGridResults.DataBind()
Session.Add("ResultsDataTable", dtb)
When the user clicks the export button, those original values are retrieved, as below:
dtb = Session.Item("ResultsDataTable")
WebExcelExporter1.DataExportMode = DataExportMode.AllDataInDataSource WebExcelExporter1.DownloadName = "ListingsSummary" webGridResults.DataSource = dtb webGridResults.DataBind() WebExcelExporter1.Export(webGridResults)
Hello Dan,
Thank you for sharing this in the forum!
We believe sharing solutions could benefit all community members.