1 package jp.satomichan.nucalgen;
4 import java.io.FileInputStream;
5 import java.io.FileOutputStream;
6 import java.util.ArrayList;
8 import java.util.Map.Entry;
9 import java.util.regex.Matcher;
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;
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;
29 public class Nucalgen {
30 //開始位置 最初の食品の、最初の栄養成分の位置 (E4セル)
31 private static final int ROW_INDEX_START = 3;
32 private static final int COL_INDEX_START = 4;
34 public static void main(String[] args) {
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());
46 NamedAreaStore namedArea = NamedAreaStore.getInstance();
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"));
59 NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);
62 Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName));
64 if(cmd.hasOption("use-processed-table") == false) {
66 MextStdFoodCompTable stdCompTable = new MextStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
67 stdCompTable.processInto(outputWorkbook);
72 Sheet calcSheet = outputWorkbook.createSheet("栄養価計算");
73 outputWorkbook.setSheetOrder("栄養価計算", 0);
74 if(cmd.hasOption("set-protect")) {
75 calcSheet.protectSheet("");
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「食品番号」セル
81 CellStylePool csPool = new CellStylePool(outputWorkbook);
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());
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());
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);
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));
116 colIndex = COL_INDEX_START;
117 for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
118 Cell thisCell = thisRow.createCell(colIndex);
119 thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
121 if(aColumn.getFormula().length() >= 1) {
123 String formula = aColumn.getFormula();
124 for(String aAlias : nch.getNutritionAliasList()) {
125 formula = replaceFormula(formula, aAlias, rowIndex, COL_INDEX_START + nch.indexOf(aAlias));
127 thisCell.setCellFormula(formula);
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 + ",\"\")");
140 usedTableList.add(aColumn.getTable());
147 namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);
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);
159 if(aColumn.getAlias().length() > 0) {
160 namedArea.save("AREA_" + aColumn.getAlias(), ROW_INDEX_START, colIndex, rowIndex -1, colIndex);
162 if(aColumn.isUseSum()) {
163 namedArea.save("SUM_" + aColumn.getAlias(), rowIndex, colIndex);
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) +
178 String[] additionOptionValues = cmd.getOptionValues("addition");
179 if(additionOptionValues != null) {
180 for(String aAdditionFileName : additionOptionValues) {
182 rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedArea);
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)) {
197 outputWorkbook.removeSheetAt(si);
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();
209 } catch (Exception e) {
210 // TODO 自動生成された catch ブロック
224 private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex,
225 NamedAreaStore namedArea) {
227 AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
228 for(AcRow acRow : ac.getRows()) { //行ごとのループ
229 Row thisRow = calcSheet.createRow(rowIndex);
232 for(AcCell acCell : acRow.getCells()) { //セルごとのループ
233 Cell thisCell = thisRow.createCell(colIndex);
235 //alias 「付加行」内の別名定義(制約:右方・下方のセルからしか参照できない)
236 if(acCell.getAlias() != null) {
237 namedArea.save(acCell.getAlias(), rowIndex, colIndex);
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();
247 formula = replaceFormula(formula, k, v);
249 thisCell.setCellFormula(formula);
252 } else if(acCell.getValue() != null) {
253 thisCell.setCellValue(acCell.getValue());
255 //formula でも value でもない
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(">$", "");
284 //計算式中の 文字列(名前付き範囲)→セル座標 置換
285 private static String replaceFormula(String formula, String target, int cellRow, int cellCol) {
286 return replaceFormula(formula, target, new CellReference(cellRow, cellCol).formatAsString());