Read an Excel file uploaded using FileUpload Control without saving it on the server

Question

Need to be able to read an Excel file uploaded using FileUploadControl in ASP.NET. The solution will be hosted on a server. I do not want to store the Excel file on the server. I would like to directly convert the excel content into a dataset or a datatable and utilize.

Below are the two solutions I already found but would not work for me.

1. LINQTOEXCEL - This method works when you have an excel file on your local machine and you are running your code on the local machine. In my case, the user is trying to upload an excel file from his local machine using a webpage hosted on a server.

2. ExcelDataReader - I am currently using this one, but this is a third party tool. I cannot move this to our customer. Also if a row/column intersection is carrying a formula, then that row/column intersection's data is not being read into the dataset.

Most of the suggestions i found on google and StackOverflow work when both the excel and the .NET solution are on the same machine. But in mine, I need it to work when the solution is hosted on a server, and users are trying to upload excel using the hosted webpage on their local machine. If you have any other suggestions, could you please let me know?

Answer 1

You can use the InputStream property of the HttpPostedFile to read the file into memory.

Here's an example which shows how to create a DataTable from the IO.Stream of a HttpPostedFile using EPPlus:

protected void UploadButton_Click(Object sender, EventArgs e) {     if (FileUpload1.HasFile && Path.GetExtension(FileUpload1.FileName) == ".xlsx")     {         using (var excel = new ExcelPackage(FileUpload1.PostedFile.InputStream))         {             var tbl = new DataTable();             var ws = excel.Workbook.Worksheets.First();             var hasHeader = true;  // adjust accordingly             // add DataColumns to DataTable             foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])                 tbl.Columns.Add(hasHeader ? firstRowCell.Text                     : String.Format("Column {0}", firstRowCell.Start.Column));              // add DataRows to DataTable             int startRow = hasHeader ? 2 : 1;             for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)             {                 var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];                 DataRow row = tbl.NewRow();                 foreach (var cell in wsRow)                     row[cell.Start.Column - 1] = cell.Text;                 tbl.Rows.Add(row);             }             var msg = String.Format("DataTable successfully created from excel-file. Colum-count:{0} Row-count:{1}",                                     tbl.Columns.Count, tbl.Rows.Count);             UploadStatusLabel.Text = msg;         }     }     else      {         UploadStatusLabel.Text = "You did not specify a file to upload.";     } } 

Here's the VB.NET version:

Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)     If (FileUpload1.HasFile AndAlso IO.Path.GetExtension(FileUpload1.FileName) = ".xlsx") Then         Using excel = New ExcelPackage(FileUpload1.PostedFile.InputStream)             Dim tbl = New DataTable()             Dim ws = excel.Workbook.Worksheets.First()             Dim hasHeader = True ' change it if required '             ' create DataColumns '             For Each firstRowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column)                 tbl.Columns.Add(If(hasHeader,                                    firstRowCell.Text,                                    String.Format("Column {0}", firstRowCell.Start.Column)))             Next             ' add rows to DataTable '             Dim startRow = If(hasHeader, 2, 1)             For rowNum = startRow To ws.Dimension.End.Row                 Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column)                 Dim row = tbl.NewRow()                 For Each cell In wsRow                     row(cell.Start.Column - 1) = cell.Text                 Next                 tbl.Rows.Add(row)             Next             Dim msg = String.Format("DataTable successfully created from excel-file Colum-count:{0} Row-count:{1}",                                     tbl.Columns.Count, tbl.Rows.Count)             UploadStatusLabel.Text = msg         End Using     Else         UploadStatusLabel.Text = "You did not specify an excel-file to upload."     End If End Sub 

For the sake of completeness, here's the aspx:

<div>    <h4>Select a file to upload:</h4>     <asp:FileUpload id="FileUpload1"                         runat="server">    </asp:FileUpload>     <br /><br />     <asp:Button id="UploadButton"         Text="Upload file"        OnClick="UploadButton_Click"        runat="server">    </asp:Button>         <hr />     <asp:Label id="UploadStatusLabel"        runat="server">    </asp:Label>         </div> 

Answer 2

//Best Way To read file direct from stream IExcelDataReader excelReader = null; //file.InputStream is the file stream stored in memeory by any ways like by upload file control or from database int excelFlag = 1; //this flag us used for execl file format .xls or .xlsx if (excelFlag == 1) {     //1. Reading from a binary Excel file ('97-2003 format; *.xls)     excelReader = ExcelReaderFactory.CreateBinaryReader(file.InputStream); } else if(excelFlag == 2)                                 {     //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)     excelReader = ExcelReaderFactory.CreateOpenXmlReader(file.InputStream); }  if (excelReader != null) {     //...     //3. DataSet - The result of each spreadsheet will be created in the result.Tables     ds = excelReader.AsDataSet();     //...     ////4. DataSet - Create column names from first row     //excelReader.IsFirstRowAsColumnNames = true;     //DataSet result = excelReader.AsDataSet();      ////5. Data Reader methods     //while (excelReader.Read())     //{     //    //excelReader.GetInt32(0);     //}      //6. Free resources (IExcelDataReader is IDisposable)     excelReader.Close(); }