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.List;
   8 import java.util.Map.Entry;
   9 import java.util.regex.Matcher;
  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.commons.lang.StringUtils;
  17 import org.apache.poi.ss.usermodel.Cell;
  18 import org.apache.poi.ss.usermodel.Row;
  19 import org.apache.poi.ss.usermodel.Sheet;
  20 import org.apache.poi.ss.usermodel.Workbook;
  21 import org.apache.poi.ss.usermodel.WorkbookFactory;
  22 import org.apache.poi.ss.util.CellRangeAddress;
  23 import org.apache.poi.ss.util.CellReference;
  24
  25 import jp.satomichan.nucalgen.addition.AcCell;
  26 import jp.satomichan.nucalgen.addition.AcRow;
  27 import jp.satomichan.nucalgen.addition.AdditionConfig;
  28 import jp.satomichan.nucalgen.addition.AdditionUtil;
  29
  30 public class Nucalgen {
  31         //開始位置　最初の食品の、最初の栄養成分の位置 (E4セル)
  32         private static final int ROW_INDEX_START = 3;
  33         private static final int COL_INDEX_START = 4;
  34
  35         public static void main(String[] args) {
  36                 //コマンドライン・オプション読み込み
  37                 Options options = new Options();
  38                 options.addOption(Option.builder("s").required().hasArg().longOpt("std-food-comp-table").build());
  39                 options.addOption(Option.builder("c").required().hasArg().longOpt("columns").build());
  40                 options.addOption(Option.builder("o").required().hasArg().longOpt("output").build());
  41                 options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build());
  42                 options.addOption(Option.builder("add").hasArgs().longOpt("addition").build());
  43                 options.addOption(Option.builder("sheets").hasArgs().build());
  44                 options.addOption(Option.builder("bright").hasArg().longOpt("bright-colored-vegetables-list").build());
  45                 options.addOption(Option.builder("protect").longOpt("set-protect").build());
  46                 options.addOption(Option.builder("processed").longOpt("use-processed-table").build());
  47
  48                 NamedAreaStore namedArea = NamedAreaStore.getInstance();
  49
  50                 try {
  51
  52                         //コマンドライン引数 解析
  53                         CommandLineParser parser = new DefaultParser();
  54                         CommandLine cmd = parser.parse(options, args);
  55                         final String mextStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table");
  56                         final String columnsXmlFileName = cmd.getOptionValue("columns");
  57                         final String outputXlsxFileName = cmd.getOptionValue("output");
  58                         final int lines = Integer.parseInt(cmd.getOptionValue("lines"));
  59
  60                         //コンフィグ （列設定） 読み込み
  61                         NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);
  62
  63                         //Book生成
  64                         Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName));
  65
  66                         if(cmd.hasOption("use-processed-table") == false) {
  67                                 //成分表 変換
  68                                 MextStdFoodCompTable stdCompTable = new MextStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
  69                                 stdCompTable.processInto(outputWorkbook);
  70                         }
  71
  72
  73                         //「栄養価計算」シート生成
  74                         CellStylePool csPool = new CellStylePool(outputWorkbook);
  75                         List<String> usedTableList = new ArrayList<String>();
  76                         List<String> sheetNameList = new ArrayList<String>();
  77
  78
  79                         String[] sheets = cmd.getOptionValues("sheets");
  80                         if(sheets != null && sheets.length >= 2) {
  81                                 //表が複数
  82                                 for(int si = 0; si < sheets.length - 1; si++) {
  83                                         final String sheetName =  sheets[si];
  84                                         generateCalculationSheet(outputWorkbook, sheetName, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList);
  85                                         outputWorkbook.setSheetOrder(sheetName, si);
  86                                         sheetNameList.add(sheetName);
  87                                 }
  88                                 final String sumSheetName =  sheets[sheets.length - 1];
  89                                 generateSumSheet(outputWorkbook, sumSheetName, sheetNameList, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList);
  90                                 outputWorkbook.setSheetOrder(sumSheetName, sheets.length - 1);
  91
  92                         }else {
  93                                 //表がひとつ
  94                                 final String sheetName = (sheets != null) ? sheets[0] : "栄養価計算";
  95                                 generateCalculationSheet(outputWorkbook, sheetName, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList);
  96                                 outputWorkbook.setSheetOrder(sheetName, 0);
  97                                 sheetNameList.add(sheetName);
  98                         }
  99
 100
 101                         //未使用表シート削除
 102                         for(int si = outputWorkbook.getNumberOfSheets() - 1 ; si >= 1 ; si--) {
 103                                 String sheetName = outputWorkbook.getSheetName(si);
 104                                 boolean used = false;
 105                                 for(String usedTable : usedTableList) {
 106                                         if(usedTable.equals(sheetName)) {
 107                                                 used = true;
 108                                         }
 109                                 }
 110                                 if(!used) {
 111                                         outputWorkbook.removeSheetAt(si);
 112                                 }
 113                         }
 114
 115                         //ブック出力
 116                         FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName);
 117                         outputWorkbook.setActiveSheet(0);
 118
 119                         outputWorkbook.setSelectedTab(0);
 120                         outputWorkbook.write(outputXlsxFile);
 121                         outputWorkbook.close();
 122
 123                 } catch (Exception e) {
 124                         // TODO 自動生成された catch ブロック
 125                         e.printStackTrace();
 126                 }
 127         }
 128
 129
 130
 131
 132
 133
 134
 135
 136         //「栄養価計算」シート生成
 137         private static void generateCalculationSheet(Workbook book, String sheetName, NamedAreaStore namedArea, boolean setProtect,
 138                         String[] additionOptionValues, final int lines,
 139                         NutritionColumnHolder nch, CellStylePool csPool, List<String> usedTableList) {
 140
 141
 142                 Sheet calcSheet = book.createSheet(sheetName);
 143
 144                 if(setProtect) {
 145                         calcSheet.protectSheet("");
 146                 }
 147                 calcSheet.setColumnWidth(COL_INDEX_START -2, 10240); //C「食品名」列
 148                 calcSheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1,
 149                                                    COL_INDEX_START -3, COL_INDEX_START -3));  //B2:B3「食品番号」セル
 150
 151
 152                 //「タイトル」行
 153                 Row titleRow = calcSheet.createRow(ROW_INDEX_START -2);
 154                 titleRow.createCell(COL_INDEX_START -3).setCellValue("食品番号");
 155                 titleRow.createCell(COL_INDEX_START -2).setCellValue("食品名");
 156                 titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量");
 157                 int colIndex = COL_INDEX_START;
 158                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 159                         titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
 160                         colIndex++;
 161                 }
 162
 163                 //「単位」行
 164                 Row unitRow = calcSheet.createRow(ROW_INDEX_START -1);
 165                 unitRow.createCell(COL_INDEX_START -2).setCellValue("単位");
 166                 unitRow.createCell(COL_INDEX_START -1).setCellValue("g");
 167                 colIndex = COL_INDEX_START;
 168                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 169                         unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
 170                         colIndex++;
 171                 }
 172
 173                 //「栄養計算」行
 174                 int rowIndex = ROW_INDEX_START;
 175                 for(int i = rowIndex; i < lines + ROW_INDEX_START; i++,rowIndex++) {
 176                         Row thisRow = calcSheet.createRow(rowIndex);
 177
 178                         //「食品名」
 179                         thisRow.createCell(COL_INDEX_START -3).setCellStyle(csPool.getCellStyle("00000", false));
 180                         thisRow.createCell(COL_INDEX_START -2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) +
 181                                                                      ",成分表!$B$13:$BL$2500,3,FALSE),\"\")");
 182                         thisRow.createCell(COL_INDEX_START -1).setCellStyle(csPool.getCellStyle("", false));
 183
 184                         colIndex = COL_INDEX_START;
 185                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 186                                 Cell thisCell = thisRow.createCell(colIndex);
 187                                 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 188
 189                                 if(aColumn.getFormula().length() >= 1) {
 190                                         //「計算式」列
 191                                         String formula = aColumn.getFormula();
 192                                         for(String aAlias : nch.getNutritionAliasList()) {
 193                                                 formula = replaceFormula(formula, aAlias, rowIndex, COL_INDEX_START + nch.indexOf(aAlias));
 194                                         }
 195                                         thisCell.setCellFormula(formula);
 196
 197
 198                                 } else {
 199                                         //通常の栄養素の列
 200                                         String div100 = aColumn.isUseRawValue() ? "" :  "/ 100 * $D" + (rowIndex + 1);
 201                                         thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + "," + aColumn.getTable() +
 202                                                                         "!$B$13:$BL$2500,MATCH(\"" + aColumn.getName() + "\"," +
 203                                                                         aColumn.getTable() + "!$B$12:$BL$12,0),FALSE) " + div100 + ",\"\")");
 204                                 }
 205
 206                                 colIndex++;
 207
 208                                 usedTableList.add(aColumn.getTable());
 209                                 usedTableList.add(sheetName);
 210                         }
 211
 212                 }
 213
 214
 215                 //摂取量 範囲を記憶
 216                 namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);
 217
 218
 219                 //「合計」行
 220                 Row sumRow = calcSheet.createRow(rowIndex);
 221                 sumRow.createCell(COL_INDEX_START -3).setCellValue("合計");
 222                 calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, COL_INDEX_START -3, COL_INDEX_START -2));
 223
 224                 //「摂取量」合計
 225                 Cell intakeSumCell = sumRow.createCell(COL_INDEX_START -1);
 226                 intakeSumCell.setCellFormula("SUM(" + namedArea.load("AREA_INTAKE") + ")");
 227                 namedArea.save("SUM_INTAKE", intakeSumCell);
 228
 229                 colIndex = COL_INDEX_START;
 230                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 231                         Cell thisCell = sumRow.createCell(colIndex);
 232
 233                         //範囲を記憶
 234                         String areaName = aColumn.getAlias().length() >= 1 ? ("AREA_" + aColumn.getAlias()) : ("AREAID_" + aColumn.getName());
 235                         namedArea.save(areaName, ROW_INDEX_START, colIndex, rowIndex -1, colIndex);
 236
 237                         if(aColumn.isUseSum()) {
 238                                 String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName());
 239                                 namedArea.save(sumName, thisCell);
 240                         }
 241
 242                         thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 243                         if(aColumn.isUseSum()) {
 244                                 thisCell.setCellFormula("SUM(" +
 245                                                     NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) +
 246                                                     ")");
 247                         }
 248                         colIndex++;
 249                 }
 250
 251
 252                 //「付加行」出力
 253                 if(additionOptionValues != null) {
 254                         for(String aAdditionFileName : additionOptionValues) {
 255                                 rowIndex += 2;
 256                                 rowIndex = generateAddition(aAdditionFileName, calcSheet, null, csPool, rowIndex, namedArea);
 257                         }
 258                 }
 259
 260                 calcSheet.setForceFormulaRecalculation(true);
 261
 262         }
 263
 264
 265
 266
 267
 268         //集計用シート生成
 269         private static void generateSumSheet(Workbook book, String sheetName, List<String> sheetNameList, NamedAreaStore namedArea, boolean setProtect,
 270                         String[] additionOptionValues, final int lines,
 271                         NutritionColumnHolder nch, CellStylePool csPool, List<String> usedTableList) {
 272
 273                 usedTableList.add(sheetName);
 274                 Sheet sheet = book.createSheet(sheetName);
 275
 276                 if(setProtect) {
 277                         sheet.protectSheet("");
 278                 }
 279
 280                 //「表」
 281                 sheet.setColumnWidth(COL_INDEX_START -2, 10240); //C
 282                 sheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1,
 283                                                    COL_INDEX_START -3, COL_INDEX_START -2));  //B2:C3「表」セル
 284
 285
 286                 //「タイトル」行
 287                 Row titleRow = sheet.createRow(ROW_INDEX_START -2);
 288                 titleRow.createCell(COL_INDEX_START -3).setCellValue("表");
 289                 titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量");
 290                 int colIndex = COL_INDEX_START;
 291                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 292                         titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
 293                         colIndex++;
 294                 }
 295
 296                 //「単位」行
 297                 Row unitRow = sheet.createRow(ROW_INDEX_START -1);
 298                 unitRow.createCell(COL_INDEX_START -2).setCellValue("単位");
 299                 unitRow.createCell(COL_INDEX_START -1).setCellValue("g");
 300                 colIndex = COL_INDEX_START;
 301                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 302                         unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
 303                         colIndex++;
 304                 }
 305
 306                 //表ごとの小計
 307                 int rowIndex = ROW_INDEX_START;
 308                 for(String aSheetName : sheetNameList) {
 309                         Row thisRow = sheet.createRow(rowIndex);
 310
 311                         sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex,
 312                            COL_INDEX_START -3, COL_INDEX_START -2));  //B:C「表」セル
 313
 314                         //「表」列
 315                         thisRow.createCell(COL_INDEX_START -3).setCellValue(aSheetName);
 316
 317                         //「摂取量」列
 318                         thisRow.createCell(COL_INDEX_START -1).setCellFormula(aSheetName + "!" + namedArea.load("SUM_INTAKE"));
 319
 320
 321                         //「表ごとの小計」列
 322                         colIndex = COL_INDEX_START;
 323                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 324                                 Cell thisCell = thisRow.createCell(colIndex);
 325                                 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 326                                 if(aColumn.isUseSum()) {
 327                                         String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName());
 328                                         String formula = aSheetName + "!" + namedArea.load(sumName);
 329                                         thisCell.setCellFormula(formula);
 330                                 }
 331
 332                                 colIndex++;
 333
 334                         }
 335
 336                         rowIndex++;
 337                 }
 338
 339
 340                 //摂取量 範囲を記憶
 341                 namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);
 342
 343                 //「合計」行
 344                 Row sumRow = sheet.createRow(rowIndex);
 345                 sumRow.createCell(COL_INDEX_START -3).setCellValue("合計");
 346                 sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, COL_INDEX_START -3, COL_INDEX_START -2));
 347
 348                 Cell intakeSumCell = sumRow.createCell(COL_INDEX_START -1);
 349                 intakeSumCell.setCellFormula("SUM(" + namedArea.load("AREA_INTAKE") + ")");
 350
 351
 352                 colIndex = COL_INDEX_START;
 353                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 354                         Cell thisCell = sumRow.createCell(colIndex);
 355
 356                         //範囲を記憶
 357                         String areaName = aColumn.getAlias().length() >= 1 ? ("AREA_" + aColumn.getAlias()) : ("AREAID_" + aColumn.getName());
 358                         namedArea.save(areaName, ROW_INDEX_START, colIndex, rowIndex -1, colIndex);
 359
 360                         if(aColumn.isUseSum()) {
 361                                 String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName());
 362                                 namedArea.save(sumName, thisCell);
 363                         }
 364
 365
 366                         thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 367                         if(aColumn.isUseSum()) {
 368                                 thisCell.setCellFormula("SUM(" +
 369                                                     NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) +
 370                                                     ")");
 371                         }
 372                         colIndex++;
 373                 }
 374
 375
 376                 //「付加行」出力
 377                 if(additionOptionValues != null) {
 378                         for(String aAdditionFileName : additionOptionValues) {
 379                                 rowIndex += 2;
 380                                 rowIndex = generateAddition(aAdditionFileName, sheet, sheetNameList, csPool, rowIndex, namedArea);
 381                         }
 382                 }
 383
 384                 sheet.setForceFormulaRecalculation(true);
 385
 386         }
 387
 388
 389
 390
 391         //「付加行」生成
 392         private static int generateAddition(String fileName, Sheet calcSheet, List<String> sheetNameList, CellStylePool csPool, int rowIndex,
 393                                                       NamedAreaStore namedArea) {
 394
 395                 AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
 396                 int cellCounter = 0;
 397                 for(AcRow acRow : ac.getRows()) { //行ごとのループ
 398                         Row thisRow = calcSheet.createRow(rowIndex);
 399                         int colIndex = 0;
 400
 401                         for(AcCell acCell : acRow.getCells()) { //セルごとのループ
 402                                 Cell thisCell = thisRow.createCell(colIndex);
 403
 404                                 cellCounter++;
 405                                 if(sheetNameList == null) {
 406                                         String areaName = "ADDITION_" + fileName + "_" + cellCounter;
 407                                         namedArea.save(areaName, rowIndex, colIndex);
 408                                 }
 409
 410                                 //alias 「付加行」内の別名定義（制約：右方・下方のセルからしか参照できない）
 411                                 if(acCell.getAlias() != null) {
 412                                         namedArea.save(acCell.getAlias(), rowIndex, colIndex);
 413                                 }
 414
 415                                 //formula 計算式
 416                                 if(acCell.getFormula() != null){
 417                                         String formula = acCell.getFormula();
 418
 419                                         if(sheetNameList != null && formula.indexOf("AREA_") != -1) {
 420                                                 //集計シート && 名前付き範囲 AREA_ が対象に含まれる
 421                                                 List<String> sumTarget = new ArrayList<String>();
 422                                                 for(String aSheetName : sheetNameList) {
 423                                                         String areaName = "ADDITION_" + fileName + "_" + cellCounter;
 424                                                         sumTarget.add(aSheetName + "!" + namedArea.load(areaName));
 425                                                 }
 426                                                 formula = "SUM(" + StringUtils.join(sumTarget, ",") + ")";
 427
 428                                         }else {
 429                                                 //それ以外
 430                                                 for(Entry<String, String> keyValue : namedArea.entrySet()) {
 431                                                         String k = keyValue.getKey();
 432                                                         String v = keyValue.getValue();
 433
 434                                                         formula = replaceFormula(formula, k, v);
 435                                                 }
 436                                         }
 437                                         thisCell.setCellFormula(formula);
 438
 439                                 //value そのままの値
 440                                 } else if(acCell.getValue() != null) {
 441                                         thisCell.setCellValue(acCell.getValue());
 442
 443                                 //formula でも value でもない
 444                                 } else {
 445                                         //
 446                                 }
 447
 448
 449                                 colIndex++;
 450                         } //セルごとのループ
 451
 452                         rowIndex++;
 453                 } //行ごとのループ
 454
 455
 456
 457                 return rowIndex - 1;
 458         }
 459
 460
 461
 462         //計算式中の文字列（名前付き範囲）置換
 463         private static String replaceFormula(String formula, String target, String replacement) {
 464                 formula = "<" + formula + ">";
 465                 replacement = Matcher.quoteReplacement(replacement);
 466                 formula = formula.replaceAll("([^A-Za-z0-9_])" + target + "([^A-Za-z0-9_])", "$1" + replacement + "$2");
 467                 formula = formula.replaceAll("^<", "");
 468                 formula = formula.replaceAll(">$", "");
 469                 return formula;
 470         }
 471
 472         //計算式中の 文字列（名前付き範囲）→セル座標 置換
 473         private static String replaceFormula(String formula, String target, int cellRow, int cellCol) {
 474                 return replaceFormula(formula, target, new CellReference(cellRow, cellCol).formatAsString());
 475         }
 476
 477
 478
 479
 480
 481
 482 }