From 272adc85842beafa599d5c6a284756689359ab77 Mon Sep 17 00:00:00 2001 From: satomichan Date: Sun, 27 Jun 2021 00:31:43 +0900 Subject: [PATCH] =?utf8?q?=E3=83=AA=E3=83=95=E3=82=A1=E3=82=AF=E3=82=BF?= =?utf8?q?=E3=83=AA=E3=83=B3=E3=82=B0=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 「PFCバランス」に「飽和脂肪酸」を追加。 --- nucalgen/pfc-balance.xml | 66 ++++++----- .../satomichan/nucalgen/NamedAreaStore.java | 57 ++++++++++ .../java/jp/satomichan/nucalgen/Nucalgen.java | 104 +++++++++++------- 3 files changed, 157 insertions(+), 70 deletions(-) create mode 100644 nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java diff --git a/nucalgen/pfc-balance.xml b/nucalgen/pfc-balance.xml index 5ed29f0..c8d7092 100644 --- a/nucalgen/pfc-balance.xml +++ b/nucalgen/pfc-balance.xml @@ -1,47 +1,55 @@ - string - stringPFCバランス (%) 七訂の方法で計算したエネルギー量で計算 + + PFCバランス (%) 七訂の方法で計算したエネルギー量で計算 - string - string - string - stringP - stringF - stringC + + + + P + F + C + + 飽和脂肪酸 - string - string - string - formulaROUND(SUM_P*4*100/SUM_KCAL,0)PFC_BALANCE_P_7 - formulaROUND(SUM_F*9*100/SUM_KCAL,0)PFC_BALANCE_F_7 - formula100-(PFC_BALANCE_P_7+PFC_BALANCE_F_7) + + + + ROUND(SUM_P*4*100/SUM_KCAL,0)PFC_BALANCE_P_7 + ROUND(SUM_F*9*100/SUM_KCAL,0)PFC_BALANCE_F_7 + 100-(PFC_BALANCE_P_7+PFC_BALANCE_F_7) + + ROUND(SUM_FASAT*9*100/SUM_KCAL,1) - + - string - stringPFCバランス (%) 八訂のエネルギー量で計算 + + PFCバランス (%) 八訂のエネルギー量で計算 - string - string - string - stringP - stringF - stringC + + + + P + F + C + + 飽和脂肪酸 - string - string - string - formulaROUND(SUM_P_ENG*4*100/SUM_KCAL,0)PFC_BALANCE_P_8 - formulaROUND(SUM_F_ENG*9*100/SUM_KCAL,0)PFC_BALANCE_F_8 - formula100-(PFC_BALANCE_P_8+PFC_BALANCE_F_8) + + + + ROUND(SUM_P_ENG*4*100/SUM_KCAL,0)PFC_BALANCE_P_8 + ROUND(SUM_F_ENG*9*100/SUM_KCAL,0)PFC_BALANCE_F_8 + 100-(PFC_BALANCE_P_8+PFC_BALANCE_F_8) + + ROUND(SUM_FASAT*9*100/SUM_KCAL,1) 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 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 namedAreaMap = new HashMap(); + + 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> 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 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 namedAreaMap = new HashMap(); + 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 usedTableList = new ArrayList(); - 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 _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 keyValue : _namedAreaMap.entrySet()) { + String formula = acCell.getFormula(); + for(Entry 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()); + } + + -- 2.43.0