Is it possible to deploy VSTO on a webserver, or server farm, in order to import uploaded Excel spreadsheets in a richer manner than allowed by OLE DB or ODBC? I ask this in the context of working for a major corporation with a strong MS relationship and just about unlimited quantities of all, or nearly all, MS licensed products under the sun.

Hi Andrew

It's still not clear exactly what you need to do and what these sheets actually contain, but...

Have you considered using XML (list objects linked to XML data files), if you need dynamic data updating while the user is working?

Or - as long as the workbooks don't contain any objects - using the XML file format, which would allow you to create/process entire worksheets on the server without Excel needing to be present/running? (And, looking forward to Office 2007's OpenXML file format that will also include a conversion filter to allow ALL previous Office versions to read AND write the new file format, using XML no matter what the workbooks contain). The currentXML file format will allow you to get a "list" of all sheets in a workbook, and to color-code.

Andrew,

You could do this with VSTO using the data caching feature. If your uploaded spreadsheets were VSTO enabled and the cells within them that you wanted to read on the server were cached you could use the VSTO ServerDocument class to read the data directly from the XLS file without having the overhead of running Excel on the server.

The following sample uses Word but that doesn't really make that much difference. Both Word and Excel support cached data.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2005_ta/html/OfficeVSTOBenefitsRegistration.asp

Reply here if you need more help. I'm not really familiar with the way you would do thisusingOLE DB/ODBC so it's hard for me to contrast the two approaches.

Thanks,

Ade

I have two import processes in place, one something of a bootstrap, using VSTO, that provides a lot of interactivity with the spreadsheet, allowing for good feedback to the user. The other is ODBC based, and it simply treats a spreadsheet tab as a table, spreadsheet columns as table columns, interacted with using SQL -- but the user has to provide the worksheet name, because ODBC can't provide a collection of worksheet names (as far as I'm aware). This is one feature (the ability to choose from a list of worksheets) I provide through VSTO that I'd like to see in the web-based import, and not just the bootstrap desktop client. On top of that, it would be great if I could alter the original spreadsheet with error-coded colors, and spit the whole sheet back to the user -- DEFINITELY something ODBC won't allow. Right now we still feel that the number of/potential for errors in the spreadsheet is still beyond the realm of documentation and training and templates alone to handle, and limited feedback doesn't help.

Is the only downside to actually using VSTO on the server the Excel app overhead, or is it a licensing issue? also, can the alternative approach you mention be used over the Web? we're not talking about local network operations here.

Hi Andrew

It's still not clear exactly what you need to do and what these sheets actually contain, but...

Have you considered using XML (list objects linked to XML data files), if you need dynamic data updating while the user is working?

Or - as long as the workbooks don't contain any objects - using the XML file format, which would allow you to create/process entire worksheets on the server without Excel needing to be present/running? (And, looking forward to Office 2007's OpenXML file format that will also include a conversion filter to allow ALL previous Office versions to read AND write the new file format, using XML no matter what the workbooks contain). The currentXML file format will allow you to get a "list" of all sheets in a workbook, and to color-code.

We use Excel spreadsheets to allow users to bulk import data they would otherwise enter, record by record, through the UI. The spreadsheets consists of columns of text, no objects. I need to get data from the user's spreadsheet, uploaded via server farm, into the DB. ODBC is presenting a number of issues working against this scenario.