For simply reading a cell or series of cells consider using OleDb data provider as shown below. With OleDb there is no need to worry about cleaning up objects and if the program terminates unexpectedly with office automation objects are left in memory unlike OleDb. Below I included a decent way to build the connection string also. Feel free to download the project from OneDrive.
using System;
using System.Data;
using System.Data.OleDb;
namespace ExcelReadRangeSimple
{
internal class Program
{
/// <summary>
/// This functiuon is an example of how you can setup a connection for an
/// Excel file based on the extension.
/// </summary>
/// <param name="FileName"></param>
/// <param name="Header"></param>
/// <returns></returns>
/// <remarks>
/// There are no guaranty that the settings below will be correct for
/// your Excel file. Things to tweak if it does not work
///
/// - IMEX
/// - HDR
///
/// SeeAlso for IMEX
/// http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled
/// </remarks>
static public string ConnectionString(string FileName, string Header)
{
OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
if (System.IO.Path.GetExtension(FileName).ToUpper() == ".XLS")
{
Builder.Provider = "Microsoft.Jet.OLEDB.4.0";
Builder.Add("Extended Properties", string.Format("Excel 8.0;IMEX=1;HDR={0};", Header));
}
else
{
Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
Builder.Add("Extended Properties", string.Format("Excel 12.0;IMEX=1;HDR={0};", Header));
}
Builder.DataSource = FileName;
return Builder.ConnectionString;
}
/// <summary>
///
/// </summary>
/// <param name="FileName">Full path and file name to read</param>
/// <param name="SheetName">Name of sheet to read, do not append $</param>
/// <param name="StartCell">Cell to start range i.e. A1</param>
/// <param name="EndCell">Cell to end range i.e. D30</param>
static private void DemoReadData(string FileName, string SheetName, string StartCell, string EndCell)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
DataTable dt = new DataTable();
using (OleDbConnection cn = new OleDbConnection { ConnectionString = ConnectionString(FileName, "No") })
{
cn.Open();
string SelectStatement = string.Format("SELECT F1 As Company, F2 As Contact FROM [{0}${1}:{2}]", SheetName, StartCell, EndCell);
using (OleDbCommand cmd = new OleDbCommand { CommandText = SelectStatement, Connection = cn })
{
Console.WriteLine();
Console.WriteLine("Connection string is");
Console.WriteLine(cn.ConnectionString);
Console.WriteLine();
Console.WriteLine();
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Console.WriteLine(" {0,-25} {1}", dr.GetString(1), dr.GetString(0));
}
}
else
{
Console.WriteLine("No rows!!!");
}
}
}
}
private static void Main(string[] args)
{
string FileName = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WS1.xlsx");
DemoReadData(FileName, "Sheet4", "C7", "D16");
Console.ReadLine();
}
}
}
Now if you really want to use office automation consider following the model presented below which is meticulous with how objects are used and destroyed to ensure all objects used are cleaned up correctly. Many times developer don't take the time to do this and should.
public void OpenExcel(string FileName, string SheetName, string CellAddress, string CellValue)
{
List<string> SheetNames = new List<string>();
bool Proceed = false;
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlWorkSheets = null;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(FileName);
xlApp.Visible = false;
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
SheetNames.Add(xlWorkSheet.Name);
if (xlWorkSheet.Name == SheetName)
{
Proceed = true;
Excel.Range xlRange1 = null;
xlRange1 = xlWorkSheet.Range[CellAddress];
xlRange1.Value = CellValue;
string value = xlRange1.Value;
Console.WriteLine(value);
Marshal.FinalReleaseComObject(xlRange1);
xlRange1 = null;
xlWorkSheet.SaveAs(FileName);
break;
}
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
}
xlWorkBook.Close();
xlApp.Quit();
ReleaseComObject(xlWorkSheets);
ReleaseComObject(xlWorkSheet);
ReleaseComObject(xlWorkBook);
ReleaseComObject(xlWorkBooks);
ReleaseComObject(xlApp);
if (Proceed)
{
MessageBox.Show("Found sheet, do your work here.");
}
else
{
MessageBox.Show("Sheet not located");
}
MessageBox.Show("Sheets available \n" + String.Join("\n", SheetNames.ToArray()));
}
private void ReleaseComObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception)
{
obj = null;
}
}
public string GetCellValue(string FileName, string SheetName, string CellAddress)
{
string CellValue = "";
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlWorkSheets = null;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(FileName);
xlApp.Visible = false;
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
if (xlWorkSheet.Name == SheetName)
{
Excel.Range xlRange1 = null;
xlRange1 = xlWorkSheet.Range[CellAddress];
CellValue = xlRange1.Value;
Marshal.FinalReleaseComObject(xlRange1);
xlRange1 = null;
xlWorkSheet.SaveAs(FileName);
break;
}
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
}
xlWorkBook.Close();
xlApp.Quit();
ReleaseComObject(xlWorkSheets);
ReleaseComObject(xlWorkSheet);
ReleaseComObject(xlWorkBook);
ReleaseComObject(xlWorkBooks);
ReleaseComObject(xlApp);
return CellValue;
}
