Xls file upload and content validation

[adnan.khan]

Hi,

I want to upload XLS file in jmix and also validate contents present in XLS file.
If contents present inside XLS is not proper as per validation system must show error as invalid file uploaded and not upload the file, instead create and error file which will have contents which had failed the validations to make user understandable why xls file was rejected by server to upload.
If file contents is proper and has passed the required validation then file contents must be inserted in database.

[andrey_vb (Andrey Balabanov)]

It’s not hard to implement.
Use poi to work with an Excel file.
Add dependencies in build.gradle:

implementation('org.apache.poi:poi')
implementation('org.apache.poi:poi-ooxml')

Below is a small code framework for the screen controller on which the file is loaded and processed.
I haven’t tested it in the IDE - it’s just a demonstration of the approach.

It involves handling the file download through FileStorageUploadField :: Jmix Documentation

In order to show the user a file with errors there can be many approaches in UI.
But in general, you need to create a new Excel file using poi.
Save file ( Storing Files in Database :: Jmix Documentation or Using File Storage :: Jmix Documentation).
Display the error file to the user. For example, allow a file to be downloaded after an import error message ( Downloading and Displaying Files :: Jmix Documentation).

...
@Autowired
    private FileStorageUploadField fileUploader;
...
@Subscribe("fileUploader")
    public void onFileUploaderFileUploadSucceed(SingleFileUploadField.FileUploadSucceedEvent event)  {
        importResultCode = "OK";
        File file = temporaryStorage.getFile(fileUploader.getFileId());
        //Check file and create file with errors use POI (https://poi.apache.org/) 
        FileInputStream inputStream = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        ......
        .....
        if (importResultCode.equals("OK")) {
                notifications.create()
                    .withDescription("Import finished")
                    .withType(Notifications.NotificationType.HUMANIZED)
                    .show();
         } else {
                   temporaryStorage.deleteFile(fileUploader.getFileId());
                  //Show error and dowdload XLS file with errors

            }
    }

[adnan.khan]

Thank you.