Converting excel byte array data into datatable C# API

Question

0.00/5 (No votes)

See more:

.NET4.5

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.

Throwing error at

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

<pre lang="c#">{"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.

What I have tried:

Below is the code i have tried

C#

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;

}

Posted 27-Nov-19 18:20pm

Prathap Gangireddy

Updated 27-Nov-19 22:11pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Answer 1 · 2019-11-27T21:07:00

Solution 1

I believe you cannot read the binary array from the xslx that way: you have to use automation (in order to avoid parsing yourself the xlsx format). See, for instance: Automate Excel by using Visual C# to fill or to obtain data by using arrays - Office | Microsoft Docs[^].

Posted 27-Nov-19 21:07pm

CPallini

Comments

Maciej Los 28-Nov-19 4:12am

5ed!

Maciej Los · Answer 2 · 2019-11-27T22:11:00

Sorry, but it's not quite clear what the requirement is:

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

You have to decide what kind of result you want to get: datatable or xml file.

As to you code...
Excel file (*.xlsx) is not just a text file. You can NOT read it content by using FileStream and BinaryReader. Excel file uses OpenXml format[^], which means that you have to use proper methods to read it, modify it and write it. See:
Spreadsheets (Open XML SDK) | Microsoft Docs[^]
BTW: Excel file is already XML, but in zipped form!

So, if you want to fetch tabular data from specific sheet of Excel file to the datatable object and save that datatable to an xml file, the easiest way is to use ADO.NET[^] - OleDB.

See:

C#

DataTable dt = new DataTable();
string excelfile = @"C:\PRATAP FOLDER\test.xlsx";
string xmlfile = excelfile.Replace("xlsx", "xml");
string connstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", excelFile);
using (OleDbConnection connection = new OleDbConnection(connstring))
{
	string sql = string.Format(@"SELECT * FROM [Sheet1$];", sExcelSheet);
	connection.Open();
	using(OleDbCommand command = new OleDbCommand(sql, connection))
	{
		using (OleDbDataReader reader = command.ExecuteReader())
		{
			dt.Load(reader);
		}
	}
}
if(dt.Rows.Count>0)
    dt.WriteXml(xmlfile);

Another way is to use EPPlus library[^].