1 package jp.satomichan.nucalgen;
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;
11 import java.util.Map.Entry;
12 import java.util.regex.Matcher;
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;
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;
33 public class Nucalgen {
34 private static final int COL_INDEX_START = 4;
36 public static void main(String[] args) {
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());
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());
50 Map<String, String> namedAreaMap = new HashMap<String, String>();
54 CommandLineParser parser = new DefaultParser();
55 CommandLine cmd = parser.parse(options, args);
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"));
63 NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);
66 Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName));
68 if(cmd.hasOption("use-processed-table") == false) {
70 MextStdFoodCompTable stdCompTable = new MextStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
71 stdCompTable.processInto(outputWorkbook);
76 Sheet calcSheet = outputWorkbook.createSheet("栄養価計算");
77 outputWorkbook.setSheetOrder("栄養価計算", 0);
78 if(cmd.hasOption("set-protect")) {
79 calcSheet.protectSheet("");
81 calcSheet.setColumnWidth(2, 10240);
82 calcSheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
84 CellStylePool csPool = new CellStylePool(outputWorkbook);
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());
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());
108 List<String> usedTableList = new ArrayList<String>();
110 for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
111 Row thisRow = calcSheet.createRow(rowIndex);
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));
118 colIndex = COL_INDEX_START;
119 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
120 Cell thisCell = thisRow.createCell(colIndex);
121 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
123 if(aColumn.getFormula().length() >= 1) {
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");
130 thisCell.setCellFormula(formula);
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 + ",\"\")");
140 usedTableList.add(aColumn.getTable());
147 String intakeArea = new CellReference(3, 3, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, 3, true, true).formatAsString();
148 namedAreaMap.put("AREA_INTAKE", intakeArea);
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();
161 if(aColumn.getAlias().length() > 0) {
162 namedAreaMap.put("AREA_" + aColumn.getAlias(), sumTargetArea);
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);
171 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
172 if(aColumn.isUseSum()) {
173 thisCell.setCellFormula("SUM(" + sumTargetArea + ")");
180 String[] additionOptionValues = cmd.getOptionValues("addition");
181 if(additionOptionValues != null) {
182 for(String aAdditionFileName : additionOptionValues) {
184 rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedAreaMap);
189 if(cmd.hasOption("with-group-sum")) {
191 rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap);
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)) {
205 outputWorkbook.removeSheetAt(si);
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();
217 } catch (Exception e) {
218 // TODO 自動生成された catch ブロック
225 private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
227 List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
228 "種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
229 "油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");
233 Row groupRow = calcSheet.createRow(rowIndex);
234 groupRow.createCell(1).setCellValue("食品群");
235 groupRow.createCell(3).setCellValue("摂取量(g)");
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") + ")");
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") + ")");
265 private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex,
266 Map<String,String> _namedAreaMap) {
268 System.out.println("ADDITION : " + fileName);
270 AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
271 for(AcRow acRow : ac.getRows()) { //行ごとのループ
272 Row thisRow = calcSheet.createRow(rowIndex);
275 for(AcCell acCell : acRow.getCells()) { //セルごとのループ
276 Cell thisCell = thisRow.createCell(colIndex);
278 //alias 「付加行」内の別名定義(制約:右方・下方のセルからしか参照できない)
279 if(acCell.getAlias() != null) {
280 _namedAreaMap.put(acCell.getAlias(), new CellReference(rowIndex, colIndex, true, true).formatAsString());
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(">$", "");
293 thisCell.setCellFormula(formula);
295 thisCell.setCellValue(acCell.getValue());