Can I read Excel files using the OleDbConnection Excel ACE Driver from a non-seekable System.IO.Stream rather than a file?

Daniel James Bryars picture Daniel James Bryars · Feb 2, 2012 · Viewed 7.5k times · Source

There's a similar question here In Memory OleDbConnection to Excel File but, this question was answered by avoiding it completely by doing it another way.

Here's some example code which uses an OleDbConnection to access an Excel file from disk:

static void Main(string[] args)
{
    String filePathToExcelFile = "c:\\excelfile.xls";
    Boolean hasHeaders = true;

    String connectionString = String.Format(
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" +
        "Extended Properties=\"Excel 12.0;HDR={1};IMEX=2\"",
        filePathToExcelFile, hasHeaders ? "Yes" : "No");

    using(OleDbConnection conn = new OleDbConnection(connectionString))
    using (OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", 
        conn))
    {
        conn.Open();
        OleDbDataReader datareader = command.ExecuteReader();

        while(datareader.Read())
        {
            Object[] values = new object[datareader.FieldCount];
            datareader.GetValues(values);

            Console.WriteLine(String.Join(",", values));
        }
    }
}

I'd like to pull the Excel file from a NON-SEEKABLE System.IO.Stream, not a persistent file on the disk.

The question breaks down into two parts, a) Can I "point" the OleDbConnection to a System.IO.Stream? b) If so can that be a forward only stream rather than a seekable one?

FYI: if you want to run this code snippet you'll need to install the Microsoft Access Database Engine 2010 Redistributable. And if you install the 64 bit one you'll need to target the project to be x64 and vice versa.

Answer

Onur Omer picture Onur Omer · Jun 21, 2013

Check this if it helps: http://epplus.codeplex.com/

also sample code:

string fileName = System.Windows.Forms.Application.StartupPath + "\\Resources\\SUPPLIERDECISIONKEYLIST.xlsx";
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(fileName))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets[SheetNo];