How to read an excel file in spring boot

Question

i am new to springboot and i have moderate experience in java, i was given a task to import an excel data in batches, i followed tutorials online but i am not getting any data when i run it out on postman. i don't know if i will have to create a service for the excel data. Below is my code

Data Controller

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel .*;
import java.io.File;
import java.io.IOException;
import java.util.Iterator;

import java.io.*;
import java.util.*;


@RestController
@RequestMapping("datafile")
public class DataController {

  @RequestMapping(value = "getdata", method = RequestMethod.GET)
    public void createBus() throws IOException {


         final String SAMPLE_XLSX_FILE_PATH = "C:\\project\\transita\\src\\main\\resources\\tran.xlsx";


        // Creating a Workbook from an Excel file (.xls or .xlsx)
        Workbook workbook;

        {
            try {
                workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));



            // Retrieving the number of sheets in the Workbook
            System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");

            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            System.out.println("Retrieving Sheets using Iterator");
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                System.out.println("=> " + sheet.getSheetName());

                sheet = workbook.getSheetAt(0);

                // 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("\n\nIterating over Rows and Columns using Iterator\n");
                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();
                }

                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

}

There is an [article](https://stackoverflow.com/questions/1516144/how-to-read-and-write-excel-file) about this which might be handy — Serguey Shinder, Jan 14 '21 at 09:04

score 0 · Answer 1 · answered Jan 14 '21 at 06:01

0

The code seems to be fine. Have you checked the versions of poi and poi-ooxml jars?

Also, can you add the output you are getting from your print statements? That would help debug the issue.

answered Jan 14 '21 at 06:01

VinayakaHegde

26
3

i got nothing when ran on postman..the body was empty – mohlehpa Jan 14 '21 at 07:09

Vinayak Mittal · Answer 2 · 2021-01-14T11:48:23.023

Please return the object from method createBus() to get the output in postman.

For now, you are not getting any output because your return type of createBus() controller is void so it does not return anything to the postman.

You have to change the return type of controller from void to Object of your type that you want to return to a postman.

@RequestMapping(value = "getdata", method = RequestMethod.GET)
    public Object createBus() throws IOException {

      return "returnToPostman";
    }

score -1 · Answer 3 · answered Jan 14 '21 at 05:58

    add these maven dependencies
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

controller class:

public void mapReapExcelDatatoDB(@RequestParam("file") MultipartFile 
 dataFile) throws IOException{
XSSFWorkbook workbook = new XSSFWorkbook(dataFile.getInputStream());
    XSSFSheet worksheet = workbook.getSheetAt(0);

 for(int i=1;i<worksheet.getPhysicalNumberOfRows() ;i++) {
       XSSFRow row = worksheet.getRow(i);
       int cellValue=(int) row.getCell(0).getNumericCellValue();
       String value=row.getCell(1).getStringCellValue();
}

}

Why are you suggesting that they add such old versions? [3.17 is almost 5 years old!](http://poi.apache.org/devel/history/changes-3x.html#3.17). Why not the latest stable version? — Gagravarr, Jan 14 '21 at 09:42

How to read an excel file in spring boot

3 Answers3