Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 211
Manish Bansal Avatar asked Jun 14 '18 04:06

Manish Bansal


People also ask

How read XLS from Excel in Java?

Apache POI Example to read XLSX file in Javajar and replace all HSFF classes with XSSF classes e.g. instead of using HSSFWorkbook, use XSSFWorkbook, instead of using HSFFSheet use XSSFSheet, instead of using HSSFRow use XSSFRow and instead of using HSSFCell just use XSSFCell class.

Can Java read Excel files?

In Java, reading an Excel file is not similar to reading a Word file because of cells in an Excel file. JDK does not provide a direct API to read data from Excel files for which we have to toggle to a third-party library that is Apache POI.

How do I read a XLSX file?

To read an XLSX file in R, first copy the data from Excel, then import data from the clipboard into R. How do I read an XLSX file in Python? You can read the file with the Python module named openpyxl. Download the openpyxl module, then use the Python import command to read the data from the XLSX file.


2 Answers

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.

like image 127
Manish Bansal Avatar answered Sep 16 '22 11:09

Manish Bansal


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

like image 21
Rahul Mahadik Avatar answered Sep 19 '22 11:09

Rahul Mahadik