先说几个关键的点
pom.xml依赖
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>com.deepoove</groupId>
<artifactId>poi-tl</artifactId>
<version>1.12.2</version>
<exclusions>
<exclusion>
<groupId>poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>poi-ooxml</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
<exclusions>
<exclusion>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
<exclusions>
<exclusion>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
</exclusion>
</exclusions>
</dependency>
横纵动态表格
首先读取你的数据，做好List<Map<String, Object>> list的数据，进行横表格纵表格动态填充
//XWPFTemplate模板启动所需配置LoopRowTableRenderPolicy行表格、LoopColumnTableRenderPolicy列表格
RenderPolicy policy1 = new LoopRowTableRenderPolicy(false);
Map<RenderPolicy, List<String>> polines1 = MapUtil.builder(policy1, Arrays.asList("rankScoreMapList")).map();
RenderPolicy policy2 = new LoopColumnTableRenderPolicy(false);
Map<RenderPolicy, List<String>> polines2 = MapUtil.builder(policy2, Arrays.asList("RL")).map();
List<Map<RenderPolicy, List<String>>> configList = Arrays.asList(polines1, polines2);
//渲染生成Word
String result = processWordTemplate(filename, map, scoreMapList, configList, rankScoreMapList, rankTicketTypeMapList);
return result;
读取配置并渲染数据
@SneakyThrows
public String processWordTemplate(String filename, Map<String, Object> replacements, List<Map<String, Object>> scoreMapList, List<Map<RenderPolicy, List<String>>> configList, List<Map<String, Object>> rankScoreMapList, List<Map<String, Object>> rankTicketTypeMapList) {
byte[] fileBytes = upmsService.getFileBytes(filename);
ByteArrayInputStream bis = new ByteArrayInputStream(fileBytes);
//读取配置
Configure config = Configure.createDefault();
if (ObjectUtil.isNotEmpty(configList)) {
for (Map<RenderPolicy, List<String>> map : configList) {
for (Map.Entry<RenderPolicy, List<String>> entry : map.entrySet()) {
RenderPolicy key = entry.getKey();
List<String> value = entry.getValue();
value.stream().forEach(val -> config.customPolicy(val, key));
}
}
}
//1. 渲染文本和表格数据replacements
XWPFTemplate template = XWPFTemplate.compile(bis, config).render(replacements);
XWPFDocument document = template.getXWPFDocument();
//2. 继续处理 -> 更改图表模板 -> 饼状图、折线图
List<POIXMLDocumentPart> relations = document.getRelations();
//.....详细代码见最下方。
//完成操作后保存文档
ByteArrayOutputStream bos = new ByteArrayOutputStream();
document.write(bos);
byte[] bytes = bos.toByteArray();
PoitlIOUtils.closeQuietlyMulti(template, bos);//关闭流
MultipartFile multipartFile = new MockMultipartFile(filename + ".docx", filename + ".docx", "application/vnd.openxmlformats-officedocument.wordprocessingml.document", bytes);
R r = headersRemoteUpmsService.upload(multipartFile);
if (r != null && r.isOk() && r.getData() != null) {
Map<String, Object> data = (Map<String, Object>) r.getData();
if (DataJudgeUtil.isNotBankMapKey(data, "url")) {
return data.get("url").toString();
}
}
return null;
}
效果图如下：
注意1：横动态图表会自动获取该行的宽度，然后等分到每一栏中，我们需要将变量的长度给扩大，如果需要自定义宽度，请先移步到其他博客。
注意2：如果和我的一样为不标准的表格，需要放到该列的前一列中的任一单元格去，如：得分{{RL}}。放到测评项目单元格是不对的！！
更多可以参考官网：https://deepoove.com/poi-tl/#hack-loop-table
图表：
确保你的图表为这种excel表格，而不是普通的图片
图表填充（其实就是填充对应的excel表格，一行一行的填充数据）饼图为例
List<POIXMLDocumentPart> relations = document.getRelations();
for (POIXMLDocumentPart poixmlDocumentPart : relations) {
if (poixmlDocumentPart instanceof XWPFChart) {
XWPFChart chart = (XWPFChart) poixmlDocumentPart;
XSSFSheet sheet = chart.getWorkbook().getSheetAt(0);
XSSFCell cell = sheet.getRow(0).getCell(1);
if (cell.getStringCellValue().equals("领导班子（A票）")) {
// 过滤掉总分标签
rankTicketTypeMapList = rankTicketTypeMapList.stream().filter(m -> !m.get("itemName").equals("总分")).collect(Collectors.toList());
// 设置各指标得分的单元格
for (int i = 0; i < rankTicketTypeMapList.size(); i++) {
Row dataRow = sheet.getRow(i + 1);
if (dataRow == null) {
dataRow = sheet.createRow(i + 1);
}
//设置标签名
Cell itemNameCell = dataRow.getCell(0);
if (itemNameCell == null) {
itemNameCell = dataRow.createCell(0);
}
itemNameCell.setCellValue(rankTicketTypeMapList.get(i).get("itemName").toString());
itemNameCell.setCellType(CellType.STRING);
// 设置得分单元格
Cell scoreCellA = dataRow.getCell(1);
if (scoreCellA == null) {
scoreCellA = dataRow.createCell(1);
}
scoreCellA.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_A").toString()));
scoreCellA.setCellType(CellType.NUMERIC);
Cell scoreCellB = dataRow.getCell(2);
if (scoreCellB == null) {
scoreCellB = dataRow.createCell(2);
}
scoreCellB.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_B").toString()));
scoreCellB.setCellType(CellType.NUMERIC);
Cell scoreCellC = dataRow.getCell(3);
if (scoreCellC == null) {
scoreCellC = dataRow.createCell(3);
}
scoreCellC.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_C").toString()));
scoreCellC.setCellType(CellType.NUMERIC);
Cell scoreCellALL = dataRow.getCell(4);
if (scoreCellALL == null) {
scoreCellALL = dataRow.createCell(4);
}
scoreCellALL.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_ALL").toString()));
scoreCellALL.setCellType(CellType.NUMERIC);
}
firstRow = 1;
lastRow = rankTicketTypeMapList.size();
firstCol = 1;
lastCol = 4;
// 为什么默认是八行？可能和建表图的时候添加了八行？删除多余的行
for (int i = sheet.getLastRowNum(); i > rankTicketTypeMapList.size() + 1; i--) {
sheet.removeRow(sheet.getRow(i));
}
}
}
填充完数据，需要刷新图表，才会把新数据应用到图表中：
s.replaceData(cat, val);chart.plot(chartData);
XDDFChartData chartData = chart.getChartSeries().get(0);
if (chartData instanceof XDDFPieChartData) {//判断类型为饼图
XDDFChartData.Series s = chartData.getSeries().get(0);
XDDFDataSource cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, 0, 0));
XDDFNumericalDataSource val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
s.replaceData(cat, val);
chart.plot(chartData);
}
折线图与柱状图的组合图需要分别获取并刷新
//应用数据后刷新
XDDFChartData chartData = chart.getChartSeries().get(0);
XDDFChartData chartData0 = chart.getChartSeries().get(0);//XDDFBarChartData柱状系列数据
XDDFChartData chartData1 = chart.getChartSeries().get(1);//XDDFLineChartData折线系列数据
XDDFDataSource cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, 0, 0));
for (int i = 0; i < chartData0.getSeries().size(); i++) {
XDDFChartData.Series s = chartData0.getSeries().get(i);
XDDFNumericalDataSource val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, firstCol + i, firstCol + i));
s.replaceData(cat, val);
}
for (int i = 0; i < chartData1.getSeries().size(); i++) {
XDDFChartData.Series s = chartData1.getSeries().get(i);
XDDFNumericalDataSource val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, firstCol + i + 2, firstCol + i + 2));
s.replaceData(cat, val);
}
chart.plot(chartData0);
chart.plot(chartData1);
效果如图：
此组合图的编辑如下：（根据自己需要的设置，填充数据后也要换成自己的刷新方式）
全部代码如下：
前面一直都在获取数据，主要的代码去见processWordTemplate
折线图、饼状图、折线饼状组合图均见processWordTemplate方法
package com.grp.digitization.hr.common.rest.kpiticketfeedback;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ObjectUtil;
import com.deepoove.poi.XWPFTemplate;
import com.deepoove.poi.config.Configure;
import com.deepoove.poi.plugin.table.LoopColumnTableRenderPolicy;
import com.deepoove.poi.plugin.table.LoopRowTableRenderPolicy;
import com.deepoove.poi.policy.RenderPolicy;
import com.deepoove.poi.util.PoitlIOUtils;
import com.grp.digitization.admin.api.feign.RemoteUpmsService;
import com.grp.digitization.common.core.util.R;
import com.grp.digitization.common.security.annotation.Inner;
import com.grp.digitization.hr.feign.hr.HeadersRemoteUpmsService;
import com.grp.digitization.hr.utils.DataJudgeUtil;
import com.grp.digitization.platform.server.GrpServer;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.util.*;
import java.util.stream.Collectors;
@Slf4j
@RestController()
@RequestMapping("/KpiTicketFeedback")
@Api(value = "KPICadreFeedbackReport", tags = "中层干部考核任务->生成中层干部反馈报告WORD文件")
public class KpiTicketFeedbackController {
@Resource
private RemoteUpmsService upmsService;
@Autowired
private HeadersRemoteUpmsService headersRemoteUpmsService;
// private static final String filename = "tohi-FeedbackReports_Final.docx";
private static final String filename = "tohi-88b5591ba7064d43856e162d93adb9c2.docx";
@SneakyThrows
@RequestMapping("/exportFeedbackWord/{code}")
@Inner(value = false)
@ApiOperation(value = "exportFeedbackWord", notes = "生成中层干部反馈报告")
public R exportFeedbackWord(@PathVariable String code) {
//拿到该评分任务下的每个用户，为每个用户生成一份反馈报告
String personSql = "SELECT obj.USERNAME,obj.NAME,obj.DENAME,su.US_TITLE,mv.ENDTIME,mv.TITLE,mv.YEAR\n" +
"FROM `middlecadresobjflow` obj LEFT JOIN middlecadresreview mv ON obj.MIDDLECADRESREVIEWCODE = mv.MIDDLECADRESREVIEWCODE LEFT JOIN sys_user su ON obj.USERNAME = su.username \n" +
"WHERE obj.MIDDLECADRESREVIEWCODE = '" + code + "'";
List<Map<String, Object>> personMapList = GrpServer.getGrpServer().excuteQueryListMap(personSql);
Map<String, String> resultMap = personMapList.stream()
.collect(Collectors.toMap(
m -> m.get("USERNAME").toString(),
m -> generatePersonWord(code, personMapList, m.get("USERNAME").toString())//调用方法获取生成的文件的URL
));
//写入数据库
for (Map.Entry<String, String> entry : resultMap.entrySet()) {
log.info("生成中层干部反馈报告key：{}，value：{}", entry.getKey(), entry.getValue());
String updateSql = "UPDATE middlecadresobjflow SET FEEDBACK_URL = '" + entry.getValue() + "'" +
" WHERE MIDDLECADRESREVIEWCODE = '" + code + "' AND USERNAME = '" + entry.getKey() + "'";
GrpServer.getGrpServer().excuteUpdate(updateSql);
}
return R.ok(resultMap);
}
@SneakyThrows
private String generatePersonWord(String code, List<Map<String, Object>> personMapList, String username) {
if (StringUtils.isEmpty(username)) {
return "";
}
Map<String, Object> map = new HashMap<>();//声明存放数据的map
Optional<Map<String, Object>> first = personMapList.stream().filter(m -> m.containsKey("USERNAME") && m.get("USERNAME").toString().equals(username)).findFirst();
if (first.isPresent()) {
Map<String, Object> tmp = first.get();
map.put("name", DataJudgeUtil.isNotBankMapKey(tmp, "NAME") ? tmp.get("NAME").toString() : "");
map.put("dept", DataJudgeUtil.isNotBankMapKey(tmp, "DENAME") ? tmp.get("DENAME").toString() : "");
map.put("title", DataJudgeUtil.isNotBankMapKey(tmp, "US_TITLE") ? tmp.get("US_TITLE").toString() : "");
map.put("date", DataJudgeUtil.isNotBankMapKey(tmp, "ENDTIME") ? tmp.get("ENDTIME").toString().split("T")[0] : "");
map.put("theme", DataJudgeUtil.isNotBankMapKey(tmp, "TITLE") ? tmp.get("TITLE").toString() : "");
map.put("year", DataJudgeUtil.isNotBankMapKey(tmp, "YEAR") ? tmp.get("YEAR").toString().split("-")[0] : "");
}
//获取票类型以及数量（有效）
String ticketSql = "SELECT TICKETTYPE,WEIGHT,COUNT(*) AS NUM FROM `middlecadresscore` \n" +
"WHERE MIDDLECADRESREVIEWCODE = '" + code + "' AND TICKETSTATE = '1' GROUP BY TICKETTYPE,WEIGHT";
List<Map<String, Object>> ticketMapList = GrpServer.getGrpServer().excuteQueryListMap(ticketSql);
long effective_SUM = ticketMapList.stream().map(m -> Integer.valueOf(m.get("NUM").toString())).mapToLong(Integer::intValue).sum();
Double wight_SUM = ticketMapList.stream().map(m -> Double.valueOf(m.get("WEIGHT").toString())).mapToDouble(Double::doubleValue).sum();
ticketMapList.forEach(m -> {
int count = Integer.valueOf(m.get("NUM").toString());
double percentage = (count * 100.0) / effective_SUM;
String percentageStr = String.format("%.2f", percentage) + "%";
map.put("EFF_" + m.get("TICKETTYPE").toString(), m.get("NUM").toString());
map.put("PE_" + m.get("TICKETTYPE").toString(), percentageStr);
map.put("WIGHT_" + m.get("TICKETTYPE").toString(), String.format("%.2f", Double.valueOf(m.get("WEIGHT").toString())) + "%");
});
map.put("EFF_SUM", String.valueOf(effective_SUM));
map.put("WIGHT_SUM", String.format("%.2f", wight_SUM) + "%");
//总票数
String all_ticketSql = "SELECT TICKETTYPE,WEIGHT,COUNT(*) AS NUM FROM `middlecadresscore` \n" +
"WHERE MIDDLECADRESREVIEWCODE = '" + code + "' GROUP BY TICKETTYPE,WEIGHT";
List<Map<String, Object>> all_ticketMapList = GrpServer.getGrpServer().excuteQueryListMap(all_ticketSql);
long all_SUM = all_ticketMapList.stream().map(m -> Integer.valueOf(m.get("NUM").toString())).mapToLong(Integer::intValue).sum();
all_ticketMapList.forEach(m -> {
int count = Integer.valueOf(m.get("NUM").toString());
double percentage = (count * 100.0) / all_SUM;
String percentageStr = String.format("%.2f", percentage) + "%";
map.put("ALL_" + m.get("TICKETTYPE").toString(), m.get("NUM").toString());
map.put("PA_" + m.get("TICKETTYPE").toString(), percentageStr);
});
map.put("ALL_SUM", String.valueOf(all_SUM));
// 更改图表模板-> 饼状图
//统计所有人的测评项目、综合得分
String all_scoreSql = "SELECT OBJECTNO,itemCode,itemName,SUM(SCORE*WEIGHT)/100 as synthesisScore FROM \n" +
"(SELECT mcp.itemCode,mcp.itemName,mcp.OBJECTNO,ms.ticketType,ms.WEIGHT,SUM(mcp.SCORE) / COUNT(*) AS SCORE FROM middlecadresscorecp mcp \n" +
"LEFT JOIN middlecadresscore ms ON ms.MIDDLECADRESSCORECODE = mcp.MIDDLECADRESSCORECODE WHERE ms.MIDDLECADRESREVIEWCODE = '" + code + "' AND ms.TICKETSTATE = '1' \n" +
"GROUP BY mcp.itemCode,mcp.itemName,mcp.OBJECTNO, ms.ticketType, ms.WEIGHT) tmp \n" +
"GROUP BY itemCode,itemName,OBJECTNO \n";
List<Map<String, Object>> all_scoreMapList = GrpServer.getGrpServer().excuteQueryListMap(all_scoreSql);
//统计同组人所有人的测评项目的综合得分的最低分、平均分、最高分minScore、maxScore、avgScore
// 先计算统计每个itemCode的信息
Map<String, DoubleSummaryStatistics> statisticsMap = all_scoreMapList.stream()
.collect(Collectors.groupingBy(
m -> m.get("itemCode").toString(),
Collectors.mapping(m -> Double.valueOf(m.get("synthesisScore").toString()), Collectors.summarizingDouble(Double::doubleValue))
));
all_scoreMapList.forEach(m -> {
String itemCode = m.get("itemCode").toString();
DoubleSummaryStatistics stats = statisticsMap.get(itemCode);
if (stats != null) {
m.put("minScore", stats.getMin());
m.put("maxScore", stats.getMax());
m.put("avgScore", stats.getAverage());
}
});
//计算个人的相关数据-折线图
List<Map<String, Object>> scoreMapList = all_scoreMapList.stream().filter(m -> m.containsKey("OBJECTNO") && m.get("OBJECTNO").toString().equals(username)).collect(Collectors.toList());
double score = scoreMapList.stream().map(m -> Double.valueOf(m.get("synthesisScore").toString())).mapToDouble(Double::doubleValue).sum();
double averageScore = scoreMapList.stream().map(m -> Double.valueOf(m.get("synthesisScore").toString())).mapToDouble(Double::doubleValue).average().orElse(0.0);
List<String> itemName_high = scoreMapList.stream().filter(m -> Double.valueOf(m.get("synthesisScore").toString()) >= averageScore).map(m -> m.get("itemName").toString()).collect(Collectors.toList());
List<String> itemName_low = scoreMapList.stream().filter(m -> Double.valueOf(m.get("synthesisScore").toString()) < averageScore).map(m -> m.get("itemName").toString()).collect(Collectors.toList());
map.put("SCORE", String.format("%.2f", score));
map.put("AVG_SCORE", String.format("%.2f", averageScore));
map.put("ITEM_HIGH", itemName_high.isEmpty() ? "无测评项目" : String.join("、", itemName_high));
map.put("ITEM_LOW", itemName_low.isEmpty() ? "无测评项目" : String.join("、", itemName_low));
//为all_scoreMapList每个OBJECTNO添加总分的一行数据
List<String> userList = all_scoreMapList.stream().map(m -> m.get("OBJECTNO").toString()).distinct().collect(Collectors.toList());
long count = userList.size();
userList.forEach(u -> {
double minScoreSum = all_scoreMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("minScore").toString())).mapToDouble(Double::doubleValue).sum();
double avgScoreSum = all_scoreMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("avgScore").toString())).mapToDouble(Double::doubleValue).sum();
double maxScoreSum = all_scoreMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("maxScore").toString())).mapToDouble(Double::doubleValue).sum();
double synthesisScoreSum = all_scoreMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("synthesisScore").toString())).mapToDouble(Double::doubleValue).sum();
HashMap<String, Object> tmpMap = new HashMap<>();
tmpMap.put("OBJECTNO", u);
tmpMap.put("itemCode", 0);//总分编号设置为0
tmpMap.put("itemName", "总分");
tmpMap.put("minScore", minScoreSum);
tmpMap.put("avgScore", avgScoreSum);
tmpMap.put("maxScore", maxScoreSum);
tmpMap.put("synthesisScore", synthesisScoreSum);
all_scoreMapList.add(tmpMap);
});
//个人排名渲染的list
List<Map<String, Object>> rankScoreMapList = all_scoreMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(username)).collect(Collectors.toList());
rankScoreMapList.forEach(m -> {
long rank = all_scoreMapList.stream().filter(m2 -> m2.get("itemCode").equals(m.get("itemCode")) && Double.valueOf(m2.get("synthesisScore").toString()) > Double.valueOf(m.get("synthesisScore").toString())).count() + 1;
m.put("rank", rank + "/" + count);
});
//score格式化为两位小数
rankScoreMapList.forEach(m -> {
m.put("minScore", String.format("%.2f", Double.valueOf(m.get("minScore").toString())));
m.put("avgScore", String.format("%.2f", Double.valueOf(m.get("avgScore").toString())));
m.put("maxScore", String.format("%.2f", Double.valueOf(m.get("maxScore").toString())));
m.put("synthesisScore", String.format("%.2f", Double.valueOf(m.get("synthesisScore").toString())));
});
map.put("rankScoreMapList", rankScoreMapList);
List<String> beaItemName = rankScoreMapList.stream().filter(m -> Math.abs(Double.valueOf(m.get("synthesisScore").toString()) - averageScore) < 0.25).map(m -> m.get("itemName").toString()).collect(Collectors.toList());
map.put("BEA_ITEM_NAME", beaItemName.isEmpty() ? "无测评项目" : String.join("、", beaItemName));
//统计同组人所有人的测评项目的A票score_A，B票score_B，C票score_C，（规则，票类型相同的加和求平均值）以及总分score_ALL（权重wight）
String all_ticketTypeSql = "SELECT tmp.OBJECTNO,tmp.itemCode,tmp.itemName,SUM(CASE tmp.ticketType WHEN 'A' THEN tmp.score ELSE 0 END) AS score_A,SUM(CASE tmp.ticketType WHEN 'B' THEN tmp.score ELSE 0 END) AS score_B,SUM(CASE tmp.ticketType WHEN 'C' THEN tmp.score ELSE 0 END) AS score_C, \n" +
"ROUND(SUM(CASE tmp.ticketType WHEN 'A' THEN tmp.score * tmp.WEIGHT / 100.0 ELSE 0 END) + SUM(CASE tmp.ticketType WHEN 'B' THEN tmp.score * tmp.WEIGHT / 100.0 ELSE 0 END) + SUM(CASE tmp.ticketType WHEN 'C' THEN tmp.score * tmp.WEIGHT / 100.0 ELSE 0 END), 2) AS score_ALL \n" +
"FROM( SELECT mcp.itemCode,mcp.itemName,mcp.OBJECTNO,ms.ticketType,ms.WEIGHT,SUM(mcp.SCORE) / COUNT(*) AS SCORE FROM middlecadresscorecp mcp LEFT JOIN middlecadresscore ms ON ms.MIDDLECADRESSCORECODE = mcp.MIDDLECADRESSCORECODE\n" +
" WHERE ms.MIDDLECADRESREVIEWCODE = '" + code + "' AND ms.TICKETSTATE = '1' GROUP BY mcp.itemCode, mcp.itemName, mcp.OBJECTNO, ms.ticketType, ms.WEIGHT\n" +
") tmp GROUP BY tmp.itemCode, tmp.itemName, tmp.OBJECTNO\n";
List<Map<String, Object>> all_ticketTypeMapList = GrpServer.getGrpServer().excuteQueryListMap(all_ticketTypeSql);
//为all_ticketTypeMapList每个OBJECTNO添加总分的一行数据
userList.forEach(u -> {
double score_A = all_ticketTypeMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("score_A").toString())).mapToDouble(Double::doubleValue).sum();
double score_B = all_ticketTypeMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("score_B").toString())).mapToDouble(Double::doubleValue).sum();
double score_C = all_ticketTypeMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("score_C").toString())).mapToDouble(Double::doubleValue).sum();
double score_ALL = all_ticketTypeMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(u)).map(m -> Double.valueOf(m.get("score_ALL").toString())).mapToDouble(Double::doubleValue).sum();
HashMap<String, Object> tmpMap = new HashMap<>();
tmpMap.put("OBJECTNO", u);
tmpMap.put("itemCode", 0);//总分编号设置为0
tmpMap.put("itemName", "总分");
tmpMap.put("score_A", score_A);
tmpMap.put("score_B", score_B);
tmpMap.put("score_C", score_C);
tmpMap.put("score_ALL", score_ALL);
all_ticketTypeMapList.add(tmpMap);
});
//过滤成个人信息并添加排名rank信息
List<Map<String, Object>> rankTicketTypeMapList = all_ticketTypeMapList.stream().filter(m -> m.get("OBJECTNO").toString().equals(username)).collect(Collectors.toList());
rankTicketTypeMapList.forEach(m -> {
long rank_A = all_ticketTypeMapList.stream().filter(m2 -> m2.get("itemCode").equals(m.get("itemCode")) && Double.valueOf(m2.get("score_A").toString()) > Double.valueOf(m.get("score_A").toString())).count() + 1;
m.put("rank_A", rank_A + "/" + count);
long rank_B = all_ticketTypeMapList.stream().filter(m2 -> m2.get("itemCode").equals(m.get("itemCode")) && Double.valueOf(m2.get("score_B").toString()) > Double.valueOf(m.get("score_B").toString())).count() + 1;
m.put("rank_B", rank_B + "/" + count);
long rank_C = all_ticketTypeMapList.stream().filter(m2 -> m2.get("itemCode").equals(m.get("itemCode")) && Double.valueOf(m2.get("score_C").toString()) > Double.valueOf(m.get("score_C").toString())).count() + 1;
m.put("rank_C", rank_C + "/" + count);
long rank_ALL = all_ticketTypeMapList.stream().filter(m2 -> m2.get("itemCode").equals(m.get("itemCode")) && Double.valueOf(m2.get("score_ALL").toString()) > Double.valueOf(m.get("score_ALL").toString())).count() + 1;
m.put("rank_ALL", rank_ALL + "/" + count);
});
//score格式化为两位小数
rankTicketTypeMapList.forEach(m -> {
m.put("score_A", String.format("%.2f", Double.valueOf(m.get("score_A").toString())));
m.put("score_B", String.format("%.2f", Double.valueOf(m.get("score_B").toString())));
m.put("score_C", String.format("%.2f", Double.valueOf(m.get("score_C").toString())));
m.put("score_ALL", String.format("%.2f", Double.valueOf(m.get("score_ALL").toString())));
});
// map.put("rankTicketTypeMapList",rankTicketTypeMapList);
map.put("RL", rankTicketTypeMapList);
//提取每张票上的建议
String suggestionSql = "SELECT SUGGESTION FROM `middlecadresscore` WHERE MIDDLECADRESREVIEWCODE = '" + code + "' AND TICKETSTATE = '1'";
List<Map<String, Object>> suggestionMapList = GrpServer.getGrpServer().excuteQueryListMap(suggestionSql);
if (suggestionMapList.size() >= 0) {
List<String> suggestionList = suggestionMapList.stream().filter(m -> m != null && m.containsKey("SUGGESTION") && DataJudgeUtil.isNotBankMapKey(m, "SUGGESTION")).map(m -> m.get("SUGGESTION").toString()).collect(Collectors.toList());
map.put("suggestion", String.join("\n", suggestionList));
} else {
map.put("suggestion", "无");
}
//XWPFTemplate模板启动所需配置LoopRowTableRenderPolicy行表格、LoopColumnTableRenderPolicy列表格
RenderPolicy policy1 = new LoopRowTableRenderPolicy(false);
Map<RenderPolicy, List<String>> polines1 = MapUtil.builder(policy1, Arrays.asList("rankScoreMapList")).map();
RenderPolicy policy2 = new LoopColumnTableRenderPolicy(false);
Map<RenderPolicy, List<String>> polines2 = MapUtil.builder(policy2, Arrays.asList("RL")).map();
List<Map<RenderPolicy, List<String>>> configList = Arrays.asList(polines1, polines2);
//渲染生成Word
String result = processWordTemplate(filename, map, scoreMapList, configList, rankScoreMapList, rankTicketTypeMapList);
return result;
}
@SneakyThrows
public String processWordTemplate(String filename, Map<String, Object> replacements, List<Map<String, Object>> scoreMapList, List<Map<RenderPolicy, List<String>>> configList, List<Map<String, Object>> rankScoreMapList, List<Map<String, Object>> rankTicketTypeMapList) {
byte[] fileBytes = upmsService.getFileBytes(filename);
ByteArrayInputStream bis = new ByteArrayInputStream(fileBytes);
//读取配置
Configure config = Configure.createDefault();
if (ObjectUtil.isNotEmpty(configList)) {
for (Map<RenderPolicy, List<String>> map : configList) {
for (Map.Entry<RenderPolicy, List<String>> entry : map.entrySet()) {
RenderPolicy key = entry.getKey();
List<String> value = entry.getValue();
value.stream().forEach(val -> config.customPolicy(val, key));
}
}
}
//1. 渲染文本和表格数据replacements
XWPFTemplate template = XWPFTemplate.compile(bis, config).render(replacements);
XWPFDocument document = template.getXWPFDocument();
//2. 继续处理 -> 更改图表模板 -> 饼状图、折线图
List<POIXMLDocumentPart> relations = document.getRelations();
for (POIXMLDocumentPart poixmlDocumentPart : relations) {
if (poixmlDocumentPart instanceof XWPFChart) {
int firstRow = 0;
int lastRow = 0;
int firstCol = 0;
int lastCol = 0;
XWPFChart chart = (XWPFChart) poixmlDocumentPart;
XSSFSheet sheet = chart.getWorkbook().getSheetAt(0);
XSSFCell cell = sheet.getRow(0).getCell(1);
//饼图
if (cell.getStringCellValue().equals("主体人数")) {
sheet.getRow(1).getCell(1).setCellValue(percentageStrToDouble(replacements.get("PE_A").toString()));
sheet.getRow(2).getCell(1).setCellValue(percentageStrToDouble(replacements.get("PE_B").toString()));
sheet.getRow(3).getCell(1).setCellValue(percentageStrToDouble(replacements.get("PE_C").toString()));
sheet.getRow(1).getCell(1).setCellType(CellType.NUMERIC);
sheet.getRow(2).getCell(1).setCellType(CellType.NUMERIC);
sheet.getRow(3).getCell(1).setCellType(CellType.NUMERIC);
firstRow = 1;
lastRow = 3;
firstCol = 1;
lastCol = 1;
}
//饼图
if (cell.getStringCellValue().equals("主体权重")) {
sheet.getRow(1).getCell(1).setCellValue(percentageStrToDouble(replacements.get("WIGHT_A").toString()));
sheet.getRow(2).getCell(1).setCellValue(percentageStrToDouble(replacements.get("WIGHT_B").toString()));
sheet.getRow(3).getCell(1).setCellValue(percentageStrToDouble(replacements.get("WIGHT_C").toString()));
sheet.getRow(1).getCell(1).setCellType(CellType.NUMERIC);
sheet.getRow(2).getCell(1).setCellType(CellType.NUMERIC);
sheet.getRow(3).getCell(1).setCellType(CellType.NUMERIC);
firstRow = 1;
lastRow = 3;
firstCol = 1;
lastCol = 1;
}
//折线图
if (cell.getStringCellValue().equals("各指标得分")) {
double averageScore = scoreMapList.stream().map(m -> Double.valueOf(m.get("synthesisScore").toString())).mapToDouble(Double::doubleValue).average().orElse(0.0);
String formattedAverageScore = String.format("%.2f", averageScore);
sheet.getRow(0).getCell(2).setCellValue("各指标平均分 " + formattedAverageScore);//TODO 设置标题后怎么刷新？？
// 设置各指标得分的单元格
for (int i = 0; i < scoreMapList.size(); i++) {
Row dataRow = sheet.getRow(i + 1);
if (dataRow == null) {
dataRow = sheet.createRow(i + 1);
}
//设置标签名
Cell itemNameCell = dataRow.getCell(0);
if (itemNameCell == null) {
itemNameCell = dataRow.createCell(0);
}
itemNameCell.setCellValue(scoreMapList.get(i).get("itemName").toString());
itemNameCell.setCellType(CellType.STRING);
// 设置得分单元格
Cell scoreCell = dataRow.getCell(1);
if (scoreCell == null) {
scoreCell = dataRow.createCell(1);
}
scoreCell.setCellValue(Double.valueOf(scoreMapList.get(i).get("synthesisScore").toString()));
scoreCell.setCellType(CellType.NUMERIC);
// 设置平均分单元格
Cell avgCell = dataRow.getCell(2);
if (avgCell == null) {
avgCell = dataRow.createCell(2);
}
avgCell.setCellValue(Double.valueOf(formattedAverageScore));
avgCell.setCellType(CellType.NUMERIC);
}
firstRow = 1;
lastRow = scoreMapList.size();
firstCol = 1;
lastCol = 2;
// 为什么默认是八行？可能和建表图的时候添加了八行？删除多余的行
for (int i = sheet.getLastRowNum(); i > scoreMapList.size() + 1; i--) {
sheet.removeRow(sheet.getRow(i));
}
}
//折线图与柱状图的组合图
if (cell.getStringCellValue().equals("同组人员得分范围下限")) {
// 过滤掉总分标签
rankScoreMapList = rankScoreMapList.stream().filter(m -> !m.get("itemName").equals("总分")).collect(Collectors.toList());
// 设置各指标得分的单元格
for (int i = 0; i < rankScoreMapList.size(); i++) {
Row dataRow = sheet.getRow(i + 1);
if (dataRow == null) {
dataRow = sheet.createRow(i + 1);
}
//设置标签名
Cell itemNameCell = dataRow.getCell(0);
if (itemNameCell == null) {
itemNameCell = dataRow.createCell(0);
}
itemNameCell.setCellValue(rankScoreMapList.get(i).get("itemName").toString());
itemNameCell.setCellType(CellType.STRING);
// 设置同组人员得分下限
Cell scoreLowCell = dataRow.getCell(1);
if (scoreLowCell == null) {
scoreLowCell = dataRow.createCell(1);
}
scoreLowCell.setCellValue(Double.valueOf(rankScoreMapList.get(i).get("minScore").toString()));
scoreLowCell.setCellType(CellType.NUMERIC);
// 设置同组人员得分范围
Cell scoreRangeCell = dataRow.getCell(2);
if (scoreRangeCell == null) {
scoreRangeCell = dataRow.createCell(2);
}
scoreRangeCell.setCellValue(Double.valueOf(rankScoreMapList.get(i).get("maxScore").toString()) - Double.valueOf(rankScoreMapList.get(i).get("minScore").toString()));
scoreRangeCell.setCellType(CellType.NUMERIC);
// 设置平均分单元格
Cell avgCell = dataRow.getCell(3);
if (avgCell == null) {
avgCell = dataRow.createCell(3);
}
avgCell.setCellValue(Double.valueOf(rankScoreMapList.get(i).get("avgScore").toString()));
avgCell.setCellType(CellType.NUMERIC);
// 设置平均分单元格
Cell synthesisScoreCell = dataRow.getCell(4);
if (synthesisScoreCell == null) {
synthesisScoreCell = dataRow.createCell(4);
}
synthesisScoreCell.setCellValue(Double.valueOf(rankScoreMapList.get(i).get("synthesisScore").toString()));
synthesisScoreCell.setCellType(CellType.NUMERIC);
}
firstRow = 1;
lastRow = rankScoreMapList.size();
firstCol = 1;
lastCol = 4;
//删除多余的行
for (int i = sheet.getLastRowNum(); i > rankScoreMapList.size() + 1; i--) {
sheet.removeRow(sheet.getRow(i));
}
}
//折线图
if (cell.getStringCellValue().equals("领导班子（A票）")) {
// 过滤掉总分标签
rankTicketTypeMapList = rankTicketTypeMapList.stream().filter(m -> !m.get("itemName").equals("总分")).collect(Collectors.toList());
// 设置各指标得分的单元格
for (int i = 0; i < rankTicketTypeMapList.size(); i++) {
Row dataRow = sheet.getRow(i + 1);
if (dataRow == null) {
dataRow = sheet.createRow(i + 1);
}
//设置标签名
Cell itemNameCell = dataRow.getCell(0);
if (itemNameCell == null) {
itemNameCell = dataRow.createCell(0);
}
itemNameCell.setCellValue(rankTicketTypeMapList.get(i).get("itemName").toString());
itemNameCell.setCellType(CellType.STRING);
// 设置得分单元格
Cell scoreCellA = dataRow.getCell(1);
if (scoreCellA == null) {
scoreCellA = dataRow.createCell(1);
}
scoreCellA.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_A").toString()));
scoreCellA.setCellType(CellType.NUMERIC);
Cell scoreCellB = dataRow.getCell(2);
if (scoreCellB == null) {
scoreCellB = dataRow.createCell(2);
}
scoreCellB.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_B").toString()));
scoreCellB.setCellType(CellType.NUMERIC);
Cell scoreCellC = dataRow.getCell(3);
if (scoreCellC == null) {
scoreCellC = dataRow.createCell(3);
}
scoreCellC.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_C").toString()));
scoreCellC.setCellType(CellType.NUMERIC);
Cell scoreCellALL = dataRow.getCell(4);
if (scoreCellALL == null) {
scoreCellALL = dataRow.createCell(4);
}
scoreCellALL.setCellValue(Double.valueOf(rankTicketTypeMapList.get(i).get("score_ALL").toString()));
scoreCellALL.setCellType(CellType.NUMERIC);
}
firstRow = 1;
lastRow = rankTicketTypeMapList.size();
firstCol = 1;
lastCol = 4;
// 为什么默认是八行？可能和建表图的时候添加了八行？删除多余的行
for (int i = sheet.getLastRowNum(); i > rankTicketTypeMapList.size() + 1; i--) {
sheet.removeRow(sheet.getRow(i));
}
}
//应用数据后刷新
XDDFChartData chartData = chart.getChartSeries().get(0);
if (chartData instanceof XDDFPieChartData) {//饼图
XDDFChartData.Series s = chartData.getSeries().get(0);
XDDFDataSource cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, 0, 0));
XDDFNumericalDataSource val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
s.replaceData(cat, val);
chart.plot(chartData);
} else if (chartData instanceof XDDFLineChartData) {//折线图
XDDFDataSource cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, 0, 0));
for (int i = 0; i < chartData.getSeries().size(); i++) {
XDDFChartData.Series s = chartData.getSeries().get(i);
XDDFNumericalDataSource val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, firstCol + i, firstCol + i));
s.replaceData(cat, val);
}
chart.plot(chartData);
} else {//折线图与柱状图的组合图
XDDFChartData chartData0 = chart.getChartSeries().get(0);//XDDFBarChartData柱状系列数据
XDDFChartData chartData1 = chart.getChartSeries().get(1);//XDDFLineChartData折线系列数据
XDDFDataSource cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, 0, 0));
for (int i = 0; i < chartData0.getSeries().size(); i++) {
XDDFChartData.Series s = chartData0.getSeries().get(i);
XDDFNumericalDataSource val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, firstCol + i, firstCol + i));
s.replaceData(cat, val);
}
for (int i = 0; i < chartData1.getSeries().size(); i++) {
XDDFChartData.Series s = chartData1.getSeries().get(i);
XDDFNumericalDataSource val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(firstRow, lastRow, firstCol + i + 2, firstCol + i + 2));
s.replaceData(cat, val);
}
chart.plot(chartData0);
chart.plot(chartData1);
}
}
}
//完成操作后保存文档
ByteArrayOutputStream bos = new ByteArrayOutputStream();
document.write(bos);
byte[] bytes = bos.toByteArray();
PoitlIOUtils.closeQuietlyMulti(template, bos);//关闭流
MultipartFile multipartFile = new MockMultipartFile(filename + ".docx", filename + ".docx", "application/vnd.openxmlformats-officedocument.wordprocessingml.document", bytes);
R r = headersRemoteUpmsService.upload(multipartFile);
if (r != null && r.isOk() && r.getData() != null) {
Map<String, Object> data = (Map<String, Object>) r.getData();
if (DataJudgeUtil.isNotBankMapKey(data, "url")) {
return data.get("url").toString();
}
}
return null;
}
public Double percentageStrToDouble(String percentageStr) {
String cleanedStr = percentageStr.replace("%", "");
double value = Double.parseDouble(cleanedStr);
double result = value / 100;
return result;
}
}
扫一扫
8713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
