[plenitude]

Unhandled Exception: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed
FYI:It is console based C# project,also currently I am at abeginner level,so just trying to open an XLS sheet an trying to read it

using System;

using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;


namespace Excelcalling1
{
	class Program
	{
		static void Main(string[] args)
		{
			string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
					Data Source=E:\SheetForTest.xls;Integrated Security=SSPI;
					Extended Properties=Microsoft Excel 11.0 Object Library";

			//Create the connection

			System.Data.OleDb.OleDbConnection ExcelConnection =
			new System.Data.OleDb.OleDbConnection
							 (ConnectionString);

			//create a string for the query

			string ExcelQuery;

			//Sheet1 is the sheet name
			//create the query:
			//read column with heading A from the Excel file

			ExcelQuery = "Select A from [Sheet1$]"; // from Sheet1";

			//use "Select * ... " to select the entire sheet
			//create the command

			System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);

			//Open the connection

			//ExcelConnection.Open();
			try
			{
				ExcelConnection.Open();
			}
			catch (OleDbException e)
			{
				string errorMessages = "";

				for (int i = 0; i < e.Errors.Count; i++)
				{
					errorMessages += "Index #" + i + "\n" +
									 "Message: " + e.Errors[i].Message + "\n" +
									 "NativeError: " + e.Errors[i].NativeError + "\n" +
									 "Source: " + e.Errors[i].Source + "\n" +
									 "SQLState: " + e.Errors[i].SQLState + "\n";
				}

				System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
				log.Source = "My Application";
				log.WriteEntry(errorMessages);
				Console.WriteLine("An exception occurred. Please contact your system administrator.");
			}

			//Create a reader

			System.Data.OleDb.OleDbDataReader ExcelReader;
			ExcelReader = ExcelCommand.ExecuteReader();

			//For each row after the first
			//Message box the values in the first column i.e. column 0

			while (ExcelReader.Read())
			{
				//MessageBox.Show((ExcelReader.GetValue(0)).ToString());
				Console.WriteLine("value retrieved!!");
			}
			ExcelConnection.Close();

			
		}
	}
}

Thanx in advance

[tody4me]

You need to install the PIA from Microsoft for the version of Office that is installed on the computer. The you just need to create a workbook object, an application object, and, if you want to get the data from a workbook, a worksheet object. Use those to open, close, and retrieve data from an excel spreadsheet.

This post has been edited by tody4me: 09 July 2008 - 12:56 PM

[plenitude]

Hi,
Thanx 4 reply bt I solved my problem with out downloading anything.But my soln can read excel sheet using query.


The code goes this way:-

using System;

using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;

//using Microsoft Office Excel 2003 11.0;
//using Excel;

namespace Excelcalling1
{
	class Program
	{
		static void Main(string[] args)
		{

						string F1Name = "E:\\SheetForTest.xls"; 

						string CnStr = ("Provider=Microsoft.Jet.OLEDB.4.0;" + ("Data Source="

						+ ( F1Name + (";" + "Extended Properties=\"Excel 8.0;\""))));

						DataTable DT = new DataTable();

						OleDbDataAdapter DA = new OleDbDataAdapter("Select * from [Sheet1$]", CnStr);

						Console.WriteLine("File Accessed!!!!");

				try

				{  

					DA.Fill(DT);

					//dataGridView1.DataSource = DT;

				}

				catch (Exception ex)

				{

					Console.WriteLine(ex.Message);

				}

			   

		}
	}
}

Note:You need to to do settings in Project>Add reference>Microsoft oject 11.0 library & Microsoft Excel 11.0 for this code to work.

I am not knowing how to find/query for a particular string in the excel sheet.

My aim is to select a part of excel(vertically) b/w two specific strings n read selected part.

Please reply in simple terms as I am new to programming

Any soln from scratch will also do or an extension to what I have already done

[Redian]

plenitude, on 17 Jul, 2008 - 12:24 AM, said:

Hi,
Thanx 4 reply bt I solved my problem with out downloading anything.But my soln can read excel sheet using query.


The code goes this way:-

using System;

using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;

//using Microsoft Office Excel 2003 11.0;
//using Excel;

namespace Excelcalling1
{
	class Program
	{
		static void Main(string[] args)
		{

						string F1Name = "E:\\SheetForTest.xls"; 

						string CnStr = ("Provider=Microsoft.Jet.OLEDB.4.0;" + ("Data Source="

						+ ( F1Name + (";" + "Extended Properties=\"Excel 8.0;\""))));

						DataTable DT = new DataTable();

						OleDbDataAdapter DA = new OleDbDataAdapter("Select * from [Sheet1$]", CnStr);

						Console.WriteLine("File Accessed!!!!");

				try

				{  

					DA.Fill(DT);

					//dataGridView1.DataSource = DT;

				}

				catch (Exception ex)

				{

					Console.WriteLine(ex.Message);

				}

			   

		}
	}
}

Note:You need to to do settings in Project>Add reference>Microsoft oject 11.0 library & Microsoft Excel 11.0 for this code to work.

I am not knowing how to find/query for a particular string in the excel sheet.

My aim is to select a part of excel(vertically) b/w two specific strings n read selected part.

Please reply in simple terms as I am new to programming

Any soln from scratch will also do or an extension to what I have already done

all you have to do is create an oledb command object, with the sql query command...I know it sounds complicated, but its not that hard, I'm doing the exact same thing, see these articles:
http://www.csharp-st...t/Lesson01.aspx

Its how I ended up understanding it all. Let me know if you get around the problem I'm having though: I can't get it to create a spreadsheet outside of the directory of the executable.