CODEWITHSUNDEEP
javaexcelspring-boot
Manish Bansal
Manish Bansal

Reputation: 889

How to read excel file using spring boot

I am making a spring boot application which will take the excel file and store its content and store it in database. I have tried many ways..but not successful. Does anyone have an idea about how to do this. I don't know how to make the controller for importing the excel file. And is there any dependency which I have to include for reading data from excel file

Upvotes: 22

Views: 112861

Answers (4)

Manish Bansal
Manish Bansal

Reputation: 889

Finally found the solution.

Html file for uploading the form is

<form th:action="@{/import}" method="post" enctype="multipart/form-data">
    <input type="file" th:name="file" />
    <input th:type="submit" value="Import" />
</form>

Controller class is

@PostMapping("/import")
public void mapReapExcelDatatoDB(@RequestParam("file") MultipartFile reapExcelDataFile) throws IOException {
    
    List<Test> tempStudentList = new ArrayList<Test>();
    XSSFWorkbook workbook = new XSSFWorkbook(reapExcelDataFile.getInputStream());
    XSSFSheet worksheet = workbook.getSheetAt(0);
    
    for(int i=1;i<worksheet.getPhysicalNumberOfRows() ;i++) {
        Test tempStudent = new Test();
            
        XSSFRow row = worksheet.getRow(i);
            
        tempStudent.setId((int) row.getCell(0).getNumericCellValue());
        tempStudent.setContent(row.getCell(1).getStringCellValue());
        tempStudentList.add(tempStudent);   
    }
}

Make sure to add the dependecy

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12</version>
</dependency>
<!-- excel 2007 over-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12</version>
</dependency>

Now it will work fine.

Upvotes: 45

Shahid Hussain Abbasi
Shahid Hussain Abbasi

Reputation: 2692

Works for me

    <form th:action="@{/import}" method="post" enctype="multipart/form-data">

        <input type="file" th:name="file">

    <input th:type="submit" value="Import" />

    @PostMapping("/import")
    public void mapReapExcelDatatoDB(@RequestParam("file") MultipartFile reapExcelDataFile) throws IOException {

       List<Test> tempStudentList = new ArrayList<Test>();
        XSSFWorkbook workbook = new XSSFWorkbook(reapExcelDataFile.getInputStream());
        XSSFSheet worksheet = workbook.getSheetAt(0);

        for(int i=1;i<worksheet.getPhysicalNumberOfRows() ;i++) {
            Test tempStudent = new Test();

            XSSFRow row = worksheet.getRow(i);

            tempStudent.setId((int) row.getCell(0).getNumericCellValue());
            tempStudent.setContent(row.getCell(1).getStringCellValue());
                tempStudentList.add(tempStudent);   
        }
    }

Upvotes: 1

Vdalk
Vdalk

Reputation: 11

It's also good to add this dependency too: 

<dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>3.1.0</version>
</dependency>

Upvotes: 0

Rahul Mahadik
Rahul Mahadik

Reputation: 12271

Use Apache POI library which is easily available using Maven Dependencies.

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

Code to read file

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

public class ApachePOIExcelRead {

    private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";

    public static void main(String[] args) {

        try {

            FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
            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();
        }

    }
}

Please modify above program as per your requirement. If you know your excel file column index then you can direct row to read cell e.g.row.getCell(0) where row object like XSSFRow row = (XSSFRow) iterator.next();

Hope this will helps you

Reference

Upvotes: 10

Related Questions