Click here to Skip to main content
13,146,942 members (80,193 online)
Click here to Skip to main content
Add your own
alternative version

Stats

459.9K views
20.7K downloads
50 bookmarked
Posted 18 Dec 2013

Working with Excel Using C#

, 18 Dec 2013
Rate this:
Please Sign up or sign in to vote.
Demonstration of Excel manipulations using C#

Introduction

This article targets at understanding Excel file manipulations programmatically using C#. As this article is intended for the beginner/intermediate level, a quick demonstration of read/write operations on Excel files is supplied with the help of a sample WinForms application. 

Background 

Very often we work with MS Office tools like Word, Excel, PPT, etc.. And there is a definitive way to work with these applications using the .NET Framework.  

The features implemented in the attached app include:   

  1. Loading an Excel file
  2. Read/Write from/to Excel
  3. Search contents of Excel using various filters

Let's find out how the above operations are implemented in the below sections.  

Assumptions

This app can only manipulate Excel with four columns, i.e., Name, Emp_ID, Email_ID, and Mobile_No.  

Description

In order to use Excel operations in VS, the first and foremost thing to do is to include the Microsoft.Office Object Library reference to the project. Doing so will enable us to use the namespace Microsoft.Office.Interop.Excel. This namespace is widely referred in the project, hence I have assigned an alias name 'Excel'.

using Excel=Microsoft.Office.Interop.Excel; 

Classes used 

  • Excel.Application -Top level object in the Excel object model, used to specify application level properties and application level methods
  • Excel.Worbook - Represents a single workbook within the Excel application
  • Excel.Worksheet - A member of the Worksheets collection in the Workbook object 

Let's declare these variables for further usage. 

private static Excel.Workbook MyBook = nullprivate static Excel.Application MyApp = null;
private static Excel.Worksheet MySheet = null;

Initialize the Excel

MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; 

Please note that the Workbooks.open(string) method is used to open an Excel workbook by passing the file path as a parameter to this method. By default, every workbook created has three worksheets. In this example, we have opened the first worksheet using MyBook.Sheets[1]

The last used row in Excel can be calculated using the SpecialCells method with the xlCellTypeLastCell parameter. 

Reading From Excel

The below piece of code demonstrates reading of Excel sheet cells and storing each row in an Employee object.

BindingList<Employee> EmpList = new BindingList<Employee>();
for (int index = 2; index <= lastRow; index++)
{
    System.Array MyValues = (System.Array)MySheet.get_Range("A" + 
       index.ToString(), "D" + index.ToString()).Cells.Value;
    EmpList.Add(new Employee {
      Name = MyValues.GetValue(1,1).ToString(),
      Employee_ID = MyValues.GetValue(1,2).ToString(),
      Email_ID = MyValues.GetValue(1,3).ToString(),
      Number = MyValues.GetValue(1,4).ToString()
    });
}

To my surprise, here we have used BindingList for Employee objects rather than a normal List. The reason behind this usage is that, any modification to BindingList will cause the component model object to refresh (if the data source of the UI element is set as a BindingList object). This feature is not achievable using a traditional list.

The get_Range(string, string) method takes the start cell (e.g., A1 ) and end cell (e.g., G4) as parameters and returns all the cell values in this range as a two dimensional array. Therefore, the values are added to the Employee BindingList as an Employee object. 

Writing to Excel

lastRow += 1;
MySheet.Cells[lastRow, 1] = emp.Name;
MySheet.Cells[lastRow, 2] = emp.Employee_ID;
MySheet.Cells[lastRow, 3] = emp.Email_ID;
MySheet.Cells[lastRow, 4] = emp.Number;
EmpList.Add(emp);
MyBook.Save(); 

The employee details captured in the UI are passed in the form of an object. These values are assigned to the appropriate cells [row_number, column_number]. The Workbook.Save() method is used to save the applied changes to the opened file. 

Points of Interest  

Filtration of employee details using emp_name, emp_id, email_id, and mobile is also featured in this app. Many more methods area available in the Application, WorkBook, and Worksheet classes. Do check them out!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nanda Kumar Chintam
Software Developer (Senior)
United States United States
I am a programmer, trekker, foodie and tennis/cricket follower . Over the last 7 years, I am working as a developer for web based ordering systems and web services in service oriented architecture(SOA) using various Microsoft .NET technologies. My core skills include ASP.NET,MVC, WCF, jQuery, C# and C++.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionAwesome Explaination Pin
Member 1299137714-Feb-17 18:20
memberMember 1299137714-Feb-17 18:20 
QuestionError Pin
Member 1270439726-Aug-16 3:12
memberMember 1270439726-Aug-16 3:12 
Suggestionhow to create a worksheet from cells/data from another worksheet Pin
Trucco6-Aug-16 11:38
memberTrucco6-Aug-16 11:38 
QuestionLocked Excel file after editing from Form. Pin
Member 1197947125-Sep-15 3:52
memberMember 1197947125-Sep-15 3:52 
QuestionUsing the Filtered EmpList Pin
Member 1190975118-Aug-15 10:11
memberMember 1190975118-Aug-15 10:11 
QuestionHow does the txtFileName activate the Load button. Pin
Member 16523549-Jul-15 7:44
memberMember 16523549-Jul-15 7:44 
AnswerRe: How does the txtFileName activate the Load button. Pin
Member 466849414-Jul-15 0:48
memberMember 466849414-Jul-15 0:48 
QuestionOledb Pin
Member 110305857-Sep-14 22:23
memberMember 110305857-Sep-14 22:23 
QuestionMissing source? Pin
Chuck Stoner29-Aug-14 7:48
memberChuck Stoner29-Aug-14 7:48 
AnswerRe: Missing source? Pin
Member 960641812-Apr-15 21:41
memberMember 960641812-Apr-15 21:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170915.1 | Last Updated 18 Dec 2013
Article Copyright 2013 by Nanda Kumar Chintam
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid