What is the correct way to import and export out of Excel to SQL Server and back?

Question

Looking for the correct way and control to import and export of out of Microsoft Excel programmatically.

I am willing to get a 3rd party control that supports this functionality, or I can create it myself, but looking to get this prioject done fast.

The Datasource will be offline. Although when it is online for the upload/download if there is a control that would merge them, that would work also. But best is to remain offline, don't need the support headache. Security is also an issue.

SSIS is not available on the shared database server.

The website that hosts the asp.net application is not on the same machine as the sql server.

Thank you.

Answer 1

You can use SSIS to import and export from Excel fairly easily.

If the data needs to make a round trip in a spreadsheet that has a lot of features and code, you'll probably need to be more specific and set up some kind of template/framework. Excel can access SQL Server directly through the VBA language in modules and also through its standard ODBC interface.

To clarify, you have a spreadsheet you want to upload to an asp.net application which can connect to a SQL Server, look at the spreadsheet and provide a new downloadable spreadsheet? And you cannot execute an SSIS package on the web server or on the SQL Server? I would simply use The Excel OLE Automation objects to read the incoming spreadsheet, and also to write the outgoing spreadsheet, handling the SQL data as appropriate (I would use a staging table to hold the incoming Excel data and do the work in SQL or perhaps hold the incoming data in native collections and use LINQ to SQL). There is not going to be anything off the shelf which handles general merging.

Answer 2

ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/dv_raddata/html/90072819-53c5-4c36-b97b-1b42e6a003c5.htm

or

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.oleobject(VS.80).aspx