From 0f877d7ba62205f94e615eb0ab1950b3350c1436 Mon Sep 17 00:00:00 2001
From: 林致杰 <1003392067@qq.com>
Date: 星期二, 10 十月 2023 17:06:23 +0800
Subject: [PATCH] LAQ支持
---
src/main/java/com/ots/project/tool/report/LAQ/LAQTemplate.java | 57 ++++
src/main/java/com/ots/common/utils/MapDataUtil.java | 27 ++
src/main/java/com/ots/common/enums/ReportTypeEnum.java | 2
src/main/java/com/ots/project/tool/report/LAQ/chart/LAQChart.java | 686 ++++++++++++++++++++++++++++++++++++++++++++++++++++
4 files changed, 771 insertions(+), 1 deletions(-)
diff --git a/src/main/java/com/ots/common/enums/ReportTypeEnum.java b/src/main/java/com/ots/common/enums/ReportTypeEnum.java
index 1d8c30e..2ebab18 100644
--- a/src/main/java/com/ots/common/enums/ReportTypeEnum.java
+++ b/src/main/java/com/ots/common/enums/ReportTypeEnum.java
@@ -5,7 +5,7 @@
public enum ReportTypeEnum {
Brief("Brief", "绠�鏄撶増"), Complete("Complete", "瀹屾暣鐗�"), SAQ("SAQ", "SAQ鎶ュ憡"), RuiLin("RuiLin", "鐫块偦鎶ュ憡"), CAQ("CAQ", "CAQ鎶ュ憡"),
JAQ("JAQ","JAQ鎶ュ憡"),MAQ("MAQ", "MAQ鎶ュ憡"),MAQV2("MAQV2", "MAQV2鎶ュ憡"),PAQ("PAQ", "PAQ鎶ュ憡"),MAQIAR("MAQIAR", "MAQIAR鎶ュ憡"),
- MAQTR("MAQTR", "MAQTR鎶ュ憡"),CIAQ("CIAQ", "CIAQ鎶ュ憡"),VAQ("VAQ", "VAQ鎶ュ憡");
+ MAQTR("MAQTR", "MAQTR鎶ュ憡"),CIAQ("CIAQ", "CIAQ鎶ュ憡"),VAQ("VAQ", "VAQ鎶ュ憡"),LAQ("LAQ", "LAQ鎶ュ憡");
private final String code;
private final String message;
ReportTypeEnum(String code, String message) {
diff --git a/src/main/java/com/ots/common/utils/MapDataUtil.java b/src/main/java/com/ots/common/utils/MapDataUtil.java
index a7f5015..8a00a9b 100644
--- a/src/main/java/com/ots/common/utils/MapDataUtil.java
+++ b/src/main/java/com/ots/common/utils/MapDataUtil.java
@@ -1,5 +1,8 @@
package com.ots.common.utils;
+import cn.hutool.core.collection.CollectionUtil;
+
import javax.servlet.http.HttpServletRequest;
+import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
@@ -32,4 +35,28 @@
}
return returnMap;
}
+
+ /**
+ * 瀵逛紶鍏ョ殑瀵硅薄杩涜鏁版嵁娓呮礂锛屽皢灞炴�у�间负null鍜�""鐨勫幓鎺夛紝鍏朵粬瀛楁鍚嶅拰灞炴�у�煎瓨鍏ap闆嗗悎
+ * requestParameters 涓鸿杞崲涓洪敭鍊煎鐨勫璞�
+ * dynamicPara 杞崲鍚庣殑閿�煎娣诲姞鍒扮殑鐩爣闆嗗悎
+ */
+ public static void objectToMap(Object requestParameters,Map<String, Object> dynamicPara) throws IllegalAccessException {
+ // 鑾峰彇f瀵硅薄瀵瑰簲绫讳腑鐨勬墍鏈夊睘鎬у煙
+ Field[] fields = requestParameters.getClass().getDeclaredFields();
+ for (Field field : fields) {
+ String varName = field.getName();
+ // 鑾峰彇鍘熸潵鐨勮闂帶鍒舵潈闄�
+ boolean accessFlag = field.isAccessible();
+ // 淇敼璁块棶鎺у埗鏉冮檺
+ field.setAccessible(true);
+ // 鑾峰彇鍦ㄥ璞涓睘鎬ields[i]瀵瑰簲鐨勫璞′腑鐨勫彉閲�
+ Object obj = field.get(requestParameters);
+ if (obj != null && StringUtils.isNotBlank(obj.toString()) || CollectionUtil.isNotEmpty((Iterable<?>) obj)) {
+ dynamicPara.put(varName, obj);
+ // 鎭㈠璁块棶鎺у埗鏉冮檺
+ field.setAccessible(accessFlag);
+ }
+ }
+ }
}
diff --git a/src/main/java/com/ots/project/tool/report/LAQ/LAQTemplate.java b/src/main/java/com/ots/project/tool/report/LAQ/LAQTemplate.java
new file mode 100644
index 0000000..eb1fd63
--- /dev/null
+++ b/src/main/java/com/ots/project/tool/report/LAQ/LAQTemplate.java
@@ -0,0 +1,57 @@
+package com.ots.project.tool.report.LAQ;
+
+import com.ots.framework.aspectj.lang.annotation.Excel;
+import lombok.Data;
+
+import java.math.BigDecimal;
+
+/**
+ * @description: LAQ瀵煎叆妯℃澘
+ * @author: zhijie
+ * @create: 2023-10-10 14:46
+ **/
+@Data
+public class LAQTemplate {
+
+ @Excel(name = "Name")
+ private String name;
+
+ @Excel(name = "E-mail")
+ private String email;
+
+ @Excel(name = "Scored on")
+ private String ScoredOn;
+
+ @Excel(name = "Company")
+ private String company;
+
+ @Excel(name = "OLAS")
+ private BigDecimal olas;
+
+ @Excel(name = "CA")
+ private BigDecimal ca;
+
+ @Excel(name = "BLA")
+ private BigDecimal bla;
+
+ @Excel(name = "NR")
+ private BigDecimal nr;
+
+ @Excel(name = "AR")
+ private BigDecimal ar;
+
+ @Excel(name = "OPEN")
+ private BigDecimal open;
+
+ @Excel(name = "CONS")
+ private BigDecimal cons;
+
+ @Excel(name = "EXTRA")
+ private BigDecimal extra;
+
+ @Excel(name = "AGREE")
+ private BigDecimal agree;
+
+ @Excel(name = "EMOSTA")
+ private BigDecimal emosta;
+}
\ No newline at end of file
diff --git a/src/main/java/com/ots/project/tool/report/LAQ/chart/LAQChart.java b/src/main/java/com/ots/project/tool/report/LAQ/chart/LAQChart.java
new file mode 100644
index 0000000..8f5eca0
--- /dev/null
+++ b/src/main/java/com/ots/project/tool/report/LAQ/chart/LAQChart.java
@@ -0,0 +1,686 @@
+package com.ots.project.tool.report.LAQ.chart;
+
+import com.ots.common.enums.CapabilityEnum;
+import com.ots.common.utils.StringUtils;
+import com.ots.framework.config.EssConfig;
+import com.ots.project.tool.PdfUtil;
+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.ss.usermodel.Cell;
+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.FileInputStream;
+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;
+
+/**
+ * @description: LAQ鍥捐〃
+ * @author: zhijie
+ * @create: 2023-10-10 15:01
+ **/
+@Slf4j
+public class LAQChart {
+
+
+ public static void main(String[] args) throws IOException {
+ try {
+// OutputStream os = new FileOutputStream("D:\\娴嬭瘎绯荤粺\\闇�姹俓\2023\\5.棰嗗娼滃姏鎶ュ憡\\report\\LAQ鑻辨枃鎶ュ憡-20230926.docx");
+// XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\娴嬭瘎绯荤粺\\闇�姹俓\2023\\5.棰嗗娼滃姏鎶ュ憡\\report\\LAQ鑻辨枃鎶ュ憡-20230926.docx"));
+//
+// String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\MAQ缁勫埆瀵煎叆鏁版嵁妯℃澘 -3缁�(1).xlsx";
+// //鍙樻洿鍥捐〃鏁版嵁
+// changeMAQTRChart(document,fileName);
+// document.write(os);
+
+ OutputStream os = new FileOutputStream("D:\\娴嬭瘎绯荤粺\\闇�姹俓\2023\\5.棰嗗娼滃姏鎶ュ憡\\report\\LAQ鑻辨枃鎶ュ憡-111.docx");
+// PdfUtil.convertPDF("D:\\娴嬭瘎绯荤粺\\闇�姹俓\2023\\5.棰嗗娼滃姏鎶ュ憡\\report\\LAQ鑻辨枃鎶ュ憡-20230926.docx");
+ XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\娴嬭瘎绯荤粺\\闇�姹俓\2023\\5.棰嗗娼滃姏鎶ュ憡\\report\\LAQ鑻辨枃鎶ュ憡-20230926.docx"));
+
+ 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 TreeMap<>();
+ try {
+ XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fileName));
+ Map<String,Map<Integer,List<Double>>> dataMap = new TreeMap<>();
+ 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<>();
+ //缁熻浜烘暟
+ int person = 0;
+ //琛�
+ for (int j = 1; j <= sheet.getLastRowNum(); j++) {
+ XSSFRow row = sheet.getRow(j);
+ if(isEmptyRow(row)){
+ break;
+ }
+ //鍒�
+ for (int k = 1; k < row.getLastCellNum(); k++) {
+ XSSFCell cell = row.getCell(k);
+ if(cell == null){
+ break;
+ }
+ //涓嶆槸鍒嗘暟 蹇界暐
+ Double cellValue = null;
+ try {
+ switch (cell.getCellType()) {
+ case STRING:
+ cellValue = Double.valueOf(cell.getStringCellValue());
+ break;
+ case NUMERIC:
+ cellValue = cell.getNumericCellValue();
+ break;
+ }
+ }catch (Exception e){
+ log.error("鑾峰彇鍒嗘暟澶辫触,{}"+e.getMessage());
+ e.printStackTrace();
+ }
+
+ //褰掔被涓��
+ if(map.get(k) != null){
+ map.get(k).add(cellValue);
+ }else {
+ List<Double> list = new ArrayList<>();
+ list.add(cellValue);
+ map.put(k,list);
+ }
+ }
+ person++;
+ }
+
+ //璁剧疆浜烘暟
+ List<Double> list = new ArrayList<>();
+ list.add(Double.valueOf(person));
+ map.put(999,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();
+ Map<Integer,Double> valMap = map.getValue();
+ valSeriesData.setName(map.getKey()+"锛坣="+ valMap.get(999).intValue() +"锛�");
+ 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();
+ Map<Integer,Double> valMap = map.getValue();
+ valSeriesData.setName(map.getKey()+"锛坣="+valMap.get(999).intValue()+"锛�");
+ 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 鏁版嵁婧恊xcel
+ */
+ 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());
+ }
+ }
+
+ /**
+ * 绌鸿鍒ゆ柇
+ * @param row
+ * @return
+ */
+ public static boolean isEmptyRow(XSSFRow row){
+ if(row == null || row.toString().isEmpty()){
+ return true;
+ }else{
+ Iterator<Cell> it = row.iterator();
+ boolean isEmpty = true;
+ while (it.hasNext()){
+ Cell cell = it.next();
+ if(cell.getCellType() != CellType.BLANK){
+ isEmpty = false;
+ break;
+ }
+ }
+ return isEmpty;
+ }
+ }
+
+
+ /**
+ * 鏇存柊鍥捐〃鐨勫叧鑱� 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--) {
+ if(sheet.getRow(idx) == null){
+ continue;
+ }
+ 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()+"}");
+ }
+ }
+
+}
--
Gitblit v1.9.1