业务-EasyExcel多sheet、追加列
背景
最近接到一个导出Excel的业务，需求就是多sheet，每个sheet导出不同结构，第一个sheet里面能够根据最后一列动态的追加列。原本使用的 pig4cloud 架子，使用 @ResponseExcel注解方式组装返回数据即可，但是实现过程中发现并不是所想要的效果。
这样写能够实现多 sheet 导出，但是动态的移除列然后在追加列我尝试了并没有好的方案，有可能也是我没有找到，我找到的是下面面动态的修改列名称。
多 sheet导出，只需要返 List<List> 即可。
@ResponseExcel(name = "不同Sheet的导出", sheet = {"sheet1", "sheet2"})
@PostMapping("/export")
public List<List> 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;
// 忽略
@ExcelIgnore
private String ignore;
}
@Component
public class SimpleDataHeadGenerator implements HeadGenerator {
@Override
public HeadMeta head(Class<?> clazz) {
HeadMeta headMeta = new HeadMeta();
headMeta.setHead(simpleDataHead());
// 排除 number 属性
headMeta.setIgnoreHeadFields(new HashSet<>(Collections.singletonList("number")));
return headMeta;
}
private List<List<String>> simpleDataHead() {
List<List<String>> list = new ArrayList<>();
List<String> head0 = new ArrayList<>();
head0.add("自定义字符串标题" + System.currentTimeMillis());
List<String> 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)
@GetMapping
public List<SimpleData> multi() {
List<SimpleData> 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, Map<Integer, List<? extends Object>> dataMap, String fileName, List<String> sheetNameList, List<String> 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 List<String> labelGroupName;
public LabelGroupNameRowWriteHandler(List<String> labelGroupName) {
this.labelGroupName = labelGroupName;
}
/**
* 字符串转
*/
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean isHead) {
// ONE 13 行, 我的是 13 列 具体根据自己的 Excel 定位
Cell cell = row.getCell(13);
row.removeCell(cell);
Map<String, 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.Entry<String, 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.Entry<String, Cell> stringCellEntry : map.entrySet()) {
stringCellEntry.getValue().setCellValue(stringCellEntry.getKey());
stringCellEntry.getValue().setCellStyle(firstCellStyle);
stringCellEntry.getValue().setCellStyle(firstCellStyle);
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;
}
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
}
}