上海网站注销,网店怎么开新手,江苏建科建筑培训网,seo网站推广方法需求#xff1a;需要在导出 excel时#xff0c;合并单元格自定义头信息(动态生成)#xff0c;然后才是字段列表头即导出数据。 EasyExcel - 使用table去写入#xff1a;https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E4%BD%BF%E7%94%A8table%E…需求需要在导出 excel时合并单元格自定义头信息(动态生成)然后才是字段列表头即导出数据。 EasyExcel - 使用table去写入https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E4%BD%BF%E7%94%A8table%E5%8E%BB%E5%86%99%E5%85%A5 一、代码实现
1、查询导出数据
1.1 导出实体类
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.converters.localdatetime.LocalDateTimeStringConverter;
import lombok.Data;import java.io.Serializable;
import java.time.LocalDateTime;/*** 用户信息 导出DTO*/
Data
ExcelIgnoreUnannotated
public class UserExportExcelDTO implements Serializable {private static final long serialVersionUID -5255117385546252447L;/*** 用户ID*/ExcelIgnoreprivate Long userId;/*** 用户名*/ExcelProperty(value 用户名)private String username;/*** 年龄*/ExcelProperty(value 年龄)private Integer age;/*** 电话号码*/ExcelProperty(value 电话号码)private String phone;/*** 昵称*/ExcelProperty(value 昵称)private String nickname;/*** 姓名*/ExcelProperty(value 姓名)private String name;/*** 创建时间* 使用 converter或者 DateTimeFormat格式化时间如果两者同时使用DateTimeFormat优先级高*/ExcelProperty(value 创建时间, converter LocalDateTimeStringConverter.class) // yyyy-MM-dd HH:mm:ssDateTimeFormat(yyyy年MM月dd日)private LocalDateTime createTime;}
1.2 查询导出数据方法 Overridepublic ListUserExportExcelDTO listExportExcelData(UserPageQueryRequest pageRequest) {pageRequest.setCurrentPage(1L);pageRequest.setPageSize((long) Integer.MAX_VALUE);BasePageResultUserPageDTO basePageResult pageQuery(pageRequest);if(!basePageResult.isSuccess()){return Collections.EMPTY_LIST;}return basePageResult.getPageList().stream().map(userPageDTO - {UserExportExcelDTO exportExcelDTO new UserExportExcelDTO();BeanUtils.copyProperties(userPageDTO, exportExcelDTO);return exportExcelDTO;}).collect(Collectors.toList());}2、EasyExcel使用 table写入 /*** 用户导出接口** param pageRequest 分页查询请求体*/SneakyThrowsApiOperation(value 用户导出接口)GetMapping(/exportExcel)public void exportExcel(HttpServletResponse response, RequestBody UserPageQueryRequest pageRequest) {response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);String fileName URLEncoder.encode(用户信息, StandardCharsets.UTF_8.name()).replaceAll(\\, %20);response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx);// 查询导出数据ListUserExportExcelDTO exportExcelDataList userService.listExportExcelData(pageRequest);// 自定义头信息StringBuilder headName new StringBuilder();LocalDateTime startTime pageRequest.getCreateTimeStart();LocalDateTime endTime pageRequest.getCreateTimeEnd();if (startTime ! null endTime ! null) {String startTimeStr LocalDateTimeUtil.format(startTime, DatePattern.CHINESE_DATE_PATTERN);String endTimeStr LocalDateTimeUtil.format(endTime, DatePattern.CHINESE_DATE_PATTERN);headName.append(startTimeStr).append(至).append(endTimeStr);}headName.append(时间段的用户信息);ListListString headList new ArrayList();ListString head new ArrayList();head.add(headName.toString());headList.add(head);/*** 创建 ExcelWriter* 1.创建写入的工作表不需要表头。命名为sheet1。*/ExcelWriter excelWriter null;try {excelWriter EasyExcelFactory.write(response.getOutputStream()).build();} catch (IOException e) {log.error(导出车辆在线率统计异常e, e);}WriteSheet writeSheet EasyExcel.writerSheet(sheet1).needHead(Boolean.FALSE).build();/*** 2.创建并写入表格数据设置表头为 headList需要表头。* tableNo表格序号从0开始。* OnceAbsoluteMergeStrategy创建一个合并单元格策略。*/WriteTable writeTable1 EasyExcel.writerTable(0).head(headList).registerWriteHandler(new OnceAbsoluteMergeStrategy(0, 2, 0, 5)).needHead(Boolean.TRUE).build();// 写入空数据到 writeSheetexcelWriter.write(new ArrayList(), writeSheet, writeTable1);/*** 3.创建写入表格基于AnysCarOfflineStatisticsVo类定义表头需要表头。* writerTable(3)因为 writeTable1从0开始并合并了 3行所以 writeTable2是从第4个行tableNo 3开始写入。* SimpleColumnWidthStyleStrategy设置列宽策略*/WriteTable writeTable2 EasyExcel.writerTable(3).head(UserExportExcelDTO.class).registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)).relativeHeadRowIndex(2).needHead(Boolean.TRUE).build();// 写入导出数据到 writeSheetexcelWriter.write(exportExcelDataList, writeSheet, writeTable2);excelWriter.finish();}3、导出结果 – 求知若饥虚心若愚。
