Read excel file from a stream

Question

I need a way to read a Excel file from a stream. It doesn't seem to work with the ADO.NET way of doing things.

The scenario is that a user uploads a file through a FileUpload and i need to read some values from the file and import to a database.

For several reasons I can't save the file to disk, and there is no reason to do so either.

So, anyone know of a way to read a Excel file from a FileUpload stream?

Answer 1

This can be done easily with EPPlus.

//the excel sheet as byte array (as example from a FileUpload Control)
byte[] bin = FileUpload1.FileBytes;

//gen the byte array into the memorystream
using (MemoryStream ms = new MemoryStream(bin))
using (ExcelPackage package = new ExcelPackage(ms))
{
    //get the first sheet from the excel file
    ExcelWorksheet sheet = package.Workbook.Worksheets[1];

    //loop all rows in the sheet
    for (int i = sheet.Dimension.Start.Row; i <= sheet.Dimension.End.Row; i++)
    {
        //loop all columns in a row
        for (int j = sheet.Dimension.Start.Column; j <= sheet.Dimension.End.Column; j++)
        {
            //do something with the current cell value
            string currentCellValue = sheet.Cells[i, j].Value.ToString();
        }
    }
}

Answer 2

SpreadsheetGear can do it:

SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromStream(stream);

You can try it for yourself with the free evaluation.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

It seems i found a soultion to the problem myself.

http://www.codeplex.com/ExcelDataReader

This library seems to work nicely and it takes a stream to read the excel file.

ExcelDataReader reader = new ExcelDataReader(ExcelFileUpload.PostedFile.InputStream);

Answer 4

I use ClosedXML nuget package to read excel content from stream. It has a constructor overload in XLWorkbook class which takes stream pointing to an excel file (aka workbook).

imported namespace at the top of your code file:

using ClosedXML.Excel;

Source code:

var stream = /*obtain the stream from your source*/;
if (stream.Length != 0)
{
    //handle the stream here
    using (XLWorkbook excelWorkbook = new XLWorkbook(stream))
    {
        var name = excelWorkbook.Worksheet(1).Name;
        //do more things whatever you like as you now have a handle to the entire workbook.
        var firstRow = excelWorkbook.Worksheet(1).Row(1);
    }
}

Answer 5

Infragistics has an excel component that can read an excel file from a stream.

I'm using it in a project here and it works well.

Also the open source myXls component could easily be modified to support this. The XlsDocument contstructor only supports loading from a file given by a file name, but it works by creating a FileStream and then reading the Stream, so changing it to support loading from streams should be trivial.

Edit: I see that you found a solution but I just wanted to note that I updated the source code for the component so that it now can read an excel file directly from a stream. :-)