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.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 import jp.satomichan.nucalgen.addition.AcCell;
  25 import jp.satomichan.nucalgen.addition.AcRow;
  26 import jp.satomichan.nucalgen.addition.AdditionConfig;
  27 import jp.satomichan.nucalgen.addition.AdditionUtil;
  28
  29 public class Nucalgen {
  30         //開始位置　最初の食品の、最初の栄養成分の位置 (E4セル)
  31         private static final int ROW_INDEX_START = 3;
  32         private static final int COL_INDEX_START = 4;
  33
  34         public static void main(String[] args) {
  35                 //コマンドライン・オプション読み込み
  36                 Options options = new Options();
  37                 options.addOption(Option.builder("s").required().hasArg().longOpt("std-food-comp-table").build());
  38                 options.addOption(Option.builder("c").required().hasArg().longOpt("columns").build());
  39                 options.addOption(Option.builder("o").required().hasArg().longOpt("output").build());
  40                 options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build());
  41                 options.addOption(Option.builder("add").hasArgs().longOpt("addition").build());
  42                 options.addOption(Option.builder("bright").hasArg().longOpt("bright-colored-vegetables-list").build());
  43                 options.addOption(Option.builder("protect").longOpt("set-protect").build());
  44                 options.addOption(Option.builder("processed").longOpt("use-processed-table").build());
  45
  46                 NamedAreaStore namedArea = NamedAreaStore.getInstance();
  47
  48                 try {
  49
  50                         //コマンドライン引数 解析
  51                         CommandLineParser parser = new DefaultParser();
  52                         CommandLine cmd = parser.parse(options, args);
  53                         final String mextStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table");
  54                         final String columnsXmlFileName = cmd.getOptionValue("columns");
  55                         final String outputXlsxFileName = cmd.getOptionValue("output");
  56                         final int lines = Integer.parseInt(cmd.getOptionValue("lines"));
  57
  58                         //コンフィグ （列設定） 読み込み
  59                         NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);
  60
  61                         //Book生成
  62                         Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName));
  63
  64                         if(cmd.hasOption("use-processed-table") == false) {
  65                                 //成分表 変換
  66                                 MextStdFoodCompTable stdCompTable = new MextStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
  67                                 stdCompTable.processInto(outputWorkbook);
  68                         }
  69
  70
  71                         //「栄養価計算」シート生成
  72                         Sheet calcSheet = outputWorkbook.createSheet("栄養価計算");
  73                         outputWorkbook.setSheetOrder("栄養価計算", 0);
  74                         if(cmd.hasOption("set-protect")) {
  75                                 calcSheet.protectSheet("");
  76                         }
  77                         calcSheet.setColumnWidth(COL_INDEX_START -2, 10240); //C「食品名」列
  78                         calcSheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1,
  79                                                            COL_INDEX_START -3, COL_INDEX_START -3));  //B2:B3「食品番号」セル
  80
  81                         CellStylePool csPool = new CellStylePool(outputWorkbook);
  82
  83                         //「タイトル」行
  84                         Row titleRow = calcSheet.createRow(ROW_INDEX_START -2);
  85                         titleRow.createCell(COL_INDEX_START -3).setCellValue("食品番号");
  86                         titleRow.createCell(COL_INDEX_START -2).setCellValue("食品名");
  87                         titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量");
  88                         int colIndex = COL_INDEX_START;
  89                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
  90                                 titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
  91                                 colIndex++;
  92                         }
  93
  94                         //「単位」行
  95                         Row unitRow = calcSheet.createRow(ROW_INDEX_START -1);
  96                         unitRow.createCell(COL_INDEX_START -2).setCellValue("単位");
  97                         unitRow.createCell(COL_INDEX_START -1).setCellValue("g");
  98                         colIndex = COL_INDEX_START;
  99                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 100                                 unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
 101                                 colIndex++;
 102                         }
 103
 104                         //「栄養計算」行
 105                         List<String> usedTableList = new ArrayList<String>();
 106                         int rowIndex = ROW_INDEX_START;
 107                         for(int i = rowIndex; i < lines + ROW_INDEX_START; i++,rowIndex++) {
 108                                 Row thisRow = calcSheet.createRow(rowIndex);
 109
 110                                 //「食品名」
 111                                 thisRow.createCell(1).setCellStyle(csPool.getCellStyle("00000", false));
 112                                 thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) +
 113                                                                              ",成分表!$B$13:$BL$2500,3,FALSE),\"\")");
 114                                 thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false));
 115
 116                                 colIndex = COL_INDEX_START;
 117                                 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 118                                         Cell thisCell = thisRow.createCell(colIndex);
 119                                         thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 120
 121                                         if(aColumn.getFormula().length() >= 1) {
 122                                                 //「計算式」列
 123                                                 String formula = aColumn.getFormula();
 124                                                 for(String aAlias : nch.getNutritionAliasList()) {
 125                                                         formula = replaceFormula(formula, aAlias, rowIndex, COL_INDEX_START + nch.indexOf(aAlias));
 126                                                 }
 127                                                 thisCell.setCellFormula(formula);
 128
 129
 130                                         } else {
 131                                                 //通常の栄養素の列
 132                                                 String div100 = aColumn.isUseRawValue() ? "" :  "/ 100 * $D" + (rowIndex + 1);
 133                                                 thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + "," + aColumn.getTable() +
 134                                                                                 "!$B$13:$BL$2500,MATCH(\"" + aColumn.getName() + "\"," +
 135                                                                                 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                         namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);
 148
 149
 150                         //「合計」行
 151                         Row sumRow = calcSheet.createRow(rowIndex);
 152                         sumRow.createCell(1).setCellValue("合計");
 153                         calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
 154                         colIndex = COL_INDEX_START;
 155                         for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
 156                                 Cell thisCell = sumRow.createCell(colIndex);
 157
 158                                 //範囲を記憶（alias あれば設定）
 159                                 if(aColumn.getAlias().length() > 0) {
 160                                         namedArea.save("AREA_" + aColumn.getAlias(), ROW_INDEX_START, colIndex, rowIndex -1, colIndex);
 161
 162                                         if(aColumn.isUseSum()) {
 163                                                 namedArea.save("SUM_" + aColumn.getAlias(), rowIndex, colIndex);
 164                                         }
 165                                 }
 166
 167                                 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 168                                 if(aColumn.isUseSum()) {
 169                                         thisCell.setCellFormula("SUM(" +
 170                                                             NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) +
 171                                                             ")");
 172                                 }
 173                                 colIndex++;
 174                         }
 175
 176
 177                         //「付加行」出力
 178                         String[] additionOptionValues = cmd.getOptionValues("addition");
 179                         if(additionOptionValues != null) {
 180                                 for(String aAdditionFileName : additionOptionValues) {
 181                                         rowIndex += 2;
 182                                         rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedArea);
 183                                 }
 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
 218
 219
 220
 221
 222
 223         //「付加行」生成
 224         private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex,
 225                                                       NamedAreaStore namedArea) {
 226
 227                 AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
 228                 for(AcRow acRow : ac.getRows()) { //行ごとのループ
 229                         Row thisRow = calcSheet.createRow(rowIndex);
 230                         int colIndex = 0;
 231
 232                         for(AcCell acCell : acRow.getCells()) { //セルごとのループ
 233                                 Cell thisCell = thisRow.createCell(colIndex);
 234
 235                                 //alias 「付加行」内の別名定義（制約：右方・下方のセルからしか参照できない）
 236                                 if(acCell.getAlias() != null) {
 237                                         namedArea.save(acCell.getAlias(), rowIndex, colIndex);
 238                                 }
 239
 240                                 //formula 計算式
 241                                 if(acCell.getFormula() != null){
 242                                         String formula = acCell.getFormula();
 243                                         for(Entry<String, String> keyValue : namedArea.entrySet()) {
 244                                                 String k = keyValue.getKey();
 245                                                 String v = keyValue.getValue();
 246
 247                                                 formula = replaceFormula(formula, k, v);
 248                                         }
 249                                         thisCell.setCellFormula(formula);
 250
 251                                 //value そのままの値
 252                                 } else if(acCell.getValue() != null) {
 253                                         thisCell.setCellValue(acCell.getValue());
 254
 255                                 //formula でも value でもない
 256                                 } else {
 257                                         //
 258                                 }
 259
 260
 261                                 colIndex++;
 262                         } //セルごとのループ
 263
 264                         rowIndex++;
 265                 } //行ごとのループ
 266
 267
 268
 269                 return rowIndex - 1;
 270         }
 271
 272
 273
 274         //計算式中の文字列（名前付き範囲）置換
 275         private static String replaceFormula(String formula, String target, String replacement) {
 276                 formula = "<" + formula + ">";
 277                 replacement = Matcher.quoteReplacement(replacement);
 278                 formula = formula.replaceAll("([^A-Za-z0-9_])" + target + "([^A-Za-z0-9_])", "$1" + replacement + "$2");
 279                 formula = formula.replaceAll("^<", "");
 280                 formula = formula.replaceAll(">$", "");
 281                 return formula;
 282         }
 283
 284         //計算式中の 文字列（名前付き範囲）→セル座標 置換
 285         private static String replaceFormula(String formula, String target, int cellRow, int cellCol) {
 286                 return replaceFormula(formula, target, new CellReference(cellRow, cellCol).formatAsString());
 287         }
 288
 289
 290
 291
 292
 293
 294 }