XLSX File Import in Siebel 22.3: Java Integration for Data Processing

 

In modern business processes, data transfer and integration are of great importance. In Siebel 22.3, importing an Excel file in XLSX format directly from the screen and writing its contents to the database is not a standard feature. This article discusses the challenges faced, the solution process, and critical code snippets.

Requirement

The project aimed to enable users to upload Excel files in XLSX format directly from the Siebel screen and write their contents into the database. This eliminates manual data entry and speeds up processes.

Sample excel file to import

Challenges

The primary challenge in implementing this process was that Siebel only allowed reading CSV files by default. According to confirmation from Oracle Support, Siebel supports only CSV format via “Clib.” Therefore, direct processing of XLSX files was not possible.

Solution

To overcome this challenge, the following solution was developed:

1. Reading XLSX with Java: A custom Java program was written to read specific columns from an XLSX file.
2. Dynamic Row Reading: Since the number of rows in the Excel file is not fixed, a loop was used to read all rows and store the data in SiebelPropertySet (PropSet).
3. Using Apache POI: The Apache POI library was used in Java to process Excel files.
4. Integration with OpenShift: The Java JAR file was included in the container image for deployment on OpenShift.
5. Siebel and Java Integration: A Business Service (BS) in Siebel was used to call the JAR file and process the returned PropSet.
6. Writing to Database: The PropSet returned by Java was processed and records were inserted into the Siebel database.

Critical Code Snippets

Reading Excel and Preparing PropSet with Java

// Read the first row (A1 and B1)
Row firstRow = sheet.getRow(0);
String userId = getCellValueAsString(firstRow.getCell(0));     // A1
String firstColumnB = getCellValueAsString(firstRow.getCell(1)); // B1
String firstColumnD = getCellValueAsString(firstRow.getCell(3)); // D1

output.setProperty("ColumnA1", userId);
output.setProperty("ColumnB1", firstColumnB);
output.setProperty("ColumnD1", firstColumnD);

This code block reads critical information from the first row of the Excel file and adds it to the SiebelPropertySet.

// Read other rows
int rowNum = 1;
while (rowNum <= sheet.getLastRowNum()) {
    Row currentRow = sheet.getRow(rowNum);
    if (currentRow == null) {
        rowNum++;
        continue;
    }

    String cellAValue = getCellValueAsString(currentRow.getCell(0));
    if ("end".equalsIgnoreCase(cellAValue)) {
        break;
    }

    String paramName = getCellValueAsString(currentRow.getCell(4));  // Column E
    String paramValue = getCellValueAsString(currentRow.getCell(5)); // Column F

    if (!paramName.isEmpty() && !paramValue.isEmpty()) {
        SiebelPropertySet rowSet = new SiebelPropertySet();
        rowSet.setProperty("ParamName", paramName);
        rowSet.setProperty("ParamValue", paramValue);
        output.addChild(rowSet);
    }

    rowNum++;
}

This part reads all rows from Excel and adds “ParamName” and “ParamValue” information into the PropSet.

Processing Data from Excel in Siebel BS

var bo = TheApplication().GetBusObject("BO_NAME");
var bc = bo.GetBusComp("BC_NAME");

for (var i = 0; i < outputPS.GetChildCount(); i++) {
    var paramName = outputPS.GetChild(i).GetProperty("ParamName");
    var paramValue = outputPS.GetChild(i).GetProperty("ParamValue");

    bc.ClearToQuery();
    bc.SetSearchSpec("ColumnA1_Field", ColumnA1);
    bc.SetSearchSpec("paramName_Field", paramName);
    bc.ExecuteQuery(ForwardOnly);

    if (bc.FirstRecord()) {
        bc.SetFieldValue("ParamValue_Field", paramValue);
        bc.WriteRecord();
    } else {
        bc.NewRecord(NewAfter);
        bc.SetFieldValue("ColumnA1_Field", ColumnA1);
        bc.SetFieldValue("Sub paramName_Field", paramName);
        bc.SetFieldValue("ParamValue_Field", paramValue);
        bc.SetFieldValue("columnB1_Field", columnB1);
        bc.SetFieldValue("columnD1_Field", columnD1);
        bc.WriteRecord();
    }
}

This Siebel code writes the processed PropSet data into the Business Component. Existing records are updated, while new records are inserted.