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

Posted by: Suprotim Agarwal , on 4/8/2008, in Category ASP.NET
Views: 778581
Abstract: In this article, we will see how to display data from an Excel spreadsheet using ASP.NET. We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a GridView.
Read Data From an Excel File (.xlsx) in ASP.NET
 
In this article, we will see how to display data from an Excel spreadsheet using ASP.NET. We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a GridView. Let us get started.

 

Step 1: Open Visual Studio > File > New >Website > Under Templates, click ASP.NET WebSite and choose either Visual C# or Visual Basic as the language. Select a location and click Ok.
Step 2: We will create two excel sheets and add them to the project. One excel sheet will be created in Office 2003(.xls) and the other one using Office 2007(.xlsx). Add 4 columns called EID, EName, Age and City to the ‘Sheet1’. Also add some data into the columns. Once these excel files are created, add them to your project. To add them to the project, right click project > Add Existing Item > Add the two excel files.
Step 3: We will now create a web.config file to store the connection string information. Right click project > Add New Item > Web.config. Add the following entries to the file
      <connectionStrings>
            <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"/>
            <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"/>
      </connectionStrings>
As you can observe, the connection string for xlsx (Excel 2007) contains Microsoft.ACE.OLEDB.12.0 as the provider. This is the new Access database engine OLE DB driver and is also capable of reading Excel 2003.
Step 4: Add a GridView to the Default.aspx page. We will extract  data from the excel file and bind it to the GridView.
Step 5: Let us now create a connection to the excel file and extract data from it. Before that add a reference to System.Data.OleDb;
C#
    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();
 
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
 
            // 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();
        }     
 
    }
VB.NET
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            Dim connString As String = ConfigurationManager.ConnectionStrings("xls").ConnectionString
            ' Create the connection object
            Dim oledbConn As OleDbConnection = New OleDbConnection(connString)
            Try
                  ' Open connection
                  oledbConn.Open()
 
                  ' Create OleDbCommand object and select data from worksheet Sheet1
                  Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)
 
                  ' Create new OleDbDataAdapter
                  Dim oleda As OleDbDataAdapter = New OleDbDataAdapter()
 
                  oleda.SelectCommand = cmd
 
                  ' Create a DataSet which will hold the data extracted from the worksheet.
                  Dim ds As DataSet = 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()
            End Try
 
      End Sub
All set!! Run the application and see the data getting displayed in the GridView. If you want to target the Excel 2007 sheet, just change xls to xlsx in the ConfigurationManager.ConnectionString.
I hope this article was useful and I thank you for viewing it.
If you liked the article,  Subscribe to my RSS Feed. 
Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce the The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this eBook aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on .NET Standard and the upcoming C# 8.0 too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Purchase this eBook at a Discounted Price!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for ten consecutive times. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn



Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
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

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook