How to open excel file stored in byte [] via OleDB?

Question

I have one question, and I hope that it will be easy for you. (Windows Forms Application, C #, Framework 3.5, SQL Server 2008 R2)

I do not know how to open Excel (it loads in bytes []) through OleDB.

So what I did: I downloaded the Excel file (.xls (x)) through the form and saved it in the database as varbinary (max). Now I need to read this Excel file through oleDB. I managed to load this file from the database and save it in the byte [] variable. How can I open byte [] in oleDB? When I downloaded the file for the first time (before saving it to the database), I opened it through OleDB, just passing the file path. How can I access Excel data when it is already stored in memory as byte []?

Answer 1

If you want to read using OleDB, you need to write bytes to disk. For example, you can do this:

var filename = System.IO.Path.GetTempFileName();

// Assuming that fileBytes is a byte[] containing what you read from your database        
System.IO.File.WriteAllBytes(filename, fileBytes);
var connection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=YES\"";

// Do your work on excel
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connection))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM [Sheet1$]";

        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                System.Diagnostics.Debug.WriteLine(rdr["ColumnName"]);
            }
        }
    }
    conn.Close();
}

//Cleanup
System.IO.File.Delete(filename);

If you do not want to write the file to disk, you can study a third-party library that can read excel files from a memory stream. Tools like SpreadsheetGear or Aspose are commercial tools that can accomplish this.

Answer 2

, Excel, , , , LoadFromFile. , , , . , LoadFromStream, , byte[] MemoryStream XLS.

Answer 3

emp.upload_file = Path.GetFileName(file.FileName);

emplike your created table object and file HttpPostedFileBasefile