Accessing Xls sheet Data Microsoft.Office.Interop.Excel.dll

In my last article about Accessing data from xls from .NET code I used OledbConnection but that was not covering all my needs so for the better control I found another way of calling the Xls sheet data and I have created another component. The component code and the calling code is mentioned below in c# language.

Note: This require the reference for the Microsoft.Office.Interop.Excel.dll which you can find in your local system. Go to Start -> Search -> Search for (Microsoft.Office.Interop.Excel.dll) -> Copy the same to bin folder and reference from there.

Component Code.

/*
* Created by Dalip Vohra
* Date: 07 June 2008 00:09
*/
using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace ExcelManagement.Direct
{
///

/// Description of TimedAccess.
///

public class ExcelAccess
{
#region Private Variables
ApplicationClass app;
Workbook book = null;
Worksheet sheet = null;
Range range = null;
#endregion
#region Constructor
public ExcelAccess(string _xlsFile, int _xlsFileSheetIndex)
{
app = new ApplicationClass();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
book = app.Workbooks.Open(execPath + @"\..\..\" + _xlsFile, 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);
sheet = (Worksheet)book.Worksheets[_xlsFileSheetIndex];
}
#endregion
#region ChangeSheetIndex Method
public void ChangeSheetIndex(int SheetIndex)
{
sheet = (Worksheet)book.Worksheets[SheetIndex];
}
#endregion

#region ChangeSheetToSheetName Method
public void ChangeSheetToSheetName(string NewSheetName)
{
System.Collections.IEnumerator s = ((System.Collections.IEnumerator)book.Worksheets.GetEnumerator());
int i=1;
while (s.MoveNext())
{
if (((Worksheet)s.Current).Name == NewSheetName)
{
sheet = (Worksheet)book.Worksheets[i];
return;
}
i++;
}
}
#endregion
#region Read Range Method(Based on the Starting Cell).
public object[,] ReadRange(string StartingCell)
{
try
{
range = sheet.get_Range(StartingCell, Missing.Value);
range = range.get_End(XlDirection.xlToRight);
range = range.get_End(XlDirection.xlDown);
string downAddress = range.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);
range = sheet.get_Range(StartingCell, downAddress);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Range Method.
public object[,] ReadRange(string StartingCell, string EndingCell)
{
try
{
// get a range to work with
range = sheet.get_Range(StartingCell, EndingCell);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Cell Method
public string ReadCell(string cellName)
{
try
{
range = sheet.get_Range(cellName, Missing.Value);
if (range.Value2 != null)
{
return range.Value2.ToString();
}
else
{
return string.Empty;
}
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Dispose Method
public void Dispose()
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
}
#endregion
}
}

Calling code


ExcelManagement.Direct.ExcelAccess _access = new ExcelManagement.Direct.ExcelAccess("Book1.xls", 2);
MessageBox.Show(_access.ReadCell("b1"));
_access.ChangeSheetIndex(1);
_access.ChangeSheetToSheetName("Sheet1");
object[,] values = _access.ReadRange("A1", "b2");
for (int i = 1; i <= values.GetLength(0); i++)
{
for (int j = 1; j <= values.GetLength(1); j++)
{
if (values[i, j] != null)
{
MessageBox.Show(values[i, j].ToString());
}
else
{
MessageBox.Show("");
}
}
}
_access.Dispose();

Accessing Xls Sheet Data from .NET Application

Some time back i was in need to fetch some data from the Excel sheet from .NET Application. I have done some R & D on the Net and found some great examples of the same then i created my own sample component which will serve you like the basis component and you can enhance the same futher as per your need.

/*
* Created by Dalip Vohra
*/

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace ExcelManagement.OleDB
{
///

/// Description of TimedAccess.
///

public class ExcelAccess
{
#region Private Variables
string connString;
OleDbConnection oledbConn;
#endregion

#region Constructor
public ExcelAccess(string _xlsFile, string _xlsFileSheetName)
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../Book2.xls;Extended Properties=Excel 8.0";
oledbConn = new OleDbConnection(connString);
try
{
// Open connection
oledbConn.Open();
}
catch
{
throw (new Exception("Connection Exception"));
}
}
#endregion

#region GetSheetData Method
public DataSet GetSheetData(string SheetName)
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "RecordsTable");
return ds;
}
#endregion

#region GetRangeFromSheet Method

public DataSet GetRangeFromSheet(string SheetName, String StartingCell, String EndingCell)
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + SheetName + "$" + StartingCell + ":" + EndingCell + "]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "RecordsTable");
return ds;
}
#endregion

#region Dispose Method

public void Dispose()
{
connString = null;
oledbConn.Close();
}
#endregion

}
}