unable to read excel in asp.net

Question

0.00/5 (No votes)

See more:

Hello everyone,

I making an application which upload data directly from excel, I have connected to excel with oledb connection object but i am unable to read the excel.

My code :

C#

 private string GetConnection()
    {
        string connectionString="";
        string path = fpUpload.FileName;
        string ext = System.IO.Path.GetExtension(path);
        string fileName = System.IO.Path.GetFileName(path);
        if (ext.ToString() == ".xls")
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        else if (ext.ToString() == ".xlsx")
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }
        else
        {
            Response.Write("<script type=text/javascript language=javascript>alert('Please select proper file')</script>");
        }
        return connectionString;
    }
private void GetData()
    {
        string conn = GetConnection();
        DataTable dt = new DataTable();

        string query = "SELECT * from [Sheet1$]";
        olCn = new OleDbConnection();
        olCn.ConnectionString = conn;
        olCn.Open();
     //   dt = olCn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        olCmd = new OleDbCommand(query, olCn);
        olDa = new OleDbDataAdapter(olCmd );
        ds = new DataSet();
        olDa.Fill(ds);
        UploadData(ds);

    }

and though the excel sheet contains Sheet1 in it but it gives me error that it could not find Sheet1 in excel .

Please help me regarding this..

Thanks & Regards,
Krunal Panchal

Posted 14-May-12 18:47pm

krunal25

Add a Solution

6 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)

Maciej Los · Answer 1 · 2012-05-14T22:41:00

Solution 5

The only one reason you can't read your Excel files is bad IMEX option value!

Default IMEX option should be 0 (zero).
If you would like to import data as a text, set IMEX = 1.

Posted 14-May-12 22:41pm

Maciej Los

Updated 14-May-12 23:00pm

v2

Comments

VJ Reddy 16-May-12 10:44am

Good point. 5!

Maciej Los 16-May-12 10:56am

Thank you, VJ ;)

Pandvi · Answer 2 · 2012-05-14T21:14:00

Please Check my codes:

C#

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + Server.MapPath(" test.xls") + ";" +
                "Extended Properties=Excel 8.0;";       
            OleDbConnection objConn = new OleDbConnection(sConnectionString);
            objConn.Open();

            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * from [Sheet1$]", objConn);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
             objAdapter1.SelectCommand = objCmdSelect;

           
            DataSet objDataset1 = new DataSet();            
            objAdapter1.Fill(objDataset1, "XLData");
            GridView1.DataSource = objDataset1.Tables[0].DefaultView;
            GridView1.DataBind();

Regards

Also a link available; http://forums.asp.net/t/1039391.aspx/1[^]

Sandeep Mewara · Answer 3 · 2012-05-14T20:03:00

Solution 1

Have a look at this Tip that is working: Read Excel in ASP.NET[^]

Posted 14-May-12 20:03pm

Sandeep Mewara

Prasad_Kulkarni · Answer 4 · 2012-05-14T20:25:00

Solution 2

Please refer following links:

Read Data From an Excel File (.xls) in ASP.NET using OLEDB[^]
Reading Excel File in Asp.net[^]

Posted 14-May-12 20:25pm

Prasad_Kulkarni

preet88 · Answer 5 · 2012-05-14T20:27:00

Solution 3

have a look at this
i hope that it will certainely help you

http://forums.asp.net/t/1192930.aspx[^]
Programmatically Convert Documents to PDFs the Easy Way[^]

Thanks

Posted 14-May-12 20:27pm

preet88

Updated 14-May-12 20:29pm

v2

Comments

Prasad_Kulkarni 15-May-12 2:29am

Links formatted

Gianmaria Gregori · Answer 6 · 2012-05-15T06:53:00

There are some problems in your code:
the first one is that you build the connection string with the file name and not with the path of the Excel file;
then you use a provider (Microsoft.ACE.OLEDB.4.0) that doesn't exist.
The IMEX value is wrong, but I think that it isn't the main problem.
In the following I have tried to revise it:

C++

private string GetConnection(string filePath)
{
	string connectionString="";
	string ext = System.IO.Path.GetExtension(path);
	if (ext.ToString() == ".xls")
	{
		connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
	}
	else if (ext.ToString() == ".xlsx")
	{
		connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\"";
	}
	else
	{
		Response.Write("<script type=text/javascript language="javascript">alert('Please select proper file')</script>");
	}
	return connectionString;
}

private DataTable GetData(string filePath)
{
	string conn = GetConnection(filePath);
	DataTable dt = new DataTable();

	string query = "SELECT * from [Sheet1$]";
	OleDbConnection olCn = new OleDbConnection();
	olCn.ConnectionString = conn;
	olCn.Open();
	OleDbCommand olCmd = new OleDbCommand(query, olCn);
	OleDbDataAdapter olDa = new OleDbDataAdapter(olCmd );
	DataSet ds = new DataSet();
	olDa.Fill(ds);
	dt = ds.Tables[0];
	
	return dt;
}

I hope this helps you.