--- /dev/null
+<?xml version="1.0" encoding="UTF-8" ?>
+<addition>
+ <row>
+ <cell><type>string</type><value></value></cell>
+ <cell><type>string</type><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>
+ </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>
+ </row>
+
+ <row></row>
+
+ <row>
+ <cell><type>string</type><value></value></cell>
+ <cell><type>string</type><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>
+ </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>
+ </row>
+
+
+</addition>
package jp.satomichan.nucalgen;
+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;
import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.CommandLineParser;
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;
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<String, String> namedAreaMap = new HashMap<String, String>();
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 {
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);
+ }
}
//「食品群別摂取量」出力
}
-
- //PFCバランス
- private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _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<String,String> _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<String,String> _namedAreaMap) {
}
+ //「付加行」生成
+ private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex,
+ Map<String,String> _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<String, String> 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;
+ }
--- /dev/null
+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;
+
+ }
+}
projects / nucalgen / commitdiff