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

tosuser21 — Wed, 27 Nov 2024 14:41:30 GMT

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

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:

Is there a native way in Talend to list all sheet names from an Excel file?
If Apache POI is required, what specific libraries and versions should I use to avoid compatibility issues with Talend?
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!

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