Read SFTP Sender Excel file sheetname in SAP CPI

[learninghubx01]

Hello Experts,

I have a scenario of getting an XLSX file from an SFTP (sender) using SAP CPI and sending to SAP S4. Here I need to get the sheet name before conversion to xml but I don't know how to do this reading to an Excel file.

Kindly share the groovy script code to read this before using converter class to convert to xml.

[Punith_Oswal]

Hello, 

Try the below groovy code that reads an Excel file and retrieves the sheet names before converting it to XML. This code uses Apache POI library, which is commonly used to work with Excel files in Java and Groovy.

import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import java.io.InputStream

def getSheetNamesFromExcel(InputStream inputStream) {
// Create a workbook instance from the Excel file
def workbook = WorkbookFactory.create(inputStream)

// Get the sheet names
def sheetNames = []
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheetNames.add(workbook.getSheetName(i))
}
workbook.close()

return sheetNames
}

// Example usage: Assume 'body' contains the Excel file input stream
def excelInputStream = message.getBody(InputStream)
def sheetNames = getSheetNamesFromExcel(excelInputStream)

// Log the sheet names
sheetNames.each { sheetName ->
messageLog.addAttachmentAsString("Sheet Name", sheetName, "text/plain")
}

Make sure to import Apache POI libraries (e.g., poi-ooxml, poi).

[learninghubx01]

Hello Punit,

Thank you for response.

Kindly provide me the code as per CPI. I will import the same in CPI iflow and check. I'm finding it difficult to make changes to the groovy code to use it in CPI iflow

Regards,

Amar

[Punith_Oswal]

Hello Amar, The code is already in groovy and it should work fine provided you have imported the mentioned Apache POI libraries.