package com.ots.project.tool.report.APIFan.chart;
|
|
import com.ots.project.tool.report.MAQ.base.RowData;
|
import com.ots.project.tool.report.MAQ.base.SeriesData;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.ooxml.POIXMLDocument;
|
import org.apache.poi.ooxml.POIXMLDocumentPart;
|
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;
|
|
/**
|
*@ClassName APIFanChart
|
*@Description 柱形图数据替换
|
*/
|
@Slf4j
|
public class APIFanChart {
|
//region 图形变量定义
|
private static String p_total;//工作表现预测分数
|
private static String p_retent09;//留职预测分数
|
private static String p_social01;//1.社交性
|
private static String p_cooper02;//2.合作性
|
private static String p_analth03;//3.问题解决和适应性
|
private static String p_initia04;//4.主动性
|
private static String p_persis05;//5.坚韧性
|
private static String p_depend06;//6.可靠性
|
private static String p_emotct07;//7.情绪的自我控制
|
private static String p_stress08;//8.压力的容忍度
|
|
//自我认知准确度
|
private static String p_sdecepT1;
|
private static String p_sdecept10;
|
//自我阐述的真实度
|
private static String p_impmgT1;
|
private static String p_impmg11;
|
//直接撒谎
|
private static String bst1;
|
private static String bst2;
|
//endregion
|
|
public static void main(String[] args) throws IOException {
|
try (
|
OutputStream os = new FileOutputStream("C:\\Users\\mac\\Desktop\\API_Fan_IA_CN_222.docx");
|
XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\mac\\Desktop\\API_Fan_IA_CN.docx")))
|
{
|
Map<String, Object> textMap = new HashMap<String, Object>();
|
textMap.put("P_Total","52");
|
textMap.put("P_Retent09","53");
|
textMap.put("P_Social01","54");
|
textMap.put("P_Cooper02","55");
|
textMap.put("P_Analth03","56");
|
textMap.put("P_Initia04","57");
|
textMap.put("P_Persis05","58");
|
textMap.put("P_Depend06","59");
|
textMap.put("P_Emotct07","60");
|
textMap.put("P_Stress08","61");
|
textMap.put("P_SdecepT1","62");
|
textMap.put("P_Sdecept10","63");
|
textMap.put("P_ImpmgT1","64");
|
textMap.put("P_Impmg11","65");
|
textMap.put("BST1","66");
|
textMap.put("BST2","67");
|
changeChart(document,textMap);
|
document.write(os);
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 替换Excel图表数据
|
* @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("替换APIFan图表数据,初始化数据失败!");
|
return false;
|
}
|
//图表数据
|
changePAQChart(document,seriesDatas);
|
return true;
|
}
|
|
|
/**
|
* 判断是否为空
|
* @param value
|
* @return
|
*/
|
public static boolean checkParamsIsNull(String... 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){
|
//region 获取命中数值
|
p_total = (String) textMap.get("P_Total");
|
p_retent09 = (String) textMap.get("P_Retent09");
|
p_social01 = (String) textMap.get("P_Social01");
|
p_cooper02 = (String) textMap.get("P_Cooper02");
|
p_analth03 = (String) textMap.get("P_Analth03");
|
p_initia04 = (String) textMap.get("P_Initia04");
|
p_persis05 = (String) textMap.get("P_Persis05");
|
p_depend06 = (String) textMap.get("P_Depend06");
|
p_emotct07 = (String) textMap.get("P_Emotct07");
|
p_stress08 = (String) textMap.get("P_Stress08");
|
|
p_sdecepT1 = (String) textMap.get("P_SdecepT1");
|
p_sdecept10 = (String) textMap.get("P_Sdecept10");
|
p_impmgT1 = (String) textMap.get("P_ImpmgT1");
|
p_impmg11 = (String) textMap.get("P_Impmg11");
|
bst1 = (String) textMap.get("BST1");
|
bst2 = (String) textMap.get("BST2");
|
|
String[] paramValue = new String[]{p_total, p_retent09, p_social01,p_cooper02,p_analth03,p_initia04,
|
p_persis05,p_depend06,p_emotct07,p_stress08,p_sdecepT1, p_sdecept10,p_impmgT1,p_impmg11,bst1,bst2};
|
|
//判断是否为空
|
if (checkParamsIsNull(paramValue))
|
{
|
return null;
|
}
|
|
//endregion
|
|
Double P_Total = Double.valueOf(p_total);
|
Double P_Retent09 = Double.valueOf(p_retent09);
|
Double P_Social01 = Double.valueOf(p_social01);
|
Double P_Cooper02 = Double.valueOf(p_cooper02);
|
Double P_Analth03 = Double.valueOf(p_analth03);
|
Double P_Initia04 = Double.valueOf(p_initia04);
|
Double P_Persis05 = Double.valueOf(p_persis05);
|
Double P_Depend06 = Double.valueOf(p_depend06);
|
Double P_Emotct07 = Double.valueOf(p_emotct07);
|
Double P_Stress08 = Double.valueOf(p_stress08);
|
Double P_SdecepT1 = Double.valueOf(p_sdecepT1);
|
Double P_Sdecept10 = Double.valueOf(p_sdecept10);
|
Double P_ImpmgT1 = Double.valueOf(p_impmgT1);
|
Double P_Impmg11 = Double.valueOf(p_impmg11);
|
Double BST1 = Double.valueOf(bst1);
|
Double BST2 = Double.valueOf(bst2);
|
// 测试数据
|
List<SeriesData> seriesDatas = Arrays.asList(
|
new SeriesData("留职预测分数", Arrays.asList(
|
new RowData("留职预测分数", P_Retent09)
|
)),
|
new SeriesData("人格剖像", Arrays.asList(
|
new RowData("人格剖像", P_Social01),
|
new RowData("人格剖像", P_Cooper02),
|
new RowData("人格剖像", P_Analth03),
|
new RowData("人格剖像", P_Initia04),
|
new RowData("人格剖像", P_Persis05),
|
new RowData("人格剖像", P_Depend06),
|
new RowData("人格剖像", P_Emotct07),
|
new RowData("人格剖像", P_Stress08)
|
|
)),
|
new SeriesData("自我认知的准确度", Arrays.asList(
|
new RowData("自我认知的准确度", P_Sdecept10),//顺序要倒置
|
new RowData("自我认知的准确度", P_SdecepT1)//顺序要倒置
|
|
)),
|
new SeriesData("自我阐述的真实度", Arrays.asList(
|
new RowData("自我阐述的真实度", P_Impmg11),//顺序要倒置
|
new RowData("自我阐述的真实度", P_ImpmgT1)//顺序要倒置
|
|
)),
|
new SeriesData("直接撒谎", Arrays.asList(
|
new RowData("直接撒谎", BST2),//顺序要倒置
|
new RowData("直接撒谎", BST1)//顺序要倒置
|
)),
|
new SeriesData("工作表现预测分数", Arrays.asList(
|
new RowData("工作表现预测分数", P_Total)
|
))
|
);
|
return seriesDatas;
|
}
|
|
/**
|
* 替换word图表 散点图跟柱形图组合图形
|
* @param doc
|
* @param seriesDatas
|
*/
|
public static void changePAQChart(XWPFDocument doc,List<SeriesData> seriesDatas) {
|
try {
|
//动态刷新图表
|
List<POIXMLDocumentPart> relations = doc.getRelations();
|
int k=0;
|
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);
|
|
|
//柱形图
|
if (!plot.getBarChartList().isEmpty()) {
|
CTBarChart barChart = plot.getBarChartArray(0);
|
//刷新内置excel数据
|
List<SeriesData> excelData = new ArrayList<>();
|
excelData.add(seriesDatas.get(k));
|
|
refreshExcel(excelData, workbook, sheet);
|
workbook.write(chart.getPackagePart().getOutputStream());
|
|
//柱形图数据源
|
List<SeriesData> barDatas = seriesDatas;
|
int i = 0;
|
for (CTBarSer ser : barChart.getSerList()) {
|
//更新柱形图数据缓存
|
updateBarChart(barDatas.get(k), ser.getVal());
|
++i;
|
}
|
|
++k;
|
}
|
}
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.error("API-fan柱形图图形失败：{}",e);
|
}
|
}
|
|
/**
|
* 更新图表的关联 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()) {
|
// 第2行标题
|
XSSFCell cell = title.getCell(i+1);
|
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);
|
|
//第2列开始赋值
|
XSSFCell cell = row.getCell(i+1);
|
if (cell == null) {
|
cell = row.createCell(i+1);
|
}
|
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 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);
|
}
|
|
|
/**
|
* 替换 形如： 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()+"}");
|
}
|
}
|
}
|
