例子为考勤记录导出 Controller控制层:
@GetMapping("/attendanceStatistics/export")
public void attendanceStatistics (AttendanceStatisticsScreeningDto dto,HttpServletResponse response){
attendanceRecordService.attendanceStatistics(dto,response);
}
Service实现层:
获取需要导出的数据,因为导出数组查询为分页数组,所以在pagesize用的:Integer.MAX_VALUE
@Override
public void attendanceStatistics(AttendanceStatisticsScreeningDto dto, HttpServletResponse response) {
PageVo<AttendanceStatisticsListVo> listS = this.getAttendanceStatistics(dto,PageDto.of(1,Integer.MAX_VALUE));
List<AttendanceStatisticsListVo> resultList = listS.getList();
LinkedList<AttendanceStatisticsDto> exportList = new LinkedList<>();
for (int i = 0; i < resultList.size(); i++) {
AttendanceStatisticsDto exportVo = new AttendanceStatisticsDto();
exportVo.setSalerUserName(resultList.get(i).getSalerUserName());
exportVo.setExpectedQuantity(resultList.get(i).getExpectedQuantity());
exportVo.setActualQuantity(resultList.get(i).getActualQuantity());
exportVo.setNoClocking(resultList.get(i).getNoClocking());
if (ObjectUtil.isNotEmpty(resultList.get(i).getStatisticsListBos())) {
StringBuffer listBo = new StringBuffer();
resultList.get(i).getStatisticsListBos().forEach(attendanceStatisticsListBo -> {
String time = attendanceStatisticsListBo.getTime().format(DateTimeFormatter.ISO_DATE);
String projectName = attendanceStatisticsListBo.getProjectName();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(time).append(" ").append(projectName);
listBo.append(stringBuffer).append(",");
});
exportVo.setStatisticsListBos(listBo.toString());
}
exportList.add(exportVo);
}
//定义导出文件参数
CommonExportExcelDto exportExcelDto = new CommonExportExcelDto();
exportExcelDto.setResponse(response);
exportExcelDto.setFileName("考勤统计");
exportExcelDto.setSheetName("工作表1");
exportExcelDto.setList(exportList);
exportExcelDto.setClazz(AttendanceStatisticsDto.class);
//4:输出文件
easyExcelUtil.exportExcel(exportExcelDto);
}
导出excel内容:AttendanceStatisticsDto编写(excel标题的名字),其中未打卡明细是数组,在导出中用的StringBuffer做处理
@Data
public class AttendanceStatisticsDto {
@ExcelProperty("销售人员名称")
private String salerUserName;
@ExcelProperty("应该次数")
private Integer expectedQuantity;
@ExcelProperty("实际次数")
private Integer actualQuantity;
@ExcelProperty("未打卡次数")
private Integer noClocking;
@ExcelProperty("未打卡明细")
private String statisticsListBos;
}
excel导出文件名称:CommonExportExcelDto的编写,其中写了文件名称以及excel中sheet名称以及导出的数据集
@Data
public class CommonExportExcelDto implements Serializable {
/**
* response
*/
private HttpServletResponse response;
/**
* 文件名称
*/
private String fileName;
/**
* sheet名称
*/
private String sheetName;
/**
* 数据集
*/
private List<?> list;
/**
* class
*/
private Class<?> clazz;
}
输出文件EasyExcelUtil编写:
@Component
@Slf4j
public class EasyExcelUtil {
/**
* 读取excel
*
* @param file
* @param clazz
* @return
*/
public List readExcel(MultipartFile file, Class clazz) {
List list = new ArrayList<>();
try {
EasyExcel.read(file.getInputStream(), clazz, new PageReadListener<T>(dataList -> {
for (int i = 0; i < dataList.size(); i++) {
list.add(dataList.get(i));
}
})).sheet().headRowNumber(1).doRead();
} catch (Exception e) {
log.error("解析excel失败:" + e);
}
return list;
}
/**
* 导出
*
* @param exportExcelDto
*/
public void exportExcel(CommonExportExcelDto exportExcelDto) {
HttpServletResponse response = exportExcelDto.getResponse();
try {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(exportExcelDto.getFileName() + DateUtil.today() + ".xlsx", "UTF-8"));
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(response.getOutputStream(), exportExcelDto.getClazz())
// 设置头和内容居中
.registerWriteHandler(horizontalCellStyleStrategy)
// 设置内容自适应宽度
.registerWriteHandler(new LongestCellWidthHandler())
// 导出文件名
.autoCloseStream(Boolean.TRUE).sheet(exportExcelDto.getSheetName())
.doWrite(exportExcelDto.getList());
} catch (Exception e) {
e.printStackTrace();
log.error("文件导出失败:" + e);
}
}
}