Advantage : Sometimes
it happens that we need to show very large amount of data, which is not
possible to bind in Grid View.
Suppose we have to bind 15000+ rows with Grid View
without paging, So may be it will stuck the process.
Now in that case this is best
choice to Export Your data in Excel Sheet then bind with browser using IFRAME.
Steps:
1. Create a Web Page as below (Default.aspx):
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!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 runat="server">
<title></title>
<style type="text/css">
#Iframe
{
height: 682px;
width: 964px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnShowExcel"
runat="server"
Text="Show
Excel"
onclick="btnShowExcel_Click"
/>
<br />
<br />
<iframe id="Iframe" runat="server"></iframe>
</div>
</form>
</body>
</html>
2. Add Reference in project:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Office.Interop.Excel;
public partial class _Default :
System.Web.UI.Page
{
public string Message
= string.Empty; // To
store the Error or Message
private Microsoft.Office.Interop.Excel.ApplicationClass Excel; // The Interop
Object for Excel
object Unknown = Type.Missing; // For
passing Empty values
public enum StatusType { SUCCESS, FAILED }; // To Specify
Success or Failure Types
public StatusType
Status; // To know the Current Status
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
btnShowExcel_Click(object sender, EventArgs e)
{
string strFilePath = Server.MapPath(@"ConvertedLocation\");
string strFile = "Timesheet.xls";
string[] File = strFile.Split('.');
string strExtension = File[1].ToString();
string strUrl = "http://"
+ Request.Url.Authority + "/xlsinIFrame/ConvertedLocation/";
string Filename = strFilePath + strFile.Split('.')[0] + ".html";
if (System.IO.File.Exists(Filename))
{
System.IO.File.Delete(Filename);
}
ConvertHtmlFromExcel(strFilePath + strFile, strFilePath + strFile.Split('.')[0] + ".html");
Iframe.Attributes["src"] =
strUrl + strFile.Split('.')[0] + ".html";
}
public void
ConvertHtmlFromExcel(string Source, string Target)
{
if (Excel == null)
Excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
Excel.Visible = false;
Excel.Application.Visible = false;
Excel.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMinimized;
Excel.Workbooks.Open(Source, Unknown, Unknown, Unknown, Unknown,
Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown, Unknown,
Unknown, Unknown);
object format =
Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
Excel.Workbooks[1].SaveAs(Target, format,
Unknown, Unknown, Unknown,
Unknown,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Unknown,
Unknown, Unknown, Unknown,
Unknown);
Status = StatusType.SUCCESS;
Message = Status.ToString();
}
catch (Exception
e)
{
Message = "Error :" +
e.Message.ToString().Trim();
}
finally
{
if (Excel != null)
{
Excel.Workbooks.Close();
Excel.Quit();
}
}
}
}
4. Execute the code and click on "Show Excel" Button. After clicking on button you will see excel file in iFrame as below.
Hi Nishchal Tyagi,
ReplyDeleteWhy it displayed the excel file in microsoft excel?
Isn't should be displayed in web page?
and I have an error "System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800AC472'".
Can you help me? Thank you