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

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