Converting Excel data into an XML file (Pre-defined template)

Questions about XML that are not covered by the other forums should go here.
Pratik89
Posts: 1
Joined: Sun Jan 24, 2021 9:19 pm

Converting Excel data into an XML file (Pre-defined template)

Post by Pratik89 » Sun Jan 24, 2021 9:24 pm

Hello Everyone,
I have an excel file with 5 sheets and I want to convert this into an XML File. The XML file has to be one continuous file with the data blocks repeated in a pattern.

For example in from the Big File with 5 sheets (attached), from the first sheet,
B5 goes to Line 5
B9 goes to Line 34
B11 goes to Line 59
E5:E105 goes to Line 92 (Tabular data separated by commas)
F5:F105 goes to Line 96

of the ExcelToXML file (attached). I could not attach the XML file directly so have converted it to a .txt file.

This same pattern is repeated for each sheet and pasted one below the other in the XML file (attached). I need to do this for 110 sheets automatically.

Please suggest a way to solve this.

Thank you


warm regards,
Pratik
Big_Test_File-ToXML.zip
(56.13 KiB) Downloaded 36 times
Attachments
Snapshot_XML.png
Snapshot_XML.png (14.15 KiB) Viewed 430 times

Cosmin Duna
Site Admin
Posts: 38
Joined: Wed Dec 12, 2018 5:33 pm

Re: Converting Excel data into an XML file (Pre-defined template)

Post by Cosmin Duna » Tue Feb 02, 2021 1:28 pm

Hi Pratik,
We have multiple methods to convert Excel in Oxygen:
  • Using the import action "MS Excel file" from "File>Import" menu. This can be applied only over a sheet from a document and allows you to customize the name, XML name, and conversion criterion for the root and row elements. See the following link for more information: https://www.oxygenxml.com/doc/versions/ ... box_method
  • Using the "Excel to DITA" action from the Batch Converter add-on. This allows you to convert multiple documents ones and for each excel document, you will obtain a DITA Topic that contains a table for each sheet from your excel. But this conversion cannot be customized. Before to apply the conversion please save the document with "xlsx" extension format because the plugin has some problem to interpret "xlsm" format. See the "Readme" section from the following link for more information about the add-on: https://github.com/oxygenxml/oxygen-resources-converter
The case you described is a particular one and you need a custom conversion. For this, you can create an add-on in Oxygen that use Apache POI (https://poi.apache.org/index.html) library to generate XML content. Here is how we use this library to create DITA content in the Bath Converter add-on: https://github.com/oxygenxml/oxygen-res ... erter.java
Or you can use the Batch Converter add-on to convert to DITA topic and after this, you have to create an XLST (https://www.w3schools.com/xml/xsl_intro.asp) that transform the topic in the pattern you described.

Best regards,
Cosmin
Cosmin Duna
<oXygen/> XML Editor
http://www.oxygenxml.com

Post Reply