From 33a043c52b68b0feb72f4f3a84d648793f223b83 Mon Sep 17 00:00:00 2001
From: satomichan <git.20200328@satomichan.jp>
Date: Sun, 14 Feb 2021 21:03:24 +0900
Subject: [PATCH] =?utf8?q?=E3=80=8C=E6=97=A5=E6=9C=AC=E9=A3=9F=E5=93=81?=
 =?utf8?q?=E6=A8=99=E6=BA=96=E6=88=90=E5=88=86=E8=A1=A82020=E5=B9=B4?=
 =?utf8?q?=E7=89=88=EF=BC=88=E5=85=AB=E8=A8=82=EF=BC=89=E3=80=8D=E3=81=AE?=
 =?utf8?q?=E3=80=8C2021=E5=B9=B42=E6=9C=883=E6=97=A5=E4=BF=AE=E6=AD=A3?=
 =?utf8?q?=E3=80=8D=E3=81=AB=E5=AF=BE=E5=BF=9C=EF=BC=88=E8=A1=A8=E5=BD=A2?=
 =?utf8?q?=E5=BC=8F=E3=81=AB=E5=A4=89=E6=9B=B4=E3=81=8C=E3=81=82=E3=81=A3?=
 =?utf8?q?=E3=81=9F=EF=BC=89=E3=80=82?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 .../nucalgen/MextStdFoodCompTable.java        | 99 +++++++++----------
 .../java/jp/satomichan/nucalgen/Nucalgen.java |  5 +-
 2 files changed, 49 insertions(+), 55 deletions(-)

diff --git a/src/main/java/jp/satomichan/nucalgen/MextStdFoodCompTable.java b/src/main/java/jp/satomichan/nucalgen/MextStdFoodCompTable.java
index ec67188..246f5c0 100644
--- a/src/main/java/jp/satomichan/nucalgen/MextStdFoodCompTable.java
+++ b/src/main/java/jp/satomichan/nucalgen/MextStdFoodCompTable.java
@@ -34,12 +34,11 @@ public class MextStdFoodCompTable {
 
 	//変換
 	void processInto(Workbook outputWorkbook) {
-		Sheet foodComposithonSheet = outputWorkbook.cloneSheet(0);
-		outputWorkbook.setSheetName(18, "成分表");
-		outputWorkbook.setSheetOrder("成分表", 0);
+		Sheet foodComposithonSheet = outputWorkbook.getSheetAt(0);
+		outputWorkbook.setSheetName(0, "成分表");
 
 		//「成分識別子」行
-		Row idRow = foodComposithonSheet.getRow(10);
+		Row idRow = foodComposithonSheet.getRow(11);
 		for(int i = 4; i <= 61; i++) {
 			Cell idCell = idRow.getCell(i);
 			idCell.setCellValue(idCell.toString().replaceAll(" ", ""));
@@ -47,76 +46,70 @@ public class MextStdFoodCompTable {
 		idRow.createCell(62).setCellValue("GROUP");
 		idRow.createCell(63).setCellValue("BRIGHT_COLORED_VEGETABLE");
 
-		int compSheetRowIndex = 11;
 
-		//18 の食品群ごとの処理
-		for(int group = 1; group <= 18; group++) {
-			Sheet thisSheet = outputWorkbook.getSheetAt(group);
-
-			int rowCount = 0;
-			for (Row thisRow : thisSheet) {
-				rowCount++;
-				if(rowCount < 12) {continue;}
-
-				Row compRow = foodComposithonSheet.createRow(compSheetRowIndex);
+		//セル加工
+		for(int compSheetRowIndex = 12; compSheetRowIndex <= 9999; compSheetRowIndex++) {
+			Row compRow = foodComposithonSheet.getRow(compSheetRowIndex);
+			if(compRow == null) {
+				break;
+			}
 
-				for (int cellCount = 1; cellCount <= 61; cellCount++) {
-					Cell thisCell = thisRow.getCell(cellCount);
+			int group = Integer.parseInt(compRow.getCell(1).toString()) / 1000;
 
-					String cellString = thisCell.toString();
+			for (int cellCount = 1; cellCount <= 61; cellCount++) {
+				Cell thisCell = compRow.getCell(cellCount);
 
-					cellString = cellString.replaceAll("\\(", "");
-					cellString = cellString.replaceAll("\\)", "");
-					cellString = cellString.replaceAll("-", "0");
-					cellString = cellString.replaceAll("Tr", "0");
+				String cellString = thisCell.toString();
 
-					//セル値・書式 コピー
-					//Cell compCell = foodComposithonSheet.getRow(compSheetRowIndex).getCell(cellCount);
-					Cell compCell = compRow.createCell(cellCount);
-					compCell.setCellValue(cellString);
-					compCell.setCellStyle(thisCell.getCellStyle());
-					compCell.setCellType(thisCell.getCellTypeEnum());
+				cellString = cellString.replaceAll("\\(", "");
+				cellString = cellString.replaceAll("\\)", "");
+				cellString = cellString.replaceAll("-", "0");
+				cellString = cellString.replaceAll("Tr", "0");
 
-					if(cellString.matches("^[\\d\\.]+$")) {
-						compCell.setCellValue(Double.parseDouble(cellString));
-						CellStyle aCellStyle = compCell.getCellStyle();
-						aCellStyle.setDataFormat((short) 0);
-						compCell.setCellStyle(aCellStyle);
-					}
+				//セル値・書式 コピー
+				//Cell compCell = foodComposithonSheet.getRow(compSheetRowIndex).getCell(cellCount);
+				Cell compCell = compRow.createCell(cellCount);
+				compCell.setCellValue(cellString);
+				compCell.setCellStyle(thisCell.getCellStyle());
+				compCell.setCellType(thisCell.getCellTypeEnum());
 
+				if(cellString.matches("^[\\d\\.]+$")) {
+					compCell.setCellValue(Double.parseDouble(cellString));
+					CellStyle aCellStyle = compCell.getCellStyle();
+					aCellStyle.setDataFormat((short) 0);
+					compCell.setCellStyle(aCellStyle);
+				}
 
-				} //CELL
 
+			} //for cellCount
 
 
-				//食品群（１～１８）書き込み
-				compRow.createCell(62).setCellValue(group);
 
-				//緑黄色野菜か？
-				boolean isBrightColored = false;
-				if(group == 6 && brightColoredVegetablesXmlFileName.length() > 0) {
-					String foodName = thisRow.getCell(3).getStringCellValue();
-					for(String aBright : this.brightColoredVegetableList) {
-						if(foodName.matches(".*" + aBright + ".*")) {
-							isBrightColored = true;
-							break;
-						}
-					}
+			//食品群（１～１８）書き込み
+			compRow.createCell(62).setCellValue(group);
 
-					if(isBrightColored) {
-						compRow.createCell(63).setCellValue(1);
+			//緑黄色野菜か？
+			boolean isBrightColored = false;
+			if(group == 6 && brightColoredVegetablesXmlFileName.length() > 0) {
+				String foodName = compRow.getCell(3).getStringCellValue();
+				for(String aBright : this.brightColoredVegetableList) {
+					if(foodName.matches(".*" + aBright + ".*")) {
+						isBrightColored = true;
+						break;
 					}
 				}
 
+				if(isBrightColored) {
+					compRow.createCell(63).setCellValue(1);
+				}
+			}
 
-				compSheetRowIndex++;
 
-			} //ROW
+		} //for compSheetRowIndex
 
-		} //GROUP
 
 		//元の表 削除
-		for(int i = 18; i > 0; i--) {
+		for(int i = outputWorkbook.getNumberOfSheets() - 1; i > 0; i--) {
 			outputWorkbook.removeSheetAt(i);
 		}
 	}
diff --git a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
index bb80aa3..9eb18fc 100644
--- a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
+++ b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
@@ -102,8 +102,9 @@ public class Nucalgen {
 			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;
@@ -113,7 +114,7 @@ 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());
-- 
2.43.0