目录
开发准备
导出
常用注解
导出excel到指定位置
导出excel到指定web
导入
将指定位置Excel导入并显示至web
使用ExcelWriter基于模板导出
开发准备
1.导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.1</version>
</dependency>
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
//生成表格时,次字段不生成
@ExcelIgnore
private String id;
//定义表头名称及位置,value表示列名,0表示第一列
@ExcelProperty(value = "姓名",index = 0)
private String name;
@ExcelProperty(value = "年龄",index = 1)
private String age;
@ColumnWidth(20)
@ExcelProperty(value = "生日",index = 2)
private String birthday;
}
数据源
@Component
public class StudentMapper {
public List<Student> getStudents(){
List<Student> studentList = new ArrayList<>();
studentList.add(new Student("1","小明","16","1997-03-02"));
studentList.add(new Student("2","小红","17","1993-03-02"));
studentList.add(new Student("3","小东","18","1994-03-02"));
return studentList;
}
}
导出
常用注解
@ExcelProperty 列名,通过index属性指定位置
@ExcelIgnore 忽略该字段不进行导出
@DateTimeFormat 日期格式转换,String接收excel日期使用
@NumberFormat 数字格式转换 String接收excel数字格式使用
导出excel到指定位置
public static void export2File(String path, String excelName, String sheetName, Class clazz, List data){
String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(fileName,clazz).sheet(sheetName).doWrite(data);
}
参数说明:
export2File(path,"学生表","学生信息", Student.class,studentMapper.getStudents());
path:导出位置 如:"D:\\test11\\"
excelName:导出表格名字
sheetName:第一个sheet名字
clazz:导出数据对应的实体类
List data:导出数据源
导出excel到指定web
public static void export2Web(HttpServletResponse response,String excelName, String sheetName,Class clazz,List data) throws Exception{
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//URLEncoder.encoder防止中文乱码
excelName = URLEncoder.encode(excelName,"utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
}
导入
将指定位置Excel导入并显示至web
public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {
File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));
if (!file.exists()) {
return "文件不存在!";
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
excelName = URLEncoder.encode(excelName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
try (
FileInputStream in = new FileInputStream(file);
ServletOutputStream out = response.getOutputStream();
) {
IOUtils.copy(in, out);
return "导出成功!";
} catch (Exception e) {
log.error("导出文件异常:", e);
}
return "导出失败!";
}
使用ExcelWriter基于模板导出
private void export(List<DataVO> dataList) {
ExcelWriter excelWriter = null;
//获取项目resources/template目录下模板的数据流
InputStream templateInputStream = null;
//构建第一个sheet页的数据,根据模板填充
Map<String, Object> sheetMap = new HashMap<>();
//与模板对应字段-值设置
sheetMap.put("data1", "测试数据1");
sheetMap.put("data2", "测试数据2");
sheetMap.put("data3", "测试数据3");
String filePath = null;
try {
//获取项目resources/template目录下模板的数据流
templateInputStream = new
ClassPathResource("template/data_template.xlsx").getInputStream();
if (templateInputStream.available() == 0) {
log.error("获取模板失败");
}
//设置文件名,filePath导出路径: 暂存java临时目录 最后再删除 "java.io.tmpdir"
String fileName = "测试报告" + System.currentTimeMillis() + ".xlsx";
String path = System.getProperty("java.io.tmpdir") + File.separator;
String filePath = path + fileName;
//ExcelWriter通过POI将值写入Excel
excelWriter = EasyExcel.write(filePath).withTemplate(templateInputStream).build();
// sheet-概览
WriteSheet firstSheet = EasyExcel.writerSheet(0).build();
//用数据去填充模板 取对应的值显示在模板对应的位置
excelWriter.fill(sheetMap, firstSheet);
//如果有第二页sheet 非模板 直接生成excel
WriteSheet secondSheet = EasyExcel.writerSheet(1).head(DataVO.class).build();
excelWriter.write(dataList, secondSheet );
} catch (IOException e) {
log.error("获取模板:examreport 失败。");
e.printStackTrace();
} finally {
//最后记得关闭流
excelWriter.finish();
IOUtils.closeQuietly(templateInputStream);
//删除临时文件
if (null != filePath) {
ExcelUtils.deleteFile(filePath);
}
}
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class DataVO{
@ExcelProperty(value = "姓名")
@ColumnWidth(value = 15)
private String name;
@ExcelProperty(value = "年龄")
@ColumnWidth(value = 15)
private String age;
}
模板文件data_template.xlsx
用{}表示占位,数据根据map键值填充