Is it possible to load an Excel file to memory?

Question

I have a website where businesses can load items for sale. Then end users can search for that item, and find all the stores that carry it.

Most stores sell more than 100 things, and while I do have a form for inserting a single item, it's tremendously stupid to have only this for businesses to offer things.

My idea is to have an option for businesses to upload an excel file and have my ASP.Net application extract information from the columns and populate classes, then with Entity Framework 4 magic I'll persist that information all in one fell swoop.

Are there any pitfalls I should I should be aware of when doing this? Is this possible? Is this a very taxing process on my server (will it muck up my website)?

EDIT: So far it seems that in order for me to parse XML files, I need to have Office installed. I'm not sure if Winhost.com offers that.

Another solution was to use a CSV file for receiving information. How easily would a secretary create a CSV file, remember she is probably the one who is going to be creating the file in the first place. Is this easy for a computer-impaired person to create?

Parsing a CSV would be trivial I assume, but I want to focus on user experience and not have people curse at my website for making them learn things.

EDIT 2: Completely forgot to mention, that of course I'll provide a template for them to use, it would be absolutely dumb to just assume they will follow the table layout. So don't take that problem into account. I'm also aware that I'll have to validate each column so they don't write 'lolgei' into the Price field or something like that.

Answer 1

Hi,

Some quick thoughts....

Validation of the input data is going to be very important. Since this is an autonomous process you need to have this locked down pretty tight so users don't try to upload something they shouldn't be.

Running an office automation process is usually a cumbersome solution, adds a Microsoft office dependency to your server deployments and is best avoided if possible especially if your intent is to run this this code on the post back.

A better solution might be to provide your end users with a excel template that outputs an xml file that can then be uploaded. This way its much easier to process as well as easier to validate and eliminates any dependency on Microsoft office on your server. Excel has some nice xml features build into it. Personally I would opt for this approach over sending the workbook itself to the server.

Hope this helps..

Enjoy!

Answer 2

Perhaps it would be helpful to design a management page such that many items can be entered in at once. I say this because no matter what, the user will have to key every item, even if you issue a "standard"/empty excel file as a template for them to enter their data into, which you would then import.

Answer 3

When I do this I use a CSV file instead of an XLS file since the CSV files are so much easier to parse on the server side. Once the user uploads the file, the next step is to validate the data and display any errors or warnings. This gets more complicated if the user can update existing records through the import process. Then your code has to be able to synchronize between the 2 data stores rather than just bulk inserting the records.

Answer 4

I have done something similar. Doug is right, Office automation can be a bit tricky and isn't really necessary here. How about simple csv-files? It's easy to load them up, they are parsable and can easily be validated.

The secretary could probably be trained to create an Excel-Sheet in the correct format. Saving that sheet as an CSV-File is an easy task (File->Save as->csv).

Answer 5

Can I present a sideways thought? How about building add-in for the Excel sheet, which you can distribute to your customers? It can handle all the requisite authentication, etc. and also at the push of the button, send the information to your ASP.NET server (or better yet, WCF service which you can then pub&sub). Of course at this point you won't have to worry about translating Excel format to a .NET readable format - this can all be formatted to your heart's desire from the add-in front-end, be it XML-serialized objects, csv, whatever.

Just my $0.02 worth...

Answer 6

I don't understand why you'd want to populate objects from the excel file. Why not just create a routine to allow the customer to upload the excel file, parse it, and insert the multiple items to your existing database? It seems that you already have an architecture in place already. Your desired approach seems overly complicated.

I've done imports like this before, but beware that the jet provider will eat up connections.

http://stackoverflow.com/questions/1362266/help-me-find-the-leak-in-this-excel-import-code

I ended up going with csv parsing instead.

Answer 7

The first thing to worry about is the variety of presentations that your users are going to come up with. You should probably give them a template, but even so users mess things up in very creative ways.

DO NOT use automation to read the Excel file. It's unsupported, and one day it will stop working. Have the users upload xlsx files, and use the OpenXML SDK to extract the data.

Answer 8

FWIW, I've been pretty impressed with SpreadsheetGear in the past. This wouldn't help a whole lot with the validation you're going to have to do, but you might find this an easier way to deal with the data.