package com.ots.project.tool.report.LAQ.chart;
|
|
import com.ots.common.utils.StringUtils;
|
import com.ots.common.utils.poi.ExcelUtil;
|
import com.ots.project.tool.report.LAQ.LAQTemplate;
|
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.openxml4j.util.ZipSecureFile;
|
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;
|
|
/**
|
* 全景图图表渲染
|
*/
|
@Slf4j
|
public class LAQAllChart {
|
|
/**
|
* 测试模板
|
* @param args
|
* @throws Exception
|
*/
|
public static void main(String[] args) throws Exception {
|
System.out.println("启动测试..");
|
String dataPath = "D:\\桌面文件\\LAQ全景图报告\\LAQ导入测试数据.xlsx";
|
// 获取导入数据
|
File file = new File(dataPath);
|
InputStream in = new FileInputStream(file);
|
ExcelUtil<LAQTemplate> util = new ExcelUtil<>(LAQTemplate.class);
|
List<LAQTemplate> laqTemplateList = util.importExcel(in);
|
|
|
ZipSecureFile.setMinInflateRatio(0.001);
|
try (OutputStream os = new FileOutputStream("D:\\桌面文件\\可删除\\LAQ英文报告"+UUID.randomUUID().toString()+".docx");
|
XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\桌面文件\\LAQ英文报告-叠加版本.docx"))) {
|
|
Map<String, Object> textMap = new HashMap<>();
|
textMap.put("allList",laqTemplateList);
|
changeChart(document,textMap);
|
document.write(os);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
|
|
System.out.println("处理完成...");
|
}
|
|
|
/**
|
* 替换LAQ图表数据
|
* @param document 文档对象
|
* @param textMap 数据源
|
*/
|
public static void changeChart(XWPFDocument document, Map<String, Object> textMap) {
|
// Map<Integer, List<SeriesData>> seriesDatas = initData(textMap);
|
// changeChart(document,seriesDatas,textMap);
|
System.out.println("进入全景图渲染模式");
|
try{
|
List<LAQTemplate> laqTemplateList = (List<LAQTemplate>)textMap.get("allList");
|
changeChartNew(document,laqTemplateList);
|
System.out.println("全景图渲染完成");
|
}
|
catch (Exception ex)
|
{
|
log.error("LAQ全景图异常："+ex.getMessage());
|
}
|
|
}
|
|
|
public static void changeChartNew(XWPFDocument document,List<LAQTemplate> list) {
|
if(list.size()<=0){
|
return;
|
}
|
try {
|
//获取数据
|
Map<Integer,List<SeriesData>> seriesDataMapTop = getSeriesDataByType(list,1);
|
Map<Integer,List<SeriesData>> seriesDataMapMiddle = getSeriesDataByType(list,2);
|
Map<Integer,List<SeriesData>> seriesDataMapBottom = getSeriesDataByType(list,3);
|
|
//获取标签
|
Map<Integer,String> seriesDataLablesTop = getSeriesDataLablesByType(list,1);
|
Map<Integer,String> seriesDataLablesMiddle = getSeriesDataLablesByType(list,2);
|
Map<Integer,String> seriesDataLablesBottom = getSeriesDataLablesByType(list,3);
|
|
List<POIXMLDocumentPart> relations = document.getRelations();
|
int barIndex = 1;
|
|
//三个图表的个数
|
int topIndex =1;
|
int middleIndex =1;
|
int bottomIndex =1;
|
|
for (POIXMLDocumentPart part : relations) {
|
if (part instanceof XWPFChart) {
|
// 图表元素
|
XWPFChart chart = (XWPFChart) part;
|
// 查看里面的图表数据，才能知道是什么图表
|
CTPlotArea plot = chart.getCTChart().getPlotArea();
|
getChartType(chart);
|
// excel内置表格
|
XSSFWorkbook workbook = chart.getWorkbook();
|
XSSFSheet sheet = workbook.getSheetAt(0);
|
|
//散点图
|
if (!plot.getScatterChartList().isEmpty()) {
|
CTScatterChart scatterChart = plot.getScatterChartArray(0);
|
//第一个散点图1-90，第二个91-180，第三个181-270
|
System.out.println("第"+barIndex+"个散点图");
|
|
String lables = "";//散点图的显示标签
|
List<SeriesData> seriesDatas = new ArrayList<>();
|
|
// 获取图表的值轴,并把最小值设置为-0，
|
// chart.getAxes().get(1) 获取的纵坐标，chart.getAxes().get(0) 获取的是横坐标
|
chart.getAxes().get(1).setMinimum(0);
|
Double maximum = chart.getAxes().get(0).getMaximum();
|
|
Double minimum = chart.getAxes().get(0).getMinimum();
|
// System.out.println("X轴最小值是："+minimum+",最大值是："+maximum);
|
|
//判断图表所在位置
|
if(minimum>=67)
|
{
|
seriesDatas = seriesDataMapTop.get(topIndex);
|
lables = seriesDataLablesTop.get(topIndex);
|
topIndex++;
|
// System.out.println("第一页图表个数："+topIndex+"，标签："+lables+";"+"X轴最小值是："+minimum+",最大值是："+maximum);
|
}
|
else if(minimum>=33)
|
{
|
seriesDatas = seriesDataMapMiddle.get(middleIndex);
|
lables = seriesDataLablesMiddle.get(middleIndex);
|
// System.out.println("第二页图表个数："+middleIndex);
|
|
middleIndex++;
|
}
|
else
|
{
|
seriesDatas = seriesDataMapBottom.get(bottomIndex);
|
lables = seriesDataLablesBottom.get(bottomIndex);
|
bottomIndex++;
|
// System.out.println("第三页图表个数："+bottomIndex);
|
}
|
|
|
barIndex++;
|
|
|
if(seriesDatas==null)
|
{
|
for (CTScatterSer ser : scatterChart.getSerList()) {
|
ser.getTx().getStrRef().getStrCache().getPtArray(0).setV("");
|
}
|
continue;
|
}
|
|
//刷新内置excel数据
|
refreshExcel(seriesDatas, sheet);
|
workbook.write(chart.getPackagePart().getOutputStream());
|
int i = 0;
|
for (CTScatterSer ser : scatterChart.getSerList()) {
|
//更新散点图缓存
|
updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());
|
ser.getTx().getStrRef().getStrCache().getPtArray(0).setV(lables);//设置标签
|
++i;
|
}
|
|
}
|
|
|
|
}
|
}
|
|
|
System.out.println("第一页图表个数："+(topIndex-1));
|
System.out.println("第二页图表个数："+(middleIndex-1));
|
System.out.println("第三页图表个数："+(bottomIndex-1));
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.error("LAQ.替换word图表 散点图图形失败：{}",e);
|
}
|
}
|
|
/**
|
* 更新 散点图 缓存数据
|
*
|
* @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();
|
|
|
System.out.println("[有数据]条数 x:"+xNumCnt+" y:"+yNumCnt);
|
|
//获取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);
|
if(cellValu.value == -1){
|
val.setV(null);
|
}else {
|
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);
|
if(cellValu.value == -1){
|
val.setV(null);
|
}else {
|
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;
|
}
|
|
/**
|
* 更新图表里 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{
|
if(cellValu.value != -1){
|
cell.setCellValue(cellValu.value);
|
}else{
|
cell.setCellValue((String) null);
|
}
|
}
|
|
}
|
//删除多余行数
|
int lastRowNum = sheet.getLastRowNum();
|
//log.error("lastRowNum:{},size:{}",lastRowNum,size);
|
if (lastRowNum > size) {
|
for (int idx = lastRowNum; idx > size; idx--) {
|
if(sheet.getRow(idx) == null){
|
continue;
|
}
|
sheet.removeRow(sheet.getRow(idx));
|
}
|
}
|
}
|
}
|
|
/**
|
* 获取指定类型的SeriesData。
|
* @param list
|
* @param type
|
* @return
|
*/
|
public static Map<Integer,List<SeriesData>> getSeriesDataByType(List<LAQTemplate> list,Integer type)
|
{
|
Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
|
int dataIndex =1;
|
for (int i = 0; i < list.size(); i++)
|
{
|
LAQTemplate laqTemplate = list.get(i);
|
Double olas = Double.valueOf(laqTemplate.getOlas().toString());
|
Double perfomance;
|
if(laqTemplate.getPerfomance()!= null)
|
{
|
perfomance = Double.valueOf(laqTemplate.getPerfomance().toString());
|
}
|
else {
|
perfomance = 0.0;
|
}
|
|
|
|
|
|
//如果y轴小于1，要设置成3，往上移动一下
|
if(perfomance<=1.2)
|
{
|
perfomance=perfomance+1.2;
|
}
|
//如果y轴大于97，设置成97，往下移动一点，好看
|
if(perfomance>=99)
|
{
|
perfomance=99.0;
|
}
|
|
if(type==1)
|
{
|
//x轴要往右边移动
|
if(olas>67.0&olas<=70.0)
|
{
|
olas=olas+1.5;
|
}
|
|
if(olas>=99.0)
|
{
|
olas = olas-2.0;
|
}
|
}
|
|
if(type==2)
|
{
|
//x轴要往右边移动
|
if(olas>=33.0&olas<=35.0)
|
{
|
olas= olas+1;
|
}
|
}
|
|
if(type==3)
|
{
|
//x轴要往右边移动
|
if(olas>0.0 && olas<=3.0)
|
{
|
olas= olas + 1;
|
}
|
|
}
|
|
List<SeriesData> seriesDatas = new ArrayList<>();
|
seriesDatas = Arrays.asList(
|
new SeriesData("X 值", Arrays.asList(
|
new RowData("X 值", olas)
|
)),
|
new SeriesData("Y 值", Arrays.asList(
|
new RowData("Y 值", perfomance)
|
))
|
);
|
|
if(type==1)
|
{
|
if(olas>67)
|
{
|
seriesDataMap.put(dataIndex,seriesDatas);
|
dataIndex++;
|
}
|
}
|
|
if(type==2)
|
{
|
if(olas<=67 && olas>33)
|
{
|
seriesDataMap.put(dataIndex,seriesDatas);
|
dataIndex++;
|
}
|
}
|
|
if(type==3)
|
{
|
if(olas<=33)
|
{
|
seriesDataMap.put(dataIndex,seriesDatas);
|
dataIndex++;
|
}
|
}
|
|
}
|
|
return seriesDataMap;
|
}
|
|
/**
|
* 获取散点图的所有标签
|
* @param list
|
* @param type
|
* @return
|
*/
|
public static Map<Integer,String> getSeriesDataLablesByType(List<LAQTemplate> list,Integer type)
|
{
|
Map<Integer,String> seriesDataMapLables = new HashMap<>();
|
int dataIndex =1;
|
for (int i = 0; i < list.size(); i++)
|
{
|
LAQTemplate laqTemplate = list.get(i);
|
Double olas = Double.valueOf(laqTemplate.getOlas().toString());
|
Double perfomance;
|
if(laqTemplate.getPerfomance()!= null)
|
{
|
perfomance = Double.valueOf(laqTemplate.getPerfomance().toString());
|
}
|
else {
|
perfomance = 0.0;
|
}
|
|
|
String olasStr = olas.toString();
|
String perfomanceStr = perfomance.toString();
|
|
// 判断小数部分是否为0
|
if (Math.abs(olas - Math.floor(olas)) < 1e-9) {
|
// 如果小数部分接近于0，则取整数部分
|
olasStr = String.valueOf(olas.intValue());
|
}
|
|
if (Math.abs(perfomance - Math.floor(perfomance)) < 1e-9) {
|
// 如果小数部分接近于0，则取整数部分
|
perfomanceStr = String.valueOf(perfomance.intValue());
|
}
|
|
if(type==1)
|
{
|
if(olas>67)
|
{
|
seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"\n("+olasStr+"-"+perfomanceStr+")");
|
dataIndex++;
|
}
|
}
|
|
if(type==2)
|
{
|
if(olas<=67 && olas>33)
|
{
|
seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"\n("+olasStr+"-"+perfomanceStr+")");
|
dataIndex++;
|
}
|
}
|
|
if(type==3)
|
{
|
if(olas<=33)
|
{
|
seriesDataMapLables.put(dataIndex,laqTemplate.getName()+"\n("+olasStr+"-"+perfomanceStr+")");
|
|
dataIndex++;
|
}
|
}
|
}
|
|
return seriesDataMapLables;
|
}
|
|
|
/**
|
* 获取图表有哪些类型
|
* @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()+"}");
|
}
|
}
|
}
|
