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.
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.Columns.Count; colh++)
for(int row=0; row< dsReport.Tables.Rows.Count; row++)
for(int colm=0; colm< dsReport.Tables.Columns.Count; colm++)
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" />
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
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
TextQualifier properties of the Microsoft Tabular Data control. The
UseHeader property is set to
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">
if(strName == strColumn)
if(strSequence == '+')
strSequence = '-';
strSequence = '+';
strColumn = strName;
strSequence = '+';
document.getElementById("data2").Sort = strSequence + strColumn;
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);
"')>" + dataSet.Fields(i).name + "</A>"
- With Next and Prev buttons
- With page indexes
Any one of these can be used according to the project requirements. The
document.getElementById('tbldata').dataPageSize = 100 ;
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.