package com.ots.project.tool.report.LAQ.chart;
|
|
import com.alibaba.fastjson.JSON;
|
import com.ots.common.enums.CapabilityEnum;
|
import com.ots.common.enums.LangTypeEnum;
|
import com.ots.common.enums.ReportTypeEnum;
|
import com.ots.common.utils.DateUtils;
|
import com.ots.common.utils.MapDataUtil;
|
import com.ots.common.utils.StringUtils;
|
import com.ots.common.utils.poi.ExcelUtil;
|
import com.ots.common.utils.poi.WordUtil;
|
import com.ots.framework.config.EssConfig;
|
import com.ots.project.exam.domain.TReportTemplate;
|
import com.ots.project.tool.PdfUtil;
|
import com.ots.project.tool.report.LAQ.LAQReport;
|
import com.ots.project.tool.report.LAQ.LAQTemplate;
|
import com.ots.project.tool.report.MAQ.base.RowData;
|
import com.ots.project.tool.report.MAQ.base.SeriesData;
|
import com.ots.project.tool.report.MAQNEW.MAQReport_V2;
|
import com.ots.project.tool.report.ReportResultData;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.ooxml.POIXMLDocument;
|
import org.apache.poi.ooxml.POIXMLDocumentPart;
|
import org.apache.poi.openxml4j.util.ZipSecureFile;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.xssf.usermodel.XSSFCell;
|
import org.apache.poi.xssf.usermodel.XSSFRow;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.apache.poi.xwpf.usermodel.XWPFChart;
|
import org.apache.poi.xwpf.usermodel.XWPFDocument;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
|
|
import java.io.*;
|
import java.math.BigDecimal;
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
/**
|
* @description: LAQ图表
|
* @author: zhijie
|
* @create: 2023-10-10 15:01
|
**/
|
@Slf4j
|
public class LAQChart {
|
|
|
public static void main(String[] args) throws Exception {
|
String dataPath = "D:\\测评系统\\需求\\LAQ\\report\\LAQ表头数据文件.xlsx";
|
// 获取导入数据
|
File file = new File(dataPath);
|
InputStream in = new FileInputStream(file);
|
ExcelUtil<LAQTemplate> util = new ExcelUtil<>(LAQTemplate.class);
|
List<LAQTemplate> laqTemplateList = util.importExcel(in);
|
// 生成文件集合
|
List<String> fileNameList = new ArrayList<>();
|
List<String> deleteStrList = new ArrayList<>();
|
for (int i = 0; i < laqTemplateList.size(); i++) {
|
LAQTemplate laqTemplate = laqTemplateList.get(i);
|
//组装报告所用数据
|
Map<String, Object> textMap = new HashMap<>();
|
// textMap.put("sendEmailFileName",laqTemplate.getName());
|
// textMap.put("nowDate", DateUtils.getDate());
|
// MapDataUtil.objectToMap(laqTemplate,textMap);
|
textMap.put("olas","50");
|
textMap.put("ca","32");
|
textMap.put("bla","34");
|
textMap.put("nr","3");
|
textMap.put("ar","45");
|
textMap.put("open","67");
|
textMap.put("cons","12");
|
textMap.put("extra","48");
|
textMap.put("agree","90");
|
textMap.put("emosta","23");
|
// textMap.put("perfomance","0");
|
|
|
LAQReport report = new LAQReport();
|
// ReportResultData resultData = report.getTemplateParameters(textMap, ReportTypeEnum.LAQ, LangTypeEnum.codeOf("English"));
|
// TReportTemplate template = null;
|
// String zipFileName = WordUtil.makeReportFile("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文报告-20230926.docx", template, textMap, new HashMap<>(), deleteStrList);
|
// fileNameList.add(zipFileName);
|
ZipSecureFile.setMinInflateRatio(0.001);
|
try (OutputStream os = new FileOutputStream("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文模板-wzp-修改版-1.docx");XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\测评系统\\需求\\LAQ\\report\\LAQ英文模板-wzp-修改版.docx"))){
|
Map<Integer,List<SeriesData>> seriesDatas = initData(textMap);
|
changeChart(document,seriesDatas,textMap);
|
document.write(os);
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
|
log.info("删除的文件名:{}", JSON.toJSONString(deleteStrList));
|
WordUtil.deleteFileByStr(deleteStrList);
|
}
|
}
|
|
/**
|
* 替换LAQ图表数据
|
* @param document 文档对象
|
* @param textMap 数据源
|
*/
|
public static void changeChart(XWPFDocument document, Map<String, Object> textMap) {
|
Map<Integer,List<SeriesData>> seriesDatas = initData(textMap);
|
changeChart(document,seriesDatas,textMap);
|
}
|
|
/**
|
* 初始化报告数据
|
* @param textMap
|
* @return
|
*/
|
public static Map<Integer,List<SeriesData>> initData(Map<String, Object> textMap){
|
Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
|
//获取命中数值
|
Double olas = Double.valueOf((String)textMap.get("olas"));
|
Double ca = Double.valueOf((String)textMap.get("ca"));
|
Double bla = Double.valueOf((String)textMap.get("bla"));
|
Double nr = Double.valueOf((String)textMap.get("nr"));
|
Double ar = Double.valueOf((String)textMap.get("ar"));
|
Double open = Double.valueOf((String)textMap.get("open"));
|
Double cons = Double.valueOf((String)textMap.get("cons"));
|
Double extra = Double.valueOf((String)textMap.get("extra"));
|
Double agree = Double.valueOf((String)textMap.get("agree"));
|
Double emosta = Double.valueOf((String)textMap.get("emosta"));
|
Double perfomance;
|
if(textMap.get("perfomance") != null){
|
perfomance = Double.valueOf((String)textMap.get("perfomance"));
|
}else{
|
perfomance = -1D;
|
}
|
|
|
// 图表1
|
List<SeriesData> seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"类别 1")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", olas.doubleValue())
|
))
|
);
|
seriesDataMap.put(1,seriesDatas);
|
|
// 图表2
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"Behavioral Leadership Alignment"),
|
new RowData(" ", 0,"Cognitive Ability")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", bla.doubleValue()),
|
new RowData("系列 1", ca.doubleValue())
|
))
|
);
|
seriesDataMap.put(2,seriesDatas);
|
|
// 图表3
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"Abstract Reasoning"),
|
new RowData(" ", 0,"Numeric Reasoning")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", ar.doubleValue()),
|
new RowData("系列 1", nr.doubleValue())
|
))
|
);
|
seriesDataMap.put(3,seriesDatas);
|
|
// 图表4
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"类别 1")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", bla.doubleValue())
|
))
|
);
|
seriesDataMap.put(4,seriesDatas);
|
|
// 图表5
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"Emotional Stability"),
|
new RowData(" ", 0,"Agreeableness"),
|
new RowData(" ", 0,"Extraversion"),
|
new RowData(" ", 0,"Conscientiousness"),
|
new RowData(" ", 0,"Openness to Experience")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", emosta.doubleValue()),
|
new RowData("系列 1", agree.doubleValue()),
|
new RowData("系列 1", extra.doubleValue()),
|
new RowData("系列 1", cons.doubleValue()),
|
new RowData("系列 1", open.doubleValue())
|
))
|
);
|
seriesDataMap.put(5,seriesDatas);
|
|
// 图表6
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"Emotional Stability"),
|
new RowData(" ", 0,"Agreeableness"),
|
new RowData(" ", 0,"Extraversion"),
|
new RowData(" ", 0,"Conscientiousness"),
|
new RowData(" ", 0,"Openness to Experience")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", emosta.doubleValue()),
|
new RowData("系列 1", agree.doubleValue()),
|
new RowData("系列 1", extra.doubleValue()),
|
new RowData("系列 1", cons.doubleValue()),
|
new RowData("系列 1", open.doubleValue())
|
))
|
);
|
seriesDataMap.put(6,seriesDatas);
|
|
// 图表7
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"Emotional Stability"),
|
new RowData(" ", 0,"Agreeableness"),
|
new RowData(" ", 0,"Extraversion"),
|
new RowData(" ", 0,"Conscientiousness"),
|
new RowData(" ", 0,"Openness to Experience")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", emosta.doubleValue()),
|
new RowData("系列 1", agree.doubleValue()),
|
new RowData("系列 1", extra.doubleValue()),
|
new RowData("系列 1", cons.doubleValue()),
|
new RowData("系列 1", open.doubleValue())
|
))
|
);
|
seriesDataMap.put(7,seriesDatas);
|
|
// 图表8
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"Emotional Stability"),
|
new RowData(" ", 0,"Agreeableness"),
|
new RowData(" ", 0,"Extraversion"),
|
new RowData(" ", 0,"Conscientiousness"),
|
new RowData(" ", 0,"Openness to Experience")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", emosta.doubleValue()),
|
new RowData("系列 1", agree.doubleValue()),
|
new RowData("系列 1", extra.doubleValue()),
|
new RowData("系列 1", cons.doubleValue()),
|
new RowData("系列 1", open.doubleValue())
|
))
|
);
|
seriesDataMap.put(8,seriesDatas);
|
|
// 图表9
|
seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"Emotional Stability"),
|
new RowData(" ", 0,"Agreeableness"),
|
new RowData(" ", 0,"Extraversion"),
|
new RowData(" ", 0,"Conscientiousness"),
|
new RowData(" ", 0,"Openness to Experience")
|
)),
|
new SeriesData("系列 1", Arrays.asList(
|
new RowData("系列 1", emosta.doubleValue()),
|
new RowData("系列 1", agree.doubleValue()),
|
new RowData("系列 1", extra.doubleValue()),
|
new RowData("系列 1", cons.doubleValue()),
|
new RowData("系列 1", open.doubleValue())
|
))
|
);
|
seriesDataMap.put(9,seriesDatas);
|
|
// 图表10
|
seriesDatas = Arrays.asList(
|
new SeriesData("X 值", Arrays.asList(
|
new RowData("X 值", olas.doubleValue())
|
)),
|
new SeriesData("Y 值", Arrays.asList(
|
new RowData("Y 值", 44)
|
))
|
);
|
seriesDataMap.put(10,seriesDatas);
|
|
// 图表11
|
seriesDatas = Arrays.asList(
|
new SeriesData("X 值", Arrays.asList(
|
new RowData("X 值", perfomance.doubleValue() == -1 ? perfomance.doubleValue() : olas.doubleValue())
|
)),
|
new SeriesData("Y 值", Arrays.asList(
|
new RowData("Y 值", perfomance.doubleValue())
|
))
|
);
|
seriesDataMap.put(11,seriesDatas);
|
|
return seriesDataMap;
|
}
|
|
/**
|
* 替换LAQ图表数据
|
* @param document 文档对象
|
* @param seriesDataMap
|
* @param textMap 数据源
|
*/
|
public static void changeChart(XWPFDocument document, Map<Integer,List<SeriesData>> seriesDataMap, Map<String, Object> textMap) {
|
if(StringUtils.isEmpty(textMap)){
|
return;
|
}
|
try {
|
List<POIXMLDocumentPart> relations = document.getRelations();
|
int barIndex = 1;
|
for (POIXMLDocumentPart part : relations) {
|
if (part instanceof XWPFChart) {
|
// 图表元素
|
XWPFChart chart = (XWPFChart) part;
|
// 查看里面的图表数据，才能知道是什么图表
|
CTPlotArea plot = chart.getCTChart().getPlotArea();
|
getChartType(chart);
|
// excel内置表格
|
XSSFWorkbook workbook = chart.getWorkbook();
|
XSSFSheet sheet = workbook.getSheetAt(0);
|
|
//柱形图
|
if (!plot.getBarChartList().isEmpty()) {
|
List<SeriesData> seriesDatas = seriesDataMap.get(barIndex);
|
CTBarChart barChart = plot.getBarChartArray(0);
|
//System.out.println("柱形图{"+plot.getScatterChartList().size()+"}");
|
//刷新内置excel数据
|
refreshExcel(seriesDatas, sheet);
|
workbook.write(chart.getPackagePart().getOutputStream());
|
|
//柱形图数据源
|
// List<SeriesData> barDatas = seriesDatas.subList(1,4);
|
//因为柱形图CTBarSer没按excel顺序走 调换数据位置保持对应
|
Collections.swap(seriesDatas, 0, 1);
|
int j = 0;
|
for (CTBarSer ser : barChart.getSerList()) {
|
//更新柱形图数据缓存
|
updateBarChart(seriesDatas.get(j), ser.getVal());
|
++j;
|
}
|
barIndex++;
|
}
|
|
//散点图
|
if (!plot.getScatterChartList().isEmpty()) {
|
List<SeriesData> seriesDatas = seriesDataMap.get(barIndex);
|
CTScatterChart scatterChart = plot.getScatterChartArray(0);
|
//System.out.println("散点图{"+plot.getScatterChartList().size()+"}");
|
//刷新内置excel数据
|
refreshExcel(seriesDatas, sheet);
|
workbook.write(chart.getPackagePart().getOutputStream());
|
|
/* //散点图数据源 XY坐标
|
List<SeriesData> scatterDatas = new ArrayList<SeriesData>();
|
scatterDatas.add(seriesDatas.get(0));
|
scatterDatas.add(seriesDatas.get(4));*/
|
int i = 0;
|
for (CTScatterSer ser : scatterChart.getSerList()) {
|
//更新散点图缓存
|
updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());
|
++i;
|
}
|
barIndex++;
|
}
|
}
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.error("MAQTR替换word图表 散点图图形失败：{}",e);
|
}
|
}
|
|
|
|
/**
|
* 更新 柱形图 的缓存数据
|
*
|
* @param seriesDatas 数据
|
* @param numDataSource 数据的缓存
|
*/
|
protected static void updateBarChart(SeriesData seriesDatas, CTNumDataSource numDataSource) {
|
//获取柱形图数据数量
|
long ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();
|
int dataSize = seriesDatas.value.size();
|
for (int i = 0; i < dataSize; i++) {
|
RowData cellValu = seriesDatas.value.get(i);
|
CTNumVal val = ptNumCnt > i ? numDataSource.getNumRef().getNumCache().getPtArray(i)
|
: numDataSource.getNumRef().getNumCache().addNewPt();
|
val.setIdx(i);
|
val.setV(String.format("%.0f", cellValu.value));
|
|
}
|
|
// 更新对应excel的范围
|
numDataSource.getNumRef().setF(
|
replaceRowEnd(numDataSource.getNumRef().getF(),
|
ptNumCnt,
|
dataSize));
|
|
// 删除多的
|
if (ptNumCnt > dataSize) {
|
for (int idx = dataSize; idx < ptNumCnt; idx++) {
|
numDataSource.getNumRef().getNumCache().removePt(dataSize);
|
}
|
}
|
// 更新个数
|
numDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
|
}
|
|
|
/**
|
* 更新图表的关联 excel
|
*
|
* @param seriesDatas
|
* @param sheet
|
*/
|
protected static void refreshExcel(List<SeriesData> seriesDatas, XSSFSheet sheet) {
|
XSSFRow title = sheet.getRow(0);
|
//遍历数据数组进行excel赋值
|
for (int i = 0; i < seriesDatas.size(); i++) {
|
SeriesData data = seriesDatas.get(i);
|
if (data.name != null && !data.name.isEmpty()) {
|
// 第一行标题
|
XSSFCell cell = title.getCell(i);
|
if (cell == null) {
|
cell = title.createCell(i + 1);
|
}
|
cell.setCellValue(data.name);
|
}
|
int size = data.value.size();
|
//遍历数据进行赋值
|
for (int j = 0; j < size; j++) {
|
//从第二行开始赋值
|
XSSFRow row = sheet.getRow(j + 1);
|
if (row == null) {
|
row = sheet.createRow(j + 1);
|
}
|
RowData cellValu = data.value.get(j);
|
|
//第一列开始赋值
|
XSSFCell cell = row.getCell(i);
|
if (cell == null) {
|
cell = row.createCell(i);
|
}
|
//优先使用字符串值
|
if(StringUtils.isNotEmpty(cellValu.valueStr)){
|
cell.setCellValue(cellValu.valueStr);
|
}else{
|
if(cellValu.value != -1){
|
cell.setCellValue(cellValu.value);
|
}else{
|
cell.setCellValue((String) null);
|
}
|
}
|
|
}
|
//删除多余行数
|
int lastRowNum = sheet.getLastRowNum();
|
//log.error("lastRowNum:{},size:{}",lastRowNum,size);
|
if (lastRowNum > size) {
|
for (int idx = lastRowNum; idx > size; idx--) {
|
if(sheet.getRow(idx) == null){
|
continue;
|
}
|
sheet.removeRow(sheet.getRow(idx));
|
}
|
}
|
}
|
}
|
|
|
/**
|
* 更新 散点图 缓存数据
|
*
|
* @param seriesDatas 数据
|
* @param xDataSource X坐标数据缓存
|
* @param yDataSource Y坐标数据缓存
|
*/
|
protected static void updateScatterChart(List<SeriesData> seriesDatas, CTAxDataSource xDataSource,
|
CTNumDataSource yDataSource) {
|
|
//获取xy坐标数据条数
|
long xNumCnt = xDataSource.getNumRef().getNumCache().getPtCount().getVal();
|
long yNumCnt = yDataSource.getNumRef().getNumCache().getPtCount().getVal();
|
|
//获取XY最新数据
|
List<RowData> xRowDatas = seriesDatas.get(0).getValue();
|
int xRowDataSize = xRowDatas.size();
|
List<RowData> yRowDatas = seriesDatas.get(1).getValue();
|
int yRowDataSize = yRowDatas.size();
|
|
//更新X坐标缓存
|
for (int i = 0; i < xRowDatas.size(); i++) {
|
RowData cellValu = xRowDatas.get(i);
|
CTNumVal val = xNumCnt > i ? xDataSource.getNumRef().getNumCache().getPtArray(i)
|
: xDataSource.getNumRef().getNumCache().addNewPt();
|
val.setIdx(i);
|
if(cellValu.value == -1){
|
val.setV(null);
|
}else {
|
val.setV(String.format("%.0f", cellValu.value));
|
}
|
}
|
|
//更新Y坐标缓存
|
for (int i = 0; i < yRowDatas.size(); i++) {
|
RowData cellValu = yRowDatas.get(i);
|
CTNumVal val = yNumCnt > i ? yDataSource.getNumRef().getNumCache().getPtArray(i)
|
: yDataSource.getNumRef().getNumCache().addNewPt();
|
val.setIdx(i);
|
if(cellValu.value == -1){
|
val.setV(null);
|
}else {
|
val.setV(String.format("%.0f", cellValu.value));
|
}
|
}
|
|
// 更新对应excel的范围
|
xDataSource.getNumRef().setF(
|
replaceRowEnd(xDataSource.getNumRef().getF(),
|
xNumCnt,
|
xRowDataSize));
|
yDataSource.getNumRef().setF(
|
replaceRowEnd(yDataSource.getNumRef().getF(),
|
yNumCnt,
|
yRowDataSize));
|
|
// 删除多的
|
if (xNumCnt > xRowDataSize) {
|
for (int idx = xRowDataSize; idx < xNumCnt; idx++) {
|
xDataSource.getNumRef().getNumCache().removePt(xRowDataSize);
|
}
|
}
|
if (yNumCnt > yRowDataSize) {
|
for (int idx = yRowDataSize; idx < yNumCnt; idx++) {
|
yDataSource.getNumRef().getNumCache().removePt(yRowDataSize);
|
}
|
}
|
// 更新个数
|
xDataSource.getNumRef().getNumCache().getPtCount().setVal(xRowDataSize);
|
// 更新个数
|
yDataSource.getNumRef().getNumCache().getPtCount().setVal(yRowDataSize);
|
}
|
|
|
/**
|
* 替换 形如： Sheet1!$A$2:$A$4 的字符
|
*
|
* @param range
|
* @return
|
*/
|
public static String replaceRowEnd(String range, long oldSize, long newSize) {
|
Pattern pattern = Pattern.compile("(:\\$[A-Z]+\\$)(\\d+)");
|
Matcher matcher = pattern.matcher(range);
|
if (matcher.find()) {
|
long old = Long.parseLong(matcher.group(2));
|
return range.replaceAll("(:\\$[A-Z]+\\$)(\\d+)", "$1" + Long.toString(old - oldSize + newSize));
|
}
|
return range;
|
}
|
|
/**
|
* 获取图表有哪些类型
|
* @param part
|
*/
|
public static void getChartType(POIXMLDocumentPart part){
|
XWPFChart chart = (XWPFChart) part;
|
//根据属性第一列名称切换数据类型
|
CTChart ctChart = chart.getCTChart();
|
CTPlotArea plotArea = ctChart.getPlotArea();
|
|
if (!plotArea.getBarChartList().isEmpty()) {
|
System.out.println("柱状图{"+plotArea.getBarChartList().size()+"}");
|
}
|
if (!plotArea.getPieChartList().isEmpty()) {
|
System.out.println("饼图{"+plotArea.getPieChartList().size()+"}");
|
}
|
if (!plotArea.getLineChartList().isEmpty()) {
|
System.out.println("线形图{"+plotArea.getLineChartList().size()+"}");
|
}
|
if (!plotArea.getBar3DChartList().isEmpty()) {
|
System.out.println("柱状图3D{"+plotArea.getBar3DChartList().size()+"}");
|
}
|
if (!plotArea.getScatterChartList().isEmpty()) {
|
System.out.println("散点图{"+plotArea.getScatterChartList().size()+"}");
|
}
|
}
|
|
}
|
