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
-
1Use Apache Poi with XSSF part.Guilherme Mussi– Guilherme Mussi2018-06-14 04:58:04 +00:00Commented Jun 14, 2018 at 4:58
-
1Do you want to store the file in the database or its contents in tables?rodridevops– rodridevops2018-06-14 05:02:09 +00:00Commented Jun 14, 2018 at 5:02
-
1i want its content in tables.Manish Bansal– Manish Bansal2018-06-14 06:59:22 +00:00Commented Jun 14, 2018 at 6:59
Add a comment
|
4 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.
1 Comment
surendrapanday
can you paste your pom.xml file here?
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
Comments
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);
}
}
Comments
It's also good to add this dependency too:
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
