c# - 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

I found a great lightweight open source API on Codeplex 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).

Here's the link:

http://www.codeplex.com/ExcelDataReader

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();
}