准备工作
创建数据库
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int NOT NULL,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
`photo` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '照片',
`address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`tel` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电话',
`sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
准备Excel
maven 包
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
实体类
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
@Data
public class Student implements Serializable {
@Excel(name="编号")
private Integer id;
//上传的图片会直接存到 savePath指定的目录下
@Excel(name = "头像", width = 20 , height = 40,type = 2,savePath = "D:\\student")
private String photo;
@Excel(name = "姓名")
private String name;
@Excel(name = "电话",width = 15)
private String tel;
@Excel(name = "性别")
private String sex;
@Excel(name = "地址")
private String address;
@Excel(name = "出生日期",format = "yyyy-MM-dd",width = 15.0)
private Date birthday;
}
本地图片地址映射
@Configuration
public class ResourcesConfig implements WebMvcConfigurer{
/**
* 图片地址映射
*/
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
//获取文件的真实路径
String testStudent = "D:/images/student/";
registry.addResourceHandler("/student/**").addResourceLocations("file:"+testStudent);
}
}
Controller
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.sobuy.supplysingle.common.utils.StringUtils;
import cn.sobuy.supplysingle.common.utils.UUIDUtil;
import cn.sobuy.supplysingle.pojo.Student;
import cn.sobuy.supplysingle.pojo.common.Result;
import cn.sobuy.supplysingle.service.sobuy.StudentService;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@RestController
@RequestMapping("student")
public class StudentController {
@Resource
HttpServletRequest request;
@Resource
HttpServletResponse response;
@Autowired
private StudentService studentService;
public final static String STUDENT_UPLOAD_PATH = "D:\\images\\student\\";
/**
* 导入方法
* @param file
* @return
*/
@PostMapping("upload")
public Result upload(MultipartFile file) {
try {
//params有很多参数可以自己调
ImportParams params = new ImportParams();
List<Student> students = ExcelImportUtil.importExcel(file.getInputStream(), Student.class, params);
//执行完上面这条，excel已经解析完了，图片已经暂存了，我这里做了转存
saveImage(students);
studentService.saveBatch(students);
return Result.success().message("上传成功");
} catch (Exception e) {
e.printStackTrace();
return Result.error().message("上传失败");
}
}
/**
* 导出方法
*/
@GetMapping("export")
public void export() {
try {
List<Student> students = studentService.list();
ExportParams exportParams = new ExportParams();
exportParams.setSheetName("学生信息");
exportParams.setType(ExcelType.HSSF);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,Student.class, students);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户数据表", "UTF-8") + ".xls");
response.setCharacterEncoding("UTF-8");
workbook.write(response.getOutputStream());
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 文件转存
* @param students
*/
private void saveImage(List<Student> students) {
//遍历每一条数据
for (Student student : students) {
if (StringUtils.isNotEmpty(student.getPhoto())) {
try {
//获取到暂存的文件
File tmpFile = new File(student.getPhoto());
FileInputStream fileInputStream = new FileInputStream(tmpFile);
//转换为 multipartFile 类
MultipartFile multipartFile = new MockMultipartFile("file", tmpFile.getName(), "text/plain", IOUtils.toByteArray(fileInputStream));
//获取当前的日期，按日期归档
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd/");
String format = sdf.format(new Date());
//获取到本地磁盘的路径，先建立路径
File file = new File(STUDENT_UPLOAD_PATH + format);
if (!file.isDirectory()) {
file.mkdirs();
}
//初始文件名
String originName = tmpFile.getName();
//后缀名
String suffix = originName.substring(originName.lastIndexOf("."));
//存加密后的uuid+后缀作为存到path里的文件名
String fileName = UUIDUtil.getUUID() + suffix;
File dest = new File(file.getAbsoluteFile() + File.separator + fileName);
String filePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "/student/" + format + fileName;
multipartFile.transferTo(dest);
student.setPhoto(filePath);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
vue前端代码
是个能传文件的方法都可以，页面不细究了，能用就行
我这里直接拿了官网的 element-ui 的上传
<el-upload
action=""
:http-request="uploadFile"
>
<el-button size="small" type="primary">点击上传</el-button>
</el-upload>
<el-button type="success" @click="exportFile" style="margin-top:20px">导出</el-button>
/api 是 vue.config.js里的代理转发，用于识别请求是前端还是后端的, 请求方法也是封装的 axios
//我尝试的时候 导出时候用了 axios 不能触发下载，于是改为如下
exportFile(){
window.location.href="/api/student/export"
},
//上传文件
uploadFile(file) {
let formDatas = new FormData();
formDatas.append("file", file.file);
this.postRequest("/api/student/upload", formDatas, {
"Content-Type": "multipart/form-data",
}).then((res) => {
this.$message.success({message:res.message})
});
},
导入结果
导出结果
因为是一个demo，所以我全放controller里了，不要学哦 （ ：
