From ce7c778150b82b7106e9edebbb6d69036bd389f3 Mon Sep 17 00:00:00 2001
From: linzhijie <1003392067@qq.com>
Date: 星期三, 17 五月 2023 14:29:51 +0800
Subject: [PATCH] 短信域名变更
---
src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java | 257 ++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 233 insertions(+), 24 deletions(-)
diff --git a/src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java b/src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java
index ac49bee..41f212c 100644
--- a/src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java
+++ b/src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java
@@ -1,6 +1,7 @@
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;
@@ -8,13 +9,13 @@
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.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.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import java.io.*;
@@ -33,15 +34,16 @@
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涓枃鐗堛�慚AQ缁勫埆瀵规瘮鎶ュ憡.docx"));
+ OutputStream os = new FileOutputStream("C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\鏋梍PAQ_IA_CN2.docx");
+ XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\缁堢銆�0607涓枃鐗堛�慚AQ缁勫埆瀵规瘮鎶ュ憡(1).docx"));
- String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\MAQ缁勫埆瀵煎叆鏁版嵁妯℃澘.xlsx";
+ String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\MAQ缁勫埆瀵煎叆鏁版嵁妯℃澘 -3缁�(1).xlsx";
//鍙樻洿鍥捐〃鏁版嵁
changeMAQTRChart(document,fileName);
- document.write(os);*/
- String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\缁勫埆瀵规瘮鎶ュ憡娴嬭瘯鏁版嵁.xlsx";
- replaceContent(fileName);
+ document.write(os);
+/* String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\缁勫埆瀵规瘮鎶ュ憡娴嬭瘯鏁版嵁.xlsx";
+ //replaceContent(fileName);
+ initDataNew(fileName,1);*/
}catch (Exception e){
e.printStackTrace();
}
@@ -51,43 +53,216 @@
/**
* 璁＄畻鏁版嵁骞剁敓鎴愭ā鏉�
*/
- public static void replaceContent(String fileName){
+ 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 HashMap<>();
+ 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++) {
+ 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 (CellType.NUMERIC == cell.getCellType()) {
- System.out.println(cell.getNumericCellValue());
- } else if (CellType.STRING == cell.getCellType()) {
- System.out.println(cell.getStringCellValue());
+ if(cell == null){
+ break;
}
-*/
- //table1 澶氫釜绛旈缁撴灉澶勭悊
+ //涓嶆槸鍒嗘暟 蹇界暐
+ 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(cell.getNumericCellValue());
+ map.get(k).add(cellValue);
}else {
List<Double> list = new ArrayList<>();
- list.add(cell.getNumericCellValue());
+ 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);
}
- System.out.println(dataMap);
+ //骞冲潎鍊奸泦鍚�
+ 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;
}
/**
@@ -200,7 +375,9 @@
index = 1;
}
//鑾峰彇妯℃澘鏁版嵁
- List<SeriesData> seriesDatas = initData(fileName,index);
+ List<SeriesData> seriesDatas = initDataNew(fileName,index);
+ //List<SeriesData> seriesDatas = initData(fileName,index);
+ //log.error(JSON.toJSONString(seriesDatas));
//鏁版嵁闀垮害
int size = seriesDatas.size();
// 鏌ョ湅閲岄潰鐨勫浘琛ㄦ暟鎹紝鎵嶈兘鐭ラ亾鏄粈涔堝浘琛�
@@ -242,7 +419,7 @@
}
CTScatterSer ser = scatterChart.getSerList().get(i);
- updateScatterChart(scatterDatas, ser.getXVal(), ser.getYVal());
+ updateScatterChart(scatterDatas,ser.getTx(), ser.getXVal(), ser.getYVal());
}
}
}
@@ -301,6 +478,29 @@
}
/**
+ * 绌鸿鍒ゆ柇
+ * @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
@@ -347,6 +547,9 @@
//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));
}
}
@@ -361,9 +564,15 @@
* @param xDataSource X鍧愭爣鏁版嵁缂撳瓨
* @param yDataSource Y鍧愭爣鏁版嵁缂撳瓨
*/
- protected static void updateScatterChart(List<SeriesData> seriesDatas, CTAxDataSource xDataSource,
+ 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();
--
Gitblit v1.9.1