Retrieving DataTable from Memory Stream.

I have a data table that is formatted from an array of Column Names, I.e.

        DataTable ThisTable = new DataTable();

        for (int i = 0; i <= col.GetUpperBound(0); i++)
        {
            try
            {
                ThisTable.Columns.Add(new DataColumn(col[i].ToString(), typeof(string)));
            }
            catch (Exception e)
            {
                MessageBox.Show("Uploader  Error"+e.ToString());
                return null; 
            }
        }

i then use a BinaryFormatter object to Serialize/Deserialize a memory stream so that I can read the data from the stream into a table. Essentially user开发者_JAVA百科s will be uploading an excel sheet that will be read into a database. Although I realize that it is possible, I DO NOT want to write the sheet to disk then read it in.

However when I go to retrieve the datatable from the Deserialize() call, I dont get an error, however I get an empty DataSet.

Mem would be my memory stream object.

        BinaryFormatter bformat = new BinaryFormatter();
        bformat.Serialize(Mem, ThisTable);
        Mem.Seek(0, SeekOrigin.Begin);
        byte[] bt = Mem.ToArray();


        Stream s = new MemoryStream(bt);
        Mem.Position = 0;            
        s.Position = 0; 
        ThisTable = (DataTable)bformat.Deserialize(Mem); 
        DS.Tables.Add(ThisTable);

        return DS;

I would suggest using the OpenXml SDK to read the Excel file into the DataTable.

Take a look at the answer from M_R_H for an example:

From Excel to DataTable in C# with Open XML

You can also look at the answer from amurra here for another example:

reading Excel Open XML is ignoring blank cells

Unless there is an error in the sample code you posted, you aren't populating the DataTable with any data, you are just creating columns. You can add rows to the DataTable via DataTable.Rows.Add():

foreach(var o in myDataSource)
{
    ThisTable.Rows.Add(o.Prop1, o.Prop2, etc);
}