satomichan.jp Git - nucalgen/blob - nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java

   1 package jp.satomichan.nucalgen;
   2
   3 import java.io.FileInputStream;
   4 import java.io.FileOutputStream;
   5 import java.util.ArrayList;
   6 import java.util.Arrays;
   7 import java.util.HashMap;
   8 import java.util.List;
   9 import java.util.Map;
  10
  11 import org.apache.commons.cli.CommandLine;
  12 import org.apache.commons.cli.CommandLineParser;
  13 import org.apache.commons.cli.DefaultParser;
  14 import org.apache.commons.cli.Option;
  15 import org.apache.commons.cli.Options;
  16 import org.apache.poi.ss.usermodel.Cell;
  17 import org.apache.poi.ss.usermodel.Row;
  18 import org.apache.poi.ss.usermodel.Sheet;
  19 import org.apache.poi.ss.usermodel.Workbook;
  20 import org.apache.poi.ss.usermodel.WorkbookFactory;
  21 import org.apache.poi.ss.util.CellRangeAddress;
  22 import org.apache.poi.ss.util.CellReference;
  23
  24 public class Nucalgen {
  25         private static final int COL_INDEX_START = 4;
  26
  27         public static void main(String[] args) {
  28                 //コマンドライン・オプション読み込み
  29                 Options options = new Options();
  30                 options.addOption(Option.builder("s").required().hasArg().longOpt("std-food-comp-table").build());
  31                 options.addOption(Option.builder("c").required().hasArg().longOpt("columns").build());
  32                 options.addOption(Option.builder("o").required().hasArg().longOpt("output").build());
  33                 options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build());
  34                 options.addOption(Option.builder("p").longOpt("use-processed-table").build());
  35                 options.addOption(Option.builder("pfc").longOpt("with-pfc-balance").build());
  36                 options.addOption(Option.builder("groupsum").longOpt("with-group-sum").build());
  37                 options.addOption(Option.builder("bright").hasArg().longOpt("bright-colored-vegetables-list").build());
  38                 options.addOption(Option.builder("protect").longOpt("set-protect").build());
  39                 options.addOption(Option.builder("r").longOpt("-use-processed-table").build());
  40
  41                 Map<String, String> namedAreaMap = new HashMap<String, String>();
  42
  43                 try {
  44
  45                         CommandLineParser parser = new DefaultParser();
  46                         CommandLine cmd = parser.parse(options, args);
  47
  48                         final String mextStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table");
  49                         final String columnsXmlFileName = cmd.getOptionValue("columns");
  50                         final String outputXlsxFileName = cmd.getOptionValue("output");
  51                         final int lines = Integer.parseInt(cmd.getOptionValue("lines"));
  52
  53                         //コンフィグ読み込み
  54                         NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);
  55
  56                         //Book生成
  57                         Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName));
  58
  59                         if(cmd.hasOption("use-processed-table") == false) {
  60                                 //成分表 変換
  61                                 MextStdFoodCompTable stdCompTable = new MextStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
  62                                 stdCompTable.processInto(outputWorkbook);
  63                         }
  64
  65
  66                         //「栄養価計算」シート生成
  67                         Sheet calcSheet = outputWorkbook.createSheet("栄養価計算");
  68                         outputWorkbook.setSheetOrder("栄養価計算", 0);
  69                         if(cmd.hasOption("set-protect")) {
  70                                 calcSheet.protectSheet("");
  71                         }
  72                         calcSheet.setColumnWidth(2, 10240);
  73                         calcSheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
  74
  75                         CellStylePool csPool = new CellStylePool(outputWorkbook);
  76
  77                         //「タイトル」行
  78                         Row titleRow = calcSheet.createRow(1);
  79                         titleRow.createCell(1).setCellValue("食品番号");
  80                         titleRow.createCell(2).setCellValue("食品名");
  81                         titleRow.createCell(3).setCellValue("摂取量");
  82                         int colIndex = COL_INDEX_START;
  83                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
  84                                 titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
  85                                 colIndex++;
  86                         }
  87
  88                         //「単位」行
  89                         Row unitRow = calcSheet.createRow(2);
  90                         unitRow.createCell(2).setCellValue("単位");
  91                         unitRow.createCell(3).setCellValue("g");
  92                         colIndex = COL_INDEX_START;
  93                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
  94                                 unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
  95                                 colIndex++;
  96                         }
  97
  98                         //「栄養計算」行
  99                         List<String> usedTableList = new ArrayList<String>();
 100                         int rowIndex = 3;
 101                         for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
 102                                 Row thisRow = calcSheet.createRow(rowIndex);
 103
 104                                 //「食品名」
 105                                 thisRow.createCell(1).setCellStyle(csPool.getCellStyle("00000", false));
 106                                 thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",成分表!$B$13:$BL$2500,3,FALSE),\"\")");
 107                                 thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false));
 108
 109                                 colIndex = COL_INDEX_START;
 110                                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 111                                         Cell thisCell = thisRow.createCell(colIndex);
 112                                         thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 113
 114                                         if(aColumn.getFormula().length() >= 1) {
 115                                                 //「計算式」列
 116                                                 String formula = "(" + aColumn.getFormula() + ")";
 117                                                 for(String aAlias : nch.getNutritionAliasList()) {
 118                                                         String cell = new CellReference(rowIndex, 4 + nch.indexOf(aAlias)).formatAsString();
 119                                                         formula = formula.replaceAll("([^A-Za-z0-9_])" + aAlias + "([^A-Za-z0-9_])", "$1" + cell + "$2");
 120                                                 }
 121                                                 //System.out.println(formula);
 122                                                 thisCell.setCellFormula(formula);
 123                                                 //thisCell.setCellValue(formula);
 124
 125
 126                                         } else {
 127                                                 String div100 = aColumn.isUseRawValue() ? "" :  "/ 100 * $D" + (rowIndex + 1);
 128                                                 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 + ",\"\")");
 129                                         }
 130
 131                                         colIndex++;
 132
 133                                         usedTableList.add(aColumn.getTable());
 134                                 }
 135
 136                         }
 137
 138
 139                         //摂取量 範囲を記憶
 140                         String intakeArea = new CellReference(3, 3, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, 3, true, true).formatAsString();
 141                         namedAreaMap.put("AREA_INTAKE", intakeArea);
 142
 143
 144                         //「合計」行
 145                         Row sumRow = calcSheet.createRow(rowIndex);
 146                         sumRow.createCell(1).setCellValue("合計");
 147                         calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
 148                         colIndex = COL_INDEX_START;
 149                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 150                                 Cell thisCell = sumRow.createCell(colIndex);
 151                                 String sumTargetArea = new CellReference(3, colIndex, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, colIndex, true, true).formatAsString();
 152
 153                                 //範囲を記憶（alias あれば設定）
 154                                 if(aColumn.getAlias().length() > 0) {
 155                                         namedAreaMap.put("AREA_" + aColumn.getAlias(), sumTargetArea);
 156
 157                                         if(aColumn.isUseSum()) {
 158                                                 String sumArea = new CellReference(rowIndex, colIndex, true, true).formatAsString();
 159                                                 namedAreaMap.put("SUM_" + aColumn.getAlias(), sumArea);
 160                                                 //System.out.println("SUM_" + aColumn.getAlias() + " --- " +  sumArea);
 161                                         }
 162                                 }
 163
 164                                 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 165                                 if(aColumn.isUseSum()) {
 166                                         thisCell.setCellFormula("SUM(" + sumTargetArea + ")");
 167                                 }
 168                                 colIndex++;
 169                         }
 170
 171
 172                         //「PFCバランス」出力
 173                         if(cmd.hasOption("with-pfc-balance")) {
 174                                 rowIndex += 3;
 175                                 rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex, namedAreaMap);
 176                                 rowIndex += 1;
 177                                 rowIndex = generatePfcBalance8(calcSheet, csPool, rowIndex, namedAreaMap);
 178                         }
 179
 180                         //「食品群別摂取量」出力
 181                         if(cmd.hasOption("with-group-sum")) {
 182                                 rowIndex += 3;
 183                                 rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap);
 184                         }
 185
 186
 187                         //未使用表シート削除
 188                         for(int si = outputWorkbook.getNumberOfSheets() - 1 ; si >= 1 ; si--) {
 189                                 String sheetName = outputWorkbook.getSheetName(si);
 190                                 boolean used = false;
 191                                 for(String usedTable : usedTableList) {
 192                                         if(usedTable.equals(sheetName)) {
 193                                                 used = true;
 194                                         }
 195                                 }
 196                                 if(!used) {
 197                                         outputWorkbook.removeSheetAt(si);
 198                                 }
 199                         }
 200
 201                         //ブック出力
 202                         FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName);
 203                         outputWorkbook.setActiveSheet(0);
 204                         calcSheet.setForceFormulaRecalculation(true);
 205                         outputWorkbook.setSelectedTab(0);
 206                         outputWorkbook.write(outputXlsxFile);
 207                         outputWorkbook.close();
 208
 209                 } catch (Exception e) {
 210                         // TODO 自動生成された catch ブロック
 211                         e.printStackTrace();
 212                 }
 213         }
 214
 215
 216
 217         //PFCバランス
 218         private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
 219                 Row pfbBalanceRow1 = calcSheet.createRow(rowIndex);
 220                 pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%) 七訂の方法で計算したエネルギー量で計算");
 221                 calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
 222                 pfbBalanceRow1.createCell(3).setCellValue("P");
 223                 pfbBalanceRow1.createCell(4).setCellValue("F");
 224                 pfbBalanceRow1.createCell(5).setCellValue("C");
 225
 226                 final String sumKiloCalorieCell = _namedAreaMap.get("SUM_KCAL");
 227
 228                 rowIndex++;
 229                 Row pfbBalanceRow2 = calcSheet.createRow(rowIndex);
 230                 Cell pCell = pfbBalanceRow2.createCell(3);
 231                 pCell.setCellStyle(csPool.getCellStyle("0"));
 232                 pCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_P") + "*4*100/" + sumKiloCalorieCell + ",0)");
 233
 234                 Cell fCell = pfbBalanceRow2.createCell(4);
 235                 fCell.setCellStyle(csPool.getCellStyle("0"));
 236                 fCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_F") + "*9*100/" + sumKiloCalorieCell + ",0)");
 237
 238                 Cell cCell = pfbBalanceRow2.createCell(5);
 239                 cCell.setCellStyle(csPool.getCellStyle("0"));
 240                 cCell.setCellFormula("100 - (" + new CellReference(cCell.getRowIndex(), 3).formatAsString() + " + "
 241                                               + new CellReference(cCell.getRowIndex(), 4).formatAsString() + ")");
 242
 243                 return rowIndex;
 244         }
 245
 246
 247         //PFCバランス（八訂）
 248         private static int generatePfcBalance8(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
 249                 Row pfbBalanceRow1 = calcSheet.createRow(rowIndex);
 250                 pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%) 八訂のエネルギー量で計算");
 251                 calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
 252                 pfbBalanceRow1.createCell(3).setCellValue("P");
 253                 pfbBalanceRow1.createCell(4).setCellValue("F");
 254                 pfbBalanceRow1.createCell(5).setCellValue("C");
 255
 256                 final String sumKiloCalorieCell = _namedAreaMap.get("SUM_KCAL");
 257
 258                 rowIndex++;
 259                 Row pfbBalanceRow2 = calcSheet.createRow(rowIndex);
 260                 Cell pCell = pfbBalanceRow2.createCell(3);
 261                 pCell.setCellStyle(csPool.getCellStyle("0"));
 262                 pCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_P_ENG") + "*4*100/" + sumKiloCalorieCell + ",0)");
 263
 264                 Cell fCell = pfbBalanceRow2.createCell(4);
 265                 fCell.setCellStyle(csPool.getCellStyle("0"));
 266                 fCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_F_ENG") + "*9*100/" + sumKiloCalorieCell + ",0)");
 267
 268                 Cell cCell = pfbBalanceRow2.createCell(5);
 269                 cCell.setCellStyle(csPool.getCellStyle("0"));
 270                 cCell.setCellFormula("100 - (" + new CellReference(cCell.getRowIndex(), 3).formatAsString() + " + "
 271                                               + new CellReference(cCell.getRowIndex(), 4).formatAsString() + ")");
 272
 273                 return rowIndex;
 274         }
 275
 276
 277          //群別摂取量
 278         private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
 279
 280                 List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
 281                                 "種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
 282                                 "油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");
 283
 284
 285
 286                 Row groupRow = calcSheet.createRow(rowIndex);
 287                 groupRow.createCell(1).setCellValue("食品群");
 288                 groupRow.createCell(3).setCellValue("摂取量(g)");
 289                 rowIndex++;
 290
 291                 for(int i = 1; i <= 18; i++,rowIndex++) {
 292                         Row thisRow = calcSheet.createRow(rowIndex);
 293                         thisRow.createCell(1).setCellValue(i);
 294                         thisRow.createCell(2).setCellValue(groupName.get(i));
 295                         Cell cCell = thisRow.createCell(3);
 296                         cCell.setCellStyle(csPool.getCellStyle(""));
 297                         //cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)");
 298                         cCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_GROUP") + ", " + i + ", " + _namedAreaMap.get("AREA_INTAKE") + ")");
 299
 300                         if(i == 6) {
 301                                 rowIndex++;
 302                                 thisRow = calcSheet.createRow(rowIndex);
 303                                 thisRow.createCell(2).setCellValue("うち　緑黄色野菜");
 304                                 Cell bcvCell = thisRow.createCell(3);
 305                                 bcvCell.setCellStyle(csPool.getCellStyle("0"));
 306                                 //bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)");
 307                                 bcvCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_BRIGHT_COLORED_VEGETABLE") + ", 1, " + _namedAreaMap.get("AREA_INTAKE") + ")");
 308                         }
 309
 310
 311                 }
 312
 313                 return rowIndex;
 314         }
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325 }