Need help with Exporting Sorted Data in the Datagrid/DataView into Excel

When I hit the Export button, a new browser is opened. Here I am looping through the dataview and writing the data into Excel sheet row by row. I also have Sorting enabled for the datagrid. Everything works perfect.

When I hit the export button, I always get the original row sequence... I want to sort the data first based on some column and then export the data into Excel in the sorted order.
Is there a way we can do this??
0
dnn_user
9/1/2004 10:25:27 PM
asp.net.dotnetnuke 25171 articles. 0 followers. Follow

3 Replies
378 Views

Similar Articles

[PageSpeed] 36

Hey again.  I do the same thing with sorting and filtering in the .aspx I mention in your last thread. All I do is pass the sorting/filtering params in the query string.  

I have this code snippet in the Page_Load event of the .ascx control to set the NavigateURL property of the hyperlink.

hlExport.NavigateUrl = "FacilityListExcel.aspx?region=" + FacilityPicker1.Region + "&city=" + FacilityPicker1.City + "&namefilter=" + FacilityPicker1.NameFilter + "&sortField=" + FacilityPicker1.SortField + "&sortOrder=" + FacilityPicker1.SortOrder

Then I pick up the sort/filters on the Page_Load event of the .aspx. Here is code behind for my "Export to Excel' .aspx page.

Namespace RCMS
Public Class FacilityListExcel
Inherits System.Web.UI.Page
Protected WithEvents grdFacilities As System.Web.UI.WebControls.DataGrid
Private region As String
Private city As String
Private nameFilter As String
Private sortOrder As String
Private sortField As String
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
If Not Request("region") Is Nothing Then
region = CType(Request("region"), String)
End If
If Not Request("city") Is Nothing Then
city = CType(Request("city"), String)
End If
If Not Request("nameFilter") Is Nothing Then
nameFilter = CType(Request("nameFilter"), String)
End If
If Not Request("sortOrder") Is Nothing Then
sortOrder = CType(Request("sortOrder"), String)
End If
If Not Request("sortField") Is Nothing Then
sortField = CType(Request("sortField"), String)
End If
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter
Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)
BindData()
ClearControls(Me.grdFacilities)
grdFacilities.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter.ToString())
Response.End()
Catch exc As Exception 'Module failed to load
ProcessModuleLoadException(Me, exc)
End Try
End Sub
Private Sub ClearControls(ByVal Control As Control)
Try
For i As Integer = Control.Controls.Count - 1 To 0 Step -1
ClearControls(Control.Controls(i))
Next i
If Not TypeOf (Control) Is TableCell Then
If Not Control.GetType().GetProperty("SelectedItem") Is Nothing Then
Dim literal As LiteralControl = New LiteralControl
Control.Parent.Controls.Add(literal)
Try
literal.Text = CType(Control.GetType().GetProperty("SelectedItem").GetValue(Control, Nothing), String)
Catch
End Try
Control.Parent.Controls.Remove(Control)
ElseIf Not Control.GetType().GetProperty("Text") Is Nothing Then
Dim literal As LiteralControl = New LiteralControl
Control.Parent.Controls.Add(literal)
literal.Text = CType(Control.GetType().GetProperty("Text").GetValue(Control, Nothing), String)
Control.Parent.Controls.Remove(Control)
End If
End If
Catch exc As Exception 'Module failed to load
ProcessModuleLoadException(Me, exc)
End Try
End Sub
Public Sub BindData()
Try
Dim facilityController As New facilityController
Dim facilityList As ArrayList
facilityList = facilityController.GetFacilityList(region, city, nameFilter)
Select Case sortField
Case "Name"
facilityList.Sort(New FacilitySortByName)
Case "RegionDescription"
facilityList.Sort(New FacilitySortByRegionDescription)
Case "City"
facilityList.Sort(New FacilitySortByCity)
End Select
If sortOrder = "DESC" Then
facilityList.Reverse()
End If
Me.grdFacilities.DataSource = facilityList
Me.grdFacilities.PageSize = facilityList.Count + 1
Me.grdFacilities.DataBind()
Catch exc As Exception 'Module failed to load
ProcessModuleLoadException(Me, exc)
End Try
End Sub
End Class
End Namespace
0
mathisjay
9/2/2004 2:06:08 AM
Hi again.

Thanks for the code. It did help me with the idea that I can pass the sort fields/orders in the URL and fetch at the .aspx page.
But here I am not using a Datagrid but a DataView and populating the Excel sheet from the Data in DataView directly. My code looks like this (dvExport is the DataView obtained from Session object)
dvExport.Sort = sortExpression + " " + SortOrder
Response.Write ("<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">")
Response.Write ("<head>")
Response.Write ("<!--[if gte mso 9]><xml>")
Response.Write ("<x:ExcelWorkbook>")
Response.Write ("<x:ExcelWorksheets>")
Response.Write ("<x:ExcelWorksheet>")
Response.Write ("<x:Name>"& dvExport.Table.TableName &"</x:Name>")
Response.Write ("<x:WorksheetOptions>")
Response.Write ("<x:Print>")
Response.Write ("<x:ValidPrinterInfo/>")
Response.Write ("</x:Print>")
Response.Write ("</x:WorksheetOptions>")
Response.Write ("</x:ExcelWorksheet>")
Response.Write ("</x:ExcelWorksheets>")
Response.Write ("</x:ExcelWorkbook>")
Response.Write ("</xml>")
Response.Write ("<![endif]--> ")
Response.Write ("</head>")
Response.Write ("<body><table><tr>")
'Column Headers
For j = 0 To dvExport.Table.Columns.Count - 1
Response.Write("<td align=center class=bolddata>" + CStr(dvExport.Table.Columns.Item(j).ColumnName()) + "</td>")
Next
Response.Write ("</tr>")
'Rows
For i = 0 To dvExport.Table.Rows.Count - 1
Response.Write("<tr>")
For j = 0 To dvExport.Table.Columns.Count - 1
Response.Write("<td>" + CStr(IIf(Not IsDBNull(dvExport.Table.Rows(i)(j)),dvExport.Table.Rows(i)(j),"NULL")) + "</td>")
Next
Response.Write("</tr>")
Next
Response.Write("</table></body>")
Response.Write("</html>")
I am doing this so I can better format the data after I get the exported data. Even though I am doing a sort on the DataView, it still seems to keep the order of the rows intact when I loop through the rows.
Although, with the datagrid I am able to get the sorted order in Excel. I don't want to use the datagrid here for the following reasons:
1. Some posts say that datagrid has a limitation on the number of rows it can handle and can fail if I have large DataSets. I don't want something like that to happen since we have a large customer base.
2. Here as you can see in my code: I can rename the Excel sheet based on the Report I am generating.
3. I can keep the look and feel of the Excel intact.
Please let me know your thoughts on this. Is there anyway to get around it and display the sorted DataView. Thanks a bunch!!
0
dnn_user
9/2/2004 3:07:49 PM
OK!!! I guess I solved my own question.. :-)

After you sort the Dataview as
dsExport.Sort = ....
You can do the following:
Dim datarow1 As DataRowView
FOR EACH datarow1 IN dvExport
Response.Write("<tr>")
For j = 0 To dvExport.Table.Columns.Count - 1
Response.Write("<td>" + CStr(IIf(Not IsDBNull(datarow1(j)),datarow1(j),"NULL")) + "</td>")
Next
Response.Write("</tr>")
NEXT
This way, you can loop through the sorted data.
0
dnn_user
9/2/2004 3:24:20 PM
Reply: