Converting Excel Byte Array data into Datatable C# API

Question

The requirement is that the client will send the Excel data in the form of Byte Array (will provide API link for the same to the client)

Now when the API receives the Byte Array, i want to convert into datatable for converting into XML.

Below is the code i have tried

 public string ConvertExcelByteArraytoXML()
        {

            byte[] Excelbytes = null;
            FileStream fs = File.OpenRead("C:\\PRATAP FOLDER\\test.xlsx");            
            BinaryReader binaryReader = new BinaryReader(fs);
            Excelbytes = binaryReader.ReadBytes((int)fs.Length);
            string CreateXMLFILE = string.Empty;

       // the above code was to get byte array from excel 

            DataSet tempDataSet = new DataSet();
            DataTable dt;
            // Deserializing into datatable    
            using (MemoryStream stream = new MemoryStream(Excelbytes))
            {
                BinaryFormatter bformatter = new BinaryFormatter();
                dt = (DataTable)bformatter.Deserialize(stream);

            }
            // Adding DataTable into DataSet    
            tempDataSet.Tables.Add(dt);

           using (StringWriter sw = new StringWriter())
            {
                dt.WriteXml(sw);
                CreateXMLFILE = sw.ToString();
            }
          return CreateXMLFILE;

}

Throwing error at

dt = (DataTable)bformatter.Deserialize(stream);

{"The input stream is not a valid binary format. The starting contents (in bytes) are: 50-4B-03-04-14-00-06-00-08-00-00-00-21-00-EB-7A-D2 ..."}

Can someone suggest what am i doing wrong here.

Based on my test, I can reproduce your problem. However, I find it hard for me to convert the byte array from excel to datatable successfully. Because binaryformatter doesn't know the structure of datatable. Therefore, I suggest that you could convert excel to datatable directly instead of using byte array. — Jack J Jun
– Jack J Jun, Commented Nov 28, 2019 at 2:35
Yeah that is easy way..but the client is sending in byte array format — Prathap Gangireddy
– Prathap Gangireddy, Commented Nov 28, 2019 at 5:04
If so, I suggest that you can convert byte array to excel file first. Then, you could convert the excel file to datatable. — Jack J Jun
– Jack J Jun, Commented Nov 28, 2019 at 5:38
I dont want to save the excel sheet in server/locally ...looking for an alternate approach..with this approach i have already written the code using IExcelDataReader — Prathap Gangireddy
– Prathap Gangireddy, Commented Nov 28, 2019 at 6:18
If you don't want to save the excel sheet in server/locally, I suggest that you can delete them after you using it. — Jack J Jun
– Jack J Jun, Commented Nov 29, 2019 at 1:26

Jack J Jun · Accepted Answer · 2019-11-29 01:47:37Z

Like I mentioned in the comment, you can try the following code to convert the byte array

to datatable.

Code:

class Program
    {
        static void Main(string[] args)
        {
            byte[] b = File.ReadAllBytes("D:\\2.xlsx");      // you get from the client
            File.WriteAllBytes("test.xlsx", b);             //Create an hourly file
            DataTable table = Exceltodatatable("test.xlsx");  // convert it to datatable
            File.Delete("test.xlsx");                         // delete the file
        }

        public static DataTable Exceltodatatable(string path)
        {
            DataTable dt = new DataTable();
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                using (OleDbCommand comm = new OleDbCommand())
                {
                    string sheetName = "sheet1";
                    comm.CommandText = "Select * from [" + sheetName + "$]";
                    comm.Connection = conn;
                    using (OleDbDataAdapter da = new OleDbDataAdapter())
                    {
                        da.SelectCommand = comm;
                        da.Fill(dt);
                        return dt;
                    }
                }

            }
        }
    }

Collectives™ on Stack Overflow

Converting Excel Byte Array data into Datatable C# API

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related