In Memory OleDbConnection to Excel File

Question

I would like to open an OleDbConnection to an Excel file that's in memory as a FileStream. Does anyone know if this is possible and how to accomplish it?

Big picture I would like to pass the file bytes into a .NET CLR Stored Procedure and extract the data. Perhaps there's a better way to do this that I've not considered.

Thanks in advance.

Answer 1

You can extract the data using VSTO. It has an object model for Excel. Then pass what you want to the proc. I think that would be better than trying to have the CLR stored proc decipher an Excel file stream.

Answer 2

The VSTO solution JP offers is definitely viable. However, if you really want to use OleDb and some SQL, take a look at this artcile: it's a MS knowledge base article that describes how to import data from excel into SQLSever using OleDb.

Answer 3

You can use SpreadsheetGear to open a file from a byte array in memory or from any stream with SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromMemory(byte[]) / OpenFromStream(System.IO.Stream).

Disclaimer: I own SpreadsheetGear LLC