.net C# windows application connection problem with excel file

Question

0.00/5 (No votes)

See more:

Hi All;
I m trying to connect .Net C# windows program to a excel file but it is showing "connection cannot open".

Following is the code

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Drawing.Printing;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;


namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        private OleDbConnection conn; //SqlConnection conn = new SqlConnection();
        private string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\horton\Desktop\TestExcel.xlsx";
            //@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\horton\Desktop;Extended Properties=TestExcel";
        //@"Data Source=HORTON-2A5CB16E\SQLEXPRESS;Initial Catalog=Temp;Integrated Security=SSPI;Max Pool Size=100;Connect Timeout=1000";
       public Form1()
        {
            InitializeComponent();
            conn = new OleDbConnection(conStr);
            conn.Open();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            clr();
            txtRollNumber.Focus();
            label1.Enabled = false;
            label2.Enabled = false;
        }

Thanks & regards
Indrajit

Posted 17-Apr-11 19:03pm

IndrajitDasgupat

Updated 17-Apr-11 21:05pm

Pong D. Panda

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mahendra.p25 · Answer 1 · 2011-04-17T19:11:00

Solution 2

try this

//using System.Data.OleDb;

MSIL

OleDbConnection Con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C://Documents and Settings//Admin//My Documents//Downloads//MyReport2.xls;Extended Properties=Excel 8.0;");

Con.Open();

Posted 17-Apr-11 19:11pm

Mahendra.p25

Comments

IndrajitDasgupat 18-Apr-11 1:18am

I m using that
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;

Pong D. Panda · Answer 2 · 2011-04-17T19:08:00

Solution 1

You forgot your excel file name on your Data Source property.

try something like this.

private OleDbConnection conn;
private string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\horton\Desktop\TextExcel.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1";

Good luck!

Posted 17-Apr-11 19:08pm

Pong D. Panda

Updated 17-Apr-11 19:23pm

v3

Comments

IndrajitDasgupat 18-Apr-11 1:16am

now I m giving
private string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\horton\Desktop\TestExcel;Extended Properties=""text;HDR=Yes;FMT=Delimited"

then also showing connection error

Pong D. Panda 18-Apr-11 1:22am

Your Data Source=C:\.....\Desktop\TextExcel should be the actual file name, you forgot the file extension. put xls or whatever your excel file extension is.

Just copy my sample conStr on your code, your not constructing it well.

IndrajitDasgupat 18-Apr-11 1:33am

Now it is showing "Format of the initialization string does not conform to specification starting at index 101." in following code
conn = new OleDbConnection(conStr);
I m not able to understand what should i have to do

Pong D. Panda 18-Apr-11 1:52am

post here your actual code for the connectionstring

IndrajitDasgupat 18-Apr-11 2:59am

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Drawing.Printing;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
private OleDbConnection conn; //SqlConnection conn = new SqlConnection();
private string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\horton\Desktop\TestExcel.xlsx";
//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\horton\Desktop;Extended Properties=TestExcel";
//@"Data Source=HORTON-2A5CB16E\SQLEXPRESS;Initial Catalog=Temp;Integrated Security=SSPI;Max Pool Size=100;Connect Timeout=1000";
public Form1()
{
InitializeComponent();
conn = new OleDbConnection(conStr);
conn.Open();
}
private void Form1_Load(object sender, EventArgs e)
{
clr();
txtRollNumber.Focus();
label1.Enabled = false;
label2.Enabled = false;
}

Pong D. Panda 18-Apr-11 3:06am

update your connectionstring to

private string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\horton\Desktop\TextExcel.xls;Extended Properties=Excel 8.0;";

then check if the file exists on your server.

IndrajitDasgupat 18-Apr-11 3:11am

it is showing error on " conn.Open();" code

Error is : The 'Microsoft.ACE.OLEDB.8.0' provider is not registered on the local machine.

Pong D. Panda 18-Apr-11 3:26am

Why ACE? Use Jet

Pong D. Panda 18-Apr-11 3:35am

Last choice, install office to your server machine.

Ashishmau · Answer 3 · 2011-04-17T19:13:00

Solution 3

Check your data source property

Excel file name is not there

Posted 17-Apr-11 19:13pm

Ashishmau

Comments

IndrajitDasgupat 18-Apr-11 1:19am

I had given but it is showing connection error

Ashishmau 18-Apr-11 1:53am

use this connectionstring

OleDbConnection excelcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.4.0;Data Source=your path;Extended Properties=Excel 4.0;");

Ashishmau 18-Apr-11 3:17am

You have run access database engine exe for 8.0 in your server

IndrajitDasgupat 18-Apr-11 3:21am

Yes I try with this also but same problem

private OleDbConnection conn; //SqlConnection conn = new SqlConnection();
private string conStr = @"Provider=Microsoft.ACE.OLEDB.8.0;Data Source=C:\Documents and Settings\horton\Desktop\TestExcel.xlsx;Excel;Extended Properties=Excel 8.0";

it is showing error on " conn.Open();" code

Error is : The 'Microsoft.ACE.OLEDB.8.0' provider is not registered on the local machine.

Ashishmau 18-Apr-11 3:30am

You have to run Accessdatabaseengine.exe of 8.0 for this on your machine.
You get this exe from internet.

hemantrautela · Answer 4 · 2012-09-15T00:37:00

you are using Provider=Microsoft.Jet.OLEDB.4.0 it is for ".xls" if you want to .xlsx then should be use upgrade level oledb installed in you pc...

C#

con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\book.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'");

// But here this provider should be installed in your pc
for .xls here is example code

http://hemantrautela.blogspot.in/2012/09/database-connection-in-cnet-aspnet-with.html[^]