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

Posted by under ASP.NET category on for Beginner level | Points: 250 | Views : 159720 red flag
Rating: 4.5 out of 5  
 2 vote(s)

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 using C# language.
Its very easy way compared to others.

 Download source code for Reading, Opening Excel File in C# Using Microsoft.Office.Introp.Excel Object


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.


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.( Else you may get error "No Overload Method 'Open' takes single arguments")


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

                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

                workbook.Close(true, Missing.Value, Missing.Value);

                 gvOne.DataSource = dt;//DataSource to GrigView(Id:gvOne)


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.

Page copy protected against web site content infringement by Copyscape
About the Author
Full Name: hiremath V.K
Member Level: Starter
Member Status: Member
Member Since: 4/29/2011 3:36:46 AM
Country: India
Vappath Hiremth

Login to vote for this post.

Comments or Responses

Posted by: Akiii on: 5/23/2011 | Points: 25
Hi Hiremath,

Could you please clarify, what is "Missing.Value" in your code ?

Thanks and Regards
Posted by: Akiii on: 5/23/2011 | Points: 25
i have tested the code and it is working without the "missing.value" !
I have used
workbook = appExl.Workbooks.Open(Server.MapPath("myData.xlsx"));

I see this is your first article, keep it up...

Thanks and Regards
Posted by: Rashmi.mb on: 5/24/2011 | Points: 25
Nice to see u here.....p2
Posted by: Hiremath on: 5/25/2011 | Points: 25
HI All
"Missing.value " is used because to avoid Error LIke "NO OverLoad Method 'Open' takes 1 argument(s)".
(I am using vs 2008 and c# 3.0)
Thank you
Hiremath V.K
Posted by: Hiremath on: 5/25/2011 | Points: 25
i tried without using "Missing.Value" but i got an Error.

Thank you.
Posted by: Akiii on: 5/25/2011 | Points: 25
hi hiremath....

its working fine for me.....i am using VS 2010......

Posted by: Akiii on: 5/25/2011 | Points: 25
what error did u get if you didn't write the "Missing.value" ??

Posted by: Hiremath on: 5/26/2011 | Points: 25
hi Akiii
Error is
" Error LIke "NO OverLoad Method 'Open' takes 1 argument(s)".
at this line
getting boss
May be vorsion problem. plz once try in vb2008 and c# 3.0

Posted by: Sudhindra18 on: 6/10/2011 | Points: 25
Excellent Article hiremath... Keep writing it up..

Posted by: Ashwini.nerkar on: 9/5/2011 | Points: 25
Thanks ! It helped me a lot.
Posted by: Vignesh.jvm on: 12/20/2011 | Points: 25
Hi, Thanks to Post.

In case if empty the data its through the error. using this
dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
so use, before the typecast
dr[Cnum - 1] = (string) (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2;

Thank You! May be its help others.
Posted by: Ybonda on: 6/14/2012 | Points: 25
Here is nice solution (C# Read Excel and Show in WPF DataGrid):
Posted by: Zaiba on: 9/20/2013 | Points: 25
Here is another .NET EXCEL API that also allows you to create/write, edit, read and even convert excel file to other formats:

Login to post response

Comment using Facebook(Author doesn't get notification)