Reading an Excel file 1

[JCruz063]

Hello all...

I need to read the data from an excel file in my ASP.NET 2.0 application. I am using the following C# code:

OleDbConnection cn = new OleDbConnection();
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
   "Data Source=" + [i]inputFile.PostedFile.FileName[/i] + ";" +
   "Extended Properties=Excel 8.0;";
OleDbDataAdapter cmd = new OleDbDataAdapter("SELECT * FROM [b][Sheet1$][/b]", cn);

...

The code works, but I have three issues:

1 - I get the error "The Microsoft Jet database engine cannot open the file '[some path]\myExcelFile.xls'. It is already opened exclusively by another user, or you need permission to view its data." The reason for the error is obvious: The ASP.NET user does not have permission to the folder. Now, in my development machine, the solution to this is pretty simple - adjust the folder permissions appropriately. However, what about the end user who is using my asp.net app to upload excel files? His excel file could be located anywhere in his machine and the aspnet user may not have access to it. How do I get around this without having the user (a) change the permissions on his folders, or (b) put the excel file in a specific location?

2 - What about that Sheet1$, huh? What if the user has changed the name of the sheet where the data is? How do I access that?

3 - Multiple Excel versions? What if the user has a version of excel different than "Excel 8.0" ?

Thanks.

_________________________________
I think, therefore I am. [Rene Descartes]

 

[dbsquared]

1) give the user a browse box to find the file
2) use the Office objects and open it as a file then you can go to sheet(0) or what ever.
3) wont matter with the office objects too much 2007 introduces a few quirks but that will have to be handled as they appear.


To go where no programmer has gone before.

 

[jmeckley]

1. the file shoud be located within the website, prefferably in the App_Data directory, not the client or a remote server. if this is not the case getting to the data becomes much more complex.

depending on how your authorization and authentication is configured will deremine how a user can access the file.

2. this is bluring the line between data and presentation. ado.net is meant to access persisant storage (raw data). if the user is viewing the excel file more like a report this again will complicate the project.

if the user renames a column/table then you will need a process to ask the user to configure the query they want to run.

3. you will need to have multiple connection strings and have create logic to determine the excel version.

if you provide more information about what the overall goal is we may be able to better assist you.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JCruz063]

Thanks guys...

jmeckley,
Sorry for not making myself clearer...

The object of the game is to allow users to UPLOAD THEIR OWN excel files onto MY company's server. My company allows users to store their data onto our server and view/print various reports from the data. We keep the data for up to 5 years, and each user can visit our website at any time, and (a) upload more data, (b) view various reports such as tracking, averages, etc. It works like this:

1. A given user uses program xyz which has a button that says "export data to excel file". The user presses the button and a file is created ON THE USER'S COMPUTER.

2. The user then goes to our website to upload the file to our server.

3. On our website, we have an inputFile control that allows the user to browse to a file on their systems to be uploaded to our server.

4. Then there is an 'Upload' button which the user will hit once he has located the file. The code that runs on our server attempts to make a connection must receive the file and

_________________________________
I think, therefore I am. [Rene Descartes]

 

[JCruz063]

hmmm sorry about that.... I hit the submit button by mistake.

Anyways, the point is then for the server to process the excel file and store the data in our database.

As I was typing that, I realized that I am a complete idiot! The ASP.NET can't connect to a file located on the user's computer (man, I wasn't thinking today); The file must first be saved on the server, then I could use the code given on my original post, and it'll be fine.

2. this is bluring the line between data and presentation. ado.net is meant to access persisant storage (raw data). if the user is viewing the excel file more like a report this again will complicate the project.

I would like to know how to access "the first sheet" in the document, which is where the data will be.

dbsquared,
The user already has a browse file box... Sorry for not making that clear.

use the Office objects and open it as a file then you can go to sheet(0) or what ever

That's something you should not do on a server environment.

Thanks again guys!

_________________________________
I think, therefore I am. [Rene Descartes]

 

[JCruz063]

So two questions remain:

1. How do I determine the version of Excel that was used to create the file so that I can use the appropriate connection string?

2. Once I connect to the excel file, how can I access the first sheet by index rather than by name? The examples I've come accross so far access the sheet by name, as in "SELECT * FROM [Sheet1$]". Given that the user may rename the first sheet before uploading it to our server, I need to access the sheet by index - something along the lines of "SELECT * FROM (Sheets[0])".

Thanks.

_________________________________
I think, therefore I am. [Rene Descartes]

 

[jmeckley]

1. there may be a way to stream the file and look for the version info. not 100% sure.

2. if the xls is autogenerated the end users should not be modifing the output. throw an exception if the sheet is not named properly.

if these are "random" files that a user can upload, the you may need to use Excel Interops or a library for reading xls rather than ado.net.

since your trying to query xls think of xls as a database, not an excel workbook. How would you handel user defined database changes? if the answer is you can't, the same applies to this process. if you do have a method for handeling db schema changes, apply the same principles to this process.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[dbsquared]

You could export your file to csv that way when the user opens it it comes in excel, then you can just use a streamreader to input the data.

To go where no programmer has gone before.

 

[JCruz063]

Thanks again guys...

if the xls is autogenerated the end users should not be modifing the output. throw an exception if the sheet is not named properly.

Some excel files will be autogenerated, but others won't. Now, it does make sense that I would need to know the sheet name from the excel file (just as I have to know the table name from a database, for instance). I'll do some more digging, though. Maybe there's a way...

If there isn't way, and knowing the sheet/object name is a must, then one not-so-elegant way to solve the problem would be to create an ActiveX control that would run on the client and change the name of the first sheet of the xls before uploading it to our server. Perhaps I'll try that.

if these are "random" files that a user can upload, the you may need to use Excel Interops or a library for reading xls rather than ado.net.

This is the wrong thing to do... As I mentioned in an earlier post in this thread, office automation is NOT meant to happen on a server environment, thus Office Interop is not a choice in this case.

dbsquared,
I'm not sure how the file will become *.csv. I'll try saving the incoming excel stream as csv and see how happens. If that's not the way to go about it, do you know how I can do it? Interop/automation is definitly out of the question.

Thanks.


_________________________________
I think, therefore I am. [Rene Descartes]

 

[jmeckley]

changing the stream to csv won't work a stream is just an array of bytes. csv is a formatting concern.

Excel on the server may be wrong but it's an option. I agree that i wouldn't want this either; it's slow and requires licensing. However, your trying to solve a user presntation problem with a data connectivity tool. Maybe there is another option (library) for minipulating the files to extract the information you require.

for example. I use the xml xls writer libary to generate xls on the server. this can also read xls, but expects the xls to be saved as xml

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JCruz063]

Thanks for the help Jason...

I'm actually a bit surprised that there isn't a straight-forward way to allow end users to upload excel files to a given server, and have server code extract the content of those files. Perhaps not many people require this ability and thus the proces has not been standardized.

In any case, I think I'll end up creating an activeX that will be installed on the user's machines and will convert the files to .csv before they are sent to our server. This way, I won't have to worry about Excel verions or sheet names.

What do you think?

Thanks


_________________________________
I think, therefore I am. [Rene Descartes]

 

[jmeckley]

i haven't worked with activeX, and i've written minimal js, but this seems like a valid option. I assume this is an internal appliaction for your/a company, and not public website.

Would SilverLight 2.0 be an option? If I had a requirement to locally access a user's computer I would look into this framework over activeX.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JCruz063]

My company's website is open to the general public, but it has restricted areas for those who subscribe to our services. One such service provides the ability for our clients to upload data to our server and print many reports based on such data. Thus far, the data has come in the form of text files, but now it is required that we support uploading excel files too.

I haven't created ActiveX programs either, but I have seen many websites using them for various purposes, and it looks like that's what I need.

I use javascript extensively on our website, but I've never explored its FileSystem capabilities. I doubt it can provide any aid in this case, however, due to security issues. I'll take a look at it.

As far as SilverLight goes, I was under the impression it was mainly concered with the appearance of websites, but I haven't exmained it in detail. I'll take a look at it too.

Thanks again.

_________________________________
I think, therefore I am. [Rene Descartes]

 

[jmeckley]

my understanding is silver light is client side c#. while it's primary concern is presentation it can run any c# code.

With c# on the client, you could also access the excel interops to parse the excel file.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JCruz063]

my understanding is silver light is client side c#.

I don't think that's entirely the case, but it does sound pretty tempting. I'll take a deeper look at it.

Many many thanks.

_________________________________
I think, therefore I am. [Rene Descartes]

 

[dbsquared]

Since you are loading the data onto your server for reporting then you can normalize the data any way you wish. So using the office objects or xml xls reader or whatever you choose from that point is up to you. The end user will not know what you have used to read and manipulate thier data they just want the end report. You seems like you are using sql server so you could also use it to direcetly feed a temp table then read the table and manipulate the possibilties are pretty much endless.

To go where no programmer has gone before.