成都企业网站制作,珠海网站建设维护,设计制作公司,个个大公司网站业务-EasyExcel多sheet、追加列
背景
最近接到一个导出Excel的业务#xff0c;需求就是多sheet#xff0c;每个sheet导出不同结构#xff0c;第一个sheet里面能够根据最后一列动态的追加列#xff0c;追加多少得看运营人员传了多少需求列。原本使用的 pig4cloud 架子需求就是多sheet每个sheet导出不同结构第一个sheet里面能够根据最后一列动态的追加列追加多少得看运营人员传了多少需求列。原本使用的 pig4cloud 架子使用 ResponseExcel注解方式组装返回数据即可但是实现过程中发现并不是所想要的效果。 组件地址https://github.com/pig-mesh/excel-spring-boot-starter 这样写能够实现多 sheet 导出但是动态的移除列然后在追加列我尝试了并没有好的方案有可能也是我没有找到我找到的是下面面动态的修改列名称。
多 sheet导出只需要返 ListList 即可。
ResponseExcel(name 不同Sheet的导出, sheet {sheet1, sheet2})
PostMapping(/export)
public ListList export(RequestBody queryModel model) {model.setSize(-1);return userService.userExcelList(model);
}导出并自定义头信息 Data
public class SimpleData {ExcelProperty(字符串标题)private String string;ExcelProperty(日期标题)private Date date;ExcelProperty(数字标题)private Integer number;// 忽略ExcelIgnoreprivate String ignore;
}自定义头信息生成器 注意需要实现 HeadGenerator 接口且注册为一个 spring bean. Component
public class SimpleDataHeadGenerator implements HeadGenerator {Overridepublic HeadMeta head(Class? clazz) {HeadMeta headMeta new HeadMeta();headMeta.setHead(simpleDataHead());// 排除 number 属性headMeta.setIgnoreHeadFields(new HashSet(Collections.singletonList(number)));return headMeta;}private ListListString simpleDataHead() {ListListString list new ArrayList();ListString head0 new ArrayList();head0.add(自定义字符串标题 System.currentTimeMillis());ListString head1 new ArrayList();head1.add(自定义日期标题 System.currentTimeMillis());list.add(head0);list.add(head1);return list;}
}该头生成器将固定返回 自定义字符串标题 和 自定义日期标题 两列头信息实际使用时可根据业务动态处理方便在一些权限控制时动态修改或者增删列头。 RequestMapping(/head)
RestController
public class ExcelHeadTestController {ResponseExcel(name customHead, headGenerator SimpleDataHeadGenerator.class)GetMappingpublic ListSimpleData multi() {ListSimpleData list new ArrayList();for (int i 0; i 10; i) {SimpleData simpleData new SimpleData();simpleData.setString(str i);simpleData.setNumber(i);simpleData.setDate(new Date());list.add(simpleData);}return list;}
}那就只能放弃使用组件方式自己写 EasyExcel 拦截器。
代码实现
导出工具
exHealthSheetDy 静态方法如下实现了 2 个 sheet 不同结构导出。
/*** 2 sheet 动态追加列** param response 响应* param dataMap dataMap* param fileName Excel名称* param sheetNameList sheet名称* throws Exception Exception*/
public static void exHealthSheetDy(HttpServletResponse response, MapInteger, List? extends Object dataMap, String fileName, ListString sheetNameList, ListString labelGroupName) throws Exception {// 表头样式WriteCellStyle headWriteCellStyle new WriteCellStyle();// 设置表头居中对齐headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 内容样式WriteCellStyle contentWriteCellStyle new WriteCellStyle();// 设置内容剧中对齐contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);HorizontalCellStyleStrategy horizontalCellStyleStrategy new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);ExcelWriter build EasyExcel.write(getOutputStream(fileName, response)).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(horizontalCellStyleStrategy).build();for (String s : sheetNameList) {WriteSheet writeSheet;if (s.equals(风险)) {// 风险writeSheet EasyExcel.writerSheet(s).head(HealthUserOneExcelVo.class).registerWriteHandler(new LabelGroupNameRowWriteHandler(labelGroupName)).build();build.write(dataMap.get(0), writeSheet);} else {// 指标writeSheet EasyExcel.writerSheet(s).head(HealthUserExcelIndexVo.class).build();build.write(dataMap.get(1), writeSheet);}}build.finish();
}private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {fileName URLEncoder.encode(fileName, UTF-8);response.setContentType(application/vnd.ms-excel);response.setCharacterEncoding(utf8);response.setHeader(Content-Disposition, attachment;filename fileName .xlsx);return response.getOutputStream();
}拦截器
业务需求是根据 13 列切割根据传入的要求集合追加列。
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;/*** 行拦截器 将字符串换成多列数据** author William*/
Slf4j
public class LabelGroupNameRowWriteHandler implements RowWriteHandler {/*** 样式与其他列保持一样的样式*/private CellStyle firstCellStyle;/*** 体检标签分组列表*/private ListString labelGroupName;public LabelGroupNameRowWriteHandler(ListString labelGroupName) {this.labelGroupName labelGroupName;}/*** 字符串转*/Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean isHead) {// ONE 13 行, 我的是 13 列 具体根据自己的 Excel 定位Cell cell row.getCell(13);row.removeCell(cell);MapString, Cell map new LinkedHashMap();int cellIndex 0;for (int i 0; i labelGroupName.size(); i) {if (StrUtil.isBlank(labelGroupName.get(i)) || map.containsKey(labelGroupName.get(i))) {continue;}Cell fi row.createCell(cellIndex 13);map.put(labelGroupName.get(i), fi);cellIndex;}if (!isHead) {String stringCellValue cell.getStringCellValue();try {String[] split stringCellValue.split(,);for (Map.EntryString, Cell stringCellEntry : map.entrySet()) {boolean equalsRes false;for (String s : split) {if (stringCellEntry.getKey().equals(s)) {equalsRes true;break;}}if (equalsRes) {stringCellEntry.getValue().setCellValue(有);} else {stringCellEntry.getValue().setCellValue(无);}}} catch (Exception e) {log.error(afterRowDispose Exception:{}, e.getMessage(), e);}} else {Workbook workbook writeSheetHolder.getSheet().getWorkbook();firstCellStyle firstCellStyle(workbook);for (Map.EntryString, Cell stringCellEntry : map.entrySet()) {stringCellEntry.getValue().setCellValue(stringCellEntry.getKey());stringCellEntry.getValue().setCellStyle(firstCellStyle);}}}/*** excel列样式** param workbook Workbook* return CellStyle*/public CellStyle firstCellStyle(Workbook workbook) {CellStyle cellStyle workbook.createCellStyle();// 居中cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 灰色cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);// 文字Font font workbook.createFont();font.setFontHeightInPoints((short) 14);font.setFontName(宋体);font.setBold(Boolean.TRUE);cellStyle.setFont(font);return cellStyle;}Overridepublic void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {}Overridepublic void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {}
}
