package com.ots.project.tool.report.MAQTR.chart;
|
|
import com.alibaba.fastjson.JSON;
|
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.XWPFChart;
|
import org.apache.poi.xwpf.usermodel.XWPFDocument;
|
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_CN1.docx");
|
XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\大头\\Desktop\\MAQ组别报告\\终稿【0607中文版】MAQ组别对比报告.docx"));
|
|
String fileName = "C:\\Users\\大头\\Desktop\\MAQ组别报告\\MAQ组别导入数据模板.xlsx";
|
//变更图表数据
|
changeMAQTRChart(document,fileName);
|
document.write(os);
|
}catch (Exception e){
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 初始化报告数据
|
* @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 = initData(fileName,index);
|
//数据长度
|
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.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, 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);
|
}
|
|
|
/**
|
* 替换 形如： 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()+"}");
|
}
|
}
|
|
}
|
