From ad58ba3d20b080ef113cbd196000028f83308bb3 Mon Sep 17 00:00:00 2001
From: wzp <2040239371@qq.com>
Date: 星期三, 31 一月 2024 11:59:09 +0800
Subject: [PATCH] 多表要按sheet名获取数据，多表每次读取随机修正
---
src/main/java/com/ots/project/tool/report/MAQTR/chart/MAQTRChart.java | 83 ++++++++++++++++++++++++++++++-----------
1 files changed, 61 insertions(+), 22 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 fdc8777..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
@@ -9,6 +9,7 @@
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;
@@ -36,7 +37,7 @@
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缁勫埆瀵煎叆鏁版嵁妯℃澘 (2).xlsx";
+ String fileName = "C:\\Users\\澶уご\\Desktop\\MAQ缁勫埆鎶ュ憡\\MAQ缁勫埆瀵煎叆鏁版嵁妯℃澘 -3缁�(1).xlsx";
//鍙樻洿鍥捐〃鏁版嵁
changeMAQTRChart(document,fileName);
document.write(os);
@@ -54,47 +55,62 @@
*/
public static Map<String,Map<Integer,Double>> replaceContent(String fileName){
//骞冲潎鍊奸泦鍚�
- Map<String,Map<Integer,Double>> averageMap = new HashMap<>();
+ 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++) {
XSSFRow row = sheet.getRow(j);
- if(row == null){
- continue;
+ if(isEmptyRow(row)){
+ break;
}
//鍒�
for (int k = 1; k < row.getLastCellNum(); k++) {
XSSFCell cell = row.getCell(k);
if(cell == null){
- continue;
+ break;
}
-/*
- if (CellType.NUMERIC == cell.getCellType()) {
- System.out.println(cell.getNumericCellValue());
- } else if (CellType.STRING == cell.getCellType()) {
- System.out.println(cell.getStringCellValue());
+ //涓嶆槸鍒嗘暟 蹇界暐
+ 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();
}
-*/
- switch (cell.getCellType()) {
- case STRING:
- continue;
- }
- //table1 澶氫釜绛旈缁撴灉澶勭悊
+
+ //褰掔被涓��
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);
}
//骞冲潎鍊奸泦鍚�
@@ -149,8 +165,8 @@
//鍔ㄦ�佸��
for (Map.Entry<String,Map<Integer,Double>> map : dataMap.entrySet()) {
SeriesData valSeriesData = new SeriesData();
- valSeriesData.setName(map.getKey());
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();
@@ -206,8 +222,8 @@
//鍔ㄦ�佸��
for (Map.Entry<String,Map<Integer,Double>> map : dataMap.entrySet()) {
SeriesData valSeriesData = new SeriesData();
- valSeriesData.setName(map.getKey());
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();
@@ -462,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
@@ -505,7 +544,7 @@
}
//鍒犻櫎澶氫綑琛屾暟
int lastRowNum = sheet.getLastRowNum();
- log.error("lastRowNum:{},size:{}",lastRowNum,size);
+ //log.error("lastRowNum:{},size:{}",lastRowNum,size);
if (lastRowNum > size) {
for (int idx = lastRowNum; idx > size; idx--) {
if(sheet.getRow(idx) == null){
--
Gitblit v1.9.1