X-Git-Url: https://satomichan.jp/gitweb/?p=nucalgen;a=blobdiff_plain;f=nucalgen%2Fsrc%2Fmain%2Fjava%2Fjp%2Fsatomichan%2Fnucalgen%2FNucalgen.java;h=b6065c9262bb8df7a7656526906b157076dfbe3a;hp=ba760ccef079074e2b5eb751194d9d078fde8dd3;hb=b044ded26417f6c8bba39a781f34aadc93a00d42;hpb=f7ef7136ab18af5d913f23185dacacf9e1800384 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; + }