import java.io.FileInputStream;
import java.io.FileOutputStream;
+import java.util.ArrayList;
import java.util.Arrays;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.CommandLineParser;
import org.apache.commons.cli.DefaultParser;
import org.apache.commons.cli.Option;
import org.apache.commons.cli.Options;
-import org.apache.commons.configuration.XMLConfiguration;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
-import org.apache.poi.xssf.usermodel.XSSFName;
public class Nucalgen {
+ private static final int COL_INDEX_START = 4;
public static void main(String[] args) {
//コマンドライン・オプション読み込み
options.addOption(Option.builder("protect").longOpt("set-protect").build());
options.addOption(Option.builder("r").longOpt("-use-processed-table").build());
+ Map<String, String> namedAreaMap = new HashMap<String, String>();
+
try {
CommandLineParser parser = new DefaultParser();
CommandLine cmd = parser.parse(options, args);
- final String moeStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table");
+ 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"));
//コンフィグ読み込み
- XMLConfiguration config = new XMLConfiguration(columnsXmlFileName);
- NutritionColumnHolder nc = new NutritionColumnHolder(config);
+ NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);
//Book生成
- Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(moeStdFoodCompTableFileName));
+ Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName));
if(cmd.hasOption("use-processed-table") == false) {
- //「本表」変換
- MoeStdFoodCompTable moe = new MoeStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
- moe.processInto(outputWorkbook);
+ //成分表 変換
+ MextStdFoodCompTable stdCompTable = new MextStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
+ stdCompTable.processInto(outputWorkbook);
}
- //「別表」削除
- outputWorkbook.removeSheetAt(1);
-
//「栄養価計算」シート生成
Sheet calcSheet = outputWorkbook.createSheet("栄養価計算");
titleRow.createCell(1).setCellValue("食品番号");
titleRow.createCell(2).setCellValue("食品名");
titleRow.createCell(3).setCellValue("摂取量");
- int colIndex = 4;
- for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
+ int colIndex = COL_INDEX_START;
+ for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
colIndex++;
}
Row unitRow = calcSheet.createRow(2);
unitRow.createCell(2).setCellValue("単位");
unitRow.createCell(3).setCellValue("g");
- colIndex = 4;
- for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
+ colIndex = COL_INDEX_START;
+ for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
colIndex++;
}
//「栄養計算」行
+ List<String> usedTableList = new ArrayList<String>();
int rowIndex = 3;
for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
Row thisRow = calcSheet.createRow(rowIndex);
+ //「食品名」
thisRow.createCell(1).setCellStyle(csPool.getCellStyle("00000", false));
- thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",æ\9c¬è¡¨!$B$9:$BS$2199,3,FALSE),\"\")");
+ thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",æ\88\90å\88\86表!$B$13:$BL$2500,3,FALSE),\"\")");
thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false));
- colIndex = 4;
- for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
+ colIndex = COL_INDEX_START;
+ for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
Cell thisCell = thisRow.createCell(colIndex);
thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
- String div100 = aColumn.isUseRawValue() ? "" : "/ 100 * $D" + (rowIndex + 1);
+ if(aColumn.getFormula().length() >= 1) {
+ //「計算式」列
+ 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");
+ }
+ //System.out.println(formula);
+ thisCell.setCellFormula(formula);
+ //thisCell.setCellValue(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) + ",本表!$B$9:$BS$2199,MATCH(\"" + aColumn.getName() + "\",本表!$B$6:$BS$6,0),FALSE) " + div100 + ",\"\")");
colIndex++;
+
+ usedTableList.add(aColumn.getTable());
}
}
- //摂取量 名前付き範囲
- String intakeArea = new CellReference(calcSheet.getSheetName(), 3, 3, true, true).formatAsString() + ":" + new CellReference(calcSheet.getSheetName(), rowIndex -1, 3, true, true).formatAsString();
- XSSFName intakeNamedRangeArea = (XSSFName) outputWorkbook.createName();
- intakeNamedRangeArea.setNameName("AREA_INTAKE");
- intakeNamedRangeArea.setRefersToFormula(intakeArea);
+ //摂取量 範囲を記憶
+ String intakeArea = new CellReference(3, 3, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, 3, true, true).formatAsString();
+ namedAreaMap.put("AREA_INTAKE", intakeArea);
//「合計」行
Row sumRow = calcSheet.createRow(rowIndex);
sumRow.createCell(1).setCellValue("合計");
calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
- colIndex = 4;
- for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
+ colIndex = COL_INDEX_START;
+ for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
Cell thisCell = sumRow.createCell(colIndex);
- String sumArea = new CellReference(calcSheet.getSheetName(), 3, colIndex, true, true).formatAsString() + ":" + new CellReference(calcSheet.getSheetName(), rowIndex -1, colIndex, true, true).formatAsString();
+ String sumTargetArea = new CellReference(3, colIndex, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, colIndex, true, true).formatAsString();
- //名前付き範囲（alias あれば設定）
+ //範囲を記憶（alias あれば設定）
if(aColumn.getAlias().length() > 0) {
- XSSFName namedRangeArea = (XSSFName) outputWorkbook.createName();
- namedRangeArea.setNameName("AREA_" + aColumn.getAlias());
- namedRangeArea.setRefersToFormula(sumArea);
+ namedAreaMap.put("AREA_" + aColumn.getAlias(), sumTargetArea);
if(aColumn.isUseSum()) {
- XSSFName namedRangeSum = (XSSFName) outputWorkbook.createName();
- namedRangeSum.setNameName("SUM_" + aColumn.getAlias());
- namedRangeSum.setRefersToFormula(new CellReference(calcSheet.getSheetName(), rowIndex, colIndex, true, true).formatAsString());
+ String sumArea = new CellReference(rowIndex, colIndex, true, true).formatAsString();
+ namedAreaMap.put("SUM_" + aColumn.getAlias(), sumArea);
+ //System.out.println("SUM_" + aColumn.getAlias() + " --- " + sumArea);
}
}
thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
if(aColumn.isUseSum()) {
- thisCell.setCellFormula("SUM(" + sumArea + ")");
+ thisCell.setCellFormula("SUM(" + sumTargetArea + ")");
}
colIndex++;
}
//「PFCバランス」出力
if(cmd.hasOption("with-pfc-balance")) {
rowIndex += 3;
- rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex);
+ rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex, namedAreaMap);
+ rowIndex += 1;
+ rowIndex = generatePfcBalance8(calcSheet, csPool, rowIndex, namedAreaMap);
}
//「食品群別摂取量」出力
if(cmd.hasOption("with-group-sum")) {
rowIndex += 3;
- rowIndex = generateGroupSum(calcSheet, csPool, rowIndex);
+ rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap);
+ }
+
+
+ //未使用表シート削除
+ for(int si = outputWorkbook.getNumberOfSheets() - 1 ; si >= 1 ; si--) {
+ String sheetName = outputWorkbook.getSheetName(si);
+ boolean used = false;
+ for(String usedTable : usedTableList) {
+ if(usedTable.equals(sheetName)) {
+ used = true;
+ }
+ }
+ if(!used) {
+ outputWorkbook.removeSheetAt(si);
+ }
}
//ブック出力
//PFCバランス
- private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex) {
+ private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
Row pfbBalanceRow1 = calcSheet.createRow(rowIndex);
- pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%)");
+ 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("SUM_P*4*100/(SUM_P*4+SUM_F*9+SUM_C*4)");
+ pCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_P") + "*4*100/" + sumKiloCalorieCell + ",0)");
+
Cell fCell = pfbBalanceRow2.createCell(4);
fCell.setCellStyle(csPool.getCellStyle("0"));
- fCell.setCellFormula("SUM_F*9*100/(SUM_P*4+SUM_F*9+SUM_C*4)");
+ fCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_F") + "*9*100/" + sumKiloCalorieCell + ",0)");
+
Cell cCell = pfbBalanceRow2.createCell(5);
cCell.setCellStyle(csPool.getCellStyle("0"));
- cCell.setCellFormula("SUM_C*4*100/(SUM_P*4+SUM_F*9+SUM_C*4)");
+ 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) {
+ private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
"種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
"油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");
+
+
Row groupRow = calcSheet.createRow(rowIndex);
groupRow.createCell(1).setCellValue("食品群");
groupRow.createCell(3).setCellValue("摂取量(g)");
thisRow.createCell(2).setCellValue(groupName.get(i));
Cell cCell = thisRow.createCell(3);
cCell.setCellStyle(csPool.getCellStyle(""));
- cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)");
+ //cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)");
+ cCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_GROUP") + ", " + i + ", " + _namedAreaMap.get("AREA_INTAKE") + ")");
if(i == 6) {
rowIndex++;
thisRow.createCell(2).setCellValue("うち 緑黄色野菜");
Cell bcvCell = thisRow.createCell(3);
bcvCell.setCellStyle(csPool.getCellStyle("0"));
- bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)");
-
+ //bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)");
+ bcvCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_BRIGHT_COLORED_VEGETABLE") + ", 1, " + _namedAreaMap.get("AREA_INTAKE") + ")");
}
package jp.satomichan.nucalgen;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.List;
+import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.XMLConfiguration;
public class NutritionColumnHolder {
this.nutritionColumnList.add(aNutritionColumn);
}
- NutritionColumnHolder(XMLConfiguration aConfig){
+ NutritionColumnHolder(String columnsXmlFileName){
+ XMLConfiguration aConfig = new XMLConfiguration();
+ aConfig.setDelimiterParsingDisabled(true);
+
+ try {
+ aConfig.load(columnsXmlFileName);
+ } catch (ConfigurationException e) {
+ // TODO 自動生成された catch ブロック
+ e.printStackTrace();
+ }
+
this.nutritionColumnList = new ArrayList<NutritionColumn>();
- List<Object> names = aConfig.getList("cols.column.name");
- List<Object> dispNames = aConfig.getList("cols.column.disp_name");
- List<Object> aliases = aConfig.getList("cols.column.alias");
- List<Object> formats = aConfig.getList("cols.column.format");
- List<Object> units = aConfig.getList("cols.column.unit");
- List<Object> useRawValue = aConfig.getList("cols.column.use_raw_value");
- List<Object> useSum = aConfig.getList("cols.column.use_sum");
+ for (int index = 0; index < getRepetition(aConfig,"cols.column.no"); index++) {
+ final String colIndex = "cols.column(" + index + ").";
- for (Object aName : names) {
NutritionColumn nc = new NutritionColumn();
- nc.setName((String) aName);
- nc.setDispName((String) dispNames.get(names.indexOf(aName)));
- nc.setAlias((String) aliases.get(names.indexOf(aName)));
- nc.setFormat((String) formats.get(names.indexOf(aName)));
- nc.setUnit((String) units.get(names.indexOf(aName)));
- nc.setUseRawValue(((String)useRawValue.get(names.indexOf(aName))).equalsIgnoreCase("true"));
- nc.setUseSum(((String)useSum.get(names.indexOf(aName))).equalsIgnoreCase("true"));
+ nc.setName(aConfig.getString(colIndex + "name", ""));
+ nc.setTable(aConfig.getString(colIndex + "table", ""));
+ nc.setDispName(aConfig.getString(colIndex + "disp_name", ""));
+ nc.setAlias(aConfig.getString(colIndex + "alias", ""));
+ nc.setFormula(aConfig.getString(colIndex + "formula", ""));
+ nc.setFormat(aConfig.getString(colIndex + "format", ""));
+ nc.setUnit(aConfig.getString(colIndex + "unit"));
+ nc.setUseRawValue(aConfig.getString(colIndex + "use_raw_value", "").equalsIgnoreCase("true"));
+ nc.setUseSum(aConfig.getString(colIndex + "use_sum", "").equalsIgnoreCase("true"));
this.addNutritionColumn(nc);
}
+ public int indexOf(String alias) {
+ for(int i = 0; i <= this.nutritionColumnList.size(); i++) {
+ if(this.nutritionColumnList.get(i).getAlias().equals(alias)) {
+ return i;
+ }
+ }
+
+ return -1;
+ }
+
+
+ public List<String> getNutritionAliasList(){
+ List<String> retAliasList = new ArrayList<String>();
+
+ for(NutritionColumn aColumn : this.getNutritionColumnList()) {
+ if(aColumn.getAlias().length() > 0) {
+ retAliasList.add(aColumn.getAlias());
+ }
+ }
+
+ return retAliasList;
+ }
+
+
+ private static int getRepetition(
+ final XMLConfiguration config,
+ final String path) {
+ Object obj = config.getProperty(path);
+
+ if (obj == null) {
+ return 0;
+ } else if (obj instanceof Collection) {
+ return ((Collection) obj).size();
+ }
+
+ return 1;
+ }
}
\ No newline at end of file
projects / nucalgen / commitdiff