How to read excel file with restful in spring boot

  1. Choose either Gradle or Maven and the language you want to use. This guide assumes that you chose Java.
  2. Click Dependencies and select Spring Web.
  3. Click Generate.
  4. Download the resulting ZIP file, which is an archive of a web application that is configured with your choices.

1. Getting Apache POI library

. https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml/5.1.0

2. Program to Read an excel file using Apache POI

The program shows three different ways of iterating over sheets, rows, and columns in the excel file :

import com.example.test.readfileexcel.service.Service;

import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
*
@author Mhosein-abbasi 11/9/21
*/
@RestController
@RequestMapping("/file")
public class Controller {
private final Service service;

public Controller(Service service) {
this.service = service;
}

@PostMapping("/upload")
public String upload(
@RequestParam MultipartFile file,
@RequestParam Integer numberOfSheet)
throws Exception {
return service.upload(file, numberOfSheet);
}
}
package com.example.test.readfileexcel.service;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.Iterator;

/**
*
@author Mhosein-abbasi 11/9/21
*/
@org.springframework.stereotype.Service
public class Service {
public String upload(MultipartFile file, Integer numberOfSheet) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
if (numberOfSheet == null || numberOfSheet < 0) {
numberOfSheet = workbook.getNumberOfSheets();
}
for (int i = 0; i < numberOfSheet; i++) {
// Getting the Sheet at index i
Sheet sheet = workbook.getSheetAt(i);
System.out.println("=> " + sheet.getSheetName());
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
// 1. You can obtain a rowIterator and columnIterator and iterate over them
System.out.println("Iterating over Rows and Columns using Iterator");
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// Now let's iterate over the columns of the current row
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
String cellValue = dataFormatter.formatCellValue(cell);
System.out.print(cellValue + "\t");
}
System.out.println();
}
}
return "OK";
}
}

3. Download postman (send Request)

. https://www.postman.com/downloads/

4. Create new excel(xlsx, xls) file

Since we’re not using any file format specific POI classes, the program will work for both types of file formats .xls and .xlsx.

sample

5. run application and send request by postman

Conclusion

In this article, You learned how to read excel files in spring boot using Apache POI library.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store