How to Retrieve Sheet Names from an Excel File in TOS DI 6.4.1?

[tosuser21]

Thread Body:

Hello Talend Community,

I’m using Talend Open Studio for Data Integration (TOS DI) 6.4.1 and need to retrieve the names of all the sheets from an Excel file. I’ve tried several approaches, but none seem to work. Here’s what I’ve done so far:

---

What I’ve Tried:

1. Using Apache POI in a tJava Component:

• I’ve added poi-3.10.1.jar to my job using the tLibraryLoad component.
• Below is the Java code I used in tJava:

java

Copy code

import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import java.io.FileInputStream; try { // File path from context String filePath = context.inputExcelFile; // Open the Excel file FileInputStream fis = new FileInputStream(filePath); Workbook workbook = WorkbookFactory.create(fis); // Print sheet names int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { System.out.println("Sheet Name: " + workbook.getSheetName(i)); } // Close workbook and stream workbook.close(); fis.close(); } catch (Exception e) { e.printStackTrace(); }

• Result: I get errors such as Can only iterate over an array or instance of java.lang.Iterable or NullPointerException.

---

2. Using Other Approaches:

• I also tried using older Apache POI versions (poi-3.10.1.jar) but still face the same issues.
• Verified that the Excel file exists and is valid.

---

Environment Details:

• TOS DI Version: 6.4.1
• Excel File Format: .xlsx (Office 365 generated)
• Java Version: 1.8 (as used by Talend Studio)
• Apache POI Library: poi-3.10.1.jar

---

What I Need Help With:

1. Is there a native way in Talend to list all sheet names from an Excel file?
2. If Apache POI is required, what specific libraries and versions should I use to avoid compatibility issues with Talend?
3. Can someone provide a working example or guide me through the correct steps to retrieve sheet names?

I’ve spent hours trying to get this to work, but I’m stuck. Any help or guidance would be greatly appreciated!

Thank you in advance for your support!