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 codeExcelManagement.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();
