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:
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[
^].