Reading, Opening Excel File in C# Using Microsoft.Office.Introp.Excel Object

Download

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 adding reference from .net tab.

References

   Missing.Value attribute is used in this code as parameter in functions because in C# 3.5 optional parameters are not allowed.( Else you may get error "No Overload Method 'Open' takes single arguments")

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.

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.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.