数据库还有Entity层
定义连接Excel表格的注释
请转到查看
Excel(首先要把Excel转换为文本类型===》crtl+1)
注意,
导出的日期格式为date默认的格式,(Tue Nov 03 00:00:00 CST 2020)
导入的格式为我们自己规定的格式。(2020-11-02)
两种方式的区别:
Date date=new Date();
System.out.println(date);
SimpleDateFormat a=new SimpleDateFormat("yyyy-MM-dd");
String b=a.format(date);
System.out.println(b);
输出结果是答
Mon Mar 02 12:06:29 CST 2020
2020-03-02
pom中写入依赖
<!--Thymeleaf-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!--文件上传组件-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<!--读取excel文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>
</dependency>
HTML页面
<a class="btn btn-primary btn-sm" v-on:click="dr()"><i class="fa fa-plus"></i> 导入</a>
<input type="file" name="file1" id="file1" style="display:none" v-on:change="doimportExcel()"/>
js页面
//导入
function dr(){
$("#file1").click();
}
function doimportExcel(){
var form = new FormData();
form.append("file1", document.getElementById("file1").files[0]);
$.ajax({
url: baseURL + 'platform/student/excelImport', //后台url
data: form,
cache: false,
async: false,
type: "POST", //类型,POST或者GET
dataType: 'json', //数据返回类型,可以是xml、json等
processData: false,
contentType: false,
success: function (data) { //成功,回调函数
if(data=1){
alert("导入成功");
}else{
alert("导入失败");
}
},
error: function (data) { //失败,回调函数
if(data=2){
alert("导入的文件没有值");
}
}
});
};
//有的电脑解析类型会失败,这里必须自己设定类型
//row.getCell(0).setCellType(CellType.STRING);
//哪里解析不了就写哪里,注意编号
Controller层
//处理文件上传==》导入
@RequestMapping(value = "/excelImport")
public String uploadImg1(@RequestParam("file1") MultipartFile file1, HttpServletRequest request) {
String contentType = file1.getContentType();
String fileName = file1.getOriginalFilename();
// if (file1.isEmpty()) {//1
// System.out.println("文件为空");
// return "文件为空!";
// }
try {
//根据路径获取这个操作excel的实例
HSSFWorkbook wb = new HSSFWorkbook(file1.getInputStream()); //根据页面index 获取sheet页
HSSFSheet sheet = wb.getSheetAt(0);
//如果表格一行没有就为空
if (sheet.getPhysicalNumberOfRows() == 0) {//2
return "文件为空!";
}
//实体类集合
List<PfStudent> pfStudent = new ArrayList<PfStudent>();
HSSFRow row = null;
// cell.setCellType(CellType.STRING);
//循环sesheet页中数据从第二行开始,第一行是标题
System.out.println(sheet.getPhysicalNumberOfRows());
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
//获取每一行数据
//有的电脑解析类型会失败,这里必须自己设定类型
//row.getCell(0).setCellType(CellType.STRING);
//哪里解析不了就写哪里,注意编号
row = sheet.getRow(i);
System.out.println(row.getCell(0).getStringCellValue());
System.out.println(row.getCell(1).getStringCellValue());
System.out.println(row.getCell(2).getStringCellValue());
System.out.println(row.getCell(3).getStringCellValue());
PfStudent data = new PfStudent();
// row.getCell(1).setCellType(CellType.STRING);
// String studentId = row.getCell(0).getStringCellValue().trim();
// data.setStudentId(Integer.parseInt(studentId));
data.setStudentName(row.getCell(1).getStringCellValue());
String studentSex = row.getCell(2).getStringCellValue().trim();
data.setStudentSex(Integer.parseInt(studentSex));
Date time =new Date(row.getCell(3).getStringCellValue());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String timeFormat = sdf.format(time);
if(row.getCell(3).getStringCellValue()!=null){
// data.setStudentBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(row.getCell(3).getStringCellValue()));
data.setStudentBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(timeFormat));
}
data.setStudentClass(Integer.parseInt(row.getCell(4).getStringCellValue()));
System.out.println(data.toString());
pfStudent.add(data);
pfStudentService.insert(data);
}
} catch (Exception e) {
e.printStackTrace();
}
return "1";
}