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);
- click browse button,
- select “classroom.xlsx” file which was created before,
- you can see the selected cells, written into label control
Source:
http://dontbreakthebuild.com/2011/01/30/excel-and-c-interop-with-net-4-how-to-read-data-from-excel/
http://csharp.net-informations.com/excel/csharp-read-excel.htm
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm