Reading EXCEL File and Binding to GridView Using Microsoft.Office.Interop.Excel Object in C#

Original Source

Here i am trying to help you to how to open and read the EXCEL File and bind the data to gridview server control in asp.net using C# language. 
Its very easy way compared to others.


Introduction

Hi all. Here Iam trying to help to open and read the Excel file in ASP.NET Using C# 3.5 language. In My solution I am using  Mirosoft.Office.Interop.Excel Object by addind reference from .net tab.

References


Namespace:
Using Excel=Microsoft.Office.Interop.Excel; 
Using System.Reflection;

   Missing.Value attribute is used in this code as parameter in functions because in C# 3.5 optional parameters are not allowed.

Procedure:

1> Creat a Excel file which contain user Information Ex: Id,Name,Status,Contact (FileName: Mydata.xls)

2>Add this File to your Project.

3> Add Reference on Right click on References and select Add references and click .net tab and add

Microsoft.Office.Interop.Excel Object.

4> Add GridView in desing (.aspx) file.

   

Code to Open and Read Data From Excel File.


Use the following code in page load in your aspx.cs file.

 if (!Page.IsPostBack)             {                 Excel.Application appExl;                 Excel.Workbook workbook;                 Excel.Worksheet NwSheet;                 Excel.Range ShtRange;                 appExl = new Excel.ApplicationClass();                      //Opening Excel file(myData.xlsx)                 workbook = appExl.Workbooks.Open(Server.MapPath("myData.xlsx"), Missing.Value, Missing.Value,Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value);                  NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);                 int Cnum = 0;                 int Rnum = 0;                       ShtRange = NwSheet.UsedRange; //gives the used cells in sheet                                   //Reading Excel file.                //Creating datatable to read the containt of the Sheet in File.                 DataTable dt = new DataTable();                 dt.Columns.Add("ID");                 dt.Columns.Add("Name");                 dt.Columns.Add("Status");                 dt.Columns.Add("Contact");                  for (Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)                 {                     DataRow dr = dt.NewRow(); //Reading Each Column value From sheet to datatable Colunms                                       for (Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)                  {                        dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();                     }                     dt.Rows.Add(dr); // adding Row into DataTable                     dt.AcceptChanges();                 }                  workbook.Close(true, Missing.Value, Missing.Value);                 appExl.Quit();                   gvOne.DataSource = dt;//DataSource to GrigView(Id:gvOne)                 gvOne.DataBind();       }


Conclusion

I tried this above code in my project and its working well. Even you can Edit this data in GridView and Youcan Update  the changes in Same Excel file.

Hope its helps you. thank you.