Import Excel worksheet in ASP.NET using OWC

Can anyone provide an explanation or example of importing an Excel worksheet to a dataset using OWC

I'm successfully exporting to an Excel workbook (XML format).  On the import side I'd like to import from an Excel in native Excel format, not XML.

Thanks very much to anyone who can help.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Sachintana DissanayakeConnect With a Mentor Senior Web DeveloperCommented:
Please change connection string as below.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Where, "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
Sachintana DissanayakeSenior Web DeveloperCommented:
You can use OleDbConnection with the connection string like this,
     Provider=Microsoft.Jet.OLEDB.4.0;Data Source=excelFile.xls;Extended Properties=Excel 10.0;

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("excelFile.xls")  & ";Extended Properties=Excel 10.0;"
Dim objConn As New OleDbConnection(sConnectionString)
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)
Dim objAdapter1 As New OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "ExcelData")

Open in new window

dawesleyAuthor Commented:
Thanks for replying, but I'm getting the error "Could not find installable ISAM"
on the "objConn.Open()" line.

I've tried changing the connection string syntax a number of times as per
hits I found on this error, but I always get the same error.

My latest attempt is:

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

      Dim objConn As New OleDbConnection(sConnectionString)
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Sachintana DissanayakeSenior Web DeveloperCommented:
If this is a web application, IUSR_ should have access to the folder where the excel file located.
What;s the version of excel file that you are using?
dawesleyAuthor Commented:
I'm developing on my PC, which has Excel 2003 (v.11).  I have administrative rights on my PC.
The commercial web server on which the application runs doesn't have MS Office installed
for obvious security reasons.  It uses only MS Office Web Components.

Sachintana DissanayakeSenior Web DeveloperCommented:
Not administrative rights.
You have to give permission for IUSR_ (ie. ASP.NET user).

You don't need to install MS office in the server.
First get this work on the development PC.
dawesleyAuthor Commented:
Sorry.  I didn't give you the whole picture.
In IIS, I have read and write permissions enabled and
anonymous access turned on for the directory.
Sachintana DissanayakeSenior Web DeveloperCommented:
Go to the directory that you have the excel file and grant permission for IUSR_ (ie. ASP.NET user).
dawesleyAuthor Commented:
In the file system, I pulled up the properties on the directory
and, in the SECURITY tab, gave every user and group in the list,
including all 'IUSR_' types, full access to the directory.

I'm still getting the same error.
dawesleyAuthor Commented:
I should also have mentioned that I've successfully imported a number
of comma separated text files from this folder and from subfolders
under it.
dawesleyAuthor Commented:
Following your advice, I changed my connection string to:

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

It worked!

Obviously I have to add code so I can see the results and I have to test this on the
remote server, but I'm ready to accept the solution and award the points.

I have one more question, but I'm not holding up accepting your solution based on your response.

Is there a way to get the name of the first worksheet in the workbook or to import with the
first worksheet implied instead of specifying the name?

All Courses

From novice to tech pro — start learning today.