リファクタリング。

「PFCバランス」に「飽和脂肪酸」を追加。

diff --git a/nucalgen/pfc-balance.xml b/nucalgen/pfc-balance.xml
index 5ed29f092d65d1b3b947c6f979a9c5ebaac16a77..c8d7092da3b651b82e8b0da95f1670e2fd8eb5b7 100644 (file)
--- a/nucalgen/pfc-balance.xml
+++ b/nucalgen/pfc-balance.xml
@@ -1,47 +1,55 @@
 <?xml version="1.0" encoding="UTF-8" ?>
 <addition>
        <row>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value>PFCバランス (%) 七訂の方法で計算したエネルギー量で計算</value></cell>
+               <cell />
+               <cell><value>PFCバランス (%) 七訂の方法で計算したエネルギー量で計算</value></cell>
        </row>
        <row>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value>P</value></cell>
-               <cell><type>string</type><value>F</value></cell>
-               <cell><type>string</type><value>C</value></cell>
+               <cell />
+               <cell />
+               <cell />
+               <cell><value>P</value></cell>
+               <cell><value>F</value></cell>
+               <cell><value>C</value></cell>
+               <cell />
+               <cell><value>飽和脂肪酸</value></cell>
        </row>
        <row>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>formula</type><value>ROUND(SUM_P*4*100/SUM_KCAL,0)</value><alias>PFC_BALANCE_P_7</alias></cell>
-               <cell><type>formula</type><value>ROUND(SUM_F*9*100/SUM_KCAL,0)</value><alias>PFC_BALANCE_F_7</alias></cell>
-               <cell><type>formula</type><value>100-(PFC_BALANCE_P_7+PFC_BALANCE_F_7)</value></cell>
+               <cell />
+               <cell />
+               <cell />
+               <cell><formula>ROUND(SUM_P*4*100/SUM_KCAL,0)</formula><alias>PFC_BALANCE_P_7</alias></cell>
+               <cell><formula>ROUND(SUM_F*9*100/SUM_KCAL,0)</formula><alias>PFC_BALANCE_F_7</alias></cell>
+               <cell><formula>100-(PFC_BALANCE_P_7+PFC_BALANCE_F_7)</formula></cell>
+               <cell />
+               <cell><formula>ROUND(SUM_FASAT*9*100/SUM_KCAL,1)</formula></cell>
        </row>
 
-       <row></row>
+       <row />
 
        <row>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value>PFCバランス (%) 八訂のエネルギー量で計算</value></cell>
+               <cell />
+               <cell><value>PFCバランス (%) 八訂のエネルギー量で計算</value></cell>
        </row>
        <row>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value>P</value></cell>
-               <cell><type>string</type><value>F</value></cell>
-               <cell><type>string</type><value>C</value></cell>
+               <cell />
+               <cell />
+               <cell />
+               <cell><value>P</value></cell>
+               <cell><value>F</value></cell>
+               <cell><value>C</value></cell>
+               <cell />
+               <cell><value>飽和脂肪酸</value></cell>
        </row>
        <row>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>string</type><value></value></cell>
-               <cell><type>formula</type><value>ROUND(SUM_P_ENG*4*100/SUM_KCAL,0)</value><alias>PFC_BALANCE_P_8</alias></cell>
-               <cell><type>formula</type><value>ROUND(SUM_F_ENG*9*100/SUM_KCAL,0)</value><alias>PFC_BALANCE_F_8</alias></cell>
-               <cell><type>formula</type><value>100-(PFC_BALANCE_P_8+PFC_BALANCE_F_8)</value></cell>
+               <cell />
+               <cell />
+               <cell />
+               <cell><formula>ROUND(SUM_P_ENG*4*100/SUM_KCAL,0)</formula><alias>PFC_BALANCE_P_8</alias></cell>
+               <cell><formula>ROUND(SUM_F_ENG*9*100/SUM_KCAL,0)</formula><alias>PFC_BALANCE_F_8</alias></cell>
+               <cell><formula>100-(PFC_BALANCE_P_8+PFC_BALANCE_F_8)</formula></cell>
+               <cell />
+               <cell><formula>ROUND(SUM_FASAT*9*100/SUM_KCAL,1)</formula></cell>
        </row>
 
 

diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java
new file mode 100644 (file)
index 0000000..095499e
--- /dev/null
+++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java
@@ -0,0 +1,57 @@
+package jp.satomichan.nucalgen;
+
+import java.util.HashMap;
+import java.util.Map;
+import java.util.Map.Entry;
+import java.util.Set;
+
+import org.apache.poi.ss.util.CellReference;
+
+public class NamedAreaStore {
+
+       private static NamedAreaStore instance = new NamedAreaStore();
+       
+       private Map<String, String> namedAreaMap = new HashMap<String, String>();
+       
+       private NamedAreaStore() {
+               //
+       }
+       
+       static NamedAreaStore getInstance() {
+               return NamedAreaStore.instance;
+       }
+       
+       static String getAreaString(int row1, int col1, int row2, int col2) {
+               String ret = new CellReference(row1, col1, true, true).formatAsString();
+               
+               if(row1 != row2 || col1 != col2) {
+                       ret = ret + ":" + new CellReference(row2, col2, true, true).formatAsString();
+               }
+               
+               return ret;
+       }
+       
+       String save(String areaname, int row1, int col1, int row2, int col2) {
+               String area = getAreaString(row1, col1, row2, col2);
+               
+               this.namedAreaMap.put(areaname, area);
+               
+               return area;
+       }
+       
+       
+       String save(String areaname, int row, int col) {
+               return this.save(areaname, row, col, row, col);
+       }
+       
+       
+       String load(String areaName) {
+               return this.namedAreaMap.get(areaName);
+       }
+       
+       
+       Set<Entry<String, String>> entrySet(){
+               return this.namedAreaMap.entrySet();
+       }
+       
+}

diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java
index 2cf039ed8b4619afadd7093d4c9785f84c8c3d10..941f552464f4054373674d951c416f5d4c9f4d20 100644 (file)
--- 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());
+       }
+
+