How to show Excel sheet data in Web Page using Asp.net

Advantage : Some times it happens that, we need to show data in very large quantity, which is not possible with GridView. Suppose we want to bind 15000 rows with Gridview without paging, So it will chop our server.Now in that case this is best choice to Export Your data in Excel Sheet then bind with browser using IFRAME.

Default.aspx

·         When we shall Click on Button the Excel sheet will be show as  
   given in your browser.

·         We have taken a iframe, and excel sheet is showing in IFrame.

·        Write Given below code in your Default.aspx.cs file

Note: I have used “Microsoft.Office.Interop.Excel, Microsoft.Office.Interop.Word” References here.

--*******************************************************--

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using Excel = Microsoft.Office.Interop.Excel;

using word = Microsoft.Office.Interop.Word;

public partial class _Default : System.Web.UI.Page

{

 #region Global Variabes

    public string Message = string.Empty;           // To store the Error or Message

    private word.ApplicationClass OfficeWord;       // The Interop Object for Word

    private Excel.ApplicationClass  OfficeExcel;     // 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

 #endregion

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        string strFilePath = Server.MapPath(@"ConvertedFileLocation\");

        string strFile = "Test.xlsx";

        string[] File = strFile.Split('.');

        string strExtension = File[1].ToString();

        string strUrl = "http://" + Request.Url.Authority + "/Ecel/ConvertedFileLocation/";

        string Filename = strFilePath + strFile.Split('.')[0] + ".html";

        if (System.IO.File.Exists(Filename))

        {

            System.IO.File.Delete(Filename);

        }

        ConvertExcelToHTML(strFilePath + strFile, strFilePath + strFile.Split('.')[0] + ".html");

        ifr.Attributes["src"] = strUrl + strFile.Split('.')[0] + ".html";

    }

    public void ConvertWordToHTML(object Source, object Target)

    {

        if (OfficeWord == null)                         // Check for the prior instance of the OfficeWord Object

            OfficeWord = new word.ApplicationClass();

        try

        {

            // To suppress window display the following code will help

            OfficeWord.Visible = false;

            OfficeWord.Application.Visible = false;

            OfficeWord.WindowState = word.WdWindowState.wdWindowStateMinimize;

            OfficeWord.Documents.Open(ref Source, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown);

            object format = word.WdSaveFormat.wdFormatHTML;

            OfficeWord.ActiveDocument.SaveAs(ref Target, ref format,

                    ref Unknown, ref Unknown, ref Unknown,

                    ref Unknown, ref Unknown, ref Unknown,

                    ref Unknown, ref Unknown, ref Unknown,

                    ref Unknown, ref Unknown, ref Unknown,

                   ref Unknown, ref Unknown);

            Status = StatusType.SUCCESS;

            Message = Status.ToString();

        }

        catch (Exception e)

        {

            Message = "Error :" + e.Message.ToString().Trim();

        }

        finally

        {

            if (OfficeWord != null)

            {

                OfficeWord.Documents.Close(ref Unknown, ref Unknown, ref Unknown);

                OfficeWord.Quit(ref Unknown, ref Unknown, ref Unknown);

            }

        }

    }

    public void ConvertExcelToHTML(string Source, string Target)

    {

        if (OfficeExcel == null) OfficeExcel = new Excel.ApplicationClass();

        try

        {

            OfficeExcel.Visible = false;

            OfficeExcel.Application.Visible = false;

            OfficeExcel.WindowState = Excel.XlWindowState.xlMinimized;

            OfficeExcel.Workbooks.Open(Source, Unknown,

                 Unknown, Unknown, Unknown,

                 Unknown, Unknown, Unknown,

                 Unknown, Unknown, Unknown,

                 Unknown, Unknown, Unknown, Unknown);

            object format = Excel.XlFileFormat.xlHtml;

            OfficeExcel.Workbooks[1].SaveAs(Target, format,

                    Unknown, Unknown, Unknown,

                    Unknown, 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 (OfficeExcel != null)

            {

                OfficeExcel.Workbooks.Close();

                OfficeExcel.Quit();

            }

        }

    }

}

--*******************************************************--