CODEWITHSUNDEEP
c#excelxlsx
vaibhav shah
vaibhav shah

Reputation: 5069

reading excel (.xlsx) file in c#

I am reading .xlsx file using c# like this

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                             ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";

var output = new DataSet();

using (var conn = new OleDbConnection(strConn))
{
    conn.Open();

    var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

    foreach (DataRow row in dt.Rows)
    {
        string sheet = row["TABLE_NAME"].ToString();

        var cmd = new OleDbCommand("SELECT * FROM [+"+sheet+"+]", conn);
        cmd.CommandType = CommandType.Text;

        OleDbDataAdapter xlAdapter = new OleDbDataAdapter(cmd);

        xlAdapter.Fill(output,"School");
    }
}

But I am getting error at xlAdapter.Fill(output,"School"); Error is 

The Microsoft Office Access database engine could not find the object '+_xlnm.Print_Area+'.  Make sure the object exists and that you spell its name and the path name correctly.

I am not able to figure out that what is wrong happening in code.

Upvotes: 4

Views: 17949

Answers (4)

Ajay Chaturvedi
Ajay Chaturvedi

Reputation: 115

When you define print area in your sheet "_xlnm.Print_Area" automatic added with your new sheet. Please remove print area form excel sheet or use following code

if (!dr["TABLE_NAME"].ToString().Contains("_xlnm#Print_Area"))
{    
   obj.SheetName = dr["TABLE_NAME"].ToString();
   lst.Add(obj);
}

Upvotes: 1

Azmi Kamis
Azmi Kamis

Reputation: 901

I believe your sheet is named _xlnm.Print_Area. Try changing this line

var cmd = new OleDbCommand("SELECT * FROM [+"+sheet+"+]", conn);

to

var cmd = new OleDbCommand("SELECT * FROM ["+sheet+"]", conn);

Upvotes: 3

jle
jle

Reputation: 9479

I would check what you are getting in row["TABLE_NAME"].ToString(); values. Alternatively, you can try OpenXML SDK: How to: Parse and read a large spreadsheet document (Open XML SDK)

Upvotes: 0

Freelancer
Freelancer

Reputation: 9064

variable sheet contains value: +_xlnm.Print_Area+

This +_xlnm.Print_Area+ does not actually exists.

Thats why error is comming.

Check with that object.

Upvotes: 0

Related Questions