Export Data to Excel from an ASP.NET Application

This is a common task for ASP.NET developers. You have a Web application where you expose data from a database, Web service, or third-party API and you need an "Export to Excel" button. I have used different approaches to export to Excel from a Web application. Here's some options and guidance of when to use each one.

1. Generate a CSV file: If you open a CSV file in Excel, you can see a table with data. You can use this approach if you don't need control over formatting, styles, or workbook structure. I have used this approach when I pull data from a data source and I don't need to render it on the browser. For example, you are working on a stock options solution and you connect to a Web service that pulls stock market prices. Your users don't want to see detailed stock information on a browser and prefer that you generate and Excel file where they can pivot stock prices.
2. Generate an XML file: (with or without an XSLT, depending if you want to control the schema). Have you opened an XML file in Excel? This is pretty cool. Excel allows you to open XML files with our without a schema. You can also see a table of data in Excel and can have some control on formatting if you use a schema. I have used this approach when I have a data source that is already in XML format.
3. Generate from GridView: I like this one a lot and I think it's a common practice between ASP.NET developers. Some page forms in ASP.NET Web applications display data in data controls. The GridView is a popular control that displays data in a table format. You can use it to bind to data source controls, such as SqlDataSource. You can export to Excel from a GridView using a StringWriter and an HtmlTextWriter. You can use this approach if you already have a page with a GridView. You already did a round-trip to get the data from any given source, so why do it twice? The issue is that you have little control over formatting, style, or workbook structure.
4. Generate an Excel file using the Open XML SDK 2.0: If you use this approach you gain absolute control of the spreadsheet format and content. For example, you can generate a worksheet with a table and another one with a chart based on the same data source. You can have control over formats, styles, content, and document structure. Zeyad has a great post where he provides a detailed sample for this approach: Document Assembly Solution for SpreadsheetML.

Note: I can't stop to mention the big no-no… using the Excel PIA to generate a spreadsheet server-side. This is not a good practice and it's not recommended or supported, so let's forget about this one.

Now, for those of you who are working with any of the first three approaches, you may have seen the prompt of file format differ each time you export to Excel.

You get this message because you are opening a file in Microsoft Office Excel 2007 that contains content that does not match the files extension.

I am not very annoyed about this, but I know some people are. By default, a user can decide whether to open the file when the warning message is displayed, but you can control user-notification either:

1. Updating the registry if you need to control the user-notification on a few PCs.
2. Using a Group Policy Setting if you need to control the user-notification on lots of PCs.

Here's a KB article that provides detailed steps for both options: When you open a file in Excel 2007, you receive a warning that the file format differs from the format that the file name extension specifies.

Here's a quick code sample in C# for approach 3 (Export from GridView) that you can use to export to Excel. I changed my registry following the steps explained in the previous article and it worked like a charm. No prompt!

<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" %>
<%@ Import Namespace="System.IO" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Export to Excel - GridView Sample</title>
</head>
<script language="C#" runat="server">
// Get files from selected path    
private void BindFiles() {
        DirectoryInfo di = new DirectoryInfo(tbPath.Text);
gvFiles.DataSource = di.GetFiles();
gvFiles.DataBind();
}
protected void btnExportToExcel_Click(object sender, EventArgs e) {
        ExportToExcel();
}
//Export to Excel from a GridView
protected void ExportToExcel() {
        Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=MyFiles.xls");
Response.Charset = "";
        this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
gvFiles.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
protected void Page_Load(object sender, EventArgs e) {
        BindFiles();
}
public override void VerifyRenderingInServerForm(Control control) {
    }
</script>
<body>
<form id="form1" runat="server">
<div>
<h1>
            My Files</h1>
<table border="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
<tr>
<td>
                    Path:
</td>
<td>
<asp:TextBox ID="tbPath" runat="server" Width="600px" Text="C:/"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnExportToExcel" runat="server" Text="ExportToExcel"
                        onclick="btnExportToExcel_Click" />
                </td>
</tr>
</table>
</div>
<asp:GridView ID="gvFiles" runat="server">
</asp:GridView>
<br />
    </form>
</body>
</html>

Running classic ASP on IIS 7

 

It's not really difficult to run classic ASP on Internet Information Server 7, but there are some bumps down the road. Here's a little help to get you started:

First of all, check whether you have the Windows feature for ASP installed. Go to Programs and Features in Control Panel and click Turn Windows features on and off. Navigate down the tree of features and make sure ASP is checked.

Next, go to Internet Information Server (IIS) Manager (run inetmgr from the Start Search box to get there fast) and create a new application pool for ASP.

This should be a "No managed code" and "Classic" app pool if you do not intend to mix and match the .NET Framework and ASP.NET in the same pool. The Integrated pipeline would not make sense, because it only applies to integrating HTTP modules that are either native or managed .NET implementations.

I had some difficulties setting the identity of the application pool to the new built-in IUSR account. This account replaces the former computer account called IUSR_machinename. Same goes for the IIS_WPG group for application pools that is replaced by the builtin group IIS_IUSRS. Read more about it here. Under the Advanced Settings of the "ASP" application pool you will find the Identity property under Process Model. The default value is NetworkService. I found no way to set this to the BUILTIN\IUSR by choosing SpecificUser and setting BUILTIN\USR under Identity SpecificUser Credentials. I guess that you shouldn't be running ASP sites under IUSR anymore. It is used for anonymous users automatically.

To easy the administration effort, there is a way to turn off the IUSR account without turning off anonymous identification:

appcmd set config -section:anonymousAuthentication -userName:"" --password

And lastly, create your new web application. For example, copy the ASP site folder under C:\inetpub\wwwroot, where the default installation location of "Default Web Site" of IIS. Convert the folder (or virtual directory) to an application and choose "ASP" as the application pool. Check whether the .asp extension is mapped to the correct handler and whether it is enabled. Also make sure that the identity of your application pool has sufficient rights (and no more than that) to access the files in the web site's folder.

One final note: whenever there is a error in the ASP website, and you get this error message: "An error occurred on the server when processing the URL. Please contact the system administrator",

it's probably because by default no error messages are sent to the client in IIS7. Change the setting "Send Errors To Browser" of your web application under the ASP icon to reveal such errors.

Side note: if you get the nice Internet Explorer 500 error message and no particular details at all, remember to uncheck the "Show friendly HTTP error messages" checkbox under Internet Options, Advanced of IE.

How to get selected value of dropdownlist using JavaScript.

Form have a select element that looks like this:

<select id="ddlViewBy">
<option value="1">test1</option>
<option value="2" selected="selected">test2</option>
<option value="3">test3</option>
</select>

Get Selected Value:

var e = document.getElementById("ddlViewBy");
var strUser = e.options[e.selectedIndex].value;

output:


strUser be 2.





Get Selected Item text:

var e = document.getElementById("ddlViewBy");
var strUser = e.options[e.selectedIndex].text;

output:


 strUser be test2

Find controls in the DataItem template of ASPxGridView column

Add DataItemTemplate to Grid Column to access at server side code. In case of DataRowTemplate, we create template for whole row, but if we want to create template for a single column then we use DataItemTemplate and on the place of DataRowTemplate’s
((sender as ASPxGridView).FindRowTemplateControl(..) method; here we use FindRowCellTempleteControl(..) method on HtmlRowCreated event of ASPxGridView.

Grid Markup:

  <dx:ASPxGridView ID="grvTest" AutoGenerateColumns="False" runat="server" DataSourceID="SqlDataSource1"
        OnHtmlRowPrepared="grvTest_HtmlRowPrepared" OnHtmlRowCreated="grvTest_HtmlRowCreated">
        <Columns>
            <dx:GridViewDataTextColumn Caption="RowID" Name="colRowID" VisibleIndex="0" Width="20px">
                <DataItemTemplate>
                     <dx:ASPxLabel ID="lblRowID" runat="server" Text='Label'>
                    </dx:ASPxLabel>
                </DataItemTemplate>
            </dx:GridViewDataTextColumn>

C#

protected void grvTest_HtmlRowCreated(object sender, ASPxGridViewTableRowEventArgs e)
        {
            if (e.RowType != GridViewRowType.Data) return;

            ASPxLabel label = grvTest.FindRowCellTemplateControl(e.VisibleIndex, null, 
            "lblRowID") as ASPxLabel;
            label.Text = (e.VisibleIndex + 1).ToString();
        }

How to install PHP on Windows 7 with IIS 7 via FastCGI

In this programming tutorial, we will learn how to install and configure PHP on windows 7 with IIS 7 via FastCGI. After release of php 5.3 version because php5isapi.dll is no longer available in the php releases starting from 5.3. So my recommendation is with the world’s recommendation and that is to use "FastCGI". Please consider following points before installing PHP on Windows 7.

The FastCGI module in IIS enables popular application frameworks that support the FastCGI protocol to be hosted on the IIS Web server in a high performance and reliable way. FastCGI provides a high-performance alternative to the Common Gateway Interface (CGI), which is a standard way of interfacing external applications with Web servers that has been a part of the supported IIS feature set since the first release.

1. First of all check the system type; either it is 32 bit or 64 bit. X86 means 32 bit. X64 means 64 bit. Download the appropriate php version according to your system type.
2. If you are using PHP with IIS you should use the VC9 versions of PHP.
3. A Thread Safe version should be used if you install PHP as an Apache module. The Non Thread Safe version should be used if you install PHP as a CGI binary.
4. Non-thread-safe build of PHP is recommended for PHP 5.3 version when using IIS. The non-thread-safe builds are available at » PHP for Windows: Binaries and Sources Releases.

Note:
VC9 Versions are compiled with the Visual Studio 2008 compiler and have improvements in performance and stability. The VC9 versions require you to have the » Microsoft 2008 C++ Runtime (x86) or the » Microsoft 2008 C++ Runtime (x64) installed. If you have visual studio 2008 install then you don't have to install Microsoft 2008 C++ Runtime.

What is FastCGI?
FastCGI is a standard protocol that allows the CGI executable files for application frameworks to interface with the Web server. It is different to the standard CGI protocol in that FastCGI reuses CGI processes for multiple requests, which provides a significant performance boost as compared to CGI. Support for IIS FastCGI enables IIS to host normal CGI programs like PHP or Ruby on Rails by using the FastCGI protocol, and to offer high performance and stability for production deployment of such application frameworks.
For having the IIS FastCGI support, you must have following things:

• IIS Web server
• IIS FastCGI extension
• CGI program (such as php-cgi.exe)

The Web server dispatches HTTP requests to your application to the FastCGI component, which in turn launches the CGI program executable, and forwards the request for processing. Once the request is finished and the response is returned back to the server and sent to the client, the CGI process is reused for a subsequent request. This helps to avoid the high performance penalty of starting a new process for each request, which results in better performance and scalability in a production environment. Now let’s come to the topic.

Install PHP on Windows 7 with IIS 7 via FastCGI
First of all, definitely you need to have PHP. Go to the PHP download page and get the latest non-thread-safe ZIP archive of windows binaries for Windows. As of writing this, the package is called "PHP 5.3 (5.3.6) Non-thread-safe zip package". Unzip the archive to a folder on your hard disk. I use C:\php5.

In the latest version of php, you will not find any php.ini-recommended file in php directory. php.ini-recommended is replaced by php.ini-development and php.ini-production files. php.ini-production is used for production systems; for development systems you should use php.ini-development.

Open the php.ini-development and save as php.ini in the same folder and then use the following configuration setting.
Uncomment cgi.force_redirect by removing preceding; and then set it as cgi.force_redirect = 0

This directive is required for running under IIS. It is a directory security facility required by many other web servers. However, enabling it under IIS will cause the PHP engine to fail on Windows.
It is the minimum setting you need to change so that PHP can work with IIS.

Additionally, you may want to use
Uncomment cgi.fix_pathinfo by removing preceding; and then set it as cgi.fix_pathinfo = 1
This lets PHP access real path info following the CGI Spec. The IIS FastCGI implementation needs this set.
Uncomment fastcgi.impersonate by removing preceding; and then set it as fastcgi.impersonate = 1
FastCGI under IIS supports the ability to impersonate security tokens of the calling client. This allows IIS to define the security context that the request runs under.

Uncomment extension_dir by removing preceding; and then set it as extension_dir=C:\php5\ext.
The extension_dir needs to point to the directory where PHP extensions files are stored. The path can be absolute (i.e. "C:\PHP5\ext") or relative (i.e. ".\ext"). Extensions that are listed lower in the php.ini file need to be located in the extension_dir.

Note: - It is better approach to save php.ini in c:/windows as well.

PHP is now setup on your system. The next step is to choose a web server, and enable it to run PHP. We will go with IIS 7.
Internet Information Services (IIS) 7 is available on Windows Vista, Windows 7, and Windows Server 2008.
You probably heard a lot of bad things about IIS, from various groups. If you were holding off using IIS because of that well now is the time to re-evaluate IIS with version 7. A lot of the problems that plaques IIS were cured way back in IIS 5. Introduced in IIS 7 was a new architecture similar to Apache with its modules. Everything in IIS 7 is a module, an extension that can be removed. Just need a basic HTTP server? No problem! Just remove or deactivate everything but the HTTP service module.

Now let’s install IIS 7 if it is not installed in Windows 7. Go to the Start/Control Panel/Programs/Turn Windows Features on or off and check on the Internet Information Services entry. Activate the World Wide Web Services/Application Development Features/CGI node and also Web Management Tools/IIS Management Console (the latter not shown in the figure).

Now, start the IIS Management Console; Open up the start menu, press windows+r to bring run dialog, type inetmgr and hit Enter. There, navigate to the Sites/Default Web Site/Handler Mappings node and double-click on the Handler Mappings entry.

As a result of this, the Actions panel on the right hand side changes. You will now see an option called Add Module Mapping. Clicking on it brings a dialog which you fill out as you can see in the following figure (you may need to adapt the path used to your local system).

If you do not see the FastCgiModule entry, you probably forgot to check the CGI node when installing IIS. Otherwise, close the Add Module Mapping dialog by pressing OK button. You need to confirm that you want to create a FastCGI application; click Yes.

If you don't want to save php.ini in c:\windows then restart the IIS else Reboot your pc.
Finally, create a .php page and put it in the root folder (wwwroot) of the IIS site by default C:\Inetpub\wwwroot, e.g. phpinfo.php with a simple phpinfo() call in it like the code given below.

    <?php  
    phpinfo();  
    ?>  

For Detailed information follow these links as reference:

Using FastCGI to Host PHP Applications on IIS 7


Configuring FastCGI to Host PHP Applications (IIS 7)

Displaying ASPxGridView row index number

You can achieve this through two way:

Method 1

Step 1. Add ASPxGridView on your page:

<dx:ASPxGridView ID="grvTest" AutoGenerateColumns="False" runat="server"
DataSourceID="SqlDataSource1" onhtmlrowprepared="grvTest_HtmlRowPrepared"
onhtmlrowcreated="grvTest_HtmlRowCreated">
<Columns>
<dx:GridViewDataTextColumn Caption="RowID" Name="colRowID" VisibleIndex="0"
Width="20px">
<DataItemTemplate>
<dx:ASPxLabel ID="lblRowID" runat="server" Text="lablel">
</dx:ASPxLabel>
</DataItemTemplate>
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn Caption="User Name" FieldName="UserName"
Name="colUserName" VisibleIndex="1" Width="100px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn Caption="Password" FieldName="Password"
Name="colPassword" VisibleIndex="2" Width="30px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn Caption="Role" FieldName="Role" Name="colRole"
VisibleIndex="3" Width="30px">
</dx:GridViewDataTextColumn>
</Columns>
</dx:ASPxGridView>

Step 2: Add following ASPxGridView Event code snippet to your code – behind file (.cs)

protected void grvTest_HtmlRowCreated(object sender, ASPxGridViewTableRowEventArgs e)
{
if (e.RowType != GridViewRowType.Data) return;

ASPxLabel label = grvTest.FindRowCellTemplateControl(e.VisibleIndex, null, "lblRowID") as ASPxLabel;
label.Text = (e.VisibleIndex + 1).ToString();
}

Method 2

The another way to do it is this one in the Columns section of your GridView control:

<dx:ASPxGridView ID="grvTest" AutoGenerateColumns="False" runat="server"
DataSourceID="SqlDataSource1" >
<Columns>
<dx:GridViewDataTextColumn Caption="RowID" Name="colRowID" VisibleIndex="0"
Width="20px">
<DataItemTemplate>
<dx:ASPxLabel ID="lblRowID" runat="server" Text='<%# Container.ItemIndex + 1 %>'>
</dx:ASPxLabel>
</DataItemTemplate>
</dx:GridViewDataTextColumn>

Displaying ASP GridView row index number

You can achieve this through two way:

Method 1

Step 1. Add this your GridView:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id"
DataSourceID="SqlDataSource1" OnRowCreated="GridView1_RowCreated" >

<Columns>
<asp:TemplateField HeaderText="RowId">
<ItemTemplate><asp:Label runat="server" />                      
</ItemTemplate>            
</asp:TemplateField> 
...
</Columns>

Step 2: Add following GridView Event code snippet to your code – behind file (.cs)

protected void GridView1_RowCreated(object sender, 
System.Web.UI.WebControls.GridViewRowEventArgs e)
{ 
if (e.Row.RowType == DataControlRowType.DataRow) {
// Retrieve the Label from the first column.
Label myLabel = (Label)e.Row.Cells[0].Controls[0]; 

// Set Label text equal to the rowIndex +1
myLabel.Text = e.Row.RowIndex.ToString() + 1; 
}
}

Method 2

The another way to do it is this one in the Columns section of your GridView control:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1" PageSize="5">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="CategoryName" HeaderText="CategoryName" SortExpression="CategoryName" />
<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
</Columns>
</asp:GridView>