CODEWITHSUNDEEP
javaspringapirestmultifile-uploader
User
User

Reputation: 67

Best design pattern to perform excel valudation in java

User is uploading a excel workbook in an application. As part of business logic, we need to perform data validations (mandatory fields, data type, length, enum etc). There are more than 100 columns in one Excel sheet and the data validations needs to be performed for all the columns of the records. Each column has roughly 10 validations. The frontend is developed in React and business layer is a java rest api.

Where can be the best way to store the validation rules?

Option 1: can we store the validation rules in database and then write some generic framework in java which can read the rules from database and do the valudation ?

Option 2: can we convert the excel into json and then validate it against json schema ?

Also need to keep watch on the performance impact.

Any other better option?

Upvotes: 0

Views: 479

Answers (1)

Grim
Grim

Reputation: 2040

You should not validate it in the browser, since browser-execution can be hacked. But you can provide a template-download. The template-download would be a partialy protected excel-file. Here you can predefine some rules (readonly-areas, basic validation, instructions for handling and restrictions).

You should validate it primarily in the server (single-responsibility-principle). The best way to validate it is while uploading. If the validation of the currently uploading excel-file failed, the upload must be rejected having a meaningfull message. Excel-files that are not validated should never be stored in the database (because it would violate data-integrity-principle). Notice: If there is a requirement to store the unvalidated excel-file as a "draft" to validate/complete it later, a second database should be created decoupled from the main database to store these "draft" informations.

In every case, excel-files should not be part of the main-database as binary streams! The information in the excel-files must be stored in the main-database, but not the excel-file itself.

Upvotes: 2

Related Questions