引入
com.alibaba
easyexcel
2.2.6
定义实体类
Person
@Data
public class Person {
@ExcelProperty(value = "a") //导出列标题名
int a;
@ExcelProperty(value = "b")
String b;
}
写一个controller测试
ExcelController.java
@RestController
public class ExcelController {
@GetMapping("/excel")
public void excel(HttpServletResponse response) throws IOException {
setExcelRespProp(response, "会员列表222");//导出的标题名称
ArrayList people = new ArrayList<>();
Person person = new Person();
person.setB("1111");
person.setA(2222);
people.add(person);
EasyExcel.write(response.getOutputStream())
.head(Person.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("会员列表")//第一个sheet的名称
.doWrite(people);
}
private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}
@PostMapping("/import")
public int importMemberList(@RequestPart("file") MultipartFile file) throws IOException {
List memberList = EasyExcel.read(file.getInputStream())
.head(Person.class)
.sheet()
.doReadSync();
System.out.println(memberList);
return 1;
}
}
postman测试 点击send and download即可测试导出
测试导入
cn.afterturn
easypoi-base
4.1.0
cn.afterturn
easypoi-web
4.1.0
cn.afterturn
easypoi-annotation
4.1.0
创建工具类用于设置导出表格的样式
ExcelStyleUtil.java
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 9;
private static final short FONT_SIZE_ELEVEN = 10;
private static final short FONT_SIZE_TWELVE = 10;
private CellStyle headerStyle;
private CellStyle titleStyle;
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
return styles;
}
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
return null;
}
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getbaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
return style;
}
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getbaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getbaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
private CellStyle getbaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
private Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
return font;
}
}
定义实体类
CourseDomain.java 课程实体
StudentDomain.java 学生实体
TeacherDomain.java 老师实体
关系是一门课程一个老师多个学生
CourseDomain.java
@Data
@ExcelTarget("CourseDomain")
public class CourseDomain implements java.io.Serializable{
private String id;
@Excel(name = "课程名称", orderNum = "1", width = 25,needMerge = true)
private String name;
@ExcelEntity(id = "absent")
private TeacherDomain mathTeacher;
@ExcelCollection(name = "学生", orderNum = "4")
private List students;
}
StudentDomain.java
@Data
public class StudentDomain implements java.io.Serializable {
private String id;
@Excel(name = "学生姓名", height = 20, width = 30, isimportField = "true_st")
private String name;
@Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isimportField = "true_st")
private int sex;
@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isimportField = "true_st", width = 20)
private Date birthday;
@Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
private Date registrationDate;
}
TeacherDomain.java
@Data
@ExcelTarget("TeacherDomain")
public class TeacherDomain {
private String id;
@Excel(name = "主讲老师_major,代课老师_absent", orderNum = "1", isimportField = "true_major,true_absent")
private String name;
}
controller 层测试
EasyPoiController.java
@RestController
@RequestMapping("/easypoi")
public class EasyPoiController {
public static void response(HttpServletRequest request, HttpServletResponse response,Workbook workbook){
// 重置响应对象
response.reset();
// 当前日期，用于导出文件名称
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String dateStr = "["+"excelName"+"-"+sdf.format(new Date())+"]";
// 指定下载的文件名--设置响应头 xls 或者xlsx都可以
response.setHeader("Content-Disposition", "attachment;filename=" +dateStr+".xls");
// response.setHeader("Content-Disposition", "attachment;filename=" +dateStr+".xlsx");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
// 写出数据输出流到页面
try {
OutputStream output = response.getOutputStream();
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
workbook.write(bufferedOutPut);
bufferedOutPut.flush();
bufferedOutPut.close();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static List studentList(){
ArrayList list = new ArrayList<>();
StudentDomain studentDomain = new StudentDomain();
StudentDomain studentDomain2 = new StudentDomain();
studentDomain.setId("1");
studentDomain.setName("zhangsan ");
studentDomain.setBirthday(new Date());
studentDomain.setRegistrationDate(new Date());
studentDomain.setSex(1);
studentDomain2.setId("2");
studentDomain2.setName("lisi");
studentDomain2.setBirthday(new Date());
studentDomain2.setRegistrationDate(new Date());
studentDomain2.setSex(1);
list.add(studentDomain);
list.add(studentDomain2);
return list;
}
public static List teacherList(){
ArrayList list = new ArrayList<>();
TeacherDomain teacherDomain = new TeacherDomain();
TeacherDomain teacherDomain2 = new TeacherDomain();
teacherDomain.setId("1001");
teacherDomain.setName("老师1");
teacherDomain2.setId("1002");
teacherDomain2.setName("老师2");
list.add(teacherDomain);
list.add(teacherDomain2);
return list;
}
public static List courseList(){
List stulist = studentList();
List tealist = teacherList();
ArrayList list = new ArrayList<>();
CourseDomain courseDomain = new CourseDomain();
courseDomain.setId("1");
courseDomain.setName("课程1");
courseDomain.setMathTeacher((TeacherDomain)tealist.get(0));
courseDomain.setStudents(stulist);
list.add(courseDomain);
return list;
}
@PostMapping("/exportstudent")
public void easyStudentExport(HttpServletRequest request, HttpServletResponse response){
List list = studentList();
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("计算机一班学生","学生"),
StudentDomain.class, list);
response(request,response,workbook);
}
@PostMapping("/exportStudentCourse")
public void exportStudentCourse(HttpServletRequest request, HttpServletResponse response){
List list = courseList();
ExportParams parm = new ExportParams(null, null, "测试");
parm.setStyle(ExcelStyleUtil.class);//设置样式
//导出
Workbook workbook = ExcelExportUtil.exportExcel(parm,
CourseDomain.class, list);
//io写入
response(request,response,workbook);
}
@PostMapping("/importexcel")
public void importexcel(@RequestParam("file") MultipartFile file){
importParams importParams = new importParams();
// 数据处理
//表格标题行数,默认0
// importParams.setHeadRows(1);
//表头行数,默认1
// importParams.setTitleRows(1);
try {
ExcelimportResult result = ExcelimportUtil.importExcelMore(file.getInputStream(), CourseDomain.class, importParams);
List list = result.getList();
System.out.println(list);
} catch (IOException e) {
e.printStackTrace();
System.out.println("IOException");
} catch (Exception e1) {
e1.printStackTrace();
System.out.println("Exception");
}
}
}
注意
注意点 要么用easypoi 要么用easyexcel 两个包都引用可能会导致版本问题出错 去pom.xml注释掉一个即可
