Hi Alex,
you can use custom java action to achieve the desired functionality as shown below
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import com.mendix.systemwideinterfaces.core.UserAction;
import com.mendix.systemwideinterfaces.core.IMendixObject;
import com.mendix.systemwideinterfaces.core.IMendixObjectMember;
import com.mendix.systemwideinterfaces.core.IContext;
import com.mendix.systemwideinterfaces.core.ISession;
public class ExcelDataExtractor implements UserAction<List<IMendixObject>> {
private final InputStream inputStream; // Input stream for the Excel file
public ExcelDataExtractor(InputStream inputStream) {
this.inputStream = inputStream;
}
@Override
public List<IMendixObject> executeAction(IContext context) throws Exception {
List<IMendixObject> extractedData = new ArrayList<>();
// Load the Excel workbook from the input stream
Workbook workbook = new XSSFWorkbook(inputStream);
// Get the specific sheet (modify the sheet index as needed)
Sheet sheet = workbook.getSheetAt(0);
// Loop through the rows and columns within the specified range (B5 to Z16)
for (int rowNum = 4; rowNum <= 15; rowNum++) { // Rows 5 to 16
Row row = sheet.getRow(rowNum);
if (row != null) {
for (int colNum = 1; colNum <= 25; colNum++) { // Columns B to Z
Cell cell = row.getCell(colNum);
if (cell != null) {
// Extract the cell value and map it to Mendix entity attributes
String cellValue = getCellValueAsString(cell);
// Create or update Mendix objects with the extracted data
IMendixObject extractedObject = createOrUpdateMendixObject(context, cellValue);
extractedData.add(extractedObject);
}
}
}
}
// Close the workbook and input stream
workbook.close();
inputStream.close();
return extractedData;
}
private String getCellValueAsString(Cell cell) {
if (cell.getCellType() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == CellType.NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return ""; // Handle other cell types as needed
}
}
private IMendixObject createOrUpdateMendixObject(IContext context, String cellValue) {
// Implement logic to create or update Mendix objects with the extracted data
// You'll need to map the cellValue to Mendix entity attributes
// Example code:
IMendixObject mendixObject = null;
// ... Create or update the Mendix object ...
return mendixObject;
}
@Override
public List<IMendixObject> getTaskResult() {
return null;
}
@Override
public void rollback() {
// Implement rollback logic if necessary
}
}