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!!
L. Skynyrd
Please, mark as answer when appropriate