cancel
Showing results for 
Search instead for 
Did you mean: 

CPI - XML to Excel file with Groovy

tomvanrooijen
Participant
0 Kudos

Hi,

Now I need to generate an Excel file which must be sftp-ed to a file server.

With some research I created below script which makes use of the jxl excel api.

It more or less works apart from the fact that I do not know how to get the generated workbook into the message body so that it can be ftp-ed. I am not sure if I can get the comtent of the generated xml into a string variable. the createWorkbook method does not seem to support it.

Is it possible to get the contents in the message body? At the moment the message body looks like this:

jxl.write.biff.WritableWorkbookImpl@366ff27a

My test xml looks like this:

<Response xmlns="http://localhost/something">
	<record>
		<content>some content</content>
		<id>100</id>
	</record>
	<record>
		<content>other content</content>
		<id></id>
	</record>
	<record>
		<content>3rd content</content>
		<id>500</id>
	</record>
</Response>

/*
import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import jxl.*;
import jxl.write.*;


def Message processData(Message message) {
    //Body 
       def body = message.getBody(String.class)
       def completeXml= new XmlSlurper().parseText(body)
       
       def xPath1 = completeXml.'**'.findAll{ node-> node.name() == 'content' }*.text()
       def xPath2 = completeXml.'**'.findAll{ node-> node.name() == 'id' }*.text()
       
        
       WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"))
       WritableSheet sheet = workbook.createSheet("Worksheet 1", 0)
        
        def arrayLength = xPath1.size;
        def content = "";
        def id = "";
        
        for (i = 0; i < arrayLength; i++) {
             
             Label label = new Label(0, i, xPath1[i]); //column=0,row=1
             sheet.addCell(label);
             Label label1 = new Label(1, i, xPath2[i]); //column=2,row=2
             sheet.addCell(label1); 
        }
        
      
       workbook.write();
       
       message.setBody(workbook);
       
       workbook.close(); 
       
       return message;
}

I just found out that Apache POI supports .xlsx and JXL doesn't and that POI is the better maintained and documented library so maybe POI is the better alternative. It also seems more complex though.

Or possibly should I do the SFTP in the script instead of using the CPI adapter?

Thanks in advance.

Tom

Accepted Solutions (0)

Answers (1)

Answers (1)

AlexSampayo2
Explorer
0 Kudos

Hi mate, is a CSV file useful?

I used the convert XML to CSV drag and drop function and then when saving the file i save it as myFile.xls

Good Luck!

Cheers,

Alex