From b044ded26417f6c8bba39a781f34aadc93a00d42 Mon Sep 17 00:00:00 2001 From: satomichan Date: Fri, 25 Jun 2021 22:41:34 +0900 Subject: [PATCH] =?utf8?q?=E3=80=8C=E4=BB=98=E5=8A=A0=E8=A1=8C=E3=80=8D?= =?utf8?q?=E6=A9=9F=E8=83=BD=E3=81=AE=E5=AE=9F=E8=A3=85=E4=B8=AD=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit いままで「PFCバランス計算」＆「食品群別摂取量集計」機能をベタな Java のコードで書いていたが、 これを外部ファイル(XML)で記述できるようにする。 とりあえず「PFCバランス計算」は置き換えた（当該 Java コードは削除した）。 --- nucalgen/bright-colored-vegetables.xml | 95 ++++++++++++ nucalgen/pfc-balance.xml | 48 ++++++ nucalgen/pom.xml | 8 + .../java/jp/satomichan/nucalgen/Nucalgen.java | 137 ++++++++---------- .../satomichan/nucalgen/addition/AcCell.java | 49 +++++++ .../nucalgen/addition/AcCellType.java | 17 +++ .../satomichan/nucalgen/addition/AcRow.java | 24 +++ .../nucalgen/addition/AdditionConfig.java | 26 ++++ .../nucalgen/addition/AdditionUtil.java | 59 ++++++++ 9 files changed, 390 insertions(+), 73 deletions(-) create mode 100644 nucalgen/bright-colored-vegetables.xml create mode 100644 nucalgen/pfc-balance.xml create mode 100644 nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCell.java create mode 100644 nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCellType.java create mode 100644 nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcRow.java create mode 100644 nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionConfig.java create mode 100644 nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionUtil.java diff --git a/nucalgen/bright-colored-vegetables.xml b/nucalgen/bright-colored-vegetables.xml new file mode 100644 index 0000000..6bbf9d4 --- /dev/null +++ b/nucalgen/bright-colored-vegetables.xml @@ -0,0 +1,95 @@ + + + + あさつき + あしたば + アスパラガス + いんげんまめ + さやいんげん + エンダイブ + トウミョウ + おおさかしろな + さやえんどう + おかひじき + オクラ + かぶ　葉 + 日本かぼちゃ + 西洋かぼちゃ + からしな + ぎょうじゃにんにく + みずな + キンサイ + クレソン + ケール + こごみ + こまつな + さんとうさい + ししとう + しそ　 + じゅうろくささげ + しゅんぎく + すぐきな　葉 + せり + タアサイ + かいわれだいこん + 葉だいこん + だいこん　葉 + つまみな + たいさい + たかな + たらのめ + チンゲンサイ + つくし + つるな + つるむらさき + とうがらし + トマト + ミニトマト + とんぶり + ながさきはくさい + なずな + 和種なばな + 洋種なばな + にら + 花にら + 葉にんじん + にんじん + きんとき + ミニキャロット + 茎にんにく + 葉ねぎ + こねぎ + のざわな + のびる + パクチョイ + バジル + パセリ + 青ピーマン + 赤ピーマン + トマピー + ひのな + ひろしまな + ふだんそう + ブロッコリー + ほうれんそう + みずかけな + 切りみつば + 根みつば + 糸みつば + めキャベツ + めたで + モロヘイヤ + ようさい + よめな + よもぎ + サラダな + リーフレタス + サニーレタス + レタス　水耕栽培 + サンチュ + ルッコラ + わけぎ + 葉たまねぎ + みぶな + + diff --git a/nucalgen/pfc-balance.xml b/nucalgen/pfc-balance.xml new file mode 100644 index 0000000..5ed29f0 --- /dev/null +++ b/nucalgen/pfc-balance.xml @@ -0,0 +1,48 @@ + + + + string + stringPFCバランス (%) 七訂の方法で計算したエネルギー量で計算 + + + string + string + string + stringP + stringF + stringC + + + 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) + + + + + + string + stringPFCバランス (%) 八訂のエネルギー量で計算 + + + string + string + string + stringP + stringF + stringC + + + 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) + + + + diff --git a/nucalgen/pom.xml b/nucalgen/pom.xml index 7a36375..7bf56ff 100644 --- a/nucalgen/pom.xml +++ b/nucalgen/pom.xml @@ -31,6 +31,14 @@ 1.4 + + + commons-digester + commons-digester + 2.1 + + + \ No newline at end of file diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java index ba760cc..b6065c9 100644 --- a/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java @@ -1,5 +1,6 @@ package jp.satomichan.nucalgen; +import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.util.ArrayList; @@ -7,6 +8,8 @@ import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map; +import java.util.Map.Entry; +import java.util.regex.Matcher; import org.apache.commons.cli.CommandLine; import org.apache.commons.cli.CommandLineParser; @@ -21,6 +24,12 @@ import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +import jp.satomichan.nucalgen.addition.AcCell; +import jp.satomichan.nucalgen.addition.AcCellType; +import jp.satomichan.nucalgen.addition.AcRow; +import jp.satomichan.nucalgen.addition.AdditionConfig; +import jp.satomichan.nucalgen.addition.AdditionUtil; + public class Nucalgen { private static final int COL_INDEX_START = 4; @@ -31,12 +40,12 @@ public class Nucalgen { options.addOption(Option.builder("c").required().hasArg().longOpt("columns").build()); options.addOption(Option.builder("o").required().hasArg().longOpt("output").build()); options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build()); - options.addOption(Option.builder("p").longOpt("use-processed-table").build()); - options.addOption(Option.builder("pfc").longOpt("with-pfc-balance").build()); + options.addOption(Option.builder("add").hasArgs().longOpt("addition").build()); + options.addOption(Option.builder("groupsum").longOpt("with-group-sum").build()); options.addOption(Option.builder("bright").hasArg().longOpt("bright-colored-vegetables-list").build()); options.addOption(Option.builder("protect").longOpt("set-protect").build()); - options.addOption(Option.builder("r").longOpt("-use-processed-table").build()); + options.addOption(Option.builder("processed").longOpt("use-processed-table").build()); Map namedAreaMap = new HashMap(); @@ -118,9 +127,7 @@ public class Nucalgen { 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"); } - //System.out.println(formula); thisCell.setCellFormula(formula); - //thisCell.setCellValue(formula); } else { @@ -168,13 +175,14 @@ public class Nucalgen { colIndex++; } - - //「PFCバランス」出力 - if(cmd.hasOption("with-pfc-balance")) { - rowIndex += 3; - rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex, namedAreaMap); - rowIndex += 1; - rowIndex = generatePfcBalance8(calcSheet, csPool, rowIndex, namedAreaMap); + + //「付加行」出力 + String[] additionOptionValues = cmd.getOptionValues("addition"); + if(additionOptionValues != null) { + for(String aAdditionFileName : additionOptionValues) { + rowIndex += 3; + rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedAreaMap); + } } //「食品群別摂取量」出力 @@ -213,67 +221,6 @@ public class Nucalgen { } - - //PFCバランス - private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map _namedAreaMap) { - Row pfbBalanceRow1 = calcSheet.createRow(rowIndex); - pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%) 七訂の方法で計算したエネルギー量で計算"); - calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); - pfbBalanceRow1.createCell(3).setCellValue("P"); - pfbBalanceRow1.createCell(4).setCellValue("F"); - pfbBalanceRow1.createCell(5).setCellValue("C"); - - final String sumKiloCalorieCell = _namedAreaMap.get("SUM_KCAL"); - - rowIndex++; - Row pfbBalanceRow2 = calcSheet.createRow(rowIndex); - Cell pCell = pfbBalanceRow2.createCell(3); - pCell.setCellStyle(csPool.getCellStyle("0")); - pCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_P") + "*4*100/" + sumKiloCalorieCell + ",0)"); - - Cell fCell = pfbBalanceRow2.createCell(4); - fCell.setCellStyle(csPool.getCellStyle("0")); - fCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_F") + "*9*100/" + sumKiloCalorieCell + ",0)"); - - Cell cCell = pfbBalanceRow2.createCell(5); - cCell.setCellStyle(csPool.getCellStyle("0")); - cCell.setCellFormula("100 - (" + new CellReference(cCell.getRowIndex(), 3).formatAsString() + " + " - + new CellReference(cCell.getRowIndex(), 4).formatAsString() + ")"); - - return rowIndex; - } - - - //PFCバランス（八訂） - private static int generatePfcBalance8(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map _namedAreaMap) { - Row pfbBalanceRow1 = calcSheet.createRow(rowIndex); - pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%) 八訂のエネルギー量で計算"); - calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); - pfbBalanceRow1.createCell(3).setCellValue("P"); - pfbBalanceRow1.createCell(4).setCellValue("F"); - pfbBalanceRow1.createCell(5).setCellValue("C"); - - final String sumKiloCalorieCell = _namedAreaMap.get("SUM_KCAL"); - - rowIndex++; - Row pfbBalanceRow2 = calcSheet.createRow(rowIndex); - Cell pCell = pfbBalanceRow2.createCell(3); - pCell.setCellStyle(csPool.getCellStyle("0")); - pCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_P_ENG") + "*4*100/" + sumKiloCalorieCell + ",0)"); - - Cell fCell = pfbBalanceRow2.createCell(4); - fCell.setCellStyle(csPool.getCellStyle("0")); - fCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_F_ENG") + "*9*100/" + sumKiloCalorieCell + ",0)"); - - Cell cCell = pfbBalanceRow2.createCell(5); - cCell.setCellStyle(csPool.getCellStyle("0")); - cCell.setCellFormula("100 - (" + new CellReference(cCell.getRowIndex(), 3).formatAsString() + " + " - + new CellReference(cCell.getRowIndex(), 4).formatAsString() + ")"); - - return rowIndex; - } - - //群別摂取量 private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map _namedAreaMap) { @@ -314,7 +261,51 @@ public class Nucalgen { } + //「付加行」生成 + private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex, + Map _namedAreaMap) { + + System.out.println("ADDITION : " + fileName); + AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName)); + for(AcRow acRow : ac.getRows()) { //行ごとのループ + Row thisRow = calcSheet.createRow(rowIndex); + int colIndex = 0; + + for(AcCell acCell : acRow.getCells()) { //セルごとのループ + Cell thisCell = thisRow.createCell(colIndex); + + //alias 「付加行」内の別名定義（制約：右方・下方のセルからしか参照できない） + if(acCell.getAlias() != null) { + _namedAreaMap.put(acCell.getAlias(), new CellReference(rowIndex, colIndex, true, true).formatAsString()); + } + + if(acCell.getCellType() == AcCellType.FORMULA) { + String formula = "<" + acCell.getValue() + ">"; + for(Entry keyValue : _namedAreaMap.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(">$", ""); + } + thisCell.setCellFormula(formula); + } else { + thisCell.setCellValue(acCell.getValue()); + } + + + colIndex++; + } //セルごとのループ + + rowIndex++; + } //行ごとのループ + + + + return rowIndex; + } diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCell.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCell.java new file mode 100644 index 0000000..b2d881d --- /dev/null +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCell.java @@ -0,0 +1,49 @@ +package jp.satomichan.nucalgen.addition; + +public class AcCell { + private String value; + private AcCellType type; + private String alias; + + public AcCell(){ + //System.out.println("Cell#Cell()"); + } + + public String getValue() { + return value; + } + + public void setValue(String value) { + this.value = value; + } + + + public AcCellType getCellType() { + return type; + } + + public String getType() { + return type.getId(); + } + + public void setType(String type) { + AcCellType[] types = AcCellType.values(); + for(AcCellType aType : types) { + if(aType.getId().equals(type)){ + this.setType(aType); + } + } + } + + private void setType(AcCellType type) { + this.type = type; + } + + public String getAlias() { + return alias; + } + + public void setAlias(String alias) { + this.alias = alias; + } +} diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCellType.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCellType.java new file mode 100644 index 0000000..a3074e2 --- /dev/null +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcCellType.java @@ -0,0 +1,17 @@ +package jp.satomichan.nucalgen.addition; + +public enum AcCellType { + FORMULA("formula"), + STRING("string"); + + private String id; + + + private AcCellType(String id) { + this.id = id; + } + + public String getId() { + return id; + } +} diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcRow.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcRow.java new file mode 100644 index 0000000..5fd994f --- /dev/null +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AcRow.java @@ -0,0 +1,24 @@ +package jp.satomichan.nucalgen.addition; + +import java.util.ArrayList; +import java.util.List; + +public class AcRow { + private List cells = new ArrayList(); + + public List getCells() { + return cells; + } + + public void setCells(List cells) { + this.cells = cells; + } + + + public void addCell(AcCell cell) { + this.cells.add(cell); + //System.out.println("Row#addCell()"); + } + + +} diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionConfig.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionConfig.java new file mode 100644 index 0000000..cc03b8e --- /dev/null +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionConfig.java @@ -0,0 +1,26 @@ +package jp.satomichan.nucalgen.addition; + +import java.util.ArrayList; +import java.util.List; + +public class AdditionConfig { + private List rows = new ArrayList(); + + public List getRows() { + return rows; + } + + public void setRows(List rows) { + this.rows = rows; + } + + + public void addRow(AcRow row) { + this.rows.add(row); + } + + + public AdditionConfig(){ + } + +} diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionUtil.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionUtil.java new file mode 100644 index 0000000..8b03346 --- /dev/null +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/addition/AdditionUtil.java @@ -0,0 +1,59 @@ +package jp.satomichan.nucalgen.addition; + +import java.io.File; +import java.io.FileInputStream; +import java.io.IOException; +import java.io.InputStream; + +import javax.xml.parsers.ParserConfigurationException; +import javax.xml.parsers.SAXParserFactory; + +import org.apache.commons.digester.Digester; +import org.xml.sax.SAXException; + +public class AdditionUtil { + + public static void main(String[] args) { + AdditionConfig add = additionFileReader(new File("C:\\Users\\h520s\\Desktop\\pfc-balance.xml")); + } + + + public static AdditionConfig additionFileReader(File reportXmlFile){ + SAXParserFactory factory = SAXParserFactory.newInstance(); + javax.xml.parsers.SAXParser parser = null; + AdditionConfig addition = null; + + try { + parser = factory.newSAXParser(); + + Digester digester = new Digester(parser); + + digester.addObjectCreate("addition", AdditionConfig.class); + digester.addObjectCreate("addition/row", AcRow.class); + + digester.addSetNext("addition/row", "addRow"); + digester.addSetProperties("addition/row"); + + digester.addObjectCreate("addition/row/cell", AcCell.class); + + digester.addSetNext("addition/row/cell", "addCell"); + digester.addBeanPropertySetter("addition/row/cell/type"); + digester.addBeanPropertySetter("addition/row/cell/value"); + digester.addBeanPropertySetter("addition/row/cell/alias"); + + //パースする + InputStream in; + + in = new FileInputStream(reportXmlFile); + addition = (AdditionConfig)digester.parse(in); + + + + } catch (IOException | SAXException | ParserConfigurationException e) { + throw new RuntimeException(e); + } + + return addition; + + } +} -- 2.43.0