package com.smartor.common;
|
|
import com.ruoyi.common.utils.poi.ExcelUtil;
|
import com.smartor.domain.ServiceSubtaskDetailRatioExport;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
|
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
|
import org.apache.poi.xddf.usermodel.chart.BarDirection;
|
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
|
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
|
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
|
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
|
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
|
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
|
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
|
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
|
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
|
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
|
import org.apache.poi.xssf.usermodel.XSSFChart;
|
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
|
import org.apache.poi.xssf.usermodel.XSSFDrawing;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.LinkedHashSet;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 导出「问题详情占比图表」Sheet 的构建器
|
* <p>
|
* 按任务名称分组：每个任务写一块数据（行=题目，列=选项，值=数量），并在数据右侧画一张柱状图。
|
*
|
* @author smartor
|
*/
|
public class MydOptionRatioChartBuilder implements ExcelUtil.ChartSheetBuilder {
|
|
/**
|
* 图表 Sheet 名称
|
*/
|
public static final String SHEET_NAME = "问题详情占比图表";
|
|
private final List<ServiceSubtaskDetailRatioExport> rows;
|
|
public MydOptionRatioChartBuilder(List<ServiceSubtaskDetailRatioExport> rows) {
|
this.rows = rows == null ? new ArrayList<>() : rows;
|
}
|
|
@Override
|
public void build(XSSFWorkbook workbook) {
|
if (rows.isEmpty()) {
|
return;
|
}
|
XSSFSheet sheet = workbook.createSheet(SHEET_NAME);
|
|
int startRow = 0;
|
for (Map.Entry<String, List<ServiceSubtaskDetailRatioExport>> entry : groupByTask().entrySet()) {
|
// 每写完一块数据立刻建图：SXSSF 流式工作簿里刚写的行还在内存中，图表才能读到单元格
|
startRow = writeTaskBlockWithChart(sheet, entry.getKey(), entry.getValue(), startRow) + 2;
|
}
|
}
|
|
/**
|
* 按任务名称分组（保持列表原有顺序，即 SQL 的 task_name 排序）
|
*/
|
private Map<String, List<ServiceSubtaskDetailRatioExport>> groupByTask() {
|
Map<String, List<ServiceSubtaskDetailRatioExport>> grouped = new LinkedHashMap<>();
|
for (ServiceSubtaskDetailRatioExport row : rows) {
|
String taskName = row.getTaskname() == null ? "" : row.getTaskname();
|
grouped.computeIfAbsent(taskName, k -> new ArrayList<>()).add(row);
|
}
|
return grouped;
|
}
|
|
/**
|
* 写一个任务的数据块并画柱状图，返回下一个块可用的起始行
|
*/
|
private int writeTaskBlockWithChart(XSSFSheet sheet, String taskName,
|
List<ServiceSubtaskDetailRatioExport> taskRows, int startRow) {
|
// 题目 -> (选项 -> 选该选项的人数)，选项按出现顺序收集成本任务的系列
|
Map<String, Map<String, Double>> questionOptions = new LinkedHashMap<>();
|
LinkedHashSet<String> optionSet = new LinkedHashSet<>();
|
String taskid = "";
|
for (ServiceSubtaskDetailRatioExport row : taskRows) {
|
String question = row.getQuestiontext() == null ? "" : row.getQuestiontext();
|
String option = row.getOptionresult() == null ? "" : row.getOptionresult();
|
questionOptions.computeIfAbsent(question, k -> new LinkedHashMap<>()).put(option, parseCount(row.getCount()));
|
optionSet.add(option);
|
if (taskid.isEmpty() && row.getTaskid() != null) {
|
taskid = row.getTaskid();
|
}
|
}
|
List<String> optionList = new ArrayList<>(optionSet);
|
List<String> questionList = new ArrayList<>(questionOptions.keySet());
|
|
int rowIdx = startRow;
|
// 标题行
|
Row titleRow = sheet.createRow(rowIdx++);
|
titleRow.createCell(0).setCellValue("任务名称：" + taskName + "（任务编号 " + taskid + "）");
|
|
// 表头行：题目 + 各选项（图表系列的标题就是这一行）
|
Row headerRow = sheet.createRow(rowIdx++);
|
headerRow.createCell(0).setCellValue("题目");
|
for (int i = 0; i < optionList.size(); i++) {
|
headerRow.createCell(i + 1).setCellValue(optionList.get(i));
|
}
|
|
// 数据行：值 = 选该选项的人数
|
int firstDataRow = rowIdx;
|
for (String question : questionList) {
|
Row dataRow = sheet.createRow(rowIdx++);
|
dataRow.createCell(0).setCellValue(question);
|
Map<String, Double> optionMap = questionOptions.get(question);
|
for (int i = 0; i < optionList.size(); i++) {
|
Double count = optionMap.get(optionList.get(i));
|
dataRow.createCell(i + 1).setCellValue(count == null ? 0D : count);
|
}
|
}
|
int lastDataRow = rowIdx - 1;
|
|
// 柱状图：分类=题目，系列=选项，数值=选该选项的人数
|
int chartCol = optionList.size() + 3;
|
int chartHeight = Math.max(16, questionList.size() + 4);
|
XSSFDrawing drawing = sheet.createDrawingPatriarch();
|
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0,
|
chartCol, firstDataRow - 1, chartCol + 10, firstDataRow - 1 + chartHeight);
|
XSSFChart chart = drawing.createChart(anchor);
|
chart.setTitleText(taskName);
|
chart.setTitleOverlay(false);
|
|
XDDFChartLegend legend = chart.getOrAddLegend();
|
legend.setPosition(LegendPosition.BOTTOM);
|
|
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
|
bottomAxis.setTitle("题目");
|
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
|
leftAxis.setTitle("数量");
|
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
|
|
XDDFDataSource<String> categories = XDDFDataSourcesFactory.fromStringCellRange(sheet,
|
new CellRangeAddress(firstDataRow, lastDataRow, 0, 0));
|
XDDFBarChartData barChart = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
|
for (int i = 0; i < optionList.size(); i++) {
|
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
|
new CellRangeAddress(firstDataRow, lastDataRow, i + 1, i + 1));
|
XDDFChartData.Series series = barChart.addSeries(categories, values);
|
series.setTitle(optionList.get(i), null);
|
}
|
barChart.setBarDirection(BarDirection.COL);
|
// 关键：不调用 plot 图表不会显示
|
chart.plot(barChart);
|
|
return rowIdx;
|
}
|
|
/**
|
* 数量字符串 -> 数字，异常值按 0 处理
|
*/
|
private double parseCount(String count) {
|
if (count == null || count.isEmpty()) {
|
return 0D;
|
}
|
try {
|
return Double.parseDouble(count.trim());
|
} catch (NumberFormatException e) {
|
return 0D;
|
}
|
}
|
}
|
