package com.ots.project.tool.report.SAQ.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.IOException;
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
/**
|
* @description: SAQ图表 散点图跟柱形图数据变更
|
* @author: zhijie
|
* @create: 2021-06-07 23:31
|
**/
|
@Slf4j
|
public class SAQChart {
|
|
|
public static void main(String[] args) throws IOException {
|
XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\测评系统\\需求\\2.docx"));
|
Map<String, Object> textMap = new HashMap<String, Object>();
|
changeChart(document,textMap);
|
}
|
|
/**
|
* 替换SAQ图表数据
|
* @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("替换SAQ图表数据,初始化数据失败!");
|
return false;
|
}
|
//SAQ图表数据
|
changeSAQChart(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){
|
//获取命中数值
|
String p_sde23 = (String) textMap.get("P_SDE23");
|
String p_im24 = (String) textMap.get("P_IM24");
|
String p_incon25 = (String) textMap.get("P_INCON25");
|
//判断是否为空
|
if (checkParamsIsNull(p_sde23, p_im24, p_incon25)) {
|
return null;
|
}
|
Double P_SDE23 = Double.valueOf(p_sde23);
|
Double P_IM24 = Double.valueOf(p_im24);
|
Double P_INCON25 = Double.valueOf(p_incon25);
|
|
// 测试数据
|
List<SeriesData> seriesDatas = Arrays.asList(
|
new SeriesData("个人数值在此调整", Arrays.asList(
|
new RowData("个人数值在此调整", P_INCON25),
|
new RowData("个人数值在此调整", P_IM24),
|
new RowData("个人数值在此调整", P_SDE23)
|
)),
|
new SeriesData("主柱形数据（x轴）1", Arrays.asList(
|
new RowData("主柱形数据（x轴）1", 0),
|
new RowData("主柱形数据（x轴）1", 5),
|
new RowData("主柱形数据（x轴）1", 5)
|
)),
|
new SeriesData("主柱形数据（x轴）2", Arrays.asList(
|
new RowData("主柱形数据（x轴）2", 5),
|
new RowData("主柱形数据（x轴）2", 5),
|
new RowData("主柱形数据（x轴）2", 5)
|
)),
|
new SeriesData("辅助柱形数据", Arrays.asList(
|
new RowData("辅助柱形数据", 100),
|
new RowData("辅助柱形数据", 95),
|
new RowData("辅助柱形数据", 95)
|
)),
|
new SeriesData("Y轴", Arrays.asList(
|
new RowData("Y轴", 27),
|
new RowData("Y轴", 60),
|
new RowData("Y轴", 93)
|
))
|
);
|
return seriesDatas;
|
}
|
|
/**
|
* 替换word图表 散点图跟柱形图组合图形
|
* @param doc
|
* @param seriesDatas
|
*/
|
public static void changeSAQChart(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);
|
|
//散点图
|
if (!plot.getScatterChartList().isEmpty()) {
|
CTScatterChart scatterChart = plot.getScatterChartArray(0);
|
//System.out.println("散点图{"+plot.getScatterChartList().size()+"}");
|
//刷新内置excel数据
|
refreshExcel(seriesDatas, workbook, 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(scatterDatas, ser.getXVal(), ser.getYVal());
|
++i;
|
}
|
}
|
//柱形图
|
if (!plot.getBarChartList().isEmpty()) {
|
CTBarChart barChart = plot.getBarChartArray(0);
|
//System.out.println("柱形图{"+plot.getScatterChartList().size()+"}");
|
//刷新内置excel数据
|
refreshExcel(seriesDatas, workbook, sheet);
|
workbook.write(chart.getPackagePart().getOutputStream());
|
|
//柱形图数据源
|
List<SeriesData> barDatas = seriesDatas.subList(1,4);
|
//因为柱形图CTBarSer没按excel顺序走 调换数据位置保持对应
|
Collections.swap(barDatas, 1, 2);
|
int i = 0;
|
for (CTBarSer ser : barChart.getSerList()) {
|
//更新柱形图数据缓存
|
updateBarChart(barDatas.get(i), ser.getVal());
|
++i;
|
}
|
}
|
}
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.error("SAQGS替换word图表 散点图跟柱形图组合图形失败：{}",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()) {
|
// 第一行标题
|
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);
|
}
|
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 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);
|
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);
|
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);
|
}
|
|
|
/**
|
* 更新 柱形图 的缓存数据
|
*
|
* @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()+"}");
|
}
|
}
|
|
}
|
