From 34acfe6bbc98162cd8c9bb726aae0fd94c465544 Mon Sep 17 00:00:00 2001 From: 林致杰 <1003392067@qq.com> Date: 星期一, 01 八月 2022 21:56:38 +0800 Subject: [PATCH] 优化 --- src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java | 193 +++++++++++++++++++++++++++++++++++++++++++++--- 1 files changed, 180 insertions(+), 13 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..a2e2179 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; @@ -13,8 +14,7 @@ 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 +33,16 @@ public static void main(String[] args) throws IOException { try { -/* OutputStream os = new FileOutputStream("C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\鏋梍PAQ_IA_CN1.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缁勫埆瀵规瘮鎶ュ憡.docx")); - String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\MAQ缁勫埆瀵煎叆鏁版嵁妯℃澘.xlsx"; + String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\MAQ缁勫埆瀵煎叆鏁版嵁妯℃澘 (2).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,7 +52,9 @@ /** * 璁$畻鏁版嵁骞剁敓鎴愭ā鏉� */ - public static void replaceContent(String fileName){ + 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<>(); @@ -60,10 +63,17 @@ String companyName = sheet.getRow(1).getCell(0).getStringCellValue(); Map<Integer,List<Double>> map = new HashMap<>(); //琛� - for (int j = 1; j < sheet.getLastRowNum(); j++) { + 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()); @@ -71,6 +81,10 @@ System.out.println(cell.getStringCellValue()); } */ + switch (cell.getCellType()) { + case STRING: + continue; + } //table1 澶氫釜绛旈缁撴灉澶勭悊 if(map.get(k) != null){ map.get(k).add(cell.getNumericCellValue()); @@ -83,11 +97,156 @@ } 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(); + 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; } /** @@ -200,7 +359,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 +403,7 @@ } CTScatterSer ser = scatterChart.getSerList().get(i); - updateScatterChart(scatterDatas, ser.getXVal(), ser.getYVal()); + updateScatterChart(scatterDatas,ser.getTx(), ser.getXVal(), ser.getYVal()); } } } @@ -361,9 +522,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