Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Mon, 01-Sep-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
37300
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

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

Hiremath
Posted by under ASP.NET category on for Beginner level | Points: 250 | Views : 148251 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
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 asp.net 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


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


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.( 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.


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.

Page copy protected against web site content infringement by Copyscape
About the Author

Hiremath

Full Name: hiremath V.K
Member Level: Starter
Member Status: Member
Member Since: 4/29/2011 3:36:46 AM
Country: India
Vappath Hiremth
http://www.dotnetfunda.com

Login to vote for this post.
Found interesting? Add this to:


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
Akiii

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

hi....
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
Akiii

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

@Akiii
hi
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......

Regards
Akiii

Posted by: Akiii on: 5/25/2011 | Points: 25

what error did u get if you didn't write the "Missing.value" ??

Regards
Akiii

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
appExl.Workbooks.Open(Server.MapPath("myData.xlsx"));
getting boss
)
May be vorsion problem. plz once try in vb2008 and c# 3.0
Thankyou.





Posted by: Sudhindra18 on: 6/10/2011 | Points: 25

Excellent Article hiremath... Keep writing it up..

Sudhindra

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): http://www.codearsenal.net/2012/06/c-sharp-read-excel-and-show-in-wpf.html

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:

http://www.aspose.com/.net/excel-component.aspx

Login to post response

Comment using Facebook(Author doesn't get notification)