CODEWITHSUNDEEP
c#asp.netexcel
Nimit Joshi
Nimit Joshi

Reputation: 1046

Accessing Excel 2013 File in ASP.NET

I am developing an ASP.NET application. I have an Excel 2013 file named ExcelApp and i have also added in my project. I can not access the file.

Here is my DAL class:

public string ExcelConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Nimit\ExcelApplication.xlsx;Extended Properties='Excel 12.0 Xml';HDR=YES"; 
    OleDbDataAdapter DbAdap;
    DataTable dt;

    public DataTable Get_ExcelSheet()
    {
        OleDbConnection DbCon = new OleDbConnection(ExcelConnection);

        DbAdap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",DbCon);
        dt = new DataTable();
        DbAdap.Fill(dt);
        return dt;
    }

My cs file code: 

public partial class WebForm1 : System.Web.UI.Page
{
    DAL obj = new DAL();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack == true)
        {
            Get_Data();
        }
    }

    void Get_Data()
    {
        GridView1.DataSource = obj.Get_ExcelSheet();
        GridView1.DataBind();
    }
}

My debugger shows the error while executing this line:

DbAdap.Fill(dt);

The exception is:

enter image description here

Upvotes: 3

Views: 5861

Answers (5)

Sedat Kumcu
Sedat Kumcu

Reputation: 2430

When some cases, require installing 32 bit MDAC Plugin intent to solution of problem. I did that and solved the problem.

Upvotes: 0

Nimit Joshi
Nimit Joshi

Reputation: 1046

I solved my question. Here is my code:

public class DAL
{
    OleDbDataAdapter DbAdap;
    DataTable dt;

    public DataTable Get_ExcelSheet()
    {
        OleDbConnection DbCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Nimit\\ExcelApplication.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"");

        DbAdap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",DbCon);
        dt = new DataTable();
        DbAdap.Fill(dt);
        return dt;
    }
}

I simply provide my connection string in my OleDbConnection. Thats it.

Upvotes: 5

Sid
Sid

Reputation: 317

Instead of Microsoft.ACE.OLEDB.4.0 use Microsoft.ACE.OLEDB.12.0

And also try with passing relative/absolute path.

Note: In my connection string I am passing filePath+fileName

connString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\";",
                                       filePath + fileName)

Upvotes: 1

Dawid
Dawid

Reputation: 395

I think you should read again the exception. There is a clear message why this code fails you... I'll give you a hint:

Additiona information: The "Microsoft.ACE.OLEDB.40' provider is not registered on the local machine

Fix that and it should work.

Upvotes: 2

Simon
Simon

Reputation: 59

First Check out your connection string; it must contain reference for your Excel "ExcelApp.xlsx". Second the ExcelApp$ in the select must refer the sheet name and not the Excel file name.

hope you resolve with this

Upvotes: 0

Related Questions