Open Excel and put in values from C#

[JM]

Hi

I have been trying to open Excel 2003 but I am having rotten luck. Just
can't see to get it working. I looked at this article...

http://www.codeproject.com/csharp/cs...asp?print=true

Anyone have any ideas?

[Aleksandar Mraović]

This "Excel" namespace of yours is not contained in the .net framework and
looks like somebody's proprietary class. I have successfully been opening
excel worksheets with the OleDb ADO .net service provider (basically you
would connect to a spreadsheet like with any other data source, e.g. sql
server).

Let me try and translate this code to you (I tend to write my variable names
in serbian ;).

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties=""Excel 8.0;HDR=Yes;IMEX=2"";Data Source="; // The connection
string would look something like this we will add the data source later in
the code, this could be a member of the form class or your own class you
use to access your data, anyways you understand it should be visible in the
function from where you are trying to open the worksheet (e.g. a click event
handler)

// here we will get the file name of the worksheet from an OpenFileDialog.

OpenFileDialog ofd = new OpenFileDialog();
if(ofd.ShowDialog() == DialogResult.OK)
{
connectionString += ofd.FileName; // we add the file name after Data
Source (with excel worksheets the file name is the data source)
}
con = new OleDbConnection(connectionString); // we open a connection
try
{
con.Open();
}

catch(OleDbException err) // some error checking is not a bad thing.
{
MessageBox.Show(err.Message);
}
string query = "SELECT * FROM
["+Path.GetFileNameWithoutExtension(ofd.FileName)+" $A5:F1000]"; // we build
the query string, note that the table name is usually the file name plus the
excel range specifiers.

OleDbDataAdapter ad = new OleDbDataAdapter(query, con);

try
{

ad.Fill(ourTable); // we fill a table with our DataAdapter
}
catch(OleDbException err) // some error checking is not a bad thing.
{
MessageBox.Show(err.Message);
}

This example is only querying a worksheet but you get the picture, it works
the same for updating.
"JM" wrote:

Hi

I have been trying to open Excel 2003 but I am having rotten luck. Just
can't see to get it working. I looked at this article...

http://www.codeproject.com/csharp/cs...asp?print=true

Anyone have any ideas?

[JM]

Here is what worked for me! Thanks for your help!

http://msdn.microsoft.com/library/de...rp05152003.asp

"Aleksandar Mraović" wrote:

This "Excel" namespace of yours is not contained in the .net framework and
looks like somebody's proprietary class. I have successfully been opening
excel worksheets with the OleDb ADO .net service provider (basically you
would connect to a spreadsheet like with any other data source, e.g. sql
server).

Let me try and translate this code to you (I tend to write my variable names
in serbian ;).

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties=""Excel 8.0;HDR=Yes;IMEX=2"";Data Source="; // The connection
string would look something like this we will add the data source later in
the code, this could be a member of the form class or your own class you
use to access your data, anyways you understand it should be visible in the
function from where you are trying to open the worksheet (e.g. a click event
handler)

// here we will get the file name of the worksheet from an OpenFileDialog.

OpenFileDialog ofd = new OpenFileDialog();
if(ofd.ShowDialog() == DialogResult.OK)
{
connectionString += ofd.FileName; // we add the file name after Data
Source (with excel worksheets the file name is the data source)
}
con = new OleDbConnection(connectionString); // we open a connection
try
{
con.Open();
}

catch(OleDbException err) // some error checking is not a bad thing.
{
MessageBox.Show(err.Message);
}
string query = "SELECT * FROM
["+Path.GetFileNameWithoutExtension(ofd.FileName)+" $A5:F1000]"; // we build
the query string, note that the table name is usually the file name plus the
excel range specifiers.

OleDbDataAdapter ad = new OleDbDataAdapter(query, con);

try
{

ad.Fill(ourTable); // we fill a table with our DataAdapter
}
catch(OleDbException err) // some error checking is not a bad thing.
{
MessageBox.Show(err.Message);
}

This example is only querying a worksheet but you get the picture, it works
the same for updating.
"JM" wrote:

Hi

I have been trying to open Excel 2003 but I am having rotten luck. Just
can't see to get it working. I looked at this article...

http://www.codeproject.com/csharp/cs...asp?print=true

Anyone have any ideas?