EasyExcel导出
以员工实体类为例:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee implements Serializable {
/**
* 忽略这个字段
*/
@ExcelIgnore
private Long id;
@ExcelProperty("员工名")
private String name;
@ExcelProperty("员工年龄")
private Integer age;
@ExcelProperty(value = "员工工资",index = 3)
private BigDecimal salary;
@ColumnWidth(15)
@DateTimeFormat("yyyy年MM月dd日")
@ExcelProperty(value = "入职时间",index = 2)
private Date entryTime;
}
相关注解:
@ExcelIgnore:忽略导出的字段信息
@ExcelProperty:设置导出的字段名(表头),导出的字段默认按照顺序从0开始排序,如果想指定导出字段的在第几列的话,可以设置对应的index,index的值:字段所在列数-1,类似数组下标索引
@DateTimeFormat:定义日期格式
@ColumnWidth:设置字段宽度
控制器实现:
@GetMapping("/export")
public void exportEmployeeData(HttpServletResponse response) throws IOException {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("员工信息", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 查询员工信息数据
List<Employee> employeeDataList = getEmployeeDataList();
// 写入Excel
EasyExcel.write(response.getOutputStream(), Employee.class)
.sheet("员工信息")
.doWrite(employeeDataList);
}
代码理解
1.代码:
public class ReflectionOut {
public static void main(String[] args) throws IOException {
List<List<List<String>>> heads = new ArrayList<>();
List<List<List<Object>>> datasList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<List<String>> head = new ArrayList<>();
head.add(Collections.singletonList("表头1-" + (i + 1)));
head.add(Collections.singletonList("表头2-" + (i + 1)));
head.add(Collections.singletonList("表头3-" + (i + 1)));
head.add(Collections.singletonList("表头4-" + (i + 1)));
head.add(Collections.singletonList("表头5-" + (i + 1)));
heads.add(head);
}
for (int i = 0; i < 10; i++) {
List<List<Object>> datas = new ArrayList<>();
for (int d = 0; d < 10; d++) {
List<Object> data = new ArrayList<>();
data.add("数据1-" + (d + 1));
data.add("数据2-" + (d + 1));
data.add("数据3-" + (d + 1));
data.add("数据4-" + (d + 1));
data.add("数据5-" + (d + 1));
data.add("数据6-" + (d + 1));
datas.add(data);
}
datasList.add(datas);
}
FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\03062000419\\Desktop\\输出\\out.xlsx"));
try (ExcelWriter build = EasyExcel.write(fileOutputStream).build()) {
for (int i = 0; i < heads.size(); i++) {
WriteSheet excelListSheet = EasyExcel.writerSheet(i, "sheet"+i).head(heads.get(i)).build();
build.write(datasList.get(i), excelListSheet);
}
}
fileOutputStream.close();
}
}
2.输出形式:
动态导出
1.动态表头导出:
public void dynamicExport(HttpServletResponse response) throws IOException {
// 模拟动态表头数据
List<List<String>> headList = new ArrayList<>();
headList.add(Collections.singletonList("姓名"));
headList.add(Collections.singletonList("年龄"));
headList.add(Collections.singletonList("邮箱"));
// 模拟动态数据
List<List<Object>> dataList = new ArrayList<>();
dataList.add(Arrays.asList("张三", 25, "zhangsan@example.com"));
dataList.add(Arrays.asList("李四", 30, "lisi@example.com"));
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("动态导出", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 写入Excel
EasyExcel.write(response.getOutputStream())
.head(headList)
.sheet("Sheet1")
.doWrite(dataList);
}
2.动态列导出:
public void dynamicColumnExport(HttpServletResponse response, List<String> columns) throws IOException {
// 根据传入的columns动态生成表头
List<List<String>> headList = columns.stream()
.map(Collections::singletonList)
.collect(Collectors.toList());
// 获取对应列的数据
List<List<Object>> dataList = getDataByColumns(columns);
// 导出
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("动态列导出", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream())
.head(headList)
.sheet("Sheet1")
.doWrite(dataList);
}
3.动态合并单元格:
public void dynamicMergeExport(HttpServletResponse response) throws IOException {
// 准备数据
List<DemoData> list = getData();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("合并单元格导出", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 注册自定义合并策略
EasyExcel.write(response.getOutputStream(), DemoData.class)
.registerWriteHandler(new CellWriteHandler() {
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 实现自定义合并逻辑
if (!isHead && cell.getColumnIndex() == 0) {
// 第一列相同内容合并
mergeSameContent(cell);
}
}
})
.sheet("Sheet1")
.doWrite(list);
}