Java 如何使用spring boot读取excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50849800/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read excel file using spring boot
提问by Manish Bansal
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. Is anyone has idea 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
我正在制作一个 Spring Boot 应用程序，它将获取 excel 文件并存储其内容并将其存储在数据库中。我尝试了很多方法..但没有成功。有没有人知道如何做到这一点。我不知道如何制作用于导入excel文件的控制器。是否有任何依赖项，我必须包括从 excel 文件中读取数据
采纳答案by Manish Bansal
Finally found the solution.
终于找到了解决办法。
Html file for uploading the form is
用于上传表单的 HTML 文件是
<form th:action="@{/import}" method="post" enctype="multipart/form-data">
<input type="file" th:name="file">
<input th:type="submit" value="Import" />
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.
现在它会正常工作。
回答by Rahul Mahadik
Use Apache POI library which is easily available using Maven Dependencies.
使用 Apache POI 库，该库可通过 Maven 依赖项轻松获得。
<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 rowobject like XSSFRow row = (XSSFRow) iterator.next();
请根据您的要求修改上述程序。如果您知道您的 excel 文件列索引，那么您可以直接行读取单元格，例如row.getCell(0)where rowobject likeXSSFRow row = (XSSFRow) iterator.next();
Hope this will helps you
希望这会帮助你
回答by Shahid Hussain Abbasi
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);
}
}
回答by Vdalk
It's also good to add this dependencytoo:
添加此依赖项也很好：
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
