Upload an excel file read the data from file and insert it into database using spring boot rest api

Question

I want to develope an rest api which will take excel sheet as an multipart object read the content in the file and upload it to the oracle database. untill now what i did is:

   @RequestMapping(value = "/upload", method = RequestMethod.POST, consumes = javax.ws.rs.core.MediaType.MULTIPART_FORM_DATA)
    public void uploadFileHandler(@RequestParam("name") String name,
                                 @RequestParam("file") MultipartFile file) throws IOException {
        try {

            ExcelController ex=new ExcelController();
            File f1=ex.convert(file);
            FileInputStream excelFile = new FileInputStream(f1);
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();

            while (iterator.hasNext()) {

                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();

                while (cellIterator.hasNext()) {

                    Cell currentCell = cellIterator.next();
                    //getCellTypeEnum shown as deprecated for version 3.15
                    //getCellTypeEnum ill be renamed to getCellType starting from version 4.0
                    if (currentCell.getCellTypeEnum() == CellType.STRING) {
                        System.out.print(currentCell.getStringCellValue() + "--");
                    } else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
                        System.out.print(currentCell.getNumericCellValue() + "--");
                    }

                }
                System.out.println();

            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

Dependency:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

Thanks in advance.

Answer 1

Here I am writing logic to read data row by row and check every cell value based on value populate DB Entity object to persist in your oracle DB.

private ProductEntity createAndPopulateEntity(Row recordRow,List<BulkProductDetailResponse.ErrorDetail> processingError) {
    List<String> headerNames = Arrays.asList("Index", "Prouduct", "Time", "Year");
    DataFormatter dataFormatter = new DataFormatter();
    int MANDATORY_FIELDS_INDEX_LIMIT = 3;
    ProductEntity entity = new ProductEntity();
    int cellCount = 0;
    for (Cell cellData : recordRow) {
        String cellValue = dataFormatter.formatCellValue(cellData);
        if (cellCount <= MANDATORY_FIELDS_INDEX_LIMIT) {
            if (StringUtils.isNotEmpty(cellValue)) {
                switch (cellCount) {
                    case 0:
                        if (cellData.getCellType().equals(CellType.STRING)) {
                            entity.setTitle(cellValue);
                            cellCount++;
                        } else {
                            BulkProductDetailResponse.ErrorDetail error = new BulkProductDetailResponse
                                    .ErrorDetail(BulkProductDetailResponse.ValidationsType.INVALID_VALUES.getValue(), cellData.getRowIndex(), "Column:[" + headerNames.get(cellCount) + "] value data type is not expected.");
                            processingError.add(error);
                            cellCount++;
                        }
                        continue;
                    case 1:
                        if (cellData.getCellType().equals(CellType.STRING)) {
                            entity.setProductName(cellValue);
                            cellCount++;
                        } else {
                            BulkProductDetailResponse.ErrorDetail error = new BulkProductDetailResponse
                                    .ErrorDetail(BulkProductDetailResponse.ValidationsType.INVALID_VALUES.getValue(), cellData.getRowIndex(), "Column:[" + headerNames.get(cellCount) + "] value data type is not expected.");
                            processingError.add(error);
                            cellCount++;
                        }
                        continue;
                    case 2:
                        if (cellData.getCellType().equals(CellType.STRING)) {
                            entity.setPeriod(cellValue);
                            cellCount++;
                        } else {
                            BulkProductDetailResponse.ErrorDetail error = new BulkProductDetailResponse
                                    .ErrorDetail(BulkProductDetailResponse.ValidationsType.INVALID_VALUES.getValue(), cellData.getRowIndex(), "Column:[" + headerNames.get(cellCount) + "] value data type is not expected.");
                            processingError.add(error);
                            cellCount++;
                        }
                        continue;
                    case 3:
                        if (cellData.getCellType().equals(CellType.NUMERIC)) {
                            entity.setYear(Integer.parseInt(cellValue));
                            cellCount++;
                        } else {
                            BulkProductDetailResponse.ErrorDetail error = new BulkProductDetailResponse
                                    .ErrorDetail(BulkProductDetailResponse.ValidationsType.INVALID_VALUES.getValue(), cellData.getRowIndex(), "Column:[" + headerNames.get(cellCount) + "] value data type is not expected.");
                            processingError.add(error);
                            cellCount++;
                        }
                        continue;

                }

            } else {
                log.debug("Column value can not be blank for column:[" + headerNames.get(cellCount) + "].");
                BulkProductDetailResponse.ErrorDetail error = new BulkProductDetailResponse
                        .ErrorDetail(BulkProductDetailResponse.ValidationsType.INVALID_VALUES.getValue(), cellData.getRowIndex(), "Column:[" + headerNames.get(cellCount) + "] value can not be blank for \"CPI\", \"Portfolio\", \"Time Period\" and \"Year\".");
                processingError.add(error);
                cellCount++;
                continue;
            }
        } 
    }
    return entity;
}

Answer 2

You will need your connection string, as well as a stored procedure that does the insert (and possibly returns a token or maybe a new unique identifier):

String sp = "{call spInsertRecord(?, ?)}";
try (Connection con = /* you get your connection here*/) {
    if (con != null) {
        try (CallableStatement cStatement = con.prepareCall(sp)) {
            if (currentCell.getCellTypeEnum() == CellType.STRING)
                cStatement.setLong("columnString", currentCell.getStringCellValue());
            else if (currentCell.getCellTypeEnum() == CellType.NUMERIC)
                cStatement.setLong("columnLong", currentCell.getNumericCellValue());
            try (ResultSet rs = cStatement.executeQuery()) {
                if (rs != null) {
                    // do something with your return value if you have any
                    rs.close();
                } else {
                    log.error("Exception in myMethod: [ResultSet is null]");
                }
            }
            cStatement.close();
        }
    } else {
        log.error("Exception in myMethod.getConnection(): [Connection is null]");
    }

}

You can find more on how to create Oracle connection here