package com.ots.project.tool.report.CIAQ.chart;
|
|
import com.alibaba.fastjson.JSON;
|
import com.alibaba.fastjson.JSONObject;
|
import com.ots.common.utils.StringUtils;
|
import com.ots.project.tool.report.CIAQ.CIAQReport;
|
import com.ots.project.tool.report.MAQ.base.RowData;
|
import com.ots.project.tool.report.MAQ.base.SeriesData;
|
import com.ots.project.tool.report.reportCalculation.response.ReportCIAQAPIResult;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.ooxml.POIXMLDocument;
|
import org.apache.poi.ooxml.POIXMLDocumentPart;
|
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.FileOutputStream;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
/**
|
* @description: CIAQ图表 散点图跟柱形图数据变更
|
* @author: zhijie
|
* @create: 2023-04-23 16:31
|
**/
|
@Slf4j
|
public class CIAQChart {
|
|
private static Double PR_Dim01, PR_Dim02, PR_Dim03, PR_Dim04, PR_Dim05, PR_Dim06, PR_Dim07, PR_Dim08;
|
|
public static void main(String[] args) throws IOException {
|
String str = "{\"occ1L\":\"地质勘探工程技术人员（比如地质矿产调查工程技术人员）\",\"occ3L\":\"矿山工程技术人员（比如选矿与矿物加工工程技术人员）\",\"PR_Dim01\":\"56\",\"PR_Dim02\":\"78\",\"PR_Dim03\":\"40\",\"PR_Dim04\":\"67\",\"occ5H\":\"音乐指挥与演员（比如电影电视演员、乐器演奏员）\",\"occ9L\":\"信息通信网络运行管理人员（比如信息通信网络运行管理员）\",\"occ7H\":\"工艺美术与创意设计专业人员（比如服装设计人员、环境设计人员）\",\"occ1H\":\"绿化与园艺服务人员（比如盆景师、插花花艺师）\",\"occ5L\":\"机械工程技术人员（比如机械设计工程技术人员、自动控制工程技术人员）\",\"occ3H\":\"文学、艺术学研究人员\",\"occ7L\":\"工业机器人操作运维人员（比如工业机器人系统运维员、）\",\"occ10H\":\"酒类饮品调制师（比如酿酒师、酒体设计师）\",\"occ10L\":\"地质勘查人员（比如物探工、地质调查员）\",\"occ8H\":\" 陶瓷制品制造人员（比如陶瓷装饰工、古建琉璃工）\",\"PR_Dim05\":\"89\",\"PR_Dim06\":\"12\",\"PR_Dim07\":\"57\",\"PR_Dim08\":\"33\",\"occ2L\":\"测绘和地理信息工程技术人员（比如工程测量工程技术人员）\",\"occ4H\":\"文艺创作与编导人员（比如文学作家、舞蹈编导）\",\"occ8L\":\"电子工程技术人员（比如电子材料/电子元器件工程技术人员）\",\"occ6H\":\"美术专业人员（比如画家、雕塑家）\",\"occ4L\":\"冶金工程技术人员（比如冶金热能工程技术人员、稀土工程技术人员）\",\"occ2H\":\"舞台专业人员（比如灯光师、化妆师）\",\"occ6L\":\"航空工程技术人员（比如飞行器制造工程技术人员）\",\"condition\":\"1\",\"occ9H\":\"摄影扩印服务人员（比如商业摄影师）\"}";
|
Map<String ,Object> textMap = JSON.parseObject(str, HashMap.class);
|
try (OutputStream os = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\CIAQ1.docx");XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\Administrator\\Desktop\\CIAQ.docx"))){
|
changeChart(document,textMap);
|
document.write(os);
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 替换CIAQ图表数据
|
* @param document 文档对象
|
* @param textMap 报告字典键值对
|
* @return
|
*/
|
public static boolean changeChart(XWPFDocument document, Map<String, Object> textMap){
|
List<SeriesData> seriesDatas = initData(textMap);
|
if(seriesDatas == null){
|
log.error("替换CIAQ图表数据,初始化数据失败!");
|
return false;
|
}
|
//CIAQ图表数据
|
changeCIAQChart(document,seriesDatas);
|
return true;
|
}
|
|
/**
|
* 判断是否为空
|
* @param value
|
* @return
|
*/
|
public static boolean checkParamsIsNull(Double... value) {
|
for (int i = 0; i < value.length; i++) {
|
if (Objects.isNull(value[i])) {
|
return true;
|
}
|
}
|
return false;
|
}
|
|
/**
|
* 初始化报告数据
|
* @param textMap
|
* @return
|
*/
|
public static List<SeriesData> initData(Map<String, Object> textMap){
|
//获取命中数值
|
PR_Dim01 = Double.valueOf((String) textMap.get("PR_Dim01"));
|
PR_Dim02 = Double.valueOf((String) textMap.get("PR_Dim02"));
|
PR_Dim03 = Double.valueOf((String) textMap.get("PR_Dim03"));
|
PR_Dim04 = Double.valueOf((String) textMap.get("PR_Dim04"));
|
PR_Dim05 = Double.valueOf((String) textMap.get("PR_Dim05"));
|
PR_Dim06 = Double.valueOf((String) textMap.get("PR_Dim06"));
|
PR_Dim07 = Double.valueOf((String) textMap.get("PR_Dim07"));
|
PR_Dim08 = Double.valueOf((String) textMap.get("PR_Dim08"));
|
|
//判断是否为空
|
if (checkParamsIsNull(PR_Dim01, PR_Dim02, PR_Dim03, PR_Dim04, PR_Dim05, PR_Dim06, PR_Dim07, PR_Dim08)) {
|
return null;
|
}
|
|
// 测试数据
|
List<SeriesData> seriesDatas = Arrays.asList(
|
new SeriesData(" ", Arrays.asList(
|
new RowData(" ", 0,"健康科学"),
|
new RowData(" ", 0,"创造性表达"),
|
new RowData(" ", 0,"科技"),
|
new RowData(" ", 0,"人"),
|
new RowData(" ", 0,"组织"),
|
new RowData(" ", 0,"影响"),
|
new RowData(" ", 0,"自然"),
|
new RowData(" ", 0,"事物")
|
)),
|
new SeriesData("价值观分数", Arrays.asList(
|
new RowData("价值观分数", PR_Dim01),
|
new RowData("价值观分数", PR_Dim02),
|
new RowData("价值观分数", PR_Dim03),
|
new RowData("价值观分数", PR_Dim04),
|
new RowData("价值观分数", PR_Dim05),
|
new RowData("价值观分数", PR_Dim06),
|
new RowData("价值观分数", PR_Dim07),
|
new RowData("价值观分数", PR_Dim08)
|
))
|
);
|
return seriesDatas;
|
}
|
|
/**
|
* 替换word图表 雷达图带标记图表
|
* @param doc
|
* @param seriesDatas
|
*/
|
public static void changeCIAQChart(XWPFDocument doc,List<SeriesData> seriesDatas) {
|
try {
|
//动态刷新图表
|
List<POIXMLDocumentPart> relations = doc.getRelations();
|
for (POIXMLDocumentPart part : relations) {
|
if (part instanceof XWPFChart) {
|
// 图表元素
|
XWPFChart chart = (XWPFChart) part;
|
|
// 查看里面的图表数据，才能知道是什么图表
|
CTPlotArea plot = chart.getCTChart().getPlotArea();
|
// excel内置表格
|
XSSFWorkbook workbook = chart.getWorkbook();
|
XSSFSheet sheet = workbook.getSheetAt(0);
|
|
// 查看图表类型
|
// getChartType(part);
|
// 遍历数据
|
//printData(sheet);
|
|
// 雷达图
|
if (!plot.getRadarChartList().isEmpty()) {
|
CTRadarChart radarChart = plot.getRadarChartArray(0);
|
//刷新内置excel数据
|
refreshExcel(seriesDatas, workbook, sheet);
|
workbook.write(chart.getPackagePart().getOutputStream());
|
|
//雷达图数据源 散点坐标
|
for (CTRadarSer ser : radarChart.getSerList()) {
|
//更新雷达图缓存
|
updateRadarChart(seriesDatas, ser.getCat(),ser.getVal());
|
}
|
}
|
}
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.error("CIAQGS替换word图表 散点图跟柱形图组合图形失败：{}",e);
|
}
|
}
|
|
private static void printData(XSSFSheet sheet){
|
//先要遍历行
|
for(int rowNum=0;rowNum<=sheet.getLastRowNum();rowNum++) {
|
XSSFRow hssRow = sheet.getRow(rowNum);
|
if (hssRow == null) {
|
continue;
|
}
|
for (int cellNum = 0; cellNum <= hssRow.getLastCellNum(); cellNum++) {
|
XSSFCell hssfCell = hssRow.getCell(cellNum);
|
if (hssfCell == null) {
|
continue;
|
}
|
System.out.print("--" + getValue(hssfCell));
|
}
|
System.out.println();
|
}
|
}
|
|
private static String getValue(XSSFCell hssfCell){
|
if(hssfCell.getCellType()== CellType.BOOLEAN){
|
return String.valueOf(hssfCell.getBooleanCellValue());
|
}else if(hssfCell.getCellType()==CellType.NUMERIC){
|
return String.valueOf(hssfCell.getNumericCellValue());
|
}else{
|
return String.valueOf(hssfCell.getStringCellValue());
|
}
|
}
|
|
/**
|
* 更新图表的关联 excel
|
*
|
* @param seriesDatas
|
* @param workbook
|
* @param sheet
|
*/
|
protected static void refreshExcel(List<SeriesData> seriesDatas, XSSFWorkbook workbook, 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);
|
}
|
//优先使用字符串值 CIAQ第一列是中文
|
if(StringUtils.isNotEmpty(cellValu.valueStr)){
|
cell.setCellValue(cellValu.valueStr);
|
}else{
|
cell.setCellValue(cellValu.value);
|
}
|
}
|
//删除多余行数
|
int lastRowNum = sheet.getLastRowNum();
|
if (lastRowNum > size) {
|
for (int idx = lastRowNum; idx > size; idx--) {
|
sheet.removeRow(sheet.getRow(idx));
|
}
|
}
|
}
|
}
|
|
|
/**
|
* 更新 雷达图 缓存数据
|
*
|
* @param seriesDatas 数据
|
* @param xDataSource X坐标数据缓存
|
* @param yDataSource Y坐标数据缓存
|
*/
|
protected static void updateRadarChart(List<SeriesData> seriesDatas, CTAxDataSource xDataSource,
|
CTNumDataSource yDataSource) {
|
|
//获取xy坐标数据条数
|
long xStrCnt = xDataSource.getStrRef().getStrCache().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);
|
CTStrVal val = xStrCnt > i ? xDataSource.getStrRef().getStrCache().getPtArray(i)
|
: xDataSource.getStrRef().getStrCache().addNewPt();
|
val.setIdx(i);
|
val.setV(cellValu.valueStr);
|
}
|
|
//更新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);
|
val.setV(String.format("%.0f", cellValu.value));
|
}
|
|
// 更新对应excel的范围
|
xDataSource.getStrRef().setF(
|
replaceRowEnd(xDataSource.getStrRef().getF(),
|
xStrCnt,
|
xRowDataSize));
|
yDataSource.getNumRef().setF(
|
replaceRowEnd(yDataSource.getNumRef().getF(),
|
yNumCnt,
|
yRowDataSize));
|
|
// 删除多的
|
if (xStrCnt > xRowDataSize) {
|
for (int idx = xRowDataSize; idx < xStrCnt; idx++) {
|
xDataSource.getStrRef().getStrCache().removePt(xRowDataSize);
|
}
|
}
|
if (yNumCnt > yRowDataSize) {
|
for (int idx = yRowDataSize; idx < yNumCnt; idx++) {
|
yDataSource.getNumRef().getNumCache().removePt(yRowDataSize);
|
}
|
}
|
// 更新个数
|
xDataSource.getStrRef().getStrCache().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()+"}");
|
}
|
if (!plotArea.getRadarChartList().isEmpty()) {
|
System.out.println("雷达图{"+plotArea.getRadarChartList().size()+"}");
|
}
|
}
|
|
}
|
