Get Data From An Uploaded Excel File Without Saving to File System

Question

I have a requirement to allow a user of this ASP.NET web application to upload a specifically formatted Excel spreadsheet, fill arrays with data from the spreadsheet, and bind the arrays to a Oracle stored procedure for validation and insertion into the database. I must be able to read the data from the Excel spreadsheet without being able to save it to the web server's hard disk. This is the part I cannot figure out how to do. Here's a simple code example.

<%--ASP.NET Declarative--%>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Send File" OnClick="Button1_Click" />

// C# Code-Behind
protected void Button1_Click(object sender, EventArgs e) {
    var postedFile = FileUpload1.PostedFile;

    // ... Read file in memory and put in format to send to stored procedure ...

}

Can anyone help me with this? I appreciate anyone's consideration.

thx,
gabe

Answer 1

Use the FileUpload1.FileContent Stream. I guess your Excel library can handle streams directly.

Answer 2

I don't know if it can. that's the part i've been trying to figure out. i've tried messing around with the library in the Microsoft.Office.Interop.Excel assembly, but i can't find anything to that takes in a filesteam/inputstream. everything requires a file path of some kind. the other problem is that the web server doesn't have MS Excel on it, so i can't use any Interop assemblies anyway. i've seen lots of examples of using the OLE db provider but the file has to be saved on the file system first, which i can't do.

is there any other way of reading the inputsteam and grabbing MS Excel data.

Answer 3

The COM libraries of Excel does not support loading file from another source than file. But there exists a lot of third-party components, which allows you read/write excel files.

Othervise you can see a documentation for th XLS file format at [MS-XLS]: Excel Binary File Format (.xls) Structure Specification.

Or you can use a same way of office files processing like in Sharepoint Server. See Microsoft.Office.Excel.Server.WebServices Namespace.

Answer 4

This is something I've been playing with recently.

Check this post: Write an excel workbook to a memory stream .NET

It points to a great library by Carlos Aguilar Mares, which lets you work with Excel workbooks as XML.

ExcelXMLWriter

You dont need Excel installed on the server (which is kinda breaking the MS licensing anyway as you are accessing this over the web).

You can load the Excel workbook as a stream using Workbook.Load(stream)

Answer 5

hmm, that does look interesting. thx! so does this API only open XML files though?

Answer 6

Could you have your users upload a CSV file instead? Dealing with a plain text file would be much easier. I had a similar issue before and I asked the users and they were OK, saved me tons of work.

Good luck.

Answer 7

no, that is not possible. the customer already has a defined spreadsheet that i must use.

Answer 8

maybe have look on csvreader, it reads csv, xls and xlsx:

http://www.csvreader.com

Answer 9

thx for all the answers! i did find a great lightweight open source API on Codplex for doing this called ExcelDataReader. It can transform an input stream of an excel file into a System.Data.DataSet object (probably parsing using BIFF specs). it's pretty sweet. here's the link http://www.codeplex.com/ExcelDataReader and here's a code sample:

<%--ASP.NET Declarative--%>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Send File" OnClick="Button1_Click" />
<asp:GridView ID="GridView1" runat="server" />

// C# Code-Behind
protected void Button1_Click(object sender, EventArgs e) {
    // the ExcelDataReader takes a System.IO.Stream object
    var excelReader = new ExcelDataReader(FileUpload1.FileContent);
    FileUpload1.FileContent.Close();

    DataSet wb = excelReader.WorkbookData;
    // get the first worksheet of the workbook
    DataTable dt = excelReader.WorkbookData.Tables[0];

    GridView1.DataSource = dt.AsDataView();
    GridView1.DataBind();
}

Answer 10

hi , i want to use that excel open source dll in my 2008 project ! but it gave error of signature, i convert that project from 2005 to 2008 !! but now its giving error

Error: Invalid file signature Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Excel.Exceptions.InvalidHeaderException: Error: Invalid file signature

Source Error:

Line 230: } Line 231: if (!hdr.IsSignatureValid) Line 232: throw new InvalidHeaderException(Errors.ErrorHeaderSignature); Line 233: if (hdr.ByteOrder != 0xFFFE) Line 234: throw new FormatException(Errors.ErrorHeaderOrder);

Source File: C:\Projects\Excel\Core\XlsHeader.cs Line: 232

what should i do ,,,, any idea ???

Answer 11

i also want to it doesnt work with xlsx ext file or xls sheet which is saved in 2007 or vista !! is any update ?? for that