🎈 1 参考文档
🔍2 个人需求
2.1 数据需求
- 第一部分
- 粉色部分:表头固定,直接使用模板;
- 红色部分:正常数据内容,和一般的Excel导出大同小异;
- 第二部分
- 绿色部分:上面的表头是动态的,并且这些字段没有落表;
- 橙色部分:左边的实测值、总权重得分、总得分相当于固定的表头;
- 黄色部分:这部分是正常数据,但是橙色部分也需要被当作成数据。
2.2 单元格合并需求
- 紫色部分:表头部分,类别相同的进行横向和纵向合并;
- 绿色部分:根据相同类别,将表格数据进行横向合并;
- 橙色部分:相同两行实测值包括实测值所属的数据进行纵向合并;
- 红色部分:因为橙色部分加上绿色部分对应“实测值、实测值、总权重得分、总得分”一共四行,所以红色部分是相同的四行产品数据,需要进行纵向合并。
合并后的样子:
💡 3 解决方案
3.1 数据处理
使用EasyExcel利用模板填充的方式,以一个单元格为最小单位,把数据全部查出来,然后将数据处理成一行一行的形式进行填充,碰到相同的数据,就进行合并单元格。
3.2 数据字段没落表
有一部分表头数据的字段没有落表,在实际数据库中都属于一个字段,例如下图:光学、电学、声学实际上都属于category
,而不是optics
、electricity
、acoustics
。
可以使用map
的进行对数据进行处理和存储,处理后的样子:
3.3 动态表头
一般都是固定表头,然后填充数据,相当于一维的。因为表头是动态的,所以第二部分数据相当于二维的,需要将表头和表格数据分别进行填充。
EasyExcel的填充方式是通过模板进行填充导出的,那我们可以导出两次,第一次用/resources/template
下的模板文件将Excel导出成流,接着以第一次导出的Excel流,作为第二次导出的模板,最后再导出需要的Excel表格。
模板:
第一次导出:
第二次导出:
以这种方法,不仅仅是导出两次,还可以导出多次,以此处理更加复杂的表格。
3.4 合并单元格
参考官方的文章合并单元格和文章EasyExcel-合并单元格-默念x,然后根据需要自定义合并策略Strategy
并且继承于AbstractMergeStrategy
,计算出需要合并单元格数量的列表,然后利用CellRangeAddress
进行单元格合并。
CellRangeAddress cellRangeAddress = new CellRangeAddress(起始行,结尾行,起始列,结尾列);
sheet.addMergedRegionUnsafe(cellRangeAddress);
使用合并策略的方式:
ExcelWriter screenTemplateExcelWriter = EasyExcel
.write(templateOut) // 导出最终临时文件
.withTemplate(templateFileName) // 使用的模板
.registerWriteHandler(new XXXStrategy(需要的参数)) // 自定义单元格合并策略
.build();
🚀4 第一次导出(部分代码)
4.1 Excel 填充模板
-
总体样貌,模板名称为
screenTemplate.xlsx
,工作表名称为sheet0
。 -
拉长单元格,查看具体变量。
4.2 ScreenServiceImpl 业务实现层
@Service
public class ScreenServiceImpl implements ScreenService {
@Override
public void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {
// HttpServletResponse消息头参数设置
String filename = "exportFile.xlsx";
httpServletResponse.addHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + filename);
httpServletResponse.setContentType("application/octet-stream;charset=UTF-8");
httpServletResponse.addHeader("Pragma", "no-cache");
httpServletResponse.addHeader("Cache-Control", "no-cache");
// 通过ClassPathResource获取/resources/template下的模板文件
ClassPathResource classPathResource = new ClassPathResource("template/screenTemplate.xlsx");
// 这里用try-with-resource
try (
// 获取模板文件
InputStream screenParamTemplateFileName = classPathResource.getInputStream();
OutputStream screenOut = httpServletResponse.getOutputStream();
BufferedOutputStream screenBos = new BufferedOutputStream(screenOut);
) {
// --------------------------------基本配置--------------------------------
// 设置内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容水平居中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置内容垂直居中对齐
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 自动换行
contentWriteCellStyle.setWrapped(true);
// 设置字体样式和大小
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteFont.setFontName("微软雅黑");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 配置横向填充
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
// sheet名称
WriteSheet writeSheet = EasyExcel.writerSheet("sheet0").build();
// --------------------------------基本配置--------------------------------
// ---------------------模拟获取第一部分的表格数据、表头参数---------------------
List<ScreenGatherDTO> screenGatherDTOList = new ArrayList<>();
// 构造5个产品数据
for (int i = 1; i <= 5; i++) {
// 每份数据乘以4,为了合并单元格做准备
for (int j = 0; j < 4; j++) {
ScreenGatherDTO screenGatherDTO = new ScreenGatherDTO();
screenGatherDTO.setScreenSize(String.valueOf(i * 10));
screenGatherDTO.setSupplier("厂商" + i);
screenGatherDTO.setPartMode("型号" + i);
screenGatherDTO.setResolution("1080P");
screenGatherDTO.setRefreshRate("60Hz");
screenGatherDTO.setPanel("IPS");
screenGatherDTOList.add(screenGatherDTO);
}
}
if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {
for (int i = 0; i < screenGatherDTOList.size(); i++) {
// 在屏规格末尾加上表格模板参数
screenGatherDTOList.get(i).setValueTemplateParam("{screenValueTemplateParam" + i + ".value}");
}
}
// 填充第一个表头的单元格
ScreenValueExcelDTO screenValueExcelDTO = new ScreenValueExcelDTO();
List<ScreenValueExcelDTO> screenValueExcelDTOList = new ArrayList<>();
screenValueExcelDTO.setValue("产品测试");
screenValueExcelDTOList.add(screenValueExcelDTO);
// 在屏规格末尾加上表头模板参数
List<ScreenValueExcelDTO> screenTableExcelDTOList = new ArrayList<>();
for (int i = 0; i < 4; i++) {
ScreenValueExcelDTO screenTableExcelDTO = new ScreenValueExcelDTO();
switch (i) {
case 0:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.modelName}");
break;
case 1:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemCategory}");
break;
case 2:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemName}");
break;
case 3:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.subTestItemName}");
break;
default:
break;
}
screenTableExcelDTOList.add(screenTableExcelDTO);
}
// ---------------------模拟获取第一部分的表格数据、表头参数---------------------
// --------------------------------第一次导出--------------------------------
ExcelWriter screenTemplateExcelWriter = EasyExcel
.write(screenBos) // 导出临时文件,使用的是BufferedOutputStream
.withTemplate(screenParamTemplateFileName) // 使用的模板
.registerWriteHandler(new ScreenValueMergeStrategy(screenGatherDTOList, 1, 6, 5)) // 自定义单元格合并策略
.registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置内容策略,头部为null
.build();
// 填充屏规格表格数据
screenTemplateExcelWriter.fill(new FillWrapper("screenGatherDTOList", screenGatherDTOList), writeSheet);
// 填充第一个表头的单元格
screenTemplateExcelWriter.fill(new FillWrapper("screenValueExcelDTOList", screenValueExcelDTOList), writeSheet);
// 填充表头模板参数
screenTemplateExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenTableExcelDTOList), writeSheet);
screenTemplateExcelWriter.finish();
// --------------------------------第一次导出--------------------------------
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
4.3 ScreenValueMergeStrategy 自定义合并单元格策略
public class ScreenValueMergeStrategy extends AbstractMergeStrategy {
/**
* 分组,每几行合并一次
*/
private List<Integer> exportFieldGroupCountList;
/**
* 合并的目标开始列索引
*/
private Integer targetBeginColIndex;
/**
* 合并的目标结束列索引
*/
private Integer targetEndColIndex;
/**
* 需要开始合并单元格的首行索引
*/
private Integer firstRowIndex;
public ScreenValueMergeStrategy() {
}
/**
* @param exportDataList 待合并目标行的值
* @param targetBeginColIndex 合并的目标开始列索引
* @param targetEndColIndex 合并的目标结束列索引
* @param firstRowIndex 需要开始合并单元格的首行索引
*/
public ScreenValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {
this.exportFieldGroupCountList = getGroupCountList(exportDataList);
this.targetBeginColIndex = targetBeginColIndex;
this.targetEndColIndex = targetEndColIndex;
this.firstRowIndex = firstRowIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {
int rowCount = this.firstRowIndex;
for (Integer count : exportFieldGroupCountList) {
if (count == 1) {
rowCount += count;
continue;
}
// 合并单元格
CellRangeAddress cellRangeAddress;
for (int i = 0; i < targetEndColIndex - targetBeginColIndex + 1; i++) {
cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount + count - 2, i, i);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
rowCount += count;
}
}
}
/**
* 该方法将目标列根据值是否相同连续可合并,存储可合并的行数
*
* @param exportDataList
* @return
*/
private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < exportDataList.size(); i++) {
boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());
if (equals) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
return groupCountList;
}
}
4.4 拉长单元格并查看导出效果
-
未合并的效果。
-
合并后的效果。
🚀5 第二次导出(完整代码):以第一次导出的excel流,作为第二次导出的模板
5.1 配置文件
5.1.1 pom.xml 依赖
主要用到EasyExcel、Hutool、Lombok。
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.8</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
5.1.2 Excel 模板文件
Excel模板文件路径在:/resources/template/screenTemplate.xlsx
,文件内容同4.1。
5.2 controller
5.2.1 ScreenController
import com.example.demo.service.ScreenService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* @author Cauli
* @date 2022/12/1 9:40
* @description 控制层
*/
@RestController
@RequestMapping("/screen")
public class ScreenController {
@Autowired
private ScreenService screenService;
@GetMapping(value = "/export")
public void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {
screenService.export(httpServletRequest, httpServletResponse);
}
}
⭐5.3 service
5.3.1 ScreenServiceImpl
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.example.demo.dto.ScreenExcelDTO;
import com.example.demo.dto.ScreenGatherDTO;
import com.example.demo.dto.ScreenValueExcelDTO;
import com.example.demo.service.ScreenService;
import com.example.demo.strategy.ScreenScoreHeaderMergeStrategy;
import com.example.demo.strategy.ScreenScoreValueHorizontalMergeStrategy;
import com.example.demo.strategy.ScreenScoreValueMergeStrategy;
import com.example.demo.strategy.ScreenValueMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* @author Cauli|
* @date: 2022/12/1 9:47
* @description: 业务实现层
*/
@Service
public class ScreenServiceImpl implements ScreenService {
@Override
public void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {
// HttpServletResponse消息头参数设置
this.setHttpServletResponse(httpServletResponse);
// 通过ClassPathResource获取/resources/template下的模板文件
ClassPathResource classPathResource = new ClassPathResource("template/screenTemplate.xlsx");
// 需要导出的临时模板文件
InputStream screenTemporaryTemplate = null;
// 这里用try-with-resource
try (
// 获取模板文件
InputStream screenParamTemplateFileName = classPathResource.getInputStream();
OutputStream screenOut = httpServletResponse.getOutputStream();
BufferedOutputStream screenBos = new BufferedOutputStream(screenOut);
ByteArrayOutputStream screenTemplateOut = new ByteArrayOutputStream();
) {
// --------------------------------基本配置--------------------------------
// 设置内容的策略
WriteCellStyle contentWriteCellStyle = this.getWriteCellStyle();
// 配置横向填充
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
// sheet名称,注意要和Excel模板中的工作表名称相同,不然无法导出数据
WriteSheet writeSheet = EasyExcel.writerSheet("sheet0").build();
// --------------------------------基本配置--------------------------------
// ---------------------模拟获取第一部分的表格数据、表头参数---------------------
// 构造第一部分产品数据
List<ScreenGatherDTO> screenGatherDTOList = this.getScreenGatherDTOList();
// 填充第一个表头的单元格
List<ScreenValueExcelDTO> screenValueExcelDTOList = this.getScreenValueExcelDTOList();
// 在屏规格末尾加上表头模板参数
List<ScreenValueExcelDTO> screenTableExcelDTOList = this.getScreenTableExcelDTOList();
// ---------------------模拟获取第一部分的表格数据、表头参数---------------------
// --------------------------------第一次导出--------------------------------
ExcelWriter screenTemplateExcelWriter = EasyExcel
.write(screenBos) // 导出临时文件,使用的是BufferedOutputStream
// .write(screenTemplateOut) // 导出最终临时文件,使用的是ByteArrayOutputStream
.withTemplate(screenParamTemplateFileName) // 使用的模板
.registerWriteHandler(new ScreenValueMergeStrategy(screenGatherDTOList, 1, 6, 5)) // 自定义单元格合并策略
.registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置内容策略,头部为null
.build();
// 填充屏规格表格数据
screenTemplateExcelWriter.fill(new FillWrapper("screenGatherDTOList", screenGatherDTOList), writeSheet);
// 填充第一个表头的单元格
screenTemplateExcelWriter.fill(new FillWrapper("screenValueExcelDTOList", screenValueExcelDTOList), writeSheet);
// 填充表头模板参数
screenTemplateExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenTableExcelDTOList), writeSheet);
screenTemplateExcelWriter.finish();
// excel导出成流
byte[] bytes = screenTemplateOut.toByteArray();
screenTemporaryTemplate = new ByteArrayInputStream(bytes);
// --------------------------------第一次导出--------------------------------
// --------------------------模拟获取第二部分数据--------------------------
// 模拟获取第二部分的表头数据
List<ScreenExcelDTO> screenScoreExcelDTOList = this.getScoreExcelDTOList();
// 模拟获取第二部分的表格数据
Map<String, List<ScreenValueExcelDTO>> screenScoreMap = this.getScreenScoreMap(screenGatherDTOList);
// --------------------------模拟获取第二部分的数据--------------------------
// --------------------------------第二次导出--------------------------------
// 测评模型表格数据开始索引
int screenScoreCostFirstColumnIndex = 7;
// 测评模型表格数据结束索引
int screenScoreTargetEndColIndex = screenScoreCostFirstColumnIndex + screenScoreMap.get("screenValueTemplateParam0").size() - 1;
// 导出最终文件
ExcelWriter screenScoreExcelWriter = EasyExcel
.write(screenBos) // 导出最终文件
.withTemplate(screenTemporaryTemplate) // 以第一次导出的excel流,作为第二次导出的模板
.registerWriteHandler(new ScreenScoreHeaderMergeStrategy(0, screenScoreExcelDTOList, 1, screenScoreCostFirstColumnIndex)) // 表头(模块)合并策略
.registerWriteHandler(new ScreenScoreHeaderMergeStrategy(1, screenScoreExcelDTOList, 2, screenScoreCostFirstColumnIndex)) // 表头合并策略
.registerWriteHandler(new ScreenScoreValueMergeStrategy(screenGatherDTOList, screenScoreCostFirstColumnIndex, screenScoreTargetEndColIndex, 5)) // 表格数据合并策略
.registerWriteHandler(new ScreenScoreValueHorizontalMergeStrategy(screenGatherDTOList, screenScoreExcelDTOList, screenScoreCostFirstColumnIndex)) // 表格数据横向合并策略
.registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置内容策略,头部为null
.build();
// 填充测评模型表头数据
screenScoreExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenScoreExcelDTOList), fillConfig, writeSheet);
// 填充测评模型表格数据
screenScoreMap.forEach((k, v) -> screenScoreExcelWriter.fill(new FillWrapper(k, v), fillConfig, writeSheet));
screenScoreExcelWriter.finish();
// --------------------------------第二次导出--------------------------------
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
// 防止出现异常,导致流关闭失败
if (screenTemporaryTemplate != null) {
try {
screenTemporaryTemplate.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
}
/**
* 模拟获取第二部分的表格数据
*
* @param screenGatherDTOList
* @return
*/
private Map<String, List<ScreenValueExcelDTO>> getScreenScoreMap(List<ScreenGatherDTO> screenGatherDTOList) {
// 模拟这部分数据的字段没有落表,采用Map存储和处理数据
Map<String, List<ScreenValueExcelDTO>> screenScoreMap = new LinkedHashMap<>();
if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {
for (int j = 0; j < screenGatherDTOList.size(); j++) {
Map<String, String> map = new LinkedHashMap<>();
if ((j + 1) % 4 == 0) {
map.put("项目", "总得分");
} else if ((j + 1) % 3 == 0) {
map.put("项目", "总权重得分");
} else {
map.put("项目", "实测值");
}
for (int i = 1; i <= 1; i++) {
map.put("光学测试子项" + i, "1");
}
for (int i = 1; i <= 2; i++) {
map.put("电学测试子项" + i, "2");
}
for (int i = 1; i <= 3; i++) {
map.put("声学测试子项" + i, "3");
}
screenGatherDTOList.get(j).setScoreMap(map);
}
for (int i = 0; i < screenGatherDTOList.size(); i++) {
List<ScreenValueExcelDTO> valueExcelDTOList = new ArrayList<>();
Map<String, String> scoreMap = screenGatherDTOList.get(i).getScoreMap();
if (MapUtils.isNotEmpty(scoreMap)) {
scoreMap.forEach((k, v) -> {
ScreenValueExcelDTO valueExcelDTO = new ScreenValueExcelDTO();
valueExcelDTO.setValue(v);
valueExcelDTOList.add(valueExcelDTO);
});
}
// 填充表格数据
screenScoreMap.put("screenValueTemplateParam" + i, valueExcelDTOList);
}
}
return screenScoreMap;
}
/**
* 模拟获取第二部分的表头数据
*
* @return
*/
private List<ScreenExcelDTO> getScoreExcelDTOList() {
List<ScreenExcelDTO> screenScoreExcelDTOList = new ArrayList<>();
for (int i = 1; i <= 1; i++) {
ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();
screenExcelDTO.setModelName("产品测试");
screenExcelDTO.setTestItemCategory("光学");
screenExcelDTO.setTestItemName("光学");
screenExcelDTO.setSubTestItemName("光学测试子项" + i);
screenScoreExcelDTOList.add(screenExcelDTO);
}
for (int i = 1; i <= 2; i++) {
ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();
screenExcelDTO.setModelName("产品测试");
screenExcelDTO.setTestItemCategory("电学");
screenExcelDTO.setTestItemName("电学");
screenExcelDTO.setSubTestItemName("电学测试子项" + i);
screenScoreExcelDTOList.add(screenExcelDTO);
}
for (int i = 1; i <= 3; i++) {
ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();
screenExcelDTO.setModelName("产品测试");
screenExcelDTO.setTestItemCategory("声学");
screenExcelDTO.setTestItemName("声学");
screenExcelDTO.setSubTestItemName("声学测试子项" + i);
screenScoreExcelDTOList.add(screenExcelDTO);
}
return screenScoreExcelDTOList;
}
/**
* 在屏规格末尾加上表头模板参数
*
* @return
*/
private List<ScreenValueExcelDTO> getScreenTableExcelDTOList() {
List<ScreenValueExcelDTO> screenTableExcelDTOList = new ArrayList<>();
for (int i = 0; i < 4; i++) {
ScreenValueExcelDTO screenTableExcelDTO = new ScreenValueExcelDTO();
switch (i) {
case 0:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.modelName}");
break;
case 1:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemCategory}");
break;
case 2:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemName}");
break;
case 3:
screenTableExcelDTO.setValue("{screenTableExcelDTOList.subTestItemName}");
break;
default:
break;
}
screenTableExcelDTOList.add(screenTableExcelDTO);
}
return screenTableExcelDTOList;
}
/**
* 填充第一个表头的单元格
*
* @return
*/
private List<ScreenValueExcelDTO> getScreenValueExcelDTOList() {
ScreenValueExcelDTO screenValueExcelDTO = new ScreenValueExcelDTO();
List<ScreenValueExcelDTO> screenValueExcelDTOList = new ArrayList<>();
screenValueExcelDTO.setValue("产品测试");
screenValueExcelDTOList.add(screenValueExcelDTO);
return screenValueExcelDTOList;
}
/**
* 构造第一部分产品数据
*
* @return
*/
private List<ScreenGatherDTO> getScreenGatherDTOList() {
List<ScreenGatherDTO> screenGatherDTOList = new ArrayList<>();
// 构造5个产品数据
for (int i = 1; i <= 5; i++) {
// 每份数据乘以4,为了合并单元格做准备
for (int j = 0; j < 4; j++) {
ScreenGatherDTO screenGatherDTO = new ScreenGatherDTO();
screenGatherDTO.setScreenSize(String.valueOf(i * 10));
screenGatherDTO.setSupplier("厂商" + i);
screenGatherDTO.setPartMode("型号" + i);
screenGatherDTO.setResolution("1080P");
screenGatherDTO.setRefreshRate("60Hz");
screenGatherDTO.setPanel("IPS");
screenGatherDTOList.add(screenGatherDTO);
}
}
if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {
for (int i = 0; i < screenGatherDTOList.size(); i++) {
// 在屏规格末尾加上表格模板参数
screenGatherDTOList.get(i).setValueTemplateParam("{screenValueTemplateParam" + i + ".value}");
}
}
return screenGatherDTOList;
}
/**
* 设置内容的策略
*
* @return
*/
private WriteCellStyle getWriteCellStyle() {
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容水平居中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置内容垂直居中对齐
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 自动换行
contentWriteCellStyle.setWrapped(true);
// 设置字体样式和大小
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteFont.setFontName("微软雅黑");
contentWriteCellStyle.setWriteFont(contentWriteFont);
return contentWriteCellStyle;
}
/**
* HttpServletResponse消息头参数设置
*
* @param httpServletResponse
*/
private void setHttpServletResponse(HttpServletResponse httpServletResponse) {
String filename = "exportFile.xlsx";
httpServletResponse.addHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + filename);
httpServletResponse.setContentType("application/octet-stream;charset=UTF-8");
httpServletResponse.addHeader("Pragma", "no-cache");
httpServletResponse.addHeader("Cache-Control", "no-cache");
}
}
5.3.2 ScreenService
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* @author Cauli
* @date 2022/12/1 9:47
* @description 业务层
*/
public interface ScreenService {
/**
* 导出
*
* @param httpServletRequest
* @param httpServletResponse
*/
void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse);
}
⭐5.4 strategy
5.4.1 ScreenValueMergeStrategy
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenGatherDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.List;
/**
* @author yicheng1.he
* @date 2022/12/1 14:06
* @description 第一部分表格数据合并策略
*/
public class ScreenValueMergeStrategy extends AbstractMergeStrategy {
/**
* 分组,每几行合并一次
*/
private List<Integer> exportFieldGroupCountList;
/**
* 合并的目标开始列索引
*/
private Integer targetBeginColIndex;
/**
* 合并的目标结束列索引
*/
private Integer targetEndColIndex;
/**
* 需要开始合并单元格的首行索引
*/
private Integer firstRowIndex;
public ScreenValueMergeStrategy() {
}
/**
* @param exportDataList 待合并目标行的值
* @param targetBeginColIndex 合并的目标开始列索引
* @param targetEndColIndex 合并的目标结束列索引
* @param firstRowIndex 需要开始合并单元格的首行索引
*/
public ScreenValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {
this.exportFieldGroupCountList = getGroupCountList(exportDataList);
this.targetBeginColIndex = targetBeginColIndex;
this.targetEndColIndex = targetEndColIndex;
this.firstRowIndex = firstRowIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {
int rowCount = this.firstRowIndex;
for (Integer count : exportFieldGroupCountList) {
if (count == 1) {
rowCount += count;
continue;
}
// 合并单元格
CellRangeAddress cellRangeAddress;
for (int i = 0; i < targetEndColIndex - targetBeginColIndex + 1; i++) {
cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount + count - 2, i, i);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
rowCount += count;
}
}
}
/**
* 该方法将目标列根据值是否相同连续可合并,存储可合并的行数
*
* @param exportDataList
* @return
*/
private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < exportDataList.size(); i++) {
boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());
if (equals) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
return groupCountList;
}
}
5.4.2 ScreenScoreHeaderMergeStrategy
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenExcelDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.List;
/**
* @author Cauli
* @date 2022/12/1 15:51
* @description 第二部分表头合并策略
*/
public class ScreenScoreHeaderMergeStrategy extends AbstractMergeStrategy {
/**
* 分组,每几列合并一次
*/
private List<Integer> exportFieldGroupCountList;
/**
* 目标合并行index
*/
private Integer targetRowIndex;
/**
* 需要开始合并单元格的首列index
*/
private Integer firstColumnIndex;
/**
* 0:表头 1:分类 2:测试项
*/
private Integer mergeMark;
/**
* 额外队尾数量
*/
private static final Integer TAILS_LENGTH = 2;
/**
* 表头第零行索引
*/
private static final Integer HEADER_ROW_ZERO = 0;
/**
* 表头第一行索引
*/
private static final Integer HEADER_ROW_ONE = 1;
/**
* 表头第二行索引
*/
private static final Integer HEADER_ROW_TWO = 2;
public ScreenScoreHeaderMergeStrategy() {
}
/**
* @param mergeMark 0:表头 1:分类 2:测试项
* @param exportDataList 为待合并目标列的值
* @param targetRowIndex 合并的目标行索引
* @param firstColumnIndex 需要开始合并单元格的首列索引
*/
public ScreenScoreHeaderMergeStrategy(Integer mergeMark, List<ScreenExcelDTO> exportDataList, Integer targetRowIndex, Integer firstColumnIndex) {
this.mergeMark = mergeMark;
this.exportFieldGroupCountList = getGroupCountList(exportDataList);
this.targetRowIndex = targetRowIndex;
this.firstColumnIndex = firstColumnIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (cell.getColumnIndex() == this.firstColumnIndex && cell.getRowIndex() == targetRowIndex - 1) {
int columnCount = this.firstColumnIndex;
for (Integer count : exportFieldGroupCountList) {
// 合并单元格
CellRangeAddress cellRangeAddress = null;
if (mergeMark == 0) {
cellRangeAddress = new CellRangeAddress(targetRowIndex - 1, targetRowIndex - 1, columnCount - 1, columnCount + count - 1);
} else if (mergeMark == 1) {
if (count == 1) {
cellRangeAddress = new CellRangeAddress(HEADER_ROW_ONE, HEADER_ROW_TWO, columnCount, columnCount);
} else {
cellRangeAddress = new CellRangeAddress(HEADER_ROW_ONE, HEADER_ROW_TWO, columnCount, columnCount + count - 1);
}
}
sheet.addMergedRegionUnsafe(cellRangeAddress);
columnCount += count;
}
}
}
/**
* 该方法将目标列根据值是否相同连续可合并,存储可合并的列数
*
* @param exportDataList
* @return
*/
private List<Integer> getGroupCountList(List<ScreenExcelDTO> exportDataList) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < exportDataList.size(); i++) {
boolean equals = false;
if (0 == mergeMark) {
equals = exportDataList.get(i).getModelName().equals(exportDataList.get(i - 1).getModelName());
} else {
equals = exportDataList.get(i).getTestItemName().equals(exportDataList.get(i - 1).getTestItemName());
}
if (equals) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
return groupCountList;
}
}
4.5.3 ScreenScoreValueMergeStrategy
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenGatherDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.List;
/**
* @author yicheng1.he
* @date 2022/12/1 14:06
* @description 第二部分表格数据合并策略
*/
public class ScreenScoreValueMergeStrategy extends AbstractMergeStrategy {
/**
* 分组,每几行合并一次
*/
private List<Integer> exportFieldGroupCountList;
/**
* 合并的目标开始列索引
*/
private Integer targetBeginColIndex;
/**
* 合并的目标结束列索引
*/
private Integer targetEndColIndex;
/**
* 需要开始合并单元格的首行索引
*/
private Integer firstRowIndex;
/**
* 额外的长度为2
*/
private static final Integer ADDITIONAL_LENGTH = 2;
public ScreenScoreValueMergeStrategy() {
}
/**
* @param exportDataList 待合并目标行的值
* @param targetBeginColIndex 合并的目标开始列
* @param targetEndColIndex 合并的目标结束列索引
* @param firstRowIndex 需要开始合并单元格的首行索引
*/
public ScreenScoreValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {
this.exportFieldGroupCountList = getGroupCountList(exportDataList);
this.targetBeginColIndex = targetBeginColIndex;
this.targetEndColIndex = targetEndColIndex;
this.firstRowIndex = firstRowIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {
int rowCount = this.firstRowIndex;
for (Integer count : exportFieldGroupCountList) {
if (count == 1) {
rowCount += count;
continue;
}
// 合并单元格
CellRangeAddress cellRangeAddress;
for (int i = targetBeginColIndex - 1; i <= targetEndColIndex - 1; i++) {
cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount - 1 + count - 1, i, i);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
rowCount += count;
}
}
}
/**
* 该方法将目标列根据值是否相同连续可合并,存储可合并的行数
*
* @param exportDataList
* @return
*/
private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < exportDataList.size(); i++) {
boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());
if (equals) {
count++;
} else {
groupCountList.add(count - ADDITIONAL_LENGTH);
groupCountList.add(1);
groupCountList.add(1);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count - ADDITIONAL_LENGTH);
groupCountList.add(1);
groupCountList.add(1);
return groupCountList;
}
}
5.4.3 ScreenScoreValueHorizontalMergeStrategy
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.example.demo.dto.ScreenExcelDTO;
import com.example.demo.dto.ScreenGatherDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.List;
/**
* @author yicheng1.he
* @date 2022/12/1 15:51
* @description 第二部分表头横向合并策略
*/
public class ScreenScoreValueHorizontalMergeStrategy extends AbstractMergeStrategy {
/**
* 分组,每几行合并一次
*/
private List<Integer> rowFieldGroupCountList;
/**
* 分组,每几列合并一次
*/
private List<Integer> colFieldGroupCountList;
/**
* 目标合并列索引
*/
private Integer targetColIndex;
/**
* 需要合并的行索引
*/
private Integer targetRowIndex;
/**
* 需要开始合并单元格的首列索引
*/
private Integer firstColumnIndex;
/**
* 表格数据第一行索引
*/
private static final Integer FIRST_ROW_INDEX = 5;
/**
* 额外的长度为2
*/
private static final Integer ADDITIONAL_LENGTH = 2;
public ScreenScoreValueHorizontalMergeStrategy() {
}
/**
* @param screenGatherDTOList 为待合并目标行的值
* @param screenScoreExcelDTOList 为待合并目标列的值
* @param firstColumnIndex 需要开始合并单元格的首列索引
*/
public ScreenScoreValueHorizontalMergeStrategy(List<ScreenGatherDTO> screenGatherDTOList, List<ScreenExcelDTO> screenScoreExcelDTOList, Integer firstColumnIndex) {
this.rowFieldGroupCountList = getRowGroupCountList(screenGatherDTOList);
this.colFieldGroupCountList = getColGroupCountList(screenScoreExcelDTOList);
this.firstColumnIndex = firstColumnIndex;
this.targetRowIndex = FIRST_ROW_INDEX;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (cell.getColumnIndex() == this.firstColumnIndex) {
int columnCount = this.firstColumnIndex;
// 列合并
for (int i = 0; i < colFieldGroupCountList.size(); i++) {
Integer count = colFieldGroupCountList.get(i);
if (count == 1) {
columnCount += count;
continue;
}
// 行合并
int rowIndexCount = 0;
for (int j = 0; j < rowFieldGroupCountList.size(); j++) {
if (j == 0) {
rowIndexCount += this.targetRowIndex + rowFieldGroupCountList.get(j);
} else {
rowIndexCount += rowFieldGroupCountList.get(j);
}
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndexCount - ADDITIONAL_LENGTH - 1, rowIndexCount - ADDITIONAL_LENGTH - 1, columnCount, columnCount + count - 1);
sheet.addMergedRegionUnsafe(cellRangeAddress);
cellRangeAddress = new CellRangeAddress(rowIndexCount - ADDITIONAL_LENGTH, rowIndexCount - ADDITIONAL_LENGTH, columnCount, columnCount + count - 1);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
columnCount += count;
}
}
}
/**
* 该方法将目标列根据值是否相同连续可合并,存储可合并的列数
* 防止每个产品行数不同、不一定是4行的情况,该方法计算出每个产品的行数
*
* @param exportDataList
* @return
*/
private List<Integer> getRowGroupCountList(List<ScreenGatherDTO> exportDataList) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < exportDataList.size(); i++) {
boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());
if (equals) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
return groupCountList;
}
/**
* 该方法将目标列根据值是否相同连续可合并,存储可合并的列数
*
* @param exportDataList
* @return
*/
private List<Integer> getColGroupCountList(List<ScreenExcelDTO> exportDataList) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < exportDataList.size(); i++) {
boolean equals = exportDataList.get(i).getTestItemName().equals(exportDataList.get(i - 1).getTestItemName());
if (equals) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
return groupCountList;
}
}
5.5 dto
5.5.1 ScreenExcelDTO
import lombok.Data;
import lombok.ToString;
import java.io.Serializable;
/**
* @author Cauli
* @date 2022/12/1 9:48
* @description excel模板参数返回数据模型
*/
@Data
@ToString
public class ScreenExcelDTO implements Serializable {
/**
* 模块名称
*/
private String modelName;
/**
* 测试项名称
*/
private String testItemName;
/**
* 测试子项名称
*/
private String subTestItemName;
/**
* 分类名称
*/
private String testItemCategory;
/**
* 测试项值
*/
private String testItemValue;
}
5.5.2 ScreenGatherDTO
import lombok.Data;
import lombok.ToString;
import java.util.Map;
/**
* @author Cauli
* @date 2022/12/1 15:10
* @description 查询返回数据模型
*/
@Data
@ToString
public class ScreenGatherDTO {
/**
* 产品型号
*/
private String partMode;
/**
* 厂商
*/
private String supplier;
/**
* 屏幕尺寸
*/
private String screenSize;
/**
* 屏幕分辨率
*/
private String resolution;
/**
* 屏幕刷新率
*/
private String refreshRate;
/**
* 面板属性
*/
private String panel;
/**
* 用于存储得分模块map
*/
private Map<String, String> scoreMap;
/**
* 表头数据模板参数
*/
private String valueTemplateParam;
/**
* 表格数据模板参数
*/
private String headerTemplateParam;
}
5.5.3 ScreenValueExcelDTO
import java.io.Serializable;
/**
* @author Cauli
* @date: 2022/12/1 20:20
* @description: excel值返回数据模型
*/
public class ScreenValueExcelDTO implements Serializable {
private String value;
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
@Override
public String toString() {
return "ScreenValueExcelDTO{" +
"value='" + value + '\'' +
'}';
}
}
5.6 项目目录结构
📋6 最终导出效果
📫7 代码仓库
代码量比较多,建议下载Demo进行查看。