Generating Excel Report in ASP.NET 2.0

Introduction

Excel is such a powerful tool that almost all companies make extensive use of it for analyzing data. Moreover, the internet is not as it was 10 years ago. Almost all pages that are out there on the internet are dynamic ones, that is, interacts with a database in backend to produce results. Sometimes, data that are displayed, if made available on in an Excel file, proper analysis of the data often helps in making more accurate decisions by using Excel features. In this tutorial, we will learn how to interact with Excel files, both reading and writing.

Excel - ASP.NET Scenarios

For the purpose of this tutorial, an Excel sheet had been prepared that holds record of students and their respective marks in subjects. Please note that these records are fictitious and had been input randomly.

StudentId	StudentName
10001	John Thomas
10002	Terry Lane
10003	Anne Marie
10004	Tom Sawyer
10005	Derek Ince
10006	Emerson Boyce
10007	Thommas Kolka
10008	Edison Hall
10009	Teddy Harewood
10010	Williams Yorkshire

Fig 1. Table holding information for students

StudentId	Marks
10001	50
10002	60
10003	75
10004	80
10005	90
10006	95
10007	100
10008	20
10009	48
10010	66

Fig 2. Table holding student marks in Mathematics subject

StudentId	Marks
10001	30
10002	25
10003	65
10004	78
10005	95
10006	64
10007	32
10008	88
10009	95
10010	99

Fig 3. Table holding student marks in Geography subject

Interacting with Excel

Fortunately, there are COM objects that are available to interact with the Excel file. Also, we can make use of the OleDB class to treat the Excel file as a simple table. In this way, we can fire SQL queries to the Excel file so that we can retrieve data from the sheets. An analogy can be made to a database. The Excel Workbook is considered as the Database while the Sheets are considered as tables. Microsoft Excel 11.0 Object Library should be added as Reference to get the features for interacting with Excel.

Fig 4. Adding Microsoft Excel 11.0 Object Library as Reference

The Excel file is then added to the DataSource folder in ASAP.NET to be interacted with. To connect to a certain database, a connection string is needed. The same thing applies for Excel. The following is a key added in the Web.Config file for future reference.

<add name="ExcelConnection" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|\SchoolMgt.xls;Extended Properties=Excel 8.0"/>

Note that our Excel file which contains the necessary data will be SchoolMgt.xls, as written in the connection string.

Fig 5. Excel file illustrating Excel Book containing data sheets with data

Reading data from Excel sheets

Let us build an interface for getting data from the Excel file.

Fig 6. Interface to search records per table selected.

It is important to import the following:

Imports System.Data.OleDb
Imports System.Data

The following code creates the connection and builds the query based on the selected table.

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
            Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
            Dim dbConn As New OleDbConnection(strExcelConn)
            Dim strSQL As String
 
            strSQL = "SELECT * FROM [" & cmbSheets.SelectedItem.ToString() & "$]"
 
            dbConn.Open()

            Dim cmd As New OleDbCommand(strSQL, dbConn)
            Dim dsExcel As New DataSet
            Dim daExcel As New OleDbDataAdapter(cmd)

            daExcel.Fill(dsExcel)

            dgResults.DataSource = dsExcel
            dgResults.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
End Sub

Please note that the table name in the SELECT query should be in the format [TableName$], which the square brackets and the dollar sign at the end.

Playing with SELECT statements

As you have guessed, the Excel file has turned out to be a normal database. The transparency that the OleDB connection had made is exceptional. Also, note that the first row is taken to contain field names for the columns.

Now, let us get the marks of the students in Mathematics, Geography and Total, sorted in Descending order.

Protected Sub btnGenerateReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click
Try
 
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String
 
strSQL = "SELECT S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total "& _
                     "FROM [Students$] S, [Mathematics$] M, [Geography$] G "& _
                     "WHERE(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
                     "ORDER BY (M.Marks+G.Marks) DESC"
dbConn.Open()

Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)

daExcel.Fill(dsExcel)

dgReports.DataSource = dsExcel
dgReports.DataBind()
 
Catch ex As Exception
Throw ex
End Try
End Sub

Fig 7. Report generating the students' marks and total, sorted in descending order.

Generating Excel Reports

Two ways for generating Excel report will be discussed in this tutorial. The first one is using the "Response" class and the second one by manipulating the Excel objects that "Microsoft Excel 11.0" Objects provide.

The following piece of code illustrates how this is done

    Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByResponse.Click
        '   Variables declaration
        Dim dsExport As New DataSet()
        Dim tw As New System.IO.StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
        Dim dgGrid As New DataGrid()

        dgGrid.DataSource = getData()

        '   Report Header
        hw.WriteLine(" Student Marking Report ")

        '   Get the HTML for the control.
        dgGrid.HeaderStyle.Font.Bold = True
        dgGrid.DataBind()
        dgGrid.RenderControl(hw)

        '   Write the HTML back to the browser.
        Response.ContentType = "application/vnd.ms-excel"
        Me.EnableViewState = False
        Response.Write(tw.ToString())
        Response.End()
    End Sub

• First, we create the dataset that will hold the records. Then, a StringWriter object (tw) is also created along with an HtmlTextWriter (hw) which takes as parameter the text writer object. They are important for rendering purposes; i.e. html tags can be applied like bold, italic etc. to the resulting Excel report.
• Also, a datagrid object which will store the dataset of records.
• Note that function getData() in the code snippet only contains the codes in the previous example which returns a dataset of records.
• hw.WriteLine(" Student Marking Report ") shows that you can also combine HTML codes with the output for proper display. In our example, the font size will be 5, underlined and bold.
• dgGrid.RenderControl(hw) renders the HTML object with the data that the datagrid contains. The result is normally formatted in a tabular format.
• Response.ContentType = "application/vnd.ms-excel" makes the Reponse object output to Excel.
• Response.Write(tw.ToString()) outputs the formatted object to Excel.

Figure 8. Output result showing the report that is generated in Excel

The second way for generating Excel reports from ASP.NET is to use the Microsoft Excel 11.0 Objects. This method offers more fallibility in terms of manipulating the features that Excel provides and formatting the report.

The following code snippet makes use of the various Excel objects that Excel provides to generate the report.

Imports System.Reflection
Imports Excel = Microsoft.Office.Interop.Excel

    Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
        Try
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet

            xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
            xlWorkBook.Application.Visible = True
            xlWorkSheet = xlWorkBook.ActiveSheet

            '   Gets the dataset containing the data
            Dim dsData As DataSet = getData()
            Dim i As Integer = 2

            '   Outputting the fieldnames in pink bold color
            xlWorkSheet.Cells(1, 1) = "Student ID"
            xlWorkSheet.Cells(1, 2) = "Student Name"
            xlWorkSheet.Cells(1, 3) = "Mathematics"
            xlWorkSheet.Cells(1, 4) = "Geography"
            xlWorkSheet.Cells(1, 5) = "Total"
 
            xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
            xlWorkSheet.Range("$A1:$E1").Font.Bold = True
 
            '   Outputting the data
            For Each dr As DataRow In dsData.Tables(0).Rows
                xlWorkSheet.Cells(i, 1) = dr(0)
                xlWorkSheet.Cells(i, 2) = dr(1)
                xlWorkSheet.Cells(i, 3) = dr(2)
                xlWorkSheet.Cells(i, 4) = dr(3)

                '   Building the formula for calculating the sum
                xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())

                '   Going to the next row
                i = i + 1
            Next
 
            '   Auto fit the columns
            xlWorkSheet.Columns.AutoFit()

            '   Generating the graph
            Dim chart As Excel.Chart
            chart = xlWorkBook.Charts.Add()

            With chart
                .ChartType = Excel.XlChartType.xlColumnClustered
                .SetSourceData(xlWorkSheet.Range("A1:E11"), 2)

                .HasTitle = True
                .ChartTitle.Characters.Text = "Students' marks"
 
                .Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
                .Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
            End With
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

An Excel workbook is first created. Then the active sheet is accessed by xlWorkSheet = xlWorkBook.ActiveSheet. After that, we get the data into the dataset, ready to be processed. Using the Font property, the title is displayed in Pink bold colour. Note how the Formula that Excel provides is properly used to calculate the SUM of the marks for each student xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString()).

Figure 9. Excel Report having output the list of students and their respective marks.

A chart is also generated using the objects. The x-axis consists of the names and id of each student. The y-axis, the marks for the different students. The chart is always handy for analysis of data.

Figure 10. Chart that is generated using Microsoft Excel 11.0 Objects

Summary

In this tutorial, you learned how to read data from Excel, generate report using the Response objects and to play with the Microsoft Excel 11.0 objects provided. Indeed, it is impossible to cover all features in this tutorial but many important techniques were covered to help you down the line. It is up to you to be creative and adapt the techniques to your scenarios.

Going professional with NativeExcel for .NET

Excel is powerful tool, but in real world development you rarely have Microsoft Office installed on your web server. There is no Excel on shared hosting and obtaining Office license for dedicated server is possible, but also pretty expensive. Fortunately, there is pure .NET component named Native Excel for .NET that creates or modifies Excel documents on the fly. For just $120 for single license you can use it on unlimited number of web sites and servers (source code available too). It is easier to distribute application since you work with simple assembly written in C#, instead of COM Excel object. Best of all, you don't need to learn anything new because objects and properties are identical to Excel's, so if you understood this tutorial you are already expert for NativeExcel too :).

Writing Dataset to Excel in ASP.NET

In this article, we are going to see how to open a write a dataset to a excel file and open the excel file in the browser.

In order for this to work, there is an important modification in web.config file. We have to add else you will get an 'Access is denied' error.
In the application, we have to add a reference for a COM component called "Microsoft Excel 9.0 object library".

Now we have to just loop through the dataset records and populate to each cell in the excel.
Code:

private void createDataInExcel(DataSet ds)

{

          Application oXL;

          _Workbook oWB;

          _Worksheet oSheet;

          Range oRng;

          string strCurrentDir = Server.MapPath(".") + "\\reports\\";

          try

          {

                   oXL = new Application();

                   oXL.Visible = false;

                   //Get a new workbook.

                   oWB = (_Workbook)(oXL.Workbooks.Add( Missing.Value ));

                   oSheet = (_Worksheet)oWB.ActiveSheet;

                   //System.Data.DataTable dtGridData=ds.Tables[0];

                   int iRow =2;

                   if(ds.Tables[0].Rows.Count>0)

                   {

                             //     for(int j=0;j

                             //     {

                             //      oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

                             //

                             for(int j=0;j

                             {

                                       oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

                             }

                             // For each row, print the values of each column.

                             for(int rowNo=0;rowNo

                             {

                                       for(int colNo=0;colNo

                                       {

                                                 oSheet.Cells[iRow,colNo+1]=ds.Tables[0].Rows[rowNo][colNo].ToString();

                                       }

                             }

                             iRow++;

                    }

                    oRng = oSheet.get_Range("A1", "IV1");

                    oRng.EntireColumn.AutoFit();

                    oXL.Visible = false;

                    oXL.UserControl = false;

                    string strFile ="report"+ DateTime.Now.Ticks.ToString() +".xls";//+

                    oWB.SaveAs( strCurrentDir +
               strFile,XlFileFormat.xlWorkbookNormal,null,null,false,false,XlSaveAsAccessMode.xlShared,false,false,null,null);

                   // Need all following code to clean up and remove all references!!!

                   oWB.Close(null,null,null);

                   oXL.Workbooks.Close();

                   oXL.Quit();

                   Marshal.ReleaseComObject (oRng);

                   Marshal.ReleaseComObject (oXL);

                   Marshal.ReleaseComObject (oSheet);

                   Marshal.ReleaseComObject (oWB);

                   string  strMachineName = Request.ServerVariables["SERVER_NAME"];

                   Response.Redirect("http://" + strMachineName +"/"+"ViewNorthWindSample/reports/"+strFile);

          }

          catch( Exception theException )

          {

                    Response.Write(theException.Message);

          }

}

ASP.NET Tip: Exporting Data to Excel

A common request of users is to be able to download data for use in Microsoft Excel. This is actually fairly easy to accomplish without a lot of extra work. I do most of my database work with DataTable objects, as I don't need the overhead of a DataSet. I also have a Database wrapper class that is able to easily retrieve data from a SQL Server database. Once I use my function to get my DataTable, I can start exporting the data. For this example, however, I'll use the traditional SqlDataAdapter method to retrieve the data initially.
There are a few tricks to making this work. First, you have to create an ASPX page with no HTML content in it. The only thing that should be in the ASPX page is the Page directive at the top. The second trick is to clear the existing content and send down a new content type. For Excel, you use a content type of application/vnd.ms-excel. This tells your Web browser to expect something that can be handled within Excel. The third trick is to send down the data tab-delimited per column and a newline at the end of each row. After that, Excel does the rest of the work for you.
Here's the code:

protected void Page_Load(object sender, EventArgs e)
{
       SqlConnection cn = new SqlConnection("yourconnectionstring");
       cn.Open();
       SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", cn);
       DataTable dt = new DataTable();
       da.Fill(dt);
       cn.Close();
 
       Response.Clear();
       Response.ContentType = "application/vnd.ms-excel";
       string sep = "";
       foreach (DataColumn dc in dt.Columns)
       {
              Response.Write(sep + dc.ColumnName);
              sep = "\t";
       }
       Response.Write("\n");
 
       int i;
       foreach (DataRow dr in dt.Rows)
       {
              sep = "";
              for (i = 0; i < dt.Columns.Count; i++)
              {
                     Response.Write(sep + dr[i].ToString());
                     sep = "\t";
              }
              Response.Write("\n");
       }
 
}

After I open my connection and retrieve my data, I clear the output buffer and send down a new content type, since the default type of text/HTML isn't appropriate in this case. I then loop through the columns and write out the column names, separated by tabs. At the end of the field list, I send down a newline character.
I then loop through the rows of the table and, within each row, loop through the fields. Each field value is printed out in its default format under the appropriate column header. If you have data types, such as Booleans, that should be printed in a different format, you can look at the dt.Columns collection to help determine which data type each field is and use an appropriate conversion function.
The result of running this page is a prompt to open or save the Excel spreadsheet. You can use this code with any query and any database connection. Once you've got the content cleared and the content type set, Excel does the hard work of formatting for you.

Creating PDF Documents in ASP.NET

Introduction

This short article explains how to create PDF documents from ASP.NET web pages using this free library: http://sourceforge.net/projects/itextsharp/.

The code

First of all, I will create a simple "Hello PDF". Next, I will create a more complex PDF document with tables. To start creating PDF documents, you need to download the iTextSharp library from http://sourceforge.net/projects/itextsharp/ and reference it in your project. The PDF documents are created "on the fly" by the web page "ShowPDF.aspx". This page also does a "Response.Redirect" to the created PDF. We will first import the required namespaces:

Imports System
Imports System.IO
Imports iTextSharp.text
Imports iTextSharp.text.pdf

Next, in the Page_Load event, we find out which document was requested by the user:

Collapse

Partial Class ShowPDF
   Inherits System.Web.UI.Page
   
   Protected Sub Page_Load(ByVal sender As Object, _
             ByVal e As System.EventArgs) Handles Me.Load
      If Request.QueryString("id") = 1 Then
         ShowHello()
       Else
         ShowTable()
       End If
   End Sub
End Class

The ShowHello function creates a simple document with just one string: "Hello World", and then redirects the user to the newly created document:

Sub ShowHello()
   Dim doc As Document = New Document
   PdfWriter.GetInstance(doc, New FileStream(Request.PhysicalApplicationPath + _
                         "\1.pdf", FileMode.Create))
   doc.Open()
   doc.Add(New Paragraph("Hello World"))
   doc.Close()
   Response.Redirect("~/1.pdf")
End Sub

A more complex example

The function ShowTable is slightly more complex. It also creates a PDF document and redirects the user to it:

Sub ShowTable()
   Dim doc As Document = New Document
   PdfWriter.GetInstance(doc, New FileStream(Request.PhysicalApplicationPath + _
                         "\2.pdf", FileMode.Create))
   doc.Open()
   Dim table As Table = New Table(3)
   table.BorderWidth = 1
   table.BorderColor = New Color(0, 0, 255)
   table.Padding = 3
   table.Spacing = 1
   Dim cell As Cell = New Cell("header")
   cell.Header = True
   cell.Colspan = 3
   table.AddCell(cell)
   cell = New Cell("example cell with colspan 1 and rowspan 2")
   cell.Rowspan = 2
   cell.BorderColor = New Color(255, 0, 0)
   table.AddCell(cell)
   table.AddCell("1.1")
   table.AddCell("2.1")
   table.AddCell("1.2")
   table.AddCell("2.2")
   table.AddCell("cell test1")
   cell = New Cell("big cell")
   cell.Rowspan = 2
   cell.Colspan = 2
   cell.HorizontalAlignment = Element.ALIGN_CENTER
   cell.VerticalAlignment = Element.ALIGN_MIDDLE
   cell.BackgroundColor = New Color(192, 192, 192)
   table.AddCell(cell)
   table.AddCell("cell test2")
   doc.Add(table)
   doc.Close()
   Response.Redirect("~/2.pdf")
End Sub

Using the iTextSharp library (http://sourceforge.net/projects/itextsharp/) makes it very easy to create PDF documents from web applications.

Routing in ASP.NET 4

ASP.NET 4 adds built-in support for using routing with Web Forms. Routing lets you configure an application to accept request URLs that do not map to physical files. Instead, you can use routing to define URLs that are meaningful to users and that can help with search-engine optimization (SEO) for your application. For example, the URL for a page that displays product categories in an existing application might look like the following example:

http://website/products.aspx?categoryid=12 

By using routing, you can configure the application to accept the following URL to render the same information:

http://website/products/software 

Routing has been available starting with ASP.NET 3.5 SP1. (For an example of how to use routing in ASP.NET 3.5 SP1, see the entry Using Routing With WebForms on Phil Haack's blog.) However, ASP.NET 4 includes some features that make it easier to use routing, including the following:

• The PageRouteHandler class, which is a simple HTTP handler that you use when you define routes. The class passes data to the page that the request is routed to.
• The new properties HttpRequest.RequestContext and Page.RouteData (which is a proxy for the HttpRequest.RequestContext.RouteData object). These properties make it easier to access information that is passed from the route.
• The following new expression builders, which are defined in System.Web.Compilation.RouteUrlExpressionBuilder and System.Web.Compilation.RouteValueExpressionBuilder:
• RouteUrl, which provides a simple way to create a URL that corresponds to a route URL within an ASP.NET server control.
• RouteValue, which provides a simple way to extract information from the RouteContext object.
• The RouteParameter class, which makes it easier to pass data contained in a RouteContext object to a query for a data source control (similar to FormParameter).

Routing for Web Forms Pages

The following example shows how to define a Web Forms route by using the new MapPageRoute method of the Route class:

public class Global : System.Web.HttpApplication 

{ 

  void Application_Start(object sender, EventArgs e) 

  { 

    RouteTable.Routes.MapPageRoute("SearchRoute", 

      "search/{searchterm}", "~/search.aspx"); 

    RouteTable.Routes.MapPageRoute("UserRoute", 

      "users/{username}", "~/users.aspx"); 

  } 

} 

ASP.NET 4 introduces the MapPageRoute method. The following example is equivalent to the SearchRoute definition shown in the previous example, but uses the PageRouteHandler class.

RouteTable.Routes.Add("SearchRoute", new Route("search/{searchterm}", 

  new PageRouteHandler("~/search.aspx"))); 

The code in the example maps the route to a physical page (in the first route, to ~/search.aspx). The first route definition also specifies that the parameter named searchterm should be extracted from the URL and passed to the page.
The MapPageRoute method supports the following method overloads:

• MapPageRoute(string routeName, string routeUrl, string physicalFile, bool checkPhysicalUrlAccess)
• MapPageRoute(string routeName, string routeUrl, string physicalFile, bool checkPhysicalUrlAccess, RouteValueDictionary defaults)
• MapPageRoute(string routeName, string routeUrl, string physicalFile, bool checkPhysicalUrlAccess, RouteValueDictionary defaults, RouteValueDictionary constraints)

The checkPhysicalUrlAccess parameter specifies whether the route should check the security permissions for the physical page being routed to (in this case, search.aspx) and the permissions on the incoming URL (in this case, search/{searchterm}). If the value of checkPhysicalUrlAccess is false, only the permissions of the incoming URL will be checked. These permissions are defined in the Web.config file using settings such as the following:

<configuration> 

  <location path="search.aspx"> 

    <system.web> 

      <authorization> 

        <allow roles="admin"/> 

        <deny users="*"/> 

      </authorization> 

    </system.web> 

  </location> 

  <location path="search"> 

    <system.web> 

      <authorization> 

        <allow users="*"/> 

      </authorization> 

    </system.web> 

  </location> 

</configuration> 

In the example configuration, access is denied to the physical page search.aspx for all users except those who are in the admin role. When the checkPhysicalUrlAccess parameter is set to true (which is its default value), only admin users are allowed to access the URL /search/{searchterm}, because the physical page search.aspx is restricted to users in that role. If checkPhysicalUrlAccess is set to false and the site is configured as shown in the previous example, all authenticated users are allowed to access the URL /search/{searchterm}.

Reading Routing Information in a Web Forms Page

In the code of the Web Forms physical page, you can access the information that routing has extracted from the URL (or other information that another object has added to the RouteData object) by using two new properties: HttpRequest.RequestContext and Page.RouteData. (Page.RouteData wraps HttpRequest.RequestContext.RouteData.) The following example shows how to use Page.RouteData.

protected void Page_Load(object sender, EventArgs e) 

{ 

  string searchterm = Page.RouteData.Values["searchterm"] as string; 

  label1.Text = searchterm; 

} 

The code extracts the value that was passed for the searchterm parameter, as defined in the example route earlier. Consider the following request URL:

http://localhost/search/scott/ 

When this request is made, the word "scott" would be rendered in the search.aspx page.

Accessing Routing Information in Markup

The method described in the previous section shows how to get route data in code in a Web Forms page. You can also use expressions in markup that give you access to the same information. Expression builders are a powerful and elegant way to work with declarative code. (For more information, see the entry Express Yourself With Custom Expression Builders on Phil Haack's blog.)
ASP.NET 4 includes two new expression builders for Web Forms routing. The following example shows how to use them.

<asp:HyperLink ID="HyperLink1" runat="server" 

  NavigateUrl="<%$RouteUrl:SearchTerm=scott%>">Search for Scott</asp:HyperLink> 

In the example, the RouteUrl expression is used to define a URL that is based on a route parameter. This saves you from having to hard-code the complete URL into the markup, and lets you change the URL structure later without requiring any change to this link.
Based on the route defined earlier, this markup generates the following URL:

http://localhost/search/scott

ASP.NET automatically works out the correct route (that is, it generates the correct URL) based on the input parameters. You can also include a route name in the expression, which lets you specify a route to use.
The following example shows how to use the RouteValue expression.

<asp:Label ID="Label1" runat="server" Text="<%$RouteValue:SearchTerm%>" />

When the page that contains this control runs, the value "scott" is displayed in the label.
The RouteValue expression makes it simple to use route data in markup, and it avoids having to work with the more complex Page.RouteData["x"] syntax in markup.

Using Route Data for Data Source Control Parameters

The RouteParameter class lets you specify route data as a parameter value for queries in a data source control. It works much like the class, as shown in the following example:

<asp:sqldatasource id="SqlDataSource1" runat="server" 

    connectionstring="<%$ ConnectionStrings:MyNorthwind %>" 

    selectcommand="SELECT CompanyName,ShipperID FROM Shippers where 

      CompanyName=@companyname" 

  <selectparameters> 

    <asp:routeparameter name="companyname" RouteKey="searchterm" /> 

  </selectparameters> 

</asp:sqldatasource>  

In this case, the value of the route parameter searchterm will be used for the @companyname parameter in the Select statement