「付加行」機能の実装中。
[nucalgen] / 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 }