Read Excel File With C# (INTEROP)

Nisan 29, 2014 aykutors ASP.NET, C#, Programlama 0

Automation of an Excel file allows us to doing various operations from C#. We can automate an Excel file from C# in two ways. Using Excel Object Model (using Microsoft.Office.Interop.Excel) in one way and another way is using Microsoft Jet Engine (OLEDB) to connect Excel from CSharp.

Using Excel Object Model method to connect and read excel is explained in this article. You can also see article “READ EXCEL FILE WITH C# (OLEDB)” to read Microsoft Jet Engine (OLEDB) method to connect and read excel.

First we are creating an excel file to use in this article. Filename is “classroom.xlsx” (office 2010 file) and first worksheet named as “class_list”. We are filling the worksheet as follows;

In MS Visual Studio (used 2010), open a new Windows Form Application. By using textbox(txtFileDestination), button(btnBrowse), label(lblDataToShow) and openFileDialog controls create a form as fallows;

Then from solution explorer, right click “References” and add reference “Microsoft.Office.Interop.Excel” with appropriate version number (for Office 2010, version 14.0.0.0 and for Office 2007 version 12.0.0.0).

Double click the browse button and start coding. Codes are as fallows,

 using System;  
 using System.Collections.Generic;  
 using System.ComponentModel;  
 using System.Data;  
 using System.Drawing;  
 using System.Linq;  
 using System.Text;  
 using System.Windows.Forms;  
 //to make it easy to use, we make fallowing deffination  
 using Excel = Microsoft.Office.Interop.Excel;  
 namespace excelReading  
 {  
   public partial class Form1 : Form  
   {  
     public Form1()  
     {  
       InitializeComponent();  
     }  
     private void btnBrowse_Click(object sender, EventArgs e)  
     {  
       //by clicking browse button file dialog opens and when you selected excel file  
       // file destination is writen to textbox and also a string variable  
       openFileDialog1.ShowDialog();  
       txtFileDestination.Text = openFileDialog1.FileName;  
       string file_destination = openFileDialog1.FileName;  
       //defining application  
       Excel.Application xlApp = new Excel.Application();  
       //selecting excel workbook from selected file  
       Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@file_destination);  
       //selecting first worksheet to read, you can select another page by changing number  
       Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];  
       //to select a specific cell, defining range  
       Excel.Range xlRange = xlWorksheet.UsedRange;  
       //reading the cell and show in a message box. (Cells[rowNunmer,ColomnNumber])  
       //selecting first 3 colomns in 2nd row and writng in label control  
       lblDataToShow.Text= xlRange.Cells[2, 1].Value2.ToString() + " " + xlRange.Cells[2, 2].Value2.ToString() + " " + xlRange.Cells[2, 3].Value2.ToString();  
     }  
   }  
 }

When application is run (F5);