Building Excel Services

skynyrd — Wed, 02 Jul 2008 12:26:23 GMT

Hi there,

I work for a multinational industry as a .net consultant.

I've developed a solution that I'm not happy at all with. It is hard to work with and has a poor performance.

Therefore, I'm thinking of using a SharePoint Services approach to rebuild a better solution.I am developing a SAP/VB.NET/Oracle Application that functions, basically, like this:

My solution:

1 - Thru a VB.NET UI, the user starts a download from SAP that populates a HUGE excel file.

2 - VB.NET transforms the whole file into a HUGE in-memory XML stream and sends it at once to an Oracle Database.

3 - With PL/SQL I parse the XML and populate three tables that represent a normalized model of the excel spreadsheet generated by SAP. Each file represents a version of what we call a business plan.

4 - The VB.NET UI offers the possibility to edit the business plans. For that, I have to turn the data back into an excel file and open it for the user. The user makes the changes and updates the file again. I have to validate and parse the whole excel file again.

5 - The VB.NET UI offers a series of reports that are generated based on the Oracle tables.

Problems:

Steps 1 to 3 are extremely slow. It takes about 10 minutes to generate one business plan.

Validating numbers, dates and not null cells in 80 x 1000 excel spreadsheets is not fun.

I created an excel-like user interface using text boxes for the user to update a few values of the business plan without having to update the whole excel file. But users hated it since it looks like excel but it doesn't feel like excel. I used javascript and ajax concepts to enhance the user experience but, obviously, I can't compete with excel.

WSS Solution:

1 - After the SAP download process its over, a .NET component uploads the excel file to a SharePoint Document Library. Much faster!

2 - There will be no more xml and oracle since WSS provides that for me out of the box with SQL Server and binary data.

3 - The user are free to open the excel files and update the values with versioning and security. They will love it. No development for me.4 - I will have to access the document library files to build the reports.

Problems:

Steps 1 to 3 are ready. I test it and its working wonderfully.

Step 4 is a problem because we have WSS 3 but we don't have MOSS 2007 or Excel Services. They are not planning to buy those licenses too soon so I will have to develop that by myself.

Question:

Is it possible to access values within excel files that are inside a SharePoint Document Library to build reports?

If yes, can you point me some examples so I can get started?

Thank you!!

Windows SharePoint Services Hosting

Building Excel Services