XML file to excel file conversion

Aspose.Cells Product Family
1

Hi Aspose Team,

Is there any way to convert .xml file to .xlsx file using aspose component?

Regards
Anish

2

Hi Anish,

Thanks for your posting and considering Aspose.Cells.

Aspose.Cells cannot read general XML file. It however can read the SpreadsheetML format which is a kind of XML file but it is MS-Excel oriented.

However, if you can convert your XML into a DataSet, then you can import the data into worksheet using Aspose.Cells for .NET API. Please see the following documentation article how to import data from dataset into worksheet for your reference.

http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets

3

Hi Shakeel Faiz,
I checked the links which you provided.
But i am little bit confused .
So here i am attaching an xml file.
Can you please try to convert this xml to excel file, and please provide me the code also?

Regards
Anish

4

Hi Anish,

Thank you for writing back.

As discussed earlier by my colleague that Aspose.Cells component can easily load an XML if it is of type SpreadsheetML, that an XML dialect developed by Microsoft to represent information in an Excel workbook. If any given XML follows the schema as defined by Microsoft then it can be simply loaded in an instance of Workbook. In case the provided XML isn’t in correct shape/schema then you have to find alternatives to load the data into Workbook for further processing.

Your provided XML does not follow the required schema definition, but still you can load the data into a Workbook. Please check the below provided code snippet and attached spreadsheet for your reference.

C#


var dataSet = new System.Data.DataSet();
dataSet.ReadXml(myDir + “Red_RochesterResponse File.xml”);
var book = new Aspose.Cells.Workbook();
book.Worksheets[0].Cells.ImportDataTable(dataSet.Tables[2], true, “A1”);
book.Save(myDir + “output.xlsx”);

Please note, in above example I have used DataSet to load the XML file instead of a DataTable because in order to read an XML file into a DataTable, you have to first specify the schema information by calling ReadXmlSchema to pull in a schema describing the data. Whereas, DataSet doesn't require setting the schema first.

I hope this helps.
5

Hi Babar Raza,

I tried to convert the given XML file to XLSX.
It is converting to XLSX.
But the problem is , it is not loading the complete data to worksheet.
Is it because of the given XML is sot satisfying the schema.
But if i am trying to open the given XML file as “As an XML Table” it is showing the complete details in the workbook
So how can we save this xml file using Data Table?
Can you please help me on this?
Regards
Anish

6

Hi Anish,

Thank you for writing back.

As discussed earlier, your provided XML file does not follow the SpreadsheetML format completely, therefore it cannot be loaded directly to Workbook instance. If you may have tried it, Aspose.Cells throws exception with message “Line 0: in the SpreadsheetML file.” when loaded as follow,

C#

var workbook = new Workbook(myDir + “Red_RochesterResponse File.xml”);

The code snippet provided in my previously reply is just to exhibit the usage of Cells.ImportDataTable to import XML data in worksheet, and you can surely amend the code according to your requirement.

Because your provided XML contains multiple data schema (distinct schema) therefore when read with DataSet.ReadXml method, the data is distributed into several DataTables. You can easily import the data from these tables in a similar manner as shown below,

C#
//Initialize an instance of DataSet var dataSet = new System.Data.DataSet(); //Read XML into DataSet dataSet.ReadXml(myDir + "Red_RochesterResponse File.xml"); //Create an instance of Workbook var book = new Aspose.Cells.Workbook(); //Clear Worksheet collection book.Worksheets.Clear(); //Check if XML data has been imported if (dataSet.Tables.Count > 0) { //Iterate over the tables in Dataset //Each table (DataTable) contains distinct data schema foreach (DataTable table in dataSet.Tables) { //Add an empty Worksheet to Workbook int index = book.Worksheets.Add(); //Import data into different worksheets book.Worksheets[index].Cells.ImportDataTable(table, true, "A1"); } } //Save results book.Save(myDir + "output.xlsx");

Please feel free to write back in case you face any difficulties.