X-Git-Url: https://satomichan.jp/gitweb/?p=nucalgen;a=blobdiff_plain;f=src%2Fmain%2Fjava%2Fjp%2Fsatomichan%2Fnucalgen%2FNucalgen.java;h=9eb18fcb5a579bdb36a2b4f0fcbff66ae3dfe5cb;hp=690d47d439f0880de9e1bd1804a6ccd9f23407a8;hb=33a043c52b68b0feb72f4f3a84d648793f223b83;hpb=d3d8e4a3afbaf682d390193578a235dfa94db047

diff --git a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
index 690d47d..9eb18fc 100644
--- a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
+++ b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
@@ -2,6 +2,7 @@ package jp.satomichan.nucalgen;
 
 import java.io.FileInputStream;
 import java.io.FileOutputStream;
+import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.HashMap;
 import java.util.List;
@@ -96,12 +97,14 @@ public class Nucalgen {
 			}
 
 			//「栄養計算」行
+			List<String> usedTableList = new ArrayList<String>();
 			int rowIndex = 3;
 			for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
 				Row thisRow = calcSheet.createRow(rowIndex);
 
+				//「食品名」
 				thisRow.createCell(1).setCellStyle(csPool.getCellStyle("00000", false));
-				thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",成分表!$B$12:$BL$2500,3,FALSE),\"\")");
+				thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",成分表!$B$13:$BL$2500,3,FALSE),\"\")");
 				thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false));
 
 				colIndex = 4;
@@ -111,18 +114,17 @@ public class Nucalgen {
 
 					String div100 = aColumn.isUseRawValue() ? "" :  "/ 100 * $D" + (rowIndex + 1);
 
-					thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + "," + aColumn.getTable() + "!$B$12:$BL$2500,MATCH(\"" + aColumn.getName() + "\"," + aColumn.getTable() + "!$B$11:$BL$11,0),FALSE) " + div100 + ",\"\")");
+					thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + "," + aColumn.getTable() + "!$B$13:$BL$2500,MATCH(\"" + aColumn.getName() + "\"," + aColumn.getTable() + "!$B$12:$BL$12,0),FALSE) " + div100 + ",\"\")");
 					colIndex++;
+
+					usedTableList.add(aColumn.getTable());
 				}
 
 			}
 
 
-			//摂取量 名前付き範囲
+			//摂取量 範囲を記憶
 			String intakeArea = new CellReference(3, 3, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, 3, true, true).formatAsString();
-			//XSSFName intakeNamedRangeArea = (XSSFName) outputWorkbook.createName();
-			//intakeNamedRangeArea.setNameName("AREA_INTAKE");
-			//intakeNamedRangeArea.setRefersToFormula(intakeArea);
 			namedAreaMap.put("AREA_INTAKE", intakeArea);
 
 
@@ -135,18 +137,12 @@ public class Nucalgen {
 				Cell thisCell = sumRow.createCell(colIndex);
 				String sumTargetArea = new CellReference(3, colIndex, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, colIndex, true, true).formatAsString();
 
-				//名前付き範囲（alias あれば設定）
+				//範囲を記憶（alias あれば設定）
 				if(aColumn.getAlias().length() > 0) {
-					//XSSFName namedRangeArea = (XSSFName) outputWorkbook.createName();
-					//namedRangeArea.setNameName("AREA_" + aColumn.getAlias());
-					//namedRangeArea.setRefersToFormula(sumTargetArea);
 					namedAreaMap.put("AREA_" + aColumn.getAlias(), sumTargetArea);
 
 					if(aColumn.isUseSum()) {
-						//XSSFName namedRangeSum = (XSSFName) outputWorkbook.createName();
-						//namedRangeSum.setNameName("SUM_" + aColumn.getAlias());
 						String sumArea = new CellReference(rowIndex, colIndex, true, true).formatAsString();
-						//namedRangeSum.setRefersToFormula(sumArea);
 						namedAreaMap.put("SUM_" + aColumn.getAlias(), sumArea);
 					}
 				}
@@ -171,6 +167,21 @@ public class Nucalgen {
 				rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap);
 			}
 
+
+			//未使用表シート削除
+			for(int si = outputWorkbook.getNumberOfSheets() - 1 ; si >= 1 ; si--) {
+				String sheetName = outputWorkbook.getSheetName(si);
+				boolean used = false;
+				for(String usedTable : usedTableList) {
+					if(usedTable.equals(sheetName)) {
+						used = true;
+					}
+				}
+				if(!used) {
+					outputWorkbook.removeSheetAt(si);
+				}
+			}
+
 			//ブック出力
 			FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName);
 			outputWorkbook.setActiveSheet(0);