X-Git-Url: https://satomichan.jp/gitweb/?p=nucalgen;a=blobdiff_plain;f=nucalgen%2Fsrc%2Fmain%2Fjava%2Fjp%2Fsatomichan%2Fnucalgen%2FNucalgen.java;fp=nucalgen%2Fsrc%2Fmain%2Fjava%2Fjp%2Fsatomichan%2Fnucalgen%2FNucalgen.java;h=941f552464f4054373674d951c416f5d4c9f4d20;hp=2cf039ed8b4619afadd7093d4c9785f84c8c3d10;hb=272adc85842beafa599d5c6a284756689359ab77;hpb=5ead7185b8b04d74757dd3a9b07caf0f9eca4e63

diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
index 2cf039e..941f552 100644
--- a/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
+++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
@@ -4,9 +4,7 @@ import java.io.File;
 import java.io.FileInputStream;
 import java.io.FileOutputStream;
 import java.util.ArrayList;
-import java.util.HashMap;
 import java.util.List;
-import java.util.Map;
 import java.util.Map.Entry;
 import java.util.regex.Matcher;
 
@@ -29,6 +27,8 @@ import jp.satomichan.nucalgen.addition.AdditionConfig;
 import jp.satomichan.nucalgen.addition.AdditionUtil;
 
 public class Nucalgen {
+	//開始位置　最初の食品の、最初の栄養成分の位置 (E4セル)
+	private static final int ROW_INDEX_START = 3;
 	private static final int COL_INDEX_START = 4;
 
 	public static void main(String[] args) {
@@ -43,19 +43,19 @@ public class Nucalgen {
 		options.addOption(Option.builder("protect").longOpt("set-protect").build());
 		options.addOption(Option.builder("processed").longOpt("use-processed-table").build());
 
-		Map<String, String> namedAreaMap = new HashMap<String, String>();
+		NamedAreaStore namedArea = NamedAreaStore.getInstance();
 
 		try {
 
+			//コマンドライン引数 解析
 			CommandLineParser parser = new DefaultParser();
 			CommandLine cmd = parser.parse(options, args);
-
 			final String mextStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table");
 			final String columnsXmlFileName = cmd.getOptionValue("columns");
 			final String outputXlsxFileName = cmd.getOptionValue("output");
 			final int lines = Integer.parseInt(cmd.getOptionValue("lines"));
 
-			//コンフィグ読み込み
+			//コンフィグ （列設定） 読み込み
 			NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);
 
 			//Book生成
@@ -74,16 +74,17 @@ public class Nucalgen {
 			if(cmd.hasOption("set-protect")) {
 				calcSheet.protectSheet("");
 			}
-			calcSheet.setColumnWidth(2, 10240);
-			calcSheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
+			calcSheet.setColumnWidth(COL_INDEX_START -2, 10240); //C「食品名」列
+			calcSheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1, 
+					                   COL_INDEX_START -3, COL_INDEX_START -3));  //B2:B3「食品番号」セル
 
 			CellStylePool csPool = new CellStylePool(outputWorkbook);
 
 			//「タイトル」行
-			Row titleRow = calcSheet.createRow(1);
-			titleRow.createCell(1).setCellValue("食品番号");
-			titleRow.createCell(2).setCellValue("食品名");
-			titleRow.createCell(3).setCellValue("摂取量");
+			Row titleRow = calcSheet.createRow(ROW_INDEX_START -2);
+			titleRow.createCell(COL_INDEX_START -3).setCellValue("食品番号");
+			titleRow.createCell(COL_INDEX_START -2).setCellValue("食品名");
+			titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量");
 			int colIndex = COL_INDEX_START;
 			for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 				titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
@@ -91,9 +92,9 @@ public class Nucalgen {
 			}
 
 			//「単位」行
-			Row unitRow = calcSheet.createRow(2);
-			unitRow.createCell(2).setCellValue("単位");
-			unitRow.createCell(3).setCellValue("g");
+			Row unitRow = calcSheet.createRow(ROW_INDEX_START -1);
+			unitRow.createCell(COL_INDEX_START -2).setCellValue("単位");
+			unitRow.createCell(COL_INDEX_START -1).setCellValue("g");
 			colIndex = COL_INDEX_START;
 			for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 				unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
@@ -102,13 +103,14 @@ public class Nucalgen {
 
 			//「栄養計算」行
 			List<String> usedTableList = new ArrayList<String>();
-			int rowIndex = 3;
-			for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
+			int rowIndex = ROW_INDEX_START;
+			for(int i = rowIndex; i < lines + ROW_INDEX_START; 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$13:$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 = COL_INDEX_START;
@@ -118,17 +120,19 @@ public class Nucalgen {
 
 					if(aColumn.getFormula().length() >= 1) {
 						//「計算式」列
-						String formula = "(" + aColumn.getFormula() + ")";
+						String formula = aColumn.getFormula();
 						for(String aAlias : nch.getNutritionAliasList()) {
-							String cell = new CellReference(rowIndex, 4 + nch.indexOf(aAlias)).formatAsString();
-							formula = formula.replaceAll("([^A-Za-z0-9_])" + aAlias + "([^A-Za-z0-9_])", "$1" + cell + "$2");
+							formula = replaceFormula(formula, aAlias, rowIndex, COL_INDEX_START + nch.indexOf(aAlias));
 						}
 						thisCell.setCellFormula(formula);
 
 
 					} else {
+						//通常の栄養素の列
 						String div100 = aColumn.isUseRawValue() ? "" :  "/ 100 * $D" + (rowIndex + 1);
-						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 + ",\"\")");
+						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++;
@@ -140,8 +144,7 @@ public class Nucalgen {
 
 
 			//摂取量 範囲を記憶
-			String intakeArea = new CellReference(3, 3, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, 3, true, true).formatAsString();
-			namedAreaMap.put("AREA_INTAKE", intakeArea);
+			namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);
 
 
 			//「合計」行
@@ -151,21 +154,21 @@ public class Nucalgen {
 			colIndex = COL_INDEX_START;
 			for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 				Cell thisCell = sumRow.createCell(colIndex);
-				String sumTargetArea = new CellReference(3, colIndex, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, colIndex, true, true).formatAsString();
 
 				//範囲を記憶（alias あれば設定）
 				if(aColumn.getAlias().length() > 0) {
-					namedAreaMap.put("AREA_" + aColumn.getAlias(), sumTargetArea);
+					namedArea.save("AREA_" + aColumn.getAlias(), ROW_INDEX_START, colIndex, rowIndex -1, colIndex);
 
 					if(aColumn.isUseSum()) {
-						String sumArea = new CellReference(rowIndex, colIndex, true, true).formatAsString();
-						namedAreaMap.put("SUM_" + aColumn.getAlias(), sumArea);
+						namedArea.save("SUM_" + aColumn.getAlias(), rowIndex, colIndex);
 					}
 				}
 
 				thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 				if(aColumn.isUseSum()) {
-					thisCell.setCellFormula("SUM(" + sumTargetArea + ")");
+					thisCell.setCellFormula("SUM(" + 
+				                            NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) +
+				                            ")");
 				}
 				colIndex++;
 			}
@@ -175,8 +178,8 @@ public class Nucalgen {
 			String[] additionOptionValues = cmd.getOptionValues("addition");
 			if(additionOptionValues != null) {
 				for(String aAdditionFileName : additionOptionValues) {
-					rowIndex += 3;
-					rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedAreaMap);
+					rowIndex += 2;
+					rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedArea);
 				}
 			}
 
@@ -210,11 +213,16 @@ public class Nucalgen {
 	}
 
 
-
-
+	
+	
+	
+	
+	
+	
+	
 	//「付加行」生成
 	private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex,
-																				Map<String,String> _namedAreaMap) {
+			                              NamedAreaStore namedArea) {
 
 		AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
 		for(AcRow acRow : ac.getRows()) { //行ごとのループ
@@ -226,19 +234,17 @@ public class Nucalgen {
 				
 				//alias 「付加行」内の別名定義（制約：右方・下方のセルからしか参照できない）
 				if(acCell.getAlias() != null) {
-					_namedAreaMap.put(acCell.getAlias(), new CellReference(rowIndex, colIndex, true, true).formatAsString());
+					namedArea.save(acCell.getAlias(), rowIndex, colIndex);
 				}
 				
 				//formula 計算式
 				if(acCell.getFormula() != null){
-					String formula = "<" + acCell.getFormula() + ">";
-					for(Entry<String, String> keyValue : _namedAreaMap.entrySet()) {
+					String formula = acCell.getFormula();
+					for(Entry<String, String> keyValue : namedArea.entrySet()) {
 						String k = keyValue.getKey();
 						String v = keyValue.getValue();
-						v = Matcher.quoteReplacement(v);
-						formula = formula.replaceAll("([^A-Za-z0-9_])" + k + "([^A-Za-z0-9_])", "$1" + v + "$2");
-						formula = formula.replaceAll("^<", "");
-						formula = formula.replaceAll(">$", "");
+						
+						formula = replaceFormula(formula, k, v);
 					}
 					thisCell.setCellFormula(formula);
 				
@@ -260,11 +266,27 @@ public class Nucalgen {
 		
 
 
-		return rowIndex;
+		return rowIndex - 1;
 	}
 
 
 
+	//計算式中の文字列（名前付き範囲）置換
+	private static String replaceFormula(String formula, String target, String replacement) {
+		formula = "<" + formula + ">";
+		replacement = Matcher.quoteReplacement(replacement);
+		formula = formula.replaceAll("([^A-Za-z0-9_])" + target + "([^A-Za-z0-9_])", "$1" + replacement + "$2");
+		formula = formula.replaceAll("^<", "");
+		formula = formula.replaceAll(">$", "");
+		return formula;
+	}
+	
+	//計算式中の 文字列（名前付き範囲）→セル座標 置換
+	private static String replaceFormula(String formula, String target, int cellRow, int cellCol) {
+		return replaceFormula(formula, target, new CellReference(cellRow, cellCol).formatAsString());
+	}
+
+