/spring-boot-apache-POI

list of reference POCs to work with excel files using apache-POI and spring-boot

Primary LanguageJava

Reference POC projects to work with excel files using Apache POI library and Java Spring-boot

All Proof of concepts are deployed to heroku with their links and sample videos included in their respective README.md file

Proof of concepts included in this repo (Summary)

  • Create excel file with single sheet from database records
    • Insert 300 dummy employees records in H2 DB on startup using @PostConstruct
    • endpoint exposed to download created excel sheet from the inserted records at runtime
  • Create excel file with multiple sheet from database records
    • insert super_heroes from different comics in DB at startup (Marvel, Detective Comics)
    • endpoint exposed to download multiple sheet excel file created at runtime
    • each sheet contains records of super_heroes from one comic
    • one sheet for all superheroes from marvel comics, one for DC and so on.
  • Validate data inside excel sheet for errors and return errors as List.of(String)
    • endpoint exposed for user to download excel template (required columns)
    • user fills in data (rows) to downloaded template
    • exception is thrown is file extension or format(column names and order) is different
    • the errors include leaving cells blank, duplicate email-id, wrong email-id, invalid number type etc
    • each row is validated for errors, if errors exists a list is returned detailing out the error messages along with it's cell position
  • Validate data inside excel sheet for errors and detail out errors in given excel file itself and return the same
    • same as above (first 4)
    • each cell of each row is validated for errors and if a cell is found to have any error it is colored in RED
    • a new sheet in the same excel file is created detailing out the error messages (type) and the cell position (row, column)
    • the above modified file is returned (downloaded automatically to users screen) if errors exists
  • Read data from excel sheet to database
    • endpoint exposing to download required excel sheet template (required columns)
    • sheet is validated for errors, if no errors are found a unique 6 digit code and the hex of the validated file is generated, the code is returned as response
    • the code is stored in a cache as key with the validated file's hex being it's value
    • user hits /employee/bulk-upload API with given code and validated file (error thrown if code is invalid or file other than the validated one is provided)
    • each row is read from the given sheet and corresponding records are created in the database