How to read excel file with restful in spring boot

summery

In this tutorial, I will share with you how to read Excel files programmatically using spring boot.

You know, Excel is the very popular file format created by Microsoft. Although it is not an opened file format, Java applications can still read and write Excel files using the Apache POI — the Java API for Microsoft Documents, because the development team uses reverse-engineering to understand the Excel file format. Hence the name POI stands for Poor Obfuscation Implementation.

original source code : github

To manually initialize the project:

1. Navigate to https://start.spring.io. This service pulls in all the dependencies you need for an application and does most of the setup for you.
2. Choose either Gradle or Maven and the language you want to use. This guide assumes that you chose Java.
3. Click Dependencies and select Spring Web.
4. Click Generate.
5. 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.

Suppose you want to read an Excel file whose content looks like the following screenshot:

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.

Thank you for reading. Until next time!