How to read excel file from file input without saving file to server

Question

I am receiving an Excel file that has been uploaded to the server by a client and all the examples that I'm looking at show how to save it then read it.

What I need to do is receive the excel file from a webpage and or FileReader and save it to a datatable with the column names being preserved from the first row of the Excel sheet.

The code below does exactly what I need but it does not show how to read the file into a data table without saving it to storage first.

I need to save the excel file and process it to make sure all the column names are correct and the data in each row is correct, after I process this information I'll then begin the process of saving it to the sql database.

How do I save the Excel file as a stream into a datatable while preserving the column names?

**Note the table name is not important, I will only process the first sheet of the Excel book, the column names and data type in each row is what is most important.

Code was cited from: http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx

using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration; 

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        }
    }
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr="";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable(); 
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close(); 

        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath); 
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

Answer 1

You can do this using EPPlus with something like the following:

protected void btnUpload_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        DataTable dataTable = new DataTable();
        using (MemoryStream mStream = new MemoryStream(fileContents))
        {
            using (var excelPackage = new ExcelPackage(mStream))
            {
                ExcelWorksheet firstSheet = excelPackage.Workbook.Worksheets.First();
                var endAddress = firstSheet.Dimension.End;
                dataTable.TableName = firstSheet.Name;
                ExcelRange headerRange = firstSheet.Cells[1, 1, 1,endAddress.Column ];

                //Add columns using headers
                foreach (var cell in headerRange)
                {
                    dataTable.Columns.Add(cell.Value.ToString()); //You can hardcode whatever type you need to here
                }

                //Add Data:
                for (int rowIdx = 2; rowIdx <= endAddress.Row; rowIdx++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int colIdx = 1; colIdx <= endAddress.Column; colIdx++)
                    {
                        dataRow[colIdx - 1] = firstSheet.Cells[rowIdx, colIdx].Value;
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }
        }
        //Now Do whatever you want with your DataTable:
        GridView1.DataSource = dataTable;
        GridView1.DataBind();
    }
}

The only issue is you are not reading any kind of schema from the imported file so you don't get any types (the above example ends up with each row filled with general object type).

If you need a strongly typed data table and you know in advance the type of columns you need in the DataTable then you can switch out the line creating the columns. For Example:

dataTable.Columns.Add(cell.Value.ToString(),typeof(int));

If you don't know in advance the types of column and still need the strongly typed table then I guess you could kludge something using the cell's Style.Numberformat property to decide which type to pass into the column contructor.