package com.ots.project.tool.report.MAQTR.chart;
|
|
import com.alibaba.fastjson.JSON;
|
import com.ots.common.enums.CapabilityEnum;
|
import com.ots.common.utils.StringUtils;
|
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.hssf.usermodel.HSSFWorkbook;
|
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.*;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
|
|
import java.io.*;
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
/**
|
* @description: MAQTR图表 带直线和数据标记散点图数据变更
|
* @author: zhijie
|
* @create: 2022-06-16 22:01
|
**/
|
@Slf4j
|
public class MAQTRChart {
|
|
|
public static void main(String[] args) throws IOException {
|
try {
|
OutputStream os = new FileOutputStream("C:\\Users\\大头\\Desktop\\MAQ组别报告\\林_PAQ_IA_CN2.docx");
|
XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\大头\\Desktop\\MAQ组别报告\\终稿【0607中文版】MAQ组别对比报告.docx"));
|
|
String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\MAQ组别导入数据模板 (2).xlsx";
|
//变更图表数据
|
changeMAQTRChart(document,fileName);
|
document.write(os);
|
/* String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\组别对比报告测试数据.xlsx";
|
//replaceContent(fileName);
|
initDataNew(fileName,1);*/
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
}
|
|
|
/**
|
* 计算数据并生成模板
|
*/
|
public static Map<String,Map<Integer,Double>> replaceContent(String fileName){
|
//平均值集合
|
Map<String,Map<Integer,Double>> averageMap = new HashMap<>();
|
try {
|
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fileName));
|
Map<String,Map<Integer,List<Double>>> dataMap = new HashMap<>();
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
XSSFSheet sheet = wb.getSheetAt(i);
|
String companyName = sheet.getRow(1).getCell(0).getStringCellValue();
|
Map<Integer,List<Double>> map = new HashMap<>();
|
//行
|
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
|
XSSFRow row = sheet.getRow(j);
|
if(row == null){
|
continue;
|
}
|
//列
|
for (int k = 1; k < row.getLastCellNum(); k++) {
|
XSSFCell cell = row.getCell(k);
|
if(cell == null){
|
continue;
|
}
|
/*
|
if (CellType.NUMERIC == cell.getCellType()) {
|
System.out.println(cell.getNumericCellValue());
|
} else if (CellType.STRING == cell.getCellType()) {
|
System.out.println(cell.getStringCellValue());
|
}
|
*/
|
switch (cell.getCellType()) {
|
case STRING:
|
continue;
|
}
|
//table1 多个答题结果处理
|
if(map.get(k) != null){
|
map.get(k).add(cell.getNumericCellValue());
|
}else {
|
List<Double> list = new ArrayList<>();
|
list.add(cell.getNumericCellValue());
|
map.put(k,list);
|
}
|
}
|
}
|
dataMap.put(companyName,map);
|
}
|
//平均值集合
|
for (Map.Entry<String, Map<Integer, List<Double>>> map : dataMap.entrySet()) {
|
//33个个性集合值
|
Map<Integer,List<Double>> valMap = map.getValue();
|
//统计个性平均值
|
Map<Integer,Double> doubleMap = new HashMap<>();
|
for (Map.Entry<Integer, List<Double>> val : valMap.entrySet()) {
|
List<Double> doubleList = val.getValue();
|
//计算平均值
|
double average = doubleList.stream().mapToDouble(e -> e).average().orElse(0.00);
|
doubleMap.put(val.getKey(),average);
|
}
|
averageMap.put(map.getKey(),doubleMap);
|
}
|
//System.out.println(averageMap);
|
}catch (Exception e){
|
e.printStackTrace();
|
log.error("计算平均值出错!");
|
}
|
return averageMap;
|
}
|
|
/**
|
* 初始化报告数据
|
* @param fileName 数据源文件名
|
* @param index 模板sheet索引
|
* @return
|
*/
|
public static List<SeriesData> initDataNew(String fileName,Integer index){
|
Map<String,Map<Integer,Double>> dataMap = replaceContent(fileName);
|
List<SeriesData> seriesDatas = new ArrayList<>();
|
if(dataMap != null){
|
int oneIndex = 22;
|
int twoIndex = 33;
|
//1-22个性
|
if(index == 0){
|
//个性 [第一行]
|
SeriesData title = new SeriesData();
|
String name = "个性";
|
title.setName(name);
|
List<RowData> rowDataList = new ArrayList<>();
|
for (int i = oneIndex; i > 0; i--) {
|
RowData rowData = new RowData();
|
rowData.setName(name);
|
rowData.setValueStr(CapabilityEnum.codeOf(i));
|
rowDataList.add(rowData);
|
}
|
title.setValue(rowDataList);
|
seriesDatas.add(title);
|
//动态值
|
for (Map.Entry<String,Map<Integer,Double>> map : dataMap.entrySet()) {
|
SeriesData valSeriesData = new SeriesData();
|
valSeriesData.setName(map.getKey());
|
Map<Integer,Double> valMap = map.getValue();
|
List<RowData> valRowDataList = new ArrayList<>();
|
for (int i = oneIndex; i > 0; i--) {
|
RowData rowData = new RowData();
|
rowData.setName(map.getKey());
|
rowData.setValue(valMap.get(i));
|
valRowDataList.add(rowData);
|
}
|
valSeriesData.setValue(valRowDataList);
|
seriesDatas.add(valSeriesData);
|
}
|
//固定辅助列
|
String auxiliary = "辅助";
|
SeriesData auxiliaryTitle = new SeriesData();
|
auxiliaryTitle.setName(auxiliary);
|
List<RowData> auxiliaryRowDataList = new ArrayList<>();
|
for (int i = 1; i <= oneIndex; i++) {
|
RowData rowData = new RowData();
|
rowData.setName(auxiliary);
|
rowData.setValue(i);
|
auxiliaryRowDataList.add(rowData);
|
}
|
auxiliaryTitle.setValue(auxiliaryRowDataList);
|
seriesDatas.add(auxiliaryTitle);
|
//坐标线
|
String blank = " ";
|
SeriesData blankTitle = new SeriesData();
|
blankTitle.setName(blank);
|
List<RowData> blankTitleRowDataList = new ArrayList<>();
|
for (int i = 1; i <= oneIndex; i++) {
|
RowData rowData = new RowData();
|
rowData.setName(blank);
|
rowData.setValue(0);
|
blankTitleRowDataList.add(rowData);
|
}
|
blankTitle.setValue(blankTitleRowDataList);
|
seriesDatas.add(blankTitle);
|
}
|
//23-33个性
|
if(index == 1){
|
//个性 [第一行]
|
SeriesData title = new SeriesData();
|
String name = "个性";
|
title.setName(name);
|
List<RowData> rowDataList = new ArrayList<>();
|
for (int i = twoIndex; i > oneIndex; i--) {
|
RowData rowData = new RowData();
|
rowData.setName(name);
|
rowData.setValueStr(CapabilityEnum.codeOf(i));
|
rowDataList.add(rowData);
|
}
|
title.setValue(rowDataList);
|
seriesDatas.add(title);
|
//动态值
|
for (Map.Entry<String,Map<Integer,Double>> map : dataMap.entrySet()) {
|
SeriesData valSeriesData = new SeriesData();
|
valSeriesData.setName(map.getKey());
|
Map<Integer,Double> valMap = map.getValue();
|
List<RowData> valRowDataList = new ArrayList<>();
|
for (int i = twoIndex; i > oneIndex; i--) {
|
RowData rowData = new RowData();
|
rowData.setName(map.getKey());
|
rowData.setValue(valMap.get(i));
|
valRowDataList.add(rowData);
|
}
|
valSeriesData.setValue(valRowDataList);
|
seriesDatas.add(valSeriesData);
|
}
|
//固定辅助列
|
String auxiliary = "辅助";
|
SeriesData auxiliaryTitle = new SeriesData();
|
auxiliaryTitle.setName(auxiliary);
|
List<RowData> auxiliaryRowDataList = new ArrayList<>();
|
for (int i = 1; i <= (twoIndex-oneIndex); i++) {
|
RowData rowData = new RowData();
|
rowData.setName(auxiliary);
|
rowData.setValue(i);
|
auxiliaryRowDataList.add(rowData);
|
}
|
auxiliaryTitle.setValue(auxiliaryRowDataList);
|
seriesDatas.add(auxiliaryTitle);
|
//坐标线
|
String blank = " ";
|
SeriesData blankTitle = new SeriesData();
|
blankTitle.setName(blank);
|
List<RowData> blankTitleRowDataList = new ArrayList<>();
|
for (int i = 1; i <= (twoIndex-oneIndex); i++) {
|
RowData rowData = new RowData();
|
rowData.setName(blank);
|
rowData.setValue(0);
|
blankTitleRowDataList.add(rowData);
|
}
|
blankTitle.setValue(blankTitleRowDataList);
|
seriesDatas.add(blankTitle);
|
}
|
}
|
return seriesDatas;
|
}
|
|
/**
|
* 初始化报告数据
|
* @param fileName 数据源文件名
|
* @param index 模板sheet索引
|
* @return
|
*/
|
public static List<SeriesData> initData(String fileName,Integer index){
|
List<SeriesData> seriesDatas = new ArrayList<>();
|
try {
|
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fileName));
|
XSSFSheet sheet1 = wb.getSheetAt(index);
|
|
//数据处理 先获取表头长度 遍历表头行数、一一对应放进表头集合里
|
Map<Integer,SeriesData> map = new HashMap<>();
|
XSSFRow row = sheet1.getRow(0);
|
for (int i = 0; i < row.getLastCellNum(); i++) {
|
//为空忽略
|
/*if(StringUtils.isEmpty(getName(row.getCell(i)))){
|
continue;
|
}*/
|
SeriesData seriesData = new SeriesData(getName(row.getCell(i)));
|
map.put(i,seriesData);
|
//读取每一行每一列
|
for (int j = 1; j <= sheet1.getLastRowNum(); j++) {
|
//每一行
|
XSSFRow row1 = sheet1.getRow(j);
|
RowData rowData = new RowData();
|
if(seriesData.getValue() == null){
|
List<RowData> rowDatas = new ArrayList<>();
|
//设置表头
|
rowData.setName(getName(row.getCell(i)));
|
//根据类型判断赋值
|
switch (row1.getCell(i).getCellType()) {
|
case STRING:
|
rowData.setValueStr(row1.getCell(i).getStringCellValue());
|
break;
|
case NUMERIC:
|
rowData.setValue(row1.getCell(i).getNumericCellValue());
|
break;
|
default:
|
break;
|
}
|
rowDatas.add(rowData);
|
seriesData.setValue(rowDatas);
|
}else{
|
//设置表头
|
rowData.setName(getName(row.getCell(i)));
|
//根据类型判断赋值
|
switch (row1.getCell(i).getCellType()) {
|
case STRING:
|
rowData.setValueStr(row1.getCell(i).getStringCellValue());
|
break;
|
case NUMERIC:
|
rowData.setValue(row1.getCell(i).getNumericCellValue());
|
break;
|
default:
|
break;
|
}
|
seriesData.getValue().add(rowData);
|
}
|
}
|
seriesDatas.add(seriesData);
|
}
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
return seriesDatas;
|
}
|
|
/**
|
* 根据单元格类型取值
|
* @return 返回字符值
|
*/
|
private static String getName(XSSFCell cell){
|
String name = null;
|
switch (cell.getCellType()) {
|
case STRING:
|
name = cell.getStringCellValue();
|
break;
|
case NUMERIC:
|
name = cell.getNumericCellValue()+"";
|
break;
|
}
|
return name;
|
}
|
|
/**
|
* 替换MAQTR图表数据
|
* @param document 文档对象
|
* @param fileName 数据源excel
|
*/
|
public static void changeMAQTRChart(XWPFDocument document,String fileName) {
|
if(StringUtils.isEmpty(fileName)){
|
return;
|
}
|
try {
|
List<POIXMLDocumentPart> relations = document.getRelations();
|
for (POIXMLDocumentPart part : relations) {
|
if (part instanceof XWPFChart) {
|
// 图表元素
|
XWPFChart chart = (XWPFChart) part;
|
String partName = chart.getPackagePart().getPartName().getName();
|
|
Integer index = 0;
|
if(partName.contains("chart1.xml")){
|
index = 0;
|
}else if(partName.contains("chart2.xml")){
|
index = 1;
|
}
|
//获取模板数据
|
List<SeriesData> seriesDatas = initDataNew(fileName,index);
|
//List<SeriesData> seriesDatas = initData(fileName,index);
|
//log.error(JSON.toJSONString(seriesDatas));
|
//数据长度
|
int size = seriesDatas.size();
|
// 查看里面的图表数据，才能知道是什么图表
|
CTPlotArea plot = chart.getCTChart().getPlotArea();
|
// excel内置表格
|
XSSFWorkbook workbook = chart.getWorkbook();
|
XSSFSheet sheet = workbook.getSheetAt(0);
|
|
//散点图
|
if (!plot.getScatterChartList().isEmpty()) {
|
CTScatterChart scatterChart = plot.getScatterChartArray(0);
|
|
//去除多余数据源
|
int remain = 8 - size;
|
if(remain != 0){
|
for (int j = 1; j <= remain; j++) {
|
int removeSize = scatterChart.getSerList().size() - j - 1;
|
scatterChart.getSerList().remove(removeSize);
|
//删除excel多余列
|
//deleteColumn(sheet,8 - remain);
|
}
|
}
|
|
//刷新内置excel数据
|
refreshExcel(seriesDatas, sheet);
|
workbook.write(chart.getPackagePart().getOutputStream());
|
|
//散点图数据源 XY坐标
|
for (int i = 0; i < scatterChart.getSerList().size(); i++) {
|
//根据图表数据线获取对应数据源 数值是X轴 辅助Y轴
|
List<SeriesData> scatterDatas = new ArrayList<SeriesData>();
|
//最后一个数据源需拿取最后一列0000 作为x轴
|
if((i+1) == size-2){
|
scatterDatas.add(seriesDatas.get(size-1));
|
scatterDatas.add(seriesDatas.get(size-2));
|
}else{
|
scatterDatas.add(seriesDatas.get(i+1));
|
scatterDatas.add(seriesDatas.get(size-2));
|
}
|
|
CTScatterSer ser = scatterChart.getSerList().get(i);
|
updateScatterChart(scatterDatas,ser.getTx(), ser.getXVal(), ser.getYVal());
|
}
|
}
|
}
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.error("MAQTR替换word图表 散点图图形失败：{}",e);
|
}
|
}
|
|
/**
|
* 删除列
|
* @param sheet
|
* @param columnToDelete
|
*/
|
public static void deleteColumn(XSSFSheet sheet, int columnToDelete) {
|
for (int r = 0; r <= sheet.getLastRowNum(); r++) {
|
XSSFRow row = sheet.getRow(r);
|
for (int c = columnToDelete; c <= row.getLastCellNum(); c++) {
|
XSSFCell cOld = row.getCell(c);
|
if (cOld != null) {
|
row.removeCell(cOld);
|
}
|
XSSFCell cNext = row.getCell(c + 1);
|
if (cNext != null) {
|
XSSFCell cNew = row.createCell(c, cNext.getCellType());
|
cloneCell(cNew, cNext);
|
if (r == 0) {
|
sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
|
}
|
}
|
}
|
}
|
}
|
|
/**
|
* 右边列左移,样式值设置
|
* @param cNew
|
* @param cOld
|
*/
|
private static void cloneCell(XSSFCell cNew, XSSFCell cOld) {
|
cNew.setCellComment(cOld.getCellComment());
|
cNew.setCellStyle(cOld.getCellStyle());
|
if (CellType.BOOLEAN == cNew.getCellType()) {
|
cNew.setCellValue(cOld.getBooleanCellValue());
|
} else if (CellType.NUMERIC == cNew.getCellType()) {
|
cNew.setCellValue(cOld.getNumericCellValue());
|
} else if (CellType.STRING == cNew.getCellType()) {
|
cNew.setCellValue(cOld.getStringCellValue());
|
} else if (CellType.ERROR == cNew.getCellType()) {
|
cNew.setCellValue(cOld.getErrorCellValue());
|
} else if (CellType.FORMULA == cNew.getCellType()) {
|
cNew.setCellValue(cOld.getCellFormula());
|
}
|
}
|
|
/**
|
* 更新图表的关联 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{
|
cell.setCellValue(cellValu.value);
|
}
|
|
}
|
//删除多余行数
|
int lastRowNum = sheet.getLastRowNum();
|
//log.error("lastRowNum:{},size:{}",lastRowNum,size);
|
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,CTSerTx serTitle, CTAxDataSource xDataSource,
|
CTNumDataSource yDataSource) {
|
|
// 更新系列标题
|
if(StringUtils.isNotEmpty(seriesDatas.get(0).getName())){
|
serTitle.getStrRef().setF(serTitle.getStrRef().getF());
|
serTitle.getStrRef().getStrCache().getPtArray(0).setV(seriesDatas.get(0).getName());
|
}
|
|
//获取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);
|
}
|
|
|
/**
|
* 替换 形如： 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()+"}");
|
}
|
}
|
|
}
|
