Aspose.Cells to customized xml output

Aspose.Cells Product Family
1

Hi,
We tried below code from this link Convert Excel XLS XLSX to XML in C# | C# Excel (XLS, XLSX) to XML Converter,
Workbook workbook = new Workbook(“sample.xlsx”);
workbook.Save(“Spreadsheet.xml”);
XmlSaveOptions xmlSaveOptions = new XmlSaveOptions();
workbook.Save(“data.xml”, xmlSaveOptions);

It is producing xml output as shown there.

We have custom requirement where we are expecting xml output in below format or refer attached image,

<root>
	<Student ID=1>
		<NAME>Student1</NAME>
		<DateofBirth>10-mar-2012</DateofBirth>
	</fund>
	<Student ID=2>
		<NAME>Student2</NAME>
		<DateofBirth>21-mar-2012</DateofBirth>
	</fund>
	<Student ID=3>
		<NAME>Student3</NAME>
		<DateofBirth>15-mar-2012</DateofBirth>
	</fund>
</root>

image.png (2.7 KB)

and my data input will look like below,
Student NAME DateOfBirth
1 Student1 10-mar-2012
2 Student2 21-mar-2012
3 Student3 15-mar-2012

Could you let us know if this is possible using Aspose.
Also If we can set the format using dynamic template then It will be more helpful.
image.png (2.74 KB)

2

@Umeshb,
May I ask if you are using Aspose.Cells Cloud or Aspose.Cells local components?
If you are using Aspose.Cells Cloud, you can try the Convert API or SaveAs API.
However, the current output result does not meet the output format you have proposed, so we need to evaluate it further.

3

@Umeshb,

You can implement your functionality with the following code:

string filename = “BookXML.xlsx”;
Workbook wb = new Workbook(InPutFloder + filename);
wb.ExportXml(wb.Worksheets.XmlMaps[0].Name, OutPutFloder + “output.xml”);

Currently, Aspose.Cells Cloud does not support this feature. If you need this feature, we will evaluate it and try to implement it as soon as possible.
1680579793177.png (4.96 KB)

4

Hi @wangtao,
Thank you for your quick response.
We are using Aspos.Cells local components.
I checked your code and had query in below line,
wb.ExportXml(wb.Worksheets.XmlMaps[0].Name, OutPutFloder + “output.xml”);
Could you share the input file you use to see what is inside wb.Worksheets.XmlMaps[0].Name

as you can see our first node

<Student ID=1>

has attribute ID, Could you guide us how can we set that in dynamic template?

5

@Umeshb,

I moved this post to Aspose.Cells Product Family - Free Support Forum - aspose.com.

6

Please check the input file: BookXML.zip (8.3 KB)

Just locate the cell and change it’s value:

Workbook wb = new Workbook("BookXML.xlsx");

wb.ExportXml(wb.Worksheets.XmlMaps[0].Name, "output.xml");

//change a student id
wb.Worksheets[0].Cells["A2"].PutValue(11);

wb.ExportXml(wb.Worksheets.XmlMaps[0].Name, "output2.xml");
7

Hi @ Peyton.xu
Thank you for sharing BookXML.zip.
Could you please help understanding how did you create the xml mapping inside excel file?
Could you share the code or reference you used for xml mappings?
I am trying to use below code but its not working

string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook wb = new Workbook();
wb.Worksheets.XmlMaps.Add(dataDir + “sample.xml”);
wb.Save(dataDir + “output_out.xlsx”);

8

@Umeshb

I just create a xml file based your shared xml data(make a little change) data.zip (265 Bytes)
, then import the xml file by Developer->Import in ExcelScreenshot_import_xml.png (26.6 KB)

Note: if “Developer” menu is not there, you can add it by Excel->Options->Customize the Ribbon

9

@Umeshb

Aspose.Cells can do the same work:

Workbook wb = new Workbook();
wb.ImportXml("data.xml", wb.Worksheets[0].Name, 0, 0);

wb.Save("output.xlsx");
10

Thank you, This worked. :slight_smile:

11

@Umeshb,

It is nice to know that it’s working on your end. Feel free to write us back if you have further queries or comments, we will be happy to assist you soon.

12

Hi @peyton.xu/@amjad.sahi
Above things are working fine separately.
Now I am trying to get this added to our code where we are using dynamic templates.
I am facing one challenge here where I first generate report using dynamic template and dataset. Now I want to apply xml mapping on generated report and then generate the final xml output file.
I have attached the code and input files.
Can you check if it possible to apply xml mapping on already generated excel file without clearing data from that file?Student.zip (9.3 KB)

13

@Umeshb,

Thanks for template file and sample code.

Please note, in MS Excel, every XML table is bound to specific XML map. How could you accomplish the task in MS Excel using its Developer (XML) options? Could you share your expected Excel file. Also, share how did you create the file using MS Excel using relevant MS Excel (menu) options. We will check it soon.

14

Hi @amjad.sahi,
For now we are generating all reports as excel using templates as I shared one sample template and code. comment #1 the code is what is already working. We need help in comment #2, #3 and #4 where we need help to convert that excel file into XML using XML mapping.

If you see my original question here where I have share expected output in xml format.

15

@Umeshb,

We need your expected output Excel file (after performing all the steps manually in MS Excel as what you do in code - where existing xml mapping is extended) and expected XML file. Also, give us current output Excel file and output XML by Aspose.Cells. We will check it soon.

16

Hi @amjad.sahi ,
I am attaching files

  1. Dynamic Template - Student_Template.xlsx

  2. Output using dynamic template - StudentReport.xlsx

  3. XML mapping input file -

  4. We want to add xml mapping to output file StudentReport.xlsx without clearing its data and we want to provide that file as input to below line code
    Workbook workbook = new Workbook(“StudentReport.xlsx”); // NOTE: Here we want pass file with xml mapping
    workbook.ExportXml(workbook.Worksheets.XmlMaps[0].Name, “FinalReport.xml”);
    Student_Template.zip (17.0 KB)

  5. and my FinalReport.xml should look like attached file. NOTE: We are not getting this output for now at this point as point 4 is not yet accomplish for xml mapping where we need your help

17

@Umeshb,

Did you check it in MS Excel manually? I checked and it seems in MS Excel, you cannot add XML mapping based on existing data present in the sheet in the workbook. So, data which was filled/processed against markers cannot add XML maps. You have to import XML file for it which will then add XML maps accordingly. If you still think that is possible in MS Excel, I would again request you to kindly do share a sample Excel file where you have added XML maps based on existing data in the worksheet. I guess you cannot do that because XML maps are added based on external XML files. Aspose.Cells follows MS Excel standards and specifications, so if you cannot accomplish your task in MS Excel, you cannot do that via Aspose.Cells APIs.

By the way, if you just need to export XML file based on your data set, you may try using DataSet.WriteXml or DataTable.WriteXml method for the task.

18

@Umeshb

I created a xsd file based your expected result xml file:sample.xsd.zip (429 Bytes)

Please try the following code to add XmlMap, link to exsiting data, and then export xml data:

Workbook wb = new Workbook("StudentReport.xlsx");
Worksheet sheet = wb.Worksheets[0];
Cells cells = sheet.Cells;

//create table
sheet.ListObjects.Add(0, 0, cells.MaxDataRow, cells.MaxDataColumn, true);

//create XmlMap based on xsd
int mapIndex = wb.Worksheets.XmlMaps.Add("sample.xsd");

//link XmlMap to data in sheet
string mapName = wb.Worksheets.XmlMaps[mapIndex].Name;
cells.LinkToXmlMap(mapName, 0, 0, "/root/Student/ID");
cells.LinkToXmlMap(mapName, 0, 1, "/root/Student/NAME");
cells.LinkToXmlMap(mapName, 0, 2, "/root/Student/DateofBirth");

wb.ExportXml(mapName, "output.xml");