1、先在maven中引用EasyExcel的jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.5</version>
</dependency>
2、如何向单个sheet导出数据
1、定义导出数据对象,可以定义列名和列宽
public class Export {
//忽略该字段
@ExcelIgnore
private Integer column1;
//定义导出属性
@ExcelProperty(index = 0, value = "列名")
@ColumnWidth(10)
private Integer column2;
}
2、执行导出操作
@GetMapping(value = "/download")
public void download(HttpServletRequest request, HttpServletResponse response) throws IOException {
List<Export> list= new ArrayList<>();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//对中文文件名进行编码
String fileName = URLEncoder.encode("文件名", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), Export.class).sheet("sheet名称").doWrite(list);
}
3、如何向多个sheet导出数据
@GetMapping(value = "/download")
public void download(HttpServletRequest request, HttpServletResponse response) throws IOException {
ExcelWriter excelWriter = null;
List<Export1> list1= new ArrayList<>();
List<Export2> list2= new ArrayList<>();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("文件名", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
excelWriter = EasyExcel.write(response.getOutputStream()).build();
//向第一个sheet写入数据
WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "sheet1名称").head(Export1.class).build();
excelWriter.write(list1, writeSheet1);
//向第二个sheet写入数据
WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "sheet2名称").head(Export2.class).build();
excelWriter.write(list2, writeSheet2);
excelWriter.finish();
}
4、如何导入数据
1、首先要定义AnalysisEventListener的实现,如何将从Excel导入的数据进行处理
public class Import {
@ExcelProperty(index = 0)
private String index1;
@ExcelProperty(index = 1)
private String index2;
}
public class ExcelListener extends AnalysisEventListener<Import> {
private Service service;
private List<Object> list = new ArrayList<>();
public ExcelListener(Service service) {
super();
this.service = service;
}
@Override
public void invoke(Import data, AnalysisContext analysisContext) {
//将Import数据转换成Object对象
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//调用service方法,将list中数据存入数据库
}
}
2、定义SpringMVC代码如何处理上传的Excel文件
@RequestMapping(value = "/import")
@ResponseBody
public Response import(MultipartFile file, HttpServletRequest request, HttpServletResponse response, ModelAndView mav) throws Exception {
//创建自定义AnalysisEventListener实现
AnalysisEventListener listener = new ExcelListener(service);
EasyExcel.read(multipartFileToFile(file), Import.class, listener).sheet().doRead();
return Response.ok();
}
//将MultipartFile转换成File对象
public static File multipartFileToFile(MultipartFile file) throws Exception {
File toFile = null;
if (file.equals("") || file.getSize() <= 0) {
file = null;
} else {
InputStream ins = null;
ins = file.getInputStream();
toFile = new File(file.getOriginalFilename());
inputStreamToFile(ins, toFile);
ins.close();
}
return toFile;
}
//获取流文件
public static void inputStreamToFile(InputStream ins, File file) {
try {
OutputStream os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
ins.close();
} catch (Exception e) {
e.printStackTrace();
}
}