package jp.satomichan.nucalgen;
+import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
-import java.util.Arrays;
+import java.util.ArrayList;
import java.util.List;
+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.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;
+
+import jp.satomichan.nucalgen.addition.AcCell;
+import jp.satomichan.nucalgen.addition.AcRow;
+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) {
//コマンドライン・オプション読み込み
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("groupsum").longOpt("with-group-sum").build());
+ options.addOption(Option.builder("add").hasArgs().longOpt("addition").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());
+
+ NamedAreaStore namedArea = NamedAreaStore.getInstance();
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("栄養価計算");
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("摂取量");
- int colIndex = 4;
- for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
+ 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());
colIndex++;
}
//「単位」行
- Row unitRow = calcSheet.createRow(2);
- unitRow.createCell(2).setCellValue("単位");
- unitRow.createCell(3).setCellValue("g");
- colIndex = 4;
- for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
+ 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());
colIndex++;
}
//「栄養計算」行
- int rowIndex = 3;
- for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
+ List<String> usedTableList = new ArrayList<String>();
+ 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$9:$BS$2199,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 = 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()) {
+ 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) + ",本表!$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);
+ //摂取量 範囲を記憶
+ namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);
//「合計」行
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();
- //名前付き範囲（alias あれば設定）
+ //範囲を記憶（alias あれば設定）
if(aColumn.getAlias().length() > 0) {
- XSSFName namedRangeArea = (XSSFName) outputWorkbook.createName();
- namedRangeArea.setNameName("AREA_" + aColumn.getAlias());
- namedRangeArea.setRefersToFormula(sumArea);
+ namedArea.save("AREA_" + aColumn.getAlias(), ROW_INDEX_START, colIndex, rowIndex -1, colIndex);
if(aColumn.isUseSum()) {
- XSSFName namedRangeSum = (XSSFName) outputWorkbook.createName();
- namedRangeSum.setNameName("SUM_" + aColumn.getAlias());
- namedRangeSum.setRefersToFormula(new CellReference(calcSheet.getSheetName(), rowIndex, colIndex, true, true).formatAsString());
+ namedArea.save("SUM_" + aColumn.getAlias(), rowIndex, colIndex);
}
}
thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
if(aColumn.isUseSum()) {
- thisCell.setCellFormula("SUM(" + sumArea + ")");
+ thisCell.setCellFormula("SUM(" +
+ NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) +
+ ")");
}
colIndex++;
}
-
- //「PFCバランス」出力
- if(cmd.hasOption("with-pfc-balance")) {
- rowIndex += 3;
- rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex);
+
+ //「付加行」出力
+ String[] additionOptionValues = cmd.getOptionValues("addition");
+ if(additionOptionValues != null) {
+ for(String aAdditionFileName : additionOptionValues) {
+ rowIndex += 2;
+ rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedArea);
+ }
}
- //「食品群別摂取量」出力
- if(cmd.hasOption("with-group-sum")) {
- rowIndex += 3;
- rowIndex = generateGroupSum(calcSheet, csPool, rowIndex);
+
+ //未使用表シート削除
+ 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);
+ }
}
//ブック出力
}
+
+
+
+
+
+
+
+ //「付加行」生成
+ private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex,
+ NamedAreaStore namedArea) {
- //PFCバランス
- private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex) {
- 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");
-
- 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)");
- 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)");
- 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)");
-
- return rowIndex;
- }
-
-
-
- //群別摂取量
- private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex) {
-
- List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
- "種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
- "油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");
-
- Row groupRow = calcSheet.createRow(rowIndex);
- groupRow.createCell(1).setCellValue("食品群");
- groupRow.createCell(3).setCellValue("摂取量(g)");
- rowIndex++;
-
- for(int i = 1; i <= 18; i++,rowIndex++) {
+ AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
+ for(AcRow acRow : ac.getRows()) { //行ごとのループ
Row thisRow = calcSheet.createRow(rowIndex);
- thisRow.createCell(1).setCellValue(i);
- thisRow.createCell(2).setCellValue(groupName.get(i));
- Cell cCell = thisRow.createCell(3);
- cCell.setCellStyle(csPool.getCellStyle(""));
- cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)");
-
- if(i == 6) {
- rowIndex++;
- thisRow = calcSheet.createRow(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)");
-
- }
-
+ int colIndex = 0;
+
+ for(AcCell acCell : acRow.getCells()) { //セルごとのループ
+ Cell thisCell = thisRow.createCell(colIndex);
+
+ //alias 「付加行」内の別名定義（制約：右方・下方のセルからしか参照できない）
+ if(acCell.getAlias() != null) {
+ namedArea.save(acCell.getAlias(), rowIndex, colIndex);
+ }
+
+ //formula 計算式
+ if(acCell.getFormula() != null){
+ String formula = acCell.getFormula();
+ for(Entry<String, String> keyValue : namedArea.entrySet()) {
+ String k = keyValue.getKey();
+ String v = keyValue.getValue();
+
+ formula = replaceFormula(formula, k, v);
+ }
+ thisCell.setCellFormula(formula);
+
+ //value そのままの値
+ } else if(acCell.getValue() != null) {
+ thisCell.setCellValue(acCell.getValue());
+
+ //formula でも value でもない
+ } else {
+ //
+ }
+
+
+ colIndex++;
+ } //セルごとのループ
+
+ rowIndex++;
+ } //行ごとのループ
+
- }
- 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());
+ }
projects / nucalgen / blobdiff