hit tracker

excel

Home » excel

Manipulating Excel Spreadsheet using C#

in Programming by Balamanigandan B Comments are off

C# is one of the most popular languages today and there are many reasons for this. It has many qualities that make it suitable for development for users at most all levels. Let’s discuss how we can create an Excel Spread Sheet using C#.

Microsoft Excel is perhaps the most important computer software program used in the workplace today.

Simple Excel spreadsheet Creation:

Step #1

To use the features of a Microsoft Office application from an Office project, you must use the primary interop assembly (PIA) for the application. The PIA enables managed code to interact with a Microsoft Office application’s COM-based object model.

So, Add an Extension “Microsoft Office Interop Excel“.

Project -> Add Reference -> Extension

BalaBlog1

Step #2

Include the “Microsoft Office Interop Excel” in your class file

 

Step #3

The Following C# Code Creates the Simple Excel Sheet

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 us consider the List of Mobiles which is constructed in the following C# List

 

Now Let us see how to create an Excel Spread Sheet for the above said List<Mobile> MobileList

 

Output Snapshot:

BalaBlog2

Advanced Excel Spread Sheet:

Cell Selection:

To format the Sheet, first select the cells, it may be single, multiple, entire row or entire column. The Interface Excel.Range (Microsoft.Office.Interop.Excel.Range) is used here to select the Cells.

Excel.Range formatRange;

To Select a Single Cell:

 

BalaBlog3

 

To Select a Range of Cells:

 

 

BalaBlog4

 

BalaBlog5

BalaBlog6

To Select a Row:

BalaBlog7

To Select a Column:

BalaBlog8

Font Style and Cell Alignment

Font Bold:

 

Font Color:

 

 

Cell Alignment:

 

 

Cell Background Color:

 

 

Sort data in an Excel table:

 

Filter data in an Excel table:

 

Conclusion: 

This article targets at understanding Excel file manipulations programmatically using C#. This article is intended for the beginner/intermediate level. I hope, this post will solve all your basic functionality implementation of Excel Spread Sheet. Think Big… Start Small… Do Fast…