C# Excel Connection

Question

hello I need to make a connection to an excel file to do an update of a column I have this code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection;
                System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
                string sql = null;                
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\farm.xls';Extended Properties=Excel 8.0;");
                MyConnection.Open();
                myCommand.Connection = MyConnection;
                sql = "Update [Sheet1$] set name = 'FARM GDL' where COMERCIO like 'FARM GUADALAJARA'";
                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();
                MyConnection.Close();
                MessageBox.Show("Success");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}

but something is not working, when I run it opens the connection

I can do??? please, thanks!

Are you running as x64 or x86? Jet only works on x86, so if you are running as x64 or (possibly) as Any CPU, the connection will fail. — Andrew
– Andrew, Commented Apr 20, 2012 at 22:58
What is the error message? Not working is not an helpful message here. — Steve
– Steve, Commented Apr 20, 2012 at 23:01
¿qué hace el programa dicen que en la excepción? ¿Cómo sabes que no funciona? what does the program say in the exception? How do you know it does not work? — CrazyDart
– CrazyDart, Commented Apr 20, 2012 at 23:02

Steve · Accepted Answer · 2012-04-20 23:14:04Z

3

Some errors spotted in the connection string above:

Change the connection string in this way:

MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\farm.xls';'Extended Properties=Excel 8.0;HDR=YES'");

The \ character has special meaning and need to be doubled in path strings or the whole string prefixed with @
if your Excel File has an header in the first line you should add to the connection string HDR=YES;
The Extended Properties should be enclosed in single quote

If your Excel file has not an header as first line then the whole query changes because you cannot use the name and COMERCIO as columns name. Instead you need to use the letter F followed by the column number (as set F1 = 'FARM GDL' where F2 like '...')

edited Apr 20, 2012 at 23:14

answered Apr 20, 2012 at 23:05

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Excel Connection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related