.NET Data Secrets, Part III
Sorting in Seconds, with Your Web Grid
When you're looking at a grid full of data, it's common to want to order it somehow. You may want to view employees by salary amount, from the highest paid to the lowest. Or view a customized query showing sales staff and their related telephone numbers, alphabetically.
That's all easy, with sorting. And the ASP.NET DataGrid includes built-in support for this excellent method. Here, I'm going to demonstrate the technique in its simplest form; however, you can greatly expand upon this to build a much more elegant solution.
Next—and this is the important bit—specify a 'Sort expression' value for each of the columns you're going to allow your users to sort by. When run, this will turn the column header into a hyperlink which, when clicked, will fire off an event. This event will receive your sort expression value as an argument, which you then can use to reorder the data appropriately. So, select a few values for the 'Sort expression:' for the purpose of this example, select the field names themselves (you'll see why it makes things easier next). Click OK when finished.
Next, we're ready to add code to handle this sorting ourselves. No, this process isn't automatic—we need to somehow reorder our data and then rebind to the DataGrid. I'm going to handle this by simply requerying my data source, though with an extra ORDER BY clause. Here's the code I'll be entering to respond to the SortCommand event of my DataGrid:
' Reset index, in case on a different page MyDataGrid.CurrentPageIndex = 0 ' Change SELECT statement, using the passed in 'sort ' expression' we specified earlier MyDataAdapter.SelectCommand.CommandText = _ "SELECT * FROM mytablename ORDER BY " & e.SortExpression ' Refresh data and rebind MyDataAdapter.Fill(MyDataSet) MyDataGrid.DataBind()
Here, you can see my code initially resets the index, in case we're on another "page" of the DataGrid somewhere. It then uses our DataAdapter to alter the underlying SELECT statement, ordering by the current sort expression (the field name we specified earlier). Note that there are other, more elegant ways of handling this, rather than directly rewriting the SELECT text; however, for simplicity and demonstration purposes, this technique's a winner. Finally, we refill the DataSet, then bind it back to the DataGrid.
Almost there now. To activate all your hard work, all you need to do now is select your DataGrid and change its AllowSorting property to True.
And that's it! Your grid will now allow you to click on the header text of any of your 'sort-aware' columns and put them in correct, ascending order. With a little imagination, you also can expand on this code base to allow 'reverse sorts' too. Have a play around!
Click and sort, in just a few lines of code
Amazingly Simple Method for Exporting Your Web Grid to Excel
There are some things developers just won't tell you about—little secrets, hidden developer tricks that take weeks to find out about, but just minutes to pass on. My book is all about exposing such golden nuggets—and this next baby is a prime example.
Over the next few paragraphs, I'll demonstrate how to take a regular ASP.NET DataGrid and feed it back down to your user as though it were an actual Excel spreadsheet—and you don't even need Excel on your server. Best of all, it only takes just a few extra lines of code.
Firstly, work on getting information to your DataGrid as normal; however, ensure you don't incorporate any special features such as Select buttons or paging. You can, however, apply as much color or text formatting as you wish—right-click, select 'Auto Format,' and go wild.
Next, add code similar to the following to respond to the page Load event:
' Put information into your DataGrid, for example: MyDataAdapter.Fill(MyDataSet) MyDataGrid.DataSource = MyDataSet.Tables(0) MyDataGrid.DataBind() ' Tell browser this is 'Excel' information ' and prepare to send down info Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Me.EnableViewState = False ' Get the DataGrid HTML from the control, ' then write straight to the browser Dim objSW As New System.IO.StringWriter() Dim objHTW As New System.Web.UI.HtmlTextWriter(objSW) MyDataGrid.RenderControl(objHTW) Response.Write(objSW.ToString()) Response.End()
Here, our code fills the DataGrid, and then tells the browser it'll be sending down Excel information. The HTML for the DataGrid is then retrieved and pushed down. Excel then translates this into its own cells—and your user simply sees an Excel spreadsheet containing your data embedded in their browser.
Top Tip: A word of warning, folks. A bug in the release version of Office 2000 means that accessing an .ASP or .ASPX page (i.e., "MyForm.aspx") with an embedded Excel resource like this results in a blank page (see Knowledge Base article KB266263). The workaround is that when linking to your Excel page, add a placebo query string or just a simple question mark (i.e., "MyForm.aspx?") at the end of the filename. Alternatively, read on for another solution.
What if you want to create an Excel page that will actually open in Excel—or prompt the user to save it as an actual file? Easy; just add the following line to your code above, around where you set the .ContentType property:
Response.AddHeader("content-disposition", _ "attachment;filename=myfile.xls")
This prompts the user to either open, or save with a default name of 'myfile.xls'. It also has the benefit of not suffering from the bug mentioned in the above tip.
My Web grid, embedded as an Excel sheet within Internet Explorer
Page 2 of 3