Friday 28 September 2012

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();
            }
        }
    }
}

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

No comments:

Post a Comment