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.Arrays;
   6 import java.util.List;
   7
   8 import org.apache.commons.cli.CommandLine;
   9 import org.apache.commons.cli.CommandLineParser;
  10 import org.apache.commons.cli.DefaultParser;
  11 import org.apache.commons.cli.Option;
  12 import org.apache.commons.cli.Options;
  13 import org.apache.commons.configuration.XMLConfiguration;
  14 import org.apache.poi.ss.usermodel.Cell;
  15 import org.apache.poi.ss.usermodel.Row;
  16 import org.apache.poi.ss.usermodel.Sheet;
  17 import org.apache.poi.ss.usermodel.Workbook;
  18 import org.apache.poi.ss.usermodel.WorkbookFactory;
  19 import org.apache.poi.ss.util.CellRangeAddress;
  20 import org.apache.poi.ss.util.CellReference;
  21 import org.apache.poi.xssf.usermodel.XSSFName;
  22
  23 public class Nucalgen {
  24
  25         public static void main(String[] args) {
  26                 //コマンドライン・オプション読み込み
  27                 Options options = new Options();
  28                 options.addOption(Option.builder("s").required().hasArg().longOpt("std-food-comp-table").build());
  29                 options.addOption(Option.builder("c").required().hasArg().longOpt("columns").build());
  30                 options.addOption(Option.builder("o").required().hasArg().longOpt("output").build());
  31                 options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build());
  32                 options.addOption(Option.builder("p").longOpt("use-processed-table").build());
  33                 options.addOption(Option.builder("pfc").longOpt("with-pfc-balance").build());
  34                 options.addOption(Option.builder("groupsum").longOpt("with-group-sum").build());
  35                 options.addOption(Option.builder("bright").hasArg().longOpt("bright-colored-vegetables-list").build());
  36                 options.addOption(Option.builder("protect").longOpt("set-protect").build());
  37                 options.addOption(Option.builder("r").longOpt("-use-processed-table").build());
  38
  39                 try {
  40
  41                         CommandLineParser parser = new DefaultParser();
  42                         CommandLine cmd = parser.parse(options, args);
  43
  44                         final String moeStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table");
  45                         final String columnsXmlFileName = cmd.getOptionValue("columns");
  46                         final String outputXlsxFileName = cmd.getOptionValue("output");
  47                         final int lines = Integer.parseInt(cmd.getOptionValue("lines"));
  48
  49                         //コンフィグ読み込み
  50                         XMLConfiguration config = new XMLConfiguration(columnsXmlFileName);
  51                         NutritionColumnHolder nc = new NutritionColumnHolder(config);
  52
  53                         //Book生成
  54                         Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(moeStdFoodCompTableFileName));
  55
  56                         if(cmd.hasOption("use-processed-table") == false) {
  57                                 //「本表」変換
  58                                 MoeStdFoodCompTable moe = new MoeStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
  59                                 moe.processInto(outputWorkbook);
  60                         }
  61
  62                         //「別表」削除
  63                         outputWorkbook.removeSheetAt(1);
  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 = 4;
  83                         for(NutritionColumn aColumn : nc.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 = 4;
  93                         for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
  94                                 unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
  95                                 colIndex++;
  96                         }
  97
  98                         //「栄養計算」行
  99                         int rowIndex = 3;
 100                         for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
 101                                 Row thisRow = calcSheet.createRow(rowIndex);
 102
 103                                 thisRow.createCell(1).setCellStyle(csPool.getCellStyle("00000", false));
 104                                 thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",本表!$B$9:$BS$2199,3,FALSE),\"\")");
 105                                 thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false));
 106
 107                                 colIndex = 4;
 108                                 for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
 109                                         Cell thisCell = thisRow.createCell(colIndex);
 110                                         thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 111
 112                                         String div100 = aColumn.isUseRawValue() ? "" :  "/ 100 * $D" + (rowIndex + 1);
 113
 114                                         thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + ",本表!$B$9:$BS$2199,MATCH(\"" + aColumn.getName() + "\",本表!$B$6:$BS$6,0),FALSE) " + div100 + ",\"\")");
 115                                         colIndex++;
 116                                 }
 117
 118                         }
 119
 120
 121                         //摂取量　名前付き範囲
 122                         String intakeArea = new CellReference(calcSheet.getSheetName(), 3, 3, true, true).formatAsString() + ":" + new CellReference(calcSheet.getSheetName(), rowIndex -1, 3, true, true).formatAsString();
 123                         XSSFName intakeNamedRangeArea = (XSSFName) outputWorkbook.createName();
 124                         intakeNamedRangeArea.setNameName("AREA_INTAKE");
 125                         intakeNamedRangeArea.setRefersToFormula(intakeArea);
 126
 127
 128                         //「合計」行
 129                         Row sumRow = calcSheet.createRow(rowIndex);
 130                         sumRow.createCell(1).setCellValue("合計");
 131                         calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
 132                         colIndex = 4;
 133                         for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
 134                                 Cell thisCell = sumRow.createCell(colIndex);
 135                                 String sumArea = new CellReference(calcSheet.getSheetName(), 3, colIndex, true, true).formatAsString() + ":" + new CellReference(calcSheet.getSheetName(), rowIndex -1, colIndex, true, true).formatAsString();
 136
 137                                 //名前付き範囲（alias あれば設定）
 138                                 if(aColumn.getAlias().length() > 0) {
 139                                         XSSFName namedRangeArea = (XSSFName) outputWorkbook.createName();
 140                                         namedRangeArea.setNameName("AREA_" + aColumn.getAlias());
 141                                         namedRangeArea.setRefersToFormula(sumArea);
 142
 143                                         if(aColumn.isUseSum()) {
 144                                                 XSSFName namedRangeSum = (XSSFName) outputWorkbook.createName();
 145                                                 namedRangeSum.setNameName("SUM_" + aColumn.getAlias());
 146                                                 namedRangeSum.setRefersToFormula(new CellReference(calcSheet.getSheetName(), rowIndex, colIndex, true, true).formatAsString());
 147                                         }
 148                                 }
 149
 150                                 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
 151                                 if(aColumn.isUseSum()) {
 152                                         thisCell.setCellFormula("SUM(" + sumArea + ")");
 153                                 }
 154                                 colIndex++;
 155                         }
 156
 157
 158                         //「PFCバランス」出力
 159                         if(cmd.hasOption("with-pfc-balance")) {
 160                                 rowIndex += 3;
 161                                 rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex);
 162                         }
 163
 164                         //「食品群別摂取量」出力
 165                         if(cmd.hasOption("with-group-sum")) {
 166                                 rowIndex += 3;
 167                                 rowIndex = generateGroupSum(calcSheet, csPool, rowIndex);
 168                         }
 169
 170                         //ブック出力
 171                         FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName);
 172                         outputWorkbook.setActiveSheet(0);
 173                         calcSheet.setForceFormulaRecalculation(true);
 174                         outputWorkbook.setSelectedTab(0);
 175                         outputWorkbook.write(outputXlsxFile);
 176                         outputWorkbook.close();
 177
 178                 } catch (Exception e) {
 179                         // TODO 自動生成された catch ブロック
 180                         e.printStackTrace();
 181                 }
 182         }
 183
 184
 185
 186         //PFCバランス
 187         private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex) {
 188                 Row pfbBalanceRow1 = calcSheet.createRow(rowIndex);
 189                 pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%)");
 190                 calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
 191                 pfbBalanceRow1.createCell(3).setCellValue("P");
 192                 pfbBalanceRow1.createCell(4).setCellValue("F");
 193                 pfbBalanceRow1.createCell(5).setCellValue("C");
 194
 195                 rowIndex++;
 196                 Row pfbBalanceRow2 = calcSheet.createRow(rowIndex);
 197                 Cell pCell = pfbBalanceRow2.createCell(3);
 198                 pCell.setCellStyle(csPool.getCellStyle("0"));
 199                 pCell.setCellFormula("SUM_P*4*100/(SUM_P*4+SUM_F*9+SUM_C*4)");
 200                 Cell fCell = pfbBalanceRow2.createCell(4);
 201                 fCell.setCellStyle(csPool.getCellStyle("0"));
 202                 fCell.setCellFormula("SUM_F*9*100/(SUM_P*4+SUM_F*9+SUM_C*4)");
 203                 Cell cCell = pfbBalanceRow2.createCell(5);
 204                 cCell.setCellStyle(csPool.getCellStyle("0"));
 205                 cCell.setCellFormula("SUM_C*4*100/(SUM_P*4+SUM_F*9+SUM_C*4)");
 206
 207                 return rowIndex;
 208         }
 209
 210
 211
 212          //群別摂取量
 213         private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex) {
 214
 215                 List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
 216                                 "種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
 217                                 "油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");
 218
 219                 Row groupRow = calcSheet.createRow(rowIndex);
 220                 groupRow.createCell(1).setCellValue("食品群");
 221                 groupRow.createCell(3).setCellValue("摂取量(g)");
 222                 rowIndex++;
 223
 224                 for(int i = 1; i <= 18; i++,rowIndex++) {
 225                         Row thisRow = calcSheet.createRow(rowIndex);
 226                         thisRow.createCell(1).setCellValue(i);
 227                         thisRow.createCell(2).setCellValue(groupName.get(i));
 228                         Cell cCell = thisRow.createCell(3);
 229                         cCell.setCellStyle(csPool.getCellStyle(""));
 230                         cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)");
 231
 232                         if(i == 6) {
 233                                 rowIndex++;
 234                                 thisRow = calcSheet.createRow(rowIndex);
 235                                 thisRow.createCell(2).setCellValue("うち　緑黄色野菜");
 236                                 Cell bcvCell = thisRow.createCell(3);
 237                                 bcvCell.setCellStyle(csPool.getCellStyle("0"));
 238                                 bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)");
 239
 240                         }
 241
 242
 243                 }
 244
 245                 return rowIndex;
 246         }
 247
 248
 249
 250
 251
 252
 253
 254
 255
 256
 257 }