Click here to Skip to main content
15,947,785 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 27-Nov-19 22:11pm

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[^].
 
Share this answer
 
Comments
Maciej Los 28-Nov-19 4:12am    
5ed!
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[^].
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900