From e80006ac27c3370e9a4dc02f28898d3380c57993 Mon Sep 17 00:00:00 2001
From: zhijie <1003392067@qq.com>
Date: 星期一, 16 十月 2023 22:49:31 +0800
Subject: [PATCH] 图表替换数值
---
src/main/java/com/ots/project/tool/report/LAQ/chart/LAQChart.java | 717 +++++++++++++++++++++++------------------------------------
1 files changed, 283 insertions(+), 434 deletions(-)
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
index 8f5eca0..b70254d 100644
--- 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
@@ -1,14 +1,22 @@
package com.ots.project.tool.report.LAQ.chart;
+import com.alibaba.fastjson.JSON;
import com.ots.common.enums.CapabilityEnum;
+import com.ots.common.utils.DateUtils;
+import com.ots.common.utils.MapDataUtil;
import com.ots.common.utils.StringUtils;
+import com.ots.common.utils.poi.ExcelUtil;
+import com.ots.common.utils.poi.WordUtil;
import com.ots.framework.config.EssConfig;
+import com.ots.project.exam.domain.TReportTemplate;
import com.ots.project.tool.PdfUtil;
+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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
@@ -19,10 +27,8 @@
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.io.*;
+import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@@ -36,401 +42,281 @@
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);
+ public static void main(String[] args) throws Exception {
+ String dataPath = "D:\\娴嬭瘎绯荤粺\\闇�姹俓\LAQ\\report\\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);
+ // 鐢熸垚鏂囦欢闆嗗悎
+ List<String> fileNameList = new ArrayList<>();
+ List<String> deleteStrList = new ArrayList<>();
+ for (int i = 0; i < laqTemplateList.size(); i++) {
+ LAQTemplate laqTemplate = laqTemplateList.get(i);
+ //缁勮鎶ュ憡鎵�鐢ㄦ暟鎹�
+ Map<String, Object> textMap = new HashMap<>();
+ textMap.put("sendEmailFileName",laqTemplate.getName());
+ textMap.put("nowDate", DateUtils.getDate());
+ MapDataUtil.objectToMap(laqTemplate,textMap);
- 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);
+// TReportTemplate template = null;
+// String zipFileName = WordUtil.makeReportFile("D:\\娴嬭瘎绯荤粺\\闇�姹俓\LAQ\\report\\LAQ鑻辨枃鎶ュ憡-20230926.docx", template, textMap, new HashMap<>(), deleteStrList);
+// fileNameList.add(zipFileName);
+ ZipSecureFile.setMinInflateRatio(0.001);
+ try (OutputStream os = new FileOutputStream("D:\\娴嬭瘎绯荤粺\\闇�姹俓\LAQ\\report\\LAQ.docx");XWPFDocument document = new XWPFDocument(POIXMLDocument.openPackage("D:\\娴嬭瘎绯荤粺\\闇�姹俓\LAQ\\report\\LAQ鑻辨枃鎶ュ憡-20230926.docx"))){
+ Map<Integer,List<SeriesData>> seriesDatas = initData(textMap);
+ changeChart(document,seriesDatas,textMap);
+ document.write(os);
+ }catch (Exception e){
+ e.printStackTrace();
}
- //骞冲潎鍊奸泦鍚�
- 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("璁＄畻骞冲潎鍊煎嚭閿�!");
+
+ log.info("鍒犻櫎鐨勬枃浠跺悕:{}", JSON.toJSONString(deleteStrList));
+ WordUtil.deleteFileByStr(deleteStrList);
}
- return averageMap;
}
/**
* 鍒濆鍖栨姤鍛婃暟鎹�
- * @param fileName 鏁版嵁婧愭枃浠跺悕
- * @param index 妯℃澘sheet绱㈠紩
+ * @param textMap
* @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;
+ public static Map<Integer,List<SeriesData>> initData(Map<String, Object> textMap){
+ Map<Integer,List<SeriesData>> seriesDataMap = new HashMap<>();
+ //鑾峰彇鍛戒腑鏁板��
+ BigDecimal olas = (BigDecimal) textMap.get("olas");
+ BigDecimal ca = (BigDecimal) textMap.get("ca");
+ BigDecimal bla = (BigDecimal) textMap.get("bla");
+ BigDecimal nr = (BigDecimal) textMap.get("nr");
+ BigDecimal ar = (BigDecimal) textMap.get("ar");
+ BigDecimal open = (BigDecimal) textMap.get("open");
+ BigDecimal cons = (BigDecimal) textMap.get("cons");
+ BigDecimal extra = (BigDecimal) textMap.get("extra");
+ BigDecimal agree = (BigDecimal) textMap.get("agree");
+ BigDecimal emosta = (BigDecimal) textMap.get("emosta");
+
+ // 鍥捐〃1
+ List<SeriesData> seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"绫诲埆 1")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", olas.doubleValue())
+ ))
+ );
+ seriesDataMap.put(1,seriesDatas);
+
+ // 鍥捐〃2
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"Behavioral Leadership Alignment"),
+ new RowData(" ", 0,"Cognitive Ability")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", bla.doubleValue()),
+ new RowData("绯诲垪 1", ca.doubleValue())
+ ))
+ );
+ seriesDataMap.put(2,seriesDatas);
+
+ // 鍥捐〃3
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"Abstract Reasoning"),
+ new RowData(" ", 0,"Numeric Reasoning")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", ar.doubleValue()),
+ new RowData("绯诲垪 1", nr.doubleValue())
+ ))
+ );
+ seriesDataMap.put(3,seriesDatas);
+
+ // 鍥捐〃4
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"绫诲埆 1")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", bla.doubleValue())
+ ))
+ );
+ seriesDataMap.put(4,seriesDatas);
+
+ // 鍥捐〃5
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"Emotional Stability"),
+ new RowData(" ", 0,"Agreeableness"),
+ new RowData(" ", 0,"Extraversion"),
+ new RowData(" ", 0,"Conscientiousness"),
+ new RowData(" ", 0,"Openness to Experience")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", emosta.doubleValue()),
+ new RowData("绯诲垪 1", agree.doubleValue()),
+ new RowData("绯诲垪 1", extra.doubleValue()),
+ new RowData("绯诲垪 1", cons.doubleValue()),
+ new RowData("绯诲垪 1", open.doubleValue())
+ ))
+ );
+ seriesDataMap.put(5,seriesDatas);
+
+ // 鍥捐〃6
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"Emotional Stability"),
+ new RowData(" ", 0,"Agreeableness"),
+ new RowData(" ", 0,"Extraversion"),
+ new RowData(" ", 0,"Conscientiousness"),
+ new RowData(" ", 0,"Openness to Experience")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", emosta.doubleValue()),
+ new RowData("绯诲垪 1", agree.doubleValue()),
+ new RowData("绯诲垪 1", extra.doubleValue()),
+ new RowData("绯诲垪 1", cons.doubleValue()),
+ new RowData("绯诲垪 1", open.doubleValue())
+ ))
+ );
+ seriesDataMap.put(6,seriesDatas);
+
+ // 鍥捐〃7
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"Emotional Stability"),
+ new RowData(" ", 0,"Agreeableness"),
+ new RowData(" ", 0,"Extraversion"),
+ new RowData(" ", 0,"Conscientiousness"),
+ new RowData(" ", 0,"Openness to Experience")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", emosta.doubleValue()),
+ new RowData("绯诲垪 1", agree.doubleValue()),
+ new RowData("绯诲垪 1", extra.doubleValue()),
+ new RowData("绯诲垪 1", cons.doubleValue()),
+ new RowData("绯诲垪 1", open.doubleValue())
+ ))
+ );
+ seriesDataMap.put(7,seriesDatas);
+
+ // 鍥捐〃8
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"Emotional Stability"),
+ new RowData(" ", 0,"Agreeableness"),
+ new RowData(" ", 0,"Extraversion"),
+ new RowData(" ", 0,"Conscientiousness"),
+ new RowData(" ", 0,"Openness to Experience")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", emosta.doubleValue()),
+ new RowData("绯诲垪 1", agree.doubleValue()),
+ new RowData("绯诲垪 1", extra.doubleValue()),
+ new RowData("绯诲垪 1", cons.doubleValue()),
+ new RowData("绯诲垪 1", open.doubleValue())
+ ))
+ );
+ seriesDataMap.put(8,seriesDatas);
+
+ // 鍥捐〃9
+ seriesDatas = Arrays.asList(
+ new SeriesData(" ", Arrays.asList(
+ new RowData(" ", 0,"Emotional Stability"),
+ new RowData(" ", 0,"Agreeableness"),
+ new RowData(" ", 0,"Extraversion"),
+ new RowData(" ", 0,"Conscientiousness"),
+ new RowData(" ", 0,"Openness to Experience")
+ )),
+ new SeriesData("绯诲垪 1", Arrays.asList(
+ new RowData("绯诲垪 1", emosta.doubleValue()),
+ new RowData("绯诲垪 1", agree.doubleValue()),
+ new RowData("绯诲垪 1", extra.doubleValue()),
+ new RowData("绯诲垪 1", cons.doubleValue()),
+ new RowData("绯诲垪 1", open.doubleValue())
+ ))
+ );
+ seriesDataMap.put(9,seriesDatas);
+
+ // 鍥捐〃10
+ seriesDatas = Arrays.asList(
+ new SeriesData("X 鍊�", Arrays.asList(
+ new RowData("X 鍊�", olas.doubleValue())
+ )),
+ new SeriesData("Y 鍊�", Arrays.asList(
+ new RowData("Y 鍊�", 44)
+ ))
+ );
+ seriesDataMap.put(10,seriesDatas);
+
+ return seriesDataMap;
}
/**
- * 鍒濆鍖栨姤鍛婃暟鎹�
- * @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鍥捐〃鏁版嵁
+ * 鏇挎崲LAQ鍥捐〃鏁版嵁
* @param document 鏂囨。瀵硅薄
- * @param fileName 鏁版嵁婧恊xcel
+ * @param seriesDataMap
+ * @param textMap 鏁版嵁婧�
*/
- public static void changeMAQTRChart(XWPFDocument document,String fileName) {
- if(StringUtils.isEmpty(fileName)){
+ public static void changeChart(XWPFDocument document, Map<Integer,List<SeriesData>> seriesDataMap, Map<String, Object> textMap) {
+ if(StringUtils.isEmpty(textMap)){
return;
}
try {
List<POIXMLDocumentPart> relations = document.getRelations();
+ int barIndex = 1;
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();
+ getChartType(chart);
// 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);
- }
- }
-
+ //鏌卞舰鍥�
+ if (!plot.getBarChartList().isEmpty()) {
+ List<SeriesData> seriesDatas = seriesDataMap.get(barIndex);
+ CTBarChart barChart = plot.getBarChartArray(0);
+ //System.out.println("鏌卞舰鍥緖"+plot.getScatterChartList().size()+"}");
//鍒锋柊鍐呯疆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));
- }
+ //鏌卞舰鍥炬暟鎹簮
+// List<SeriesData> barDatas = seriesDatas.subList(1,4);
+ //鍥犱负鏌卞舰鍥綜TBarSer娌℃寜excel椤哄簭璧� 璋冩崲鏁版嵁浣嶇疆淇濇寔瀵瑰簲
+ Collections.swap(seriesDatas, 0, 1);
+ int j = 0;
+ for (CTBarSer ser : barChart.getSerList()) {
+ //鏇存柊鏌卞舰鍥炬暟鎹紦瀛�
+ updateBarChart(seriesDatas.get(j), ser.getVal());
+ ++j;
+ }
+ barIndex++;
+ }
- CTScatterSer ser = scatterChart.getSerList().get(i);
- updateScatterChart(scatterDatas,ser.getTx(), ser.getXVal(), ser.getYVal());
+ //鏁ｇ偣鍥�
+ if (!plot.getScatterChartList().isEmpty()) {
+ List<SeriesData> seriesDatas = seriesDataMap.get(10);
+ CTScatterChart scatterChart = plot.getScatterChartArray(0);
+ //System.out.println("鏁ｇ偣鍥緖"+plot.getScatterChartList().size()+"}");
+ //鍒锋柊鍐呯疆excel鏁版嵁
+ refreshExcel(seriesDatas, sheet);
+ workbook.write(chart.getPackagePart().getOutputStream());
+
+/* //鏁ｇ偣鍥炬暟鎹簮 XY鍧愭爣
+ List<SeriesData> scatterDatas = new ArrayList<SeriesData>();
+ scatterDatas.add(seriesDatas.get(0));
+ scatterDatas.add(seriesDatas.get(4));*/
+ int i = 0;
+ for (CTScatterSer ser : scatterChart.getSerList()) {
+ //鏇存柊鏁ｇ偣鍥剧紦瀛�
+ updateScatterChart(seriesDatas, ser.getXVal(), ser.getYVal());
+ ++i;
}
}
}
@@ -442,72 +328,41 @@
}
}
- /**
- * 鍒犻櫎鍒�
- * @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
+ * 鏇存柊 鏌卞舰鍥� 鐨勭紦瀛樻暟鎹�
+ *
+ * @param seriesDatas 鏁版嵁
+ * @param numDataSource 鏁版嵁鐨勭紦瀛�
*/
- 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());
- }
- }
+ protected static void updateBarChart(SeriesData seriesDatas, CTNumDataSource numDataSource) {
+ //鑾峰彇鏌卞舰鍥炬暟鎹暟閲�
+ long ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();
+ int dataSize = seriesDatas.value.size();
+ for (int i = 0; i < dataSize; i++) {
+ RowData cellValu = seriesDatas.value.get(i);
+ CTNumVal val = ptNumCnt > i ? numDataSource.getNumRef().getNumCache().getPtArray(i)
+ : numDataSource.getNumRef().getNumCache().addNewPt();
+ val.setIdx(i);
+ val.setV(String.format("%.0f", cellValu.value));
- /**
- * 绌鸿鍒ゆ柇
- * @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鐨勮寖鍥�
+ numDataSource.getNumRef().setF(
+ replaceRowEnd(numDataSource.getNumRef().getF(),
+ ptNumCnt,
+ dataSize));
+
+ // 鍒犻櫎澶氱殑
+ if (ptNumCnt > dataSize) {
+ for (int idx = dataSize; idx < ptNumCnt; idx++) {
+ numDataSource.getNumRef().getNumCache().removePt(dataSize);
+ }
+ }
+ // 鏇存柊涓暟
+ numDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
}
@@ -575,14 +430,8 @@
* @param xDataSource X鍧愭爣鏁版嵁缂撳瓨
* @param yDataSource Y鍧愭爣鏁版嵁缂撳瓨
*/
- protected static void updateScatterChart(List<SeriesData> seriesDatas,CTSerTx serTitle, CTAxDataSource xDataSource,
+ protected static void updateScatterChart(List<SeriesData> seriesDatas, 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();
--
Gitblit v1.9.1