Read Data From an Excel File (.xls) in ASP.NET

Comment posted by Pratyush Mittal on Tuesday, April 8, 2008 10:41 PM

Can you please tell me what to do if I don't want to hardcore the data files (Sample1.xls - in this case) and take the input from the user ?

Comment posted by Sam on Wednesday, April 9, 2008 5:51 AM

hi, could you please tell me why display error message when run oleda.Fill(ds, "Employees")    exception shown as below
The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly


Thanks for your help!

Comment posted by Suprotim Agarwal on Thursday, April 10, 2008 12:59 AM

Pratyush - You can build the connection string at runtime.
Sam - I will look into the error.

Thanks. Suprotim

Comment posted by f on Saturday, April 19, 2008 6:20 AM

hhhhhhhhh

Comment posted by ff on Saturday, April 19, 2008 6:21 AM

ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Comment posted by Wayne on Monday, April 21, 2008 9:55 AM

Does this solution require Excel to be installed on the system running this .Net app?  I'm getting a similar error as Sam, but I don't have Excel installed.

Also, does this solution require that the spreadsheet store *values* only or can it read *formulas*?

Comment posted by RagaSudha on Wednesday, April 23, 2008 2:12 AM

This is the exception i am getting "The Microsoft Jet database engine could not find the object 'test'.  Make sure the object exists and that you spell its name and the path name correctly"  ,and my excel sheet name is test,where should i specify the path and what is this sample .xls data source for?

Comment posted by hkhk on Wednesday, April 30, 2008 5:48 PM

I had got this error
Error   1   Declaration expected." System.Data.OleDb;"   
Error   2   Type 'OleDbConnection' is not defined.
Error   3   Type 'OleDbCommand' is not defined.   
Error   4   Type 'OleDbDataAdapter' is not defined.   
Error   5   Type 'DataSet' is not defined.   
how could I solve them,,
Thanks alot

Comment posted by hkhk on Thursday, May 1, 2008 9:45 AM

I solved my problem
but there is no result in the browser
what  do you think is the problem?

Comment posted by Suprotim Agarwal on Saturday, May 3, 2008 7:16 AM

Make sure the asp.net user has proper permissions to access & read the file.

Comment posted by NADA on Wednesday, May 7, 2008 2:46 PM

how can I push the dataset into the DB?

Comment posted by dev on Tuesday, May 13, 2008 8:03 PM

hi, i got the following error:

"Could not find installable ISAM. "  
Can someone help please!

Thanks in advance!

Comment posted by dev on Wednesday, May 14, 2008 2:20 PM

hi guys.  I finally got my app work.  I had a similar problem with Sam.  However I noticed that .xls file does not have sheet1, sheet2 or sheet3 at the bottom of the page.  So i assume that's not recognized the object 'Sheet1$' while it's debugging.  In the other hand, a .xlsx file has sheet1, sheet2, and sheet3 at the bottom. So when i run this program and it works.  
Thanks to Suprotim Agarwal!  Keep up the good work.

Comment posted by SM SALMAN on Monday, May 26, 2008 1:44 AM

this all thing working but Data is not Showing In Grid
  oleda.Fill(ds,"Employees"); nothing is Filling on in ds
means ds shows null

Can any one help me how to solve this..?

Comment posted by Pavel on Monday, June 9, 2008 9:39 AM

All problem was in connection string. Example:
if you replace ConfigurationManager.ConnectionStrings["xls"].ConnectionString
on
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                    & "Data Source=" & Server.MapPath("Sample1.xls") _
                    & ";" & "Extended Properties=Excel 8.0;"
all will be work
MapPath get full path.
If nothing is Filling then make label and add in "Catch ex As Exception" label1.Text = ex.Message ...

Comment posted by Suprotim Agarwal on Tuesday, June 17, 2008 1:13 PM

dev, Pavel: Thanks for the tip. Strangely, I am able to run the code with the config settings posted. Anyways thanks for sharing your solution for those who were not able to run it.

Comment posted by Marcus on Tuesday, June 24, 2008 5:29 AM

Thanks for your article.  I know you can also write to an excel file using the OLE DB driver.  Do you know how I can write a formula to the Excel file using OLE DB? Thanks.

Comment posted by chin on Thursday, July 10, 2008 9:58 AM

hi!! can you please help me out.. i want to read and populate the data in multiple excel workbooks into a dataset , I know that it can be done using getfiles but not knowing  exactly how to approach? (vb.net)

Comment posted by Mohammad javed on Tuesday, July 15, 2008 1:13 AM

Very Nice code .very very helpful code for every one..

Comment posted by Mohammad Javed on Tuesday, July 15, 2008 1:31 AM

Thanks for your article.  I know you can also write to an excel file using the OLE DB driver.  Do you know how I can write a formula to the Excel file using OLE DB? Thanks.I am able to run the code with the config settings posted. Anyways thanks for sharing your solution for those who
were not able to run it.
Very Nice code .very very helpful code for every one..  

Comment posted by Badal Kant verma on Tuesday, July 15, 2008 1:32 AM

Very Nice code .very very helpful code for every one..

Comment posted by Suprotim Agarwal on Wednesday, July 16, 2008 2:06 PM

Mohammad: I havent really tried writing formulas. However I suggest you to post this question over here for a better response.

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming&lang=en&cr=US

Comment posted by arda on Tuesday, July 22, 2008 3:06 AM

how to read excel into datagrid that alrdy has header?Whn i insert textbox into dg column,excel file cant be opend.
Y?Can anyone resolved?

Comment posted by Sat on Thursday, July 24, 2008 12:26 AM

I have a excel sheet, one of the column has decimal values (4.5678764) When i use Microsoft.Jet.OLEDB.4.0;Data Source=file.xls;Extended Properties=Excel 8.0 as connection string and populates my dataset. But dataset only contains value(4.5678) (ie) only 4 digits. Please help me to get all the digits as in excel sheet. Thanks

Comment posted by TechTactics on Friday, March 27, 2009 6:10 AM

Hi Guys,
All who have the Excel Problem...
Lemme just tell you that, whoever is trying to upload an excel sheet with AutoFilter, generally what happens if we autofilter the excel then it creates a dummy sheet on which the filtering happens, so this gets in the way of our ASP.Net code. Please remove the filter and try using. Cheers

Comment posted by Deeraf on Monday, April 13, 2009 3:10 AM

//Edition in the original post.
//EDITION is specified in EDITION region

protected void Page_Load(object sender, EventArgs e)
    {
        string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object
        OleDbConnection oledbConn = new OleDbConnection(connString);
        try
        {
            // Open connection
            oledbConn.Open();
            #region "EDITION"
            DataTable dtSheetName = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dtSheetName == null || dtSheetName.Rows.Count==0)
            {
                if (oledbConn.State == ConnectionState.Open)
                    oledbConn.Close();
                throw new Exception("Unable to find sheet in the selected file.");
            }
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM ["+dtSheetName.Rows[0]["TABLE_NAME"].ToString()+"]", oledbConn);
            #endregion
            // Create new OleDbDataAdapter
            OleDbDataAdapter oleda = new OleDbDataAdapter();

            oleda.SelectCommand = cmd;

            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();

            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Employees");

            // Bind the data to the GridView
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();
        }
        catch
        {
        }
        finally
        {
            // Close connection
            oledbConn.Close();
        }      

    }

Comment posted by Jessica on Thursday, April 16, 2009 9:09 AM

Unfortunately it doesn't work. Nothing displayed on webpage!!!

Comment posted by akshay on Thursday, April 16, 2009 3:17 PM

I was alos getting the same error as SAM. I copied the dll to debug and release folder then it started working.

I am not sure the reason. but definately with code access security..

Thanks
akshay

Comment posted by Suprotim Agarwal on Friday, April 17, 2009 5:54 AM

Thanks Akshay.

@Everyone - This sample works well in most cases. For special cases, where you have AutoFilter or code issues, read TechTactics, Dheeraf or Akshay's tip. Thanks for your tips!

Comment posted by Alan on Friday, April 24, 2009 7:11 AM

This is a nice solution to my problem, although I have one issue.  One column in my Excel spreadsheet is populated by, predominantly, a number, but in very few cases it could be a string.  When running the code listed here the string value is read into the dataset as type DBNull while all the numbers are of type Double.  Is there any way of ensuring that all data in this column is read as a string?

Comment posted by A Different Alan on Thursday, April 30, 2009 10:10 PM

@Alan: Ah, yes, the pernicious type setting in Excel. By default, the Jet driver only samples the first 8 lines of the file to determine the type. So, alter the registry item HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel and
change "TypeGuessRows" to 0. This will cause it look at all rows before setting the type.

Also, just want to point out that Jet is considered deprecated and is not available in native 64-bit mode.

Comment posted by Chris on Thursday, June 4, 2009 4:03 PM

I'm getting Sam's error. I assumed the sample would run and copied code into an existing application. I have a file named "Wintel_May09_VIAR.xslt". I set the connection string on the fly (I have an upload process that precedes the attempt to open the file). My "sheet" has the same name as the file minus the .xslt. The spread sheet has a number of columns, one of which is named "Address". The error is: The Microsoft Office Access database engine could not find the object 'Address'. Looking at the package contents, my tables folder contains a collection of tables. table1 has a tableColumn with a uniqueName of "Address". What do I need to change?

Comment posted by oaksong on Thursday, June 4, 2009 4:21 PM

Someone should point out that the '$' tacked onto the name of the sheet name is required. I added the '$' and everything worked.

Comment posted by Westham on Saturday, June 6, 2009 6:59 AM

Use this:
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + @"; Extended Properties=""Excel 8.0;IMEX=1;HDR=YES""";

then it will work

www.helheten.net

Comment posted by khalil on Sunday, June 7, 2009 3:50 AM

It working now after i created Connection String in code ,not reading it from Web.Config

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath("Excel2007.xlsx") + ";" + "Extended Properties=Excel 12.0;";

thanks

Comment posted by Wahab Hussain on Tuesday, June 9, 2009 2:08 AM

You guys can check this article as well. Here you can read the data from excel as well as write the data to excel.
http://www.codeproject.com/KB/vb/ExcelDataManipulation.aspx

Comment posted by Suprotim Agarwal on Wednesday, June 10, 2009 3:56 AM

Thanks oaksong, Westham, Khalil for pointing out solutions to those whose code was not working.

Wahab: Thanks for that link

Comment posted by filip on Tuesday, July 14, 2009 7:30 AM

Hi,

nice article but oledb connection is slow way to work with Excel files. You could try using some 3rd party component like GemBox.Spreadsheet - .NET component for Excel which is free for commercial use (limit is 150 rows).

http://www.gemboxsoftware.com/GBSpreadsheet.htm

Filip

Comment posted by mrk on Friday, September 25, 2009 9:13 AM

I am trying to read from Excel 2003 sheet. I am getting the following error when I try to open the connection:
"The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data."

The connection string I am uisng is "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;';Data Source =CI.xls".

No idea where I am going wrong.

Thanks in advance,
mrk

Comment posted by Ajk on Wednesday, October 21, 2009 11:48 AM

This code works perfect as it suppose to. and Thank you for the code. i made the following change though.. to make it read more excel files..

1)
web.config added {0}

   <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0"/>

2)
in the VS project/solution did create create a folder called excel and put all .xls files under to use the following code

string connString = string.Format(ConfigurationManager.ConnectionStrings["xls"].ConnectionString, Server.MapPath(@"\excel\mysample.xls")) ;
            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                // Open connection
                oledbConn.Open();
                string sql = "SELECT * FROM [Sheet1$]";
   
.............
...................



again thanks..

Comment posted by ANSHUMAN on Tuesday, October 27, 2009 7:20 AM

Hi All,

I have generated an xml spreadsheet (*.xls) using XSL. I have another program that reads this spreasheet and processes it.

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filnavn + ";" + "Extended Properties=Excel 8.0;"

        Dim objCon As OleDbConnection = New OleDbConnection(sConnectionString)

        objCon.Open()  // Here i get the EXCEPTION - External table is not in Expected format

        Dim objCmdSelect As OleDbCommand = New OleDbCommand("SELECT * FROM [General$]", objCon)

        Dim objAdapater As OleDbDataAdapter = New OleDbDataAdapter()

        objAdapater.SelectCommand = objCmdSelect

        Dim _DSGen As DataSet = New DataSet()

        objAdapater.Fill(_DSGen)

I m facing an issue while opening a connection to the generated spreadsheet. It says "{"External table is not in the expected format."}".

My Connection string is

Dim "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filnavn + ";" + "Extended Properties=Excel 8.0;"

Do i need to change anything to this string ? I tried using a dataset and calling dataSet.ReadXml(excelFileName); But the dataset doesnt contain my table. Any inputs on how to read an xml spreadsheet ?

1.How to read XML SpreadSheet in OleDb? How to Convert XML SpreadSheet to Microsoft Office Excel Sheet?

2.How to import "XML Spreadsheet" data into DataTable? Any Other Methods available?

Many Thanks

Comment posted by Thureni on Wednesday, December 2, 2009 1:44 AM

Hi, is it possible to read the first sheet regardless of the sheet's name?

Comment posted by Emanuele on Saturday, December 5, 2009 3:09 AM

I'm a rookie in asp.net and i'm trying to understand your code but using my own file excel i don't receive any data...
i tryied to add a Label and to fill a text "complete" after the GridView1.DataBind(), but i don't receive any answer...

I'm looking for the download code to see if i make any mistakes, but i don't find anything...

could you please help me???

Thanks
Emanuele

Comment posted by Madan Chauhan on Thursday, January 21, 2010 2:40 AM

Hi to all
basically the problem is on path of excel file my code is working fine
  string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("Authors.xls") + ";" + "Extended Properties=Excel 8.0;";

        OleDbConnection oledbconn = new OleDbConnection(connString);
        try
        {
            oledbconn.Open();
            OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", oledbconn);
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            DataSet ds = new DataSet();
            oleda.Fill(ds, "Authors");
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();
        }
        catch (OleDbException ex)
        {
            lblMessage.Text = ex.Message;
        }
        finally
        {
            oledbconn.Close();
        }

Comment posted by Ayyappadas on Friday, February 5, 2010 6:12 AM

how use where condition.plz help

Comment posted by Ron on Friday, February 19, 2010 4:26 AM

Yup This Works. Make sure the Path is right.

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                    & "Data Source=" & Server.MapPath("Sample1.xls") _
                    & ";" & "Extended Properties=Excel 8.0;"

Comment posted by Ramesh on Wednesday, April 14, 2010 8:53 AM

How to compare two excel sheets and insert the records in sql by c#coding(Asp.net)

Comment posted by Xenam on Wednesday, April 28, 2010 3:12 PM

Well, this method works perfect.. but i want to know if it is possible to use the data in the excel file to be stored in a Oracle Table(not SqlServer) having same field names as that of the excel file..

Comment posted by as on Tuesday, June 29, 2010 4:42 AM

asd

Comment posted by akhter hussain on Tuesday, June 29, 2010 8:10 AM

very good and easy to understand...thanks allot..doodho nahao pooto bhalo beta.......

Comment posted by akhter hussain on Wednesday, June 30, 2010 1:28 AM

very good and easy to understand...thanks allot..doodho nahao pooto bhalo beta.......

Comment posted by Peter on Wednesday, July 7, 2010 11:37 PM

Great article ! I use a best tool for creating an Excel Spreadsheet with C#,  it can reading and writing to excel 97 ~ 2010 from c#,extremely easy to use,I use it long time quit good.
http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html

Comment posted by Punit Agrahari on Thursday, September 9, 2010 4:04 PM

hi, could you please tell me why display error message when run oleda.Fill(ds, "Employees")    exception shown as below
The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly

Comment posted by Suprotim Agarwal on Friday, September 10, 2010 5:40 AM

Punit: The error could be due to a path or permission issue, not sure though. Make sure ASP.NET runtime has access to the file.

Comment posted by vamsi krishna on Monday, September 20, 2010 7:17 AM

hi, could you please tell me why display error message when run oleda.Fill(ds, "Employees")    exception shown as below
The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly what is this "EMPLOYEES"
please

Comment posted by Olebogeng on Thursday, September 30, 2010 4:08 AM

Awesome. Your site is amazing. Keep it up

Comment posted by abc on Friday, October 1, 2010 4:59 AM

what is abc

Comment posted by Joemarie Amparo on Tuesday, October 5, 2010 11:32 PM

Please Help me.
I am new to programming. I need a code to manipulate the columns and rows in the excel (e.g. sorting the columns, deleting the columns, arranging the columns) and display it on the webpage.

Thanks in advance and more power.

Comment posted by MOHIT JAIN on Monday, October 25, 2010 2:06 PM

I solved my problem
but there is no result in the browser
what  do you think is the problem?
my excel file name is book2.xls

Comment posted by Bas Goedemans on Friday, November 26, 2010 2:29 PM

@Vamsi
See the edit posted by Deeraf.
You need the exact name of the sheet you want to read. If your code can't find the object 'Sheet1$' then you (or your users) have probable renamed the sheet or you are using a different language version of Excel.
The code provided by Deeraf solves this by getting the sheet by index instead of name.

@Suprotim: Great article.

Comment posted by visver on Monday, November 29, 2010 3:45 AM

i am a non-techie! i have an application available in public domain. it has a functionality using which ppl can download a sample excel file, insert their own data and reupload to the application - application reads, validates and inserts data in the dB. as the site/application is in public domain, there is a possibility of someone writing a malicious macro and uploading the file. Is there a way to check if the file being uploaded has a macro in it (without opening it) and delete it if it contains macros.

Comment posted by Mike on Friday, December 10, 2010 1:51 AM

EXCEL READER .NET
~~~~~~~~~~~~~~~~~

EXCEL READER .NET component is very good for this.
(www.devtriogroup.com)

Comment posted by Rhico on Tuesday, December 14, 2010 3:01 AM

This solution works great if the upload excel is a "Normal" excel file with no formula and lock.
But i had encounter problems when i try to upload an .xls file which which contains xml spreadsheet that i had exported out from my website.
The reasons why i used xml to export if because the other methods using htmlwriter & reportviewer or asp.net excel library are unable to produce excel files with formula and protection or unable to allow mass download respectivitly.

This code works when i tried to upload with the excel file open but it will give the "External table is not in the expected format." error when i tried to upload with the excel file closed.

Does anyone have any solution for this?

Comment posted by David Morley on Monday, December 20, 2010 2:30 PM

I cannot get any connection string to work. They all say can't find installable ISAM. I tried getting the string from web.config and I tried writing the strings directly as indicated below :

        Dim root As String
        root = HttpContext.Current.Server.MapPath("BoxStore")
        Dim filepath As String = root & "\" & "Box_zero.xls"
        'Dim conxString As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=filepath;ExtendedProperties=""Excel 8.0;HDR=No;"""  ' Version A
        'Dim conxString As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=filepath;ExtendedProperties=""Excel 9.0;HDR=No;"""  ' Version B
        'Dim conxString As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=filepath;ExtendedProperties=""Excel 10.0;HDR=No;"""  ' Version C
        'Dim conxString As String = "OLEDB;Provider=Microsoft.jet.OLEDB.4.0;Data Source=filepath;ExtendedProperties=""Excel 8.0;HDR=No;"""  ' Version D
        'Dim conxString As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=d:\websites\3bbb3.com\wwwroot\BoxStore\Box_zero.xls;ExtendedProperties=""Excel 8.0;HDR=No;"""  ' Version E
        Dim conxString As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source= & filepath &;ExtendedProperties=""Excel 8.0;HDR=No;"""  ' Version F

I am using VS 2008 SP1. The Excel is 97-2003 and is in a folder \Boxstore. I am running out of ideas ! Any hints will be gratefully received.

Comment posted by ashish on Monday, December 20, 2010 11:42 PM

check out following articles it are of same type

http://aspdotnetmatters.blogspot.com/2010/12/get-excel-sheet-names-in-aspnet.html

http://aspdotnetmatters.blogspot.com/2010/12/reading-excel-file-in-aspnet.html

Comment posted by hamid on Tuesday, December 21, 2010 7:17 AM

Can we import the excel file through SqlConnection and how can we store the data getting from excel file to database sql server 2005

Comment posted by Manuel De Leon on Friday, December 24, 2010 12:00 AM

If you need to create Excel 2007/2010 files then give this open source a try: http://closedxml.codeplex.com

Comment posted by Mark Coe on Tuesday, January 25, 2011 9:57 PM

Thanks for sharing this details it will be great to use this in my <a href="http://www.instani.com">website</a> for managing data.

Comment posted by Ingmar Eidem on Tuesday, February 1, 2011 1:35 PM

You should have indicated where to place the file - for me it did not work until the folder where the Excel file was had Directory Browsing permissions (.net 1.1) - what will be folder rights considerations when reading a file on the system where the browser is running?

Comment posted by Swati on Wednesday, February 2, 2011 2:50 AM

Thanx Westham Now it is working.

Comment posted by Rahul salokhe on Monday, February 7, 2011 1:27 AM

Can we get data from .slk file

Comment posted by shital pawar,Megha Kharade on Thursday, March 10, 2011 6:38 AM

Thanks!!
Use this Connection String to connect
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath("Sheet1.xls") + ";" + "Extended Properties=Excel 12.0;";

Comment posted by Jesse on Friday, March 25, 2011 12:54 PM

Hi All,

This code is perfect.

If for any reason it doesn't work for you please ensure that you have the proper ACE Driver loaded. Its a must for working with 2007 Office Files. Also in your solution, please make sure you have the OleDB rferences properly set. It is NOT going to work otherwise. Thanks!

Comment posted by waqas on Monday, March 28, 2011 8:37 AM

hi i do the same code,but i is showing the error "connection not found".
can someone tell me about this problem?

Thanks

Comment posted by Nicklamort on Wednesday, March 30, 2011 7:51 PM

I had to map the full path with Server.MapPath("~/") to .xls file for the data source in the connection string in order for it to work. I was getting error: "The Microsoft Jet database engine could not find the object 'Sheet1'.  Make sure the object exists and that you spell its name and the path name correctly"

Comment posted by boris on Monday, April 18, 2011 4:01 PM

This is the best way to do it  http://excelpackage.codeplex.com/

Comment posted by Trev on Friday, April 22, 2011 1:43 PM

Thanks for posting this code, it almost worked for me straight away (im using Windows 7 Ultimate, with asp.Net3.0, C# code behind, Office 2007). The only exception error was couldn't open a connection to file as was already open, so looked to me like a permissions error with using win 7. But after trying 'string connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath("Sheet1.xls") + ";" + "Extended Properties=Excel 12.0;";' thanks to 'shital pawar,Megha Kharade' its working fine now, cheers to all.

One thing to note that no one's mentioned in your C# code you need to add a few references to get it to recognise the OleDb and Dataset classes. (copy / paste below into C# code behind .cs page)

using System.Data.OleDb; // needed for OleDbConnection, OleDbCommand, OleDbDataAdapter
using System.Data; // needed for DataSet

Thanks

Comment posted by Trev on Thursday, April 28, 2011 2:54 PM

Hi again. Ok, we are now faced with this 'really nice' way of doing this, only to find it only works on a localHost and not on a live server due to 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.' (despite its the same machine used for local testing!) which im sure others are getting the same error but no ones really covered here. Any ideas how to get around this?

If I find one in the mean time i'll post it up.

cheers

Comment posted by Varun on Tuesday, May 24, 2011 8:11 AM

Nice article . Worked like a charm , but i would like to know why did you write the $ sign with sheet and what is the significance of writing [sheet$]

Comment posted by reetu on Tuesday, May 31, 2011 12:26 PM

Get the following exception:

The Microsoft Jet database engine could not find the object 'Sheet2$'.  Make sure the object exists and that you spell its name and the path name correctly.

What to do? Please help :( its very urgent

Comment posted by reetu on Tuesday, May 31, 2011 12:37 PM

Get the following exception:

The Microsoft Jet database engine could not find the object 'Sheet2$'.  Make sure the object exists and that you spell its name and the path name correctly.

What to do? Please help :( its very urgent

Comment posted by sharath on Thursday, July 21, 2011 2:16 AM

actually i wanted to know how to retrieve the integer value from xl sheet to the asp.net code.it takes string value but not the int value.

Comment posted by Larun on Tuesday, August 9, 2011 9:47 PM

easy way to read data from an excel file(xls,xlsx) without ole automation,
http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html

Comment posted by sowz on Tuesday, October 11, 2011 4:08 PM

Thanks for posting this code. It was really helpful... Should I change anything to the connection string code If I want to connect to remote files..

Comment posted by Sonali on Monday, October 24, 2011 12:25 AM

What is "Employees" in the code?

Comment posted by Sonali on Monday, October 24, 2011 12:45 AM

What is "Employees" in the code?

Comment posted by kapil dev on Thursday, November 17, 2011 7:21 AM

Please send me the connection string for excel 2010 to connect with asp.net 3.5

thank's

Comment posted by fireiceearth on Wednesday, November 23, 2011 11:49 PM

For those who are having the error message:  "The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly."

Make sure you have the extension to the file you want (.xlx, .xlsx, etc.)

Comment posted by Hikaru on Monday, December 5, 2011 10:40 PM

I try your VB code and my output is NULL or no display. I just follow your code and I also change the path of your data source. Please help.

Comment posted by D on Monday, January 16, 2012 8:33 AM

if you dont know the name of excel sheet, u can read the name ,
conn = new OleDbConnection(connString);
                //Open connection
                if (conn.State == ConnectionState.Closed) conn.Open();
                //Create the command object

                dtSceama = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dtSceama != null)
                {
                    String[] excelSheets = new String[dtSceama.Rows.Count];
                    int i = 0;

                    // Add the sheet name to the string array.
                    foreach (DataRow row in dtSceama.Rows)
                    {
                        excelSheets[i] = row["TABLE_NAME"].ToString();
                        i++;
                    }
                    
                        query = "SELECT * FROM [" + excelSheets[0] + "]";
                        cmd = new OleDbCommand(query, conn);
                        da = new OleDbDataAdapter(cmd);
                        da.Fill(ds);
                        cmd.Dispose();
                        da.Dispose();
                    
                }
                conn.Close();

Comment posted by D on Monday, January 16, 2012 9:12 AM

if you dont know the name of excel sheet, u can read the name ,
conn = new OleDbConnection(connString);
                //Open connection
                if (conn.State == ConnectionState.Closed) conn.Open();
                //Create the command object

                dtSceama = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dtSceama != null)
                {
                    String[] excelSheets = new String[dtSceama.Rows.Count];
                    int i = 0;

                    // Add the sheet name to the string array.
                    foreach (DataRow row in dtSceama.Rows)
                    {
                        excelSheets[i] = row["TABLE_NAME"].ToString();
                        i++;
                    }
                    
                        query = "SELECT * FROM [" + excelSheets[0] + "]";
                        cmd = new OleDbCommand(query, conn);
                        da = new OleDbDataAdapter(cmd);
                        da.Fill(ds);
                        cmd.Dispose();
                        da.Dispose();
                    
                }
                conn.Close();

Comment posted by Balaji on Tuesday, January 17, 2012 4:23 AM

Nice one

Comment posted by santosh on Tuesday, February 21, 2012 4:55 AM

Hello,
my excel file column contains Numeric and Text both types of data.
Excel read only one type of data either read Numeric value or either Text format.
I want to read both types of data. provide me solution on this how to read both type of values.

Comment posted by eric on Friday, March 2, 2012 5:41 AM

why Employees?

Comment posted by amresh on Thursday, March 15, 2012 1:52 PM

how to update delete and insert records into excel file using asp.net gridview.
Please explane with code.
Thanks in advance.

Comment posted by Gayathri on Friday, April 6, 2012 10:48 AM

Hello,

I have problem with retrieving the updated excel sheet. The code works perfectly fine. But, when if i add another field or cell to the sheet i cannot view the updated info when i build the solution again. I am new to asp.net. Could any one help?

Comment posted by vimala on Friday, May 18, 2012 5:19 AM

@evryone....thanks ..nice article especially to deeraj...could any one pls tell from a excel how to extract worksheet names and extract data based on coulmn headings and column value, i need to retrieve particular rows ? pls send reply to this and to my mail :vimaladave@gmail.com

Comment posted by vallari on Sunday, July 8, 2012 11:45 PM

{"The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."}
what has to be done... mine is in c# with excel2007

Comment posted by vallari on Sunday, July 8, 2012 11:58 PM

{"The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."}
what has to be done... mine is in c# with excel2007

Comment posted by Bert on Wednesday, August 15, 2012 6:26 AM

According to Microsoft,

"The Access Database Engine 2010 Redistributable is not intended:
...
To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services."

My understanding is that it will open a new object everytime it's called and never release them.

bert

Comment posted by pmd on Tuesday, February 26, 2013 11:12 AM

hi, i tried your code to read excel data in winform application. but not working.it says
Additional information: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

can you please tell me how to solve this problem.

Comment posted by Alexes on Tuesday, October 15, 2013 8:05 AM

If you want the code to read you excel file in ASP.NET then i would recommend you to use this .NET Library from Aspose: http://www.aspose.com/.net/excel-component.aspx