一.pom.xml中导入所需要的依赖:
<!--读取excel文件-->
//操作03版的Excel,即以.xls结尾的excel表
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
//操作07版的Excel,即以.xlsx结尾的Excel表
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
二.编写前端代码:
//HTML:
<input id="articleImageFile" name="excelFile" type="file" class="form-control" style="width: 300px; display: inline;" />
<a class="btn btn-warning" id = "import" onclick="importExcel()" shiro:hasPermission="system:student:import">
<i class="fa fa-upload"></i> 导入</a>
//js代码:
<script>
function importExcel() {
var formData = new FormData();
var name = $("#articleImageFile").val();
formData.append("file", $("#articleImageFile")[0].files[0]);
formData.append("name", name);//这个地方可以传递多个参数
$.ajax({
url: '/system/student/importExcel',
type: 'POST',
async: false,
data: formData,
// 告诉jQuery不要去处理发送的数据
processData: false,
// 告诉jQuery不要去设置Content-Type请求头
contentType: false,
beforeSend: function () {
console.log("正在进行,请稍候");
},
success: function (responseStr) {
if (responseStr == "上传成功") {
alert("导入成功");
} else {
alert("导入失败");
}
}
});
}
</script>
三.controller层代码:
@PostMapping("/importExcel")
@ResponseBody
public String importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
System.out.println("file"+file.getSize());
try {
// @RequestParam("file") MultipartFile file 是用来接收前端传递过来的文件
// 1.创建workbook对象,读取整个文档
InputStream inputStream = file.getInputStream();
//POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
// 2.读取页脚sheet
XSSFSheet sheetAt = wb.getSheetAt(0);
// 3.循环读取某一行
int index = 0;
for (Row row : sheetAt) {
// 4.读取每一行的单元格
if (index == 0) {
index++;
continue;
}
//创建一个学生对象
SysStudent student = new SysStudent();
//将Excel表中单元格的值与学生对象的值对应
student.setName(row.getCell(0).getStringCellValue());
//因为学号是数字,Excel默认是数字类型,我的数据库是字符串类型,所以需要设置下类型
row.getCell(1).setCellType(CellType.STRING);
student.setStuId(row.getCell(1).getStringCellValue());
student.setIdentity(row.getCell(2).getStringCellValue());
student.setDescription(row.getCell(3).getStringCellValue());
student.setProvince(row.getCell(4).getStringCellValue());
sysStudentService.insertSysStudent(student);
row.getCell(0).setCellType(CellType.STRING);
String stringCellValue = row.getCell(0).getStringCellValue();
row.getCell(1).setCellType(CellType.STRING);
String stringCellValue2 = row.getCell(1).getStringCellValue();
row.getCell(2).setCellType(CellType.STRING);
String stringCellValue3 = row.getCell(2).getStringCellValue();
row.getCell(3).setCellType(CellType.STRING);
String stringCellValue4 = row.getCell(3).getStringCellValue();
row.getCell(4).setCellType(CellType.STRING);
String stringCellValue5 = row.getCell(4).getStringCellValue();
// 写多少个具体看大家上传的文件有多少列.....
// 测试是否读取到数据,及数据的正确性
System.out.println(stringCellValue);
System.out.println(stringCellValue2);
System.out.println(stringCellValue3);
System.out.println(stringCellValue4);
System.out.println(stringCellValue5);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "上传成功";
}