Issue
I am very new to Springboot. I need to create a rest API which accepts Excel file. I need to use the data from the excel file to perform action. I know how to create API with @RequestParam and @RequestBody, but not sure how to create one for excel file. I am not storing the file in db, so no need of model. I searched online but saw all the resources talking about uploading file through client side. I want to act on the file received in my API.
Solution
Based on your comment, you said that you want to read some values from the Workbook and use it.
Apache POI can be an awesome library for that purpose.
The Apache POI XSSF component can be used for reading .xlsx and the HSSF component can be used for reading .xls (prior to 2007 version of MS Office)
You could receive your file in a multipart/form-data request, have Spring deserialize it to you into a MultipartFile object and then use Apache POI to read the InputStream of the file, create the Workbook and use it.
Add the Apache POI XSSF Maven dependency to your project:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
Example in a Controller:
@RestController
@RequestMapping(path = "/my-controller")
public class MyController {
private final Logger log = LoggerFactory.getLogger(MyController.class);
@PostMapping(consumes = { MediaType.MULTIPART_FORM_DATA_VALUE })
public ResponseEntity<Object> handlePost(@RequestParam(name = "file") MultipartFile file) {
// validate that the file has the .xlsx extension (which doesn't mean much, actually..)
String fileName = file.getOriginalFilename();
if (fileName.substring(fileName.length() - 5, fileName.length()).equals(".xlsx")) {
try (InputStream excelIs = file.getInputStream()) {
// create the Workbook using the InputStream returned by
// MultipartFile#getInputStream()
Workbook wb = WorkbookFactory.create(excelIs);
// get the first sheet of the Workbook
Sheet sheet = wb.getSheetAt(0);
Iterator<Row> rowIt = sheet.rowIterator();
// iterating rows..
while (rowIt.hasNext()) {
Row currentRow = rowIt.next();
// as an example, i'm getting the string value of
// the first cell in the current iteration
String firstCellStringValue = currentRow.getCell(0).getStringCellValue();
log.info("{} is the value of the 1st cell in the {} row", firstCellStringValue, currentRow.getRowNum() + 1); // need to do + 1 cause the index starts at zero..
}
} catch(IOException e) {
// .. nothing to do, you could rethrow a custom exception or
// add throws declaration
throw new CustomException("Failed to process");
}
} else {
throw new CustomException("The file should be a .xlsx");
}
return ResponseEntity.ok(...); // your return
}
}
Answered By - Matheus Cirillo
