How to create .xls/.xlsx file with csv values from an incoming xml in SAP PI/PO using java mapping

[former_member202011]

Hi All,

I am trying to create a .xls/.xlsx file using Java mapping in SAP PO 7.5 with values inside the file being semicolon separated.

I have attached the screenshot for input xml and output file as reference.

capture.jpg

I am using DOM Parser to parse the input xml as below and then setting the value in the respective cells of the excel.

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder();

Document document = builder.parse(inputstream); NodeList nodelist = document.getElementsByTagName("ExchangeRate");

Please let me know how to add ; in between the different values fetched from the xml.

HSSFRow row1 = ITSFX.createRow(1);

cell = row1.createCell((short) 0);

cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("CurrencyFrom").item(0). getFirstChild() .getNodeValue());

Here i am getting the first value fetched from the xml i.e. EUR in this case.

Now i have to add the second value from the xml in the same cell [1,0] with semi colon as separator.

[former_member607993]

Hi Rajat,

Regards to this file(.xml) to file(.xls) scenario, You set the ASMA if there is any dynamic file naming scheme.

public String ASMAFileName(String DOCNUM, String CREDAT, String CRETIM, Container container) throws StreamTransformationException{

String filename = new String("");

DynamicConfiguration conf1 = (DynamicConfiguration) container.getTransformationParameters().get(StreamTransformationConstants.DYNAMIC_CONFIGURATION); DynamicConfigurationKey key1 = DynamicConfigurationKey.create("http://sap.com/xi/XI/System","FileName"); filename = "Currency" + "_" + DOCNUM + "_" + CREDAT + "_" + CRETIM + ".xls";

conf1.put(key1,filename);

return filename;

Then in the integration directory

the module configurations looks like:

Thanks and Regards - Rajesh PS

[former_member202011]

Hi Rajesh,

Thank you so much for the answer but only change i need is the last value 5911 and 405 in different column i.e.,column B.