Client-Side Tabular Display of Data and Exporting to Excel

• Download source code - 7.26 KB

Introduction

This article explains how to store data in the client side using the Microsoft Tabular Data control and using this data for tabular display. The table has sorting and pagination implemented using JavaScript. It also has an export to Excel feature using the File System Object (FSO) which saves the Excel file to the user Desktop.

Background

The client requirement for my project was to display customer data in the front end with export to Excel facility. The data was around 100,000 records. The database fetch was taking long and the front end could not handle such an amount of data in a DataSet and DataGrid. The report was taking a lot of time and at times the thread was getting aborted.

This made me look out for other options especially handling these requirements at the client side. I came across the Microsoft Tabular Data control and File System Object which I have made use of by tapping most of their features. I have also added JavaScript code for implementing indexed paging.

Using the code

The code can be downloaded from the link above, which is a working model of the article. The code is written in VS 2005 but is applicable for 2003 also. Let me explain the code starting with the Data.cs file (found in the App_Code folder).

Data.cs is a class file I've written for returning comma separated data. This is just for the article's purpose. This file can actually be your data layer where you fetch data from the database in a DataSet. In such a case, you can return a comma separated string by accessing values from the DataSet, something like this:

DataSet dsReport ;
StringBuilder strFinal = new StringBuilder() ;

for(int colh=0; colh< dsReport.Tables[0].Columns.Count; colh++)
{
    strFinal.Append(dsReport.Tables[0].Columns[colh].ColumnName.ToString()) ;
    strFinal.Append(",");
}
strFinal.Remove(strFinal.Length-1,1);
strFinal.Append("\n");

for(int row=0; row< dsReport.Tables[0].Rows.Count; row++)
{
    for(int colm=0; colm< dsReport.Tables[0].Columns.Count; colm++)
    {
        strFinal.Append(dsReport.Tables[0].Rows[row][colm].ToString().Replace(",","~")) ;
        strFinal.Append(",");
    }
    strFinal.Remove(strFinal.Length-1,1);
    strFinal.Append("\n");
}

return strFinal.ToString();

Now moving on to the GetRecords.aspx page. This page is meant for returning data to the display page using a Response object. The code written in the Data class file can very well be written in this page. One thing you must notice is that this page doesn't have any HTML content. Any HTML content has to be deleted because it gets added to the Response object while retrieving it in the display page.

Now the main page, which is TabularDisplay.aspx.

The object used to store the data at the client side is the Microsoft Tabular Data control.

<object id="data2" classid="CLSID:333C7BC4-460F-11D0-BC04-0080C7055A83" >
    <param name="FieldDelim" value="," />
    <param name="UseHeader" value="TRUE" />
    <param name="DataURL" value="GetRecords.aspx" />
</object>

classid="CLSID:333C7BC4-460F-11D0-BC04-0080C7055A83" identifies the object as Microsoft Tabular Data control. The dataurl property of the Microsoft Tabular Data control references to the page that returns data in a Response object. DataUrl can also be a text file at the server end, formatted in such a way that the object will be able to read it. This can be set by the FieldDelim and TextQualifier properties of the Microsoft Tabular Data control. The UseHeader property is set to TRUE so that the header part is not displayed. The Microsoft Tabular Data control works the same way as a DataSet works. The data has to be bound to some control in order to display it. A table or span element can be used for this purpose. What I have done in this demo is dynamically bind the data to a table using JavaScript. This is mainly because the number of columns in the report may vary all the time and thus cannot be bound statically. This also provides flexibility to the solution.

The Fill Table button builds the table. It uses properties and methods exposed by the Microsoft Tabular Data control object. The page indexes are also populated in this event. The code is self explanatory.

The Microsoft Tabular Data control also has options for sorting and filtering data. It can be done directly by using the SORT property of the object.

<PARAM NAME="Sort" VALUE="-grade">

Since the sorting has to be done dynamically in the present scenario, I have added the JavaScript function SortColumn().

function sortColumn(strName) 
{
    if(strName == strColumn)
        if(strSequence == '+')
            strSequence = '-';   // Descending sequence
        else
            strSequence = '+';   // Ascending sequence
    else {
        strColumn = strName;
        strSequence = '+';       // Default ascending
    }

    document.getElementById("data2").Sort = strSequence + strColumn;
    document.getElementById("data2").Reset();
    // Update table display
}

Note that this is the same as a DataGrid that works with a DataSet: in a similar way, the data has to be bound once again to the table after the sort, which is done by the method Reset(). The function provides alternative ASC and DESC sorts on all columns. The function is called in the onclick of the headers that are dynamically generated for this purpose during Fill Table.

for (var i=0; i<dataSet.Fields.count; i++)
{
    newcell = newrow.insertCell(-1); //insert new cell to row
    newcell.innerHTML = "<A href=javascript:sortColumn('" + dataSet.Fields(i).name + 
      "')>" + dataSet.Fields(i).name + "</A>" //dataSet.Fields(i).name ;
}

Paging is also implemented using JavaScript. There are two methods implemented here:

1. With Next and Prev buttons
2. With page indexes

Any one of these can be used according to the project requirements. The DATAPAGESIZE property of the HTML table needs to be altered to indicate the page size. This can also be set using JavaScript:

document.getElementById('tbldata').dataPageSize = 100 ;

Export to Excel functionality is implemented using the File System Object (FSO). You can learn more about FSO from my previous article: JavaScript__File_Handling.aspx.

Here, data is retrieved from the Microsoft Tabular Data control in a similar way the table is populated. The CSV file generated is directly saved onto the user's desktop.

The user must have write access in the path specified while creating a file. In cases where there is uncertainty, it is better to write the file onto the Temporary Folder of the system. The path for the Temp folder can be found using:

var fileUrl = fso.GetSpecialFolder(2) +  "MainReport.csv" ; 
varFileObject = fso.OpenTextFile(fileUrl, 2, true,0);

Hope I have explained the code well. Please write to me if any more clarification is required.

Using the downloadable project TabularDisplay.zip

Download the zip file and extract it to any system folder. Open VS 2005 and open the project using Open-->Website and select the folder. Open IE --> Tools --> Internet Options --> Security. Click on Trusted Sites and select Sites. Add the URL of your server/localhost as a trusted site (e.g.: http://localhost). This is for ActiveX to run in the system.

Select TabularDisplay.aspx as the StartPage and run the project. Click on the FILL TABLE button, which will populate data from the Data.cs page into a table. Sorting and paging is also initialized. Click on Download CSV file. A CSV file is created on your desktop containing the data.

Points of interest

Since we are using ActiveX objects, the site should be added to the trusted sites list in case of high internet security. Please vote for this article if you find it useful.