From 19de61c528f7c42dea7e66d83d39d01158f98a79 Mon Sep 17 00:00:00 2001 From: satomichan Date: Sun, 29 Nov 2020 10:45:45 +0900 Subject: [PATCH] 2020-11-29 --- pom.xml | 36 +++ .../jp/satomichan/nucalgen/CellStylePool.java | 104 +++++++ .../nucalgen/MoeStdFoodCompTable.java | 91 +++++++ .../java/jp/satomichan/nucalgen/Nucalgen.java | 257 ++++++++++++++++++ .../satomichan/nucalgen/NutritionColumn.java | 87 ++++++ .../nucalgen/NutritionColumnHolder.java | 58 ++++ .../jp/satomichan/nucalgen/package-info.java | 1 + 7 files changed, 634 insertions(+) create mode 100644 pom.xml create mode 100644 src/main/java/jp/satomichan/nucalgen/CellStylePool.java create mode 100644 src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java create mode 100644 src/main/java/jp/satomichan/nucalgen/Nucalgen.java create mode 100644 src/main/java/jp/satomichan/nucalgen/NutritionColumn.java create mode 100644 src/main/java/jp/satomichan/nucalgen/NutritionColumnHolder.java create mode 100644 src/main/java/jp/satomichan/nucalgen/package-info.java diff --git a/pom.xml b/pom.xml new file mode 100644 index 0000000..7a36375 --- /dev/null +++ b/pom.xml @@ -0,0 +1,36 @@ + + 4.0.0 + eiyou_table + eiyou_table + 0.0.1-SNAPSHOT + EiyouTable + + + org.apache.poi + poi-ooxml + 3.17 + + + + commons-configuration + commons-configuration + 1.4 + + + + commons-collections + commons-collections + 3.2.2 + + + + commons-cli + commons-cli + 1.4 + + + + + \ No newline at end of file diff --git a/src/main/java/jp/satomichan/nucalgen/CellStylePool.java b/src/main/java/jp/satomichan/nucalgen/CellStylePool.java new file mode 100644 index 0000000..bd1be5b --- /dev/null +++ b/src/main/java/jp/satomichan/nucalgen/CellStylePool.java @@ -0,0 +1,104 @@ +package jp.satomichan.nucalgen; + +import java.util.HashMap; +import java.util.Map; + +import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.xssf.usermodel.XSSFDataFormat; + +public class CellStylePool { + + private Workbook workbook; + + CellStylePool(Workbook workbook){ + this.workbook = workbook; + } + + + private Map cellStyleMap = new HashMap(); + + + CellStyle getCellStyle(String format) { + return this.getCellStyle(format, true); + } + + + CellStyle getCellStyle(String format, Boolean locked) { + CompKey key = new CompKey(format, locked); + if(this.cellStyleMap.containsKey(key)) { + return this.cellStyleMap.get(key); + }else { + CellStyle cs = this.workbook.createCellStyle(); + XSSFDataFormat xssfFormat = (XSSFDataFormat) this.workbook.createDataFormat(); + cs.setDataFormat(xssfFormat.getFormat(format)); + cs.setLocked(locked); + this.cellStyleMap.put(key, cs); + + return this.cellStyleMap.get(key); + } + } + + + class CompKey { + private String format; + private Boolean locked; + + CompKey(String format, Boolean locked){ + this.setFormat(format); + this.setLocked(locked); + } + + public String getFormat() { + return format; + } + public void setFormat(String format) { + this.format = format; + } + public Boolean getLocked() { + return locked; + } + public void setLocked(Boolean locked) { + this.locked = locked; + } + @Override + public int hashCode() { + final int prime = 31; + int result = 1; + result = prime * result + getEnclosingInstance().hashCode(); + result = prime * result + ((format == null) ? 0 : format.hashCode()); + result = prime * result + ((locked == null) ? 0 : locked.hashCode()); + return result; + } + @Override + public boolean equals(Object obj) { + if (this == obj) + return true; + if (obj == null) + return false; + if (getClass() != obj.getClass()) + return false; + CompKey other = (CompKey) obj; + if (!getEnclosingInstance().equals(other.getEnclosingInstance())) + return false; + if (format == null) { + if (other.format != null) + return false; + } else if (!format.equals(other.format)) + return false; + if (locked == null) { + if (other.locked != null) + return false; + } else if (!locked.equals(other.locked)) + return false; + return true; + } + private CellStylePool getEnclosingInstance() { + return CellStylePool.this; + } + + + } + + +} diff --git a/src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java b/src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java new file mode 100644 index 0000000..9799937 --- /dev/null +++ b/src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java @@ -0,0 +1,91 @@ +package jp.satomichan.nucalgen; + +import java.util.ArrayList; +import java.util.List; + +import org.apache.commons.configuration.ConfigurationException; +import org.apache.commons.configuration.XMLConfiguration; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; + +public class MoeStdFoodCompTable { + private String brightColoredVegetablesXmlFileName = ""; + private List brightColoredVegetableList = new ArrayList(); + + MoeStdFoodCompTable(String brightColoredVegetablesXmlFileName_){ + this.brightColoredVegetablesXmlFileName = brightColoredVegetablesXmlFileName_; + + try { + XMLConfiguration config = new XMLConfiguration(this.brightColoredVegetablesXmlFileName); + List vegetableNames = config.getList("bright-colored-vegetable.name"); + for(Object vegeObj : vegetableNames) { + this.brightColoredVegetableList.add(vegeObj.toString()); + } + + } catch (ConfigurationException e) { + e.printStackTrace(); + } + } + + + + //「本表」変換 + void processInto(Workbook outputWorkbook) { + Sheet mainSheet = outputWorkbook.getSheet("本表"); + int rowCount = 0; + int lastCol = mainSheet.getRow(5).getLastCellNum(); + mainSheet.getRow(5).getCell(4).setCellValue("廃棄率"); + mainSheet.getRow(5).createCell(lastCol + 1).setCellValue("食品群"); + mainSheet.getRow(5).createCell(lastCol + 2).setCellValue("緑黄色野菜"); + + + for (Row row : mainSheet) { + rowCount++; + if(rowCount < 8) {continue;} + + for (Cell cell : row) { + String cellString = cell.toString(); + + cellString = cellString.replaceAll("\\(", ""); + cellString = cellString.replaceAll("\\)", ""); + cellString = cellString.replaceAll("-", "0"); + cellString = cellString.replaceAll("Tr", "0"); + + + if(cellString.matches("^[\\d\\.]+$")) { + cell.setCellValue(Double.parseDouble(cellString)); + CellStyle aCellStyle = cell.getCellStyle(); + aCellStyle.setDataFormat((short) 0); + cell.setCellStyle(aCellStyle); + } + + } + + int gun = (int) row.getCell(0).getNumericCellValue(); + row.createCell(lastCol + 1).setCellValue(gun); + + + //緑黄色野菜 + if(brightColoredVegetablesXmlFileName.length() > 0) { + boolean isBrightColored = false; + String foodName = row.getCell(3).getStringCellValue(); + for(String aBright : this.brightColoredVegetableList) { + if(foodName.matches(aBright + ".*")) { + isBrightColored = true; + break; + } + } + + if(isBrightColored) { + row.createCell(lastCol + 2).setCellValue(1); + } + } + + } + + + } +} diff --git a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java new file mode 100644 index 0000000..902d85d --- /dev/null +++ b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java @@ -0,0 +1,257 @@ +package jp.satomichan.nucalgen; + +import java.io.FileInputStream; +import java.io.FileOutputStream; +import java.util.Arrays; +import java.util.List; + +import org.apache.commons.cli.CommandLine; +import org.apache.commons.cli.CommandLineParser; +import org.apache.commons.cli.DefaultParser; +import org.apache.commons.cli.Option; +import org.apache.commons.cli.Options; +import org.apache.commons.configuration.XMLConfiguration; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.usermodel.WorkbookFactory; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellReference; +import org.apache.poi.xssf.usermodel.XSSFName; + +public class Nucalgen { + + public static void main(String[] args) { + //コマンドライン・オプション読み込み + Options options = new Options(); + options.addOption(Option.builder("s").required().hasArg().longOpt("std-food-comp-table").build()); + options.addOption(Option.builder("c").required().hasArg().longOpt("columns").build()); + options.addOption(Option.builder("o").required().hasArg().longOpt("output").build()); + options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build()); + options.addOption(Option.builder("p").longOpt("use-processed-table").build()); + options.addOption(Option.builder("pfc").longOpt("with-pfc-balance").build()); + options.addOption(Option.builder("groupsum").longOpt("with-group-sum").build()); + options.addOption(Option.builder("bright").hasArg().longOpt("bright-colored-vegetables-list").build()); + options.addOption(Option.builder("protect").longOpt("set-protect").build()); + options.addOption(Option.builder("r").longOpt("use-cache-std-food-comp").build()); + + try { + + CommandLineParser parser = new DefaultParser(); + CommandLine cmd = parser.parse(options, args); + + final String moeStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table"); + final String columnsXmlFileName = cmd.getOptionValue("columns"); + final String outputXlsxFileName = cmd.getOptionValue("output"); + final int lines = Integer.parseInt(cmd.getOptionValue("lines")); + + //コンフィグ読み込み + XMLConfiguration config = new XMLConfiguration(columnsXmlFileName); + NutritionColumnHolder nc = new NutritionColumnHolder(config); + + //Book生成 + Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(moeStdFoodCompTableFileName)); + + if(cmd.hasOption("use-processed-table") == false) { + //「本表」変換 + MoeStdFoodCompTable moe = new MoeStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list")); + moe.processInto(outputWorkbook); + } + + //「別表」削除 + outputWorkbook.removeSheetAt(1); + + + //「栄養価計算」シート生成 + Sheet calcSheet = outputWorkbook.createSheet("栄養価計算"); + outputWorkbook.setSheetOrder("栄養価計算", 0); + if(cmd.hasOption("set-protect")) { + calcSheet.protectSheet(""); + } + calcSheet.setColumnWidth(2, 10240); + calcSheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1)); + + CellStylePool csPool = new CellStylePool(outputWorkbook); + + //「タイトル」行 + Row titleRow = calcSheet.createRow(1); + titleRow.createCell(1).setCellValue("食品番号"); + titleRow.createCell(2).setCellValue("食品名"); + titleRow.createCell(3).setCellValue("摂取量"); + int colIndex = 4; + for(NutritionColumn aColumn : nc.getNutritionColumnList()) { + titleRow.createCell(colIndex).setCellValue(aColumn.getDispName()); + colIndex++; + } + + //「単位」行 + Row unitRow = calcSheet.createRow(2); + unitRow.createCell(2).setCellValue("単位"); + unitRow.createCell(3).setCellValue("g"); + colIndex = 4; + for(NutritionColumn aColumn : nc.getNutritionColumnList()) { + unitRow.createCell(colIndex).setCellValue(aColumn.getUnit()); + colIndex++; + } + + //「栄養計算」行 + int rowIndex = 3; + for(int i = rowIndex; i < lines + 3; i++,rowIndex++) { + Row thisRow = calcSheet.createRow(rowIndex); + + thisRow.createCell(1).setCellStyle(csPool.getCellStyle("00000", false)); + thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",本表!$B$9:$BS$2199,3,FALSE),\"\")"); + thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false)); + + colIndex = 4; + for(NutritionColumn aColumn : nc.getNutritionColumnList()) { + Cell thisCell = thisRow.createCell(colIndex); + thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); + + String div100 = aColumn.isUseRawValue() ? "" : "/ 100 * $D" + (rowIndex + 1); + + thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + ",本表!$B$9:$BS$2199,MATCH(\"" + aColumn.getName() + "\",本表!$B$6:$BS$6,0),FALSE) " + div100 + ",\"\")"); + colIndex++; + } + + } + + + //摂取量　名前付き範囲 + String intakeArea = new CellReference(calcSheet.getSheetName(), 3, 3, true, true).formatAsString() + ":" + new CellReference(calcSheet.getSheetName(), rowIndex -1, 3, true, true).formatAsString(); + XSSFName intakeNamedRangeArea = (XSSFName) outputWorkbook.createName(); + intakeNamedRangeArea.setNameName("AREA_INTAKE"); + intakeNamedRangeArea.setRefersToFormula(intakeArea); + + + //「合計」行 + Row sumRow = calcSheet.createRow(rowIndex); + sumRow.createCell(1).setCellValue("合計"); + calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3)); + colIndex = 4; + for(NutritionColumn aColumn : nc.getNutritionColumnList()) { + Cell thisCell = sumRow.createCell(colIndex); + String sumArea = new CellReference(calcSheet.getSheetName(), 3, colIndex, true, true).formatAsString() + ":" + new CellReference(calcSheet.getSheetName(), rowIndex -1, colIndex, true, true).formatAsString(); + + //名前付き範囲（alias あれば設定） + if(aColumn.getAlias().length() > 0) { + XSSFName namedRangeArea = (XSSFName) outputWorkbook.createName(); + namedRangeArea.setNameName("AREA_" + aColumn.getAlias()); + namedRangeArea.setRefersToFormula(sumArea); + + if(aColumn.isUseSum()) { + XSSFName namedRangeSum = (XSSFName) outputWorkbook.createName(); + namedRangeSum.setNameName("SUM_" + aColumn.getAlias()); + namedRangeSum.setRefersToFormula(new CellReference(calcSheet.getSheetName(), rowIndex, colIndex, true, true).formatAsString()); + } + } + + thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); + if(aColumn.isUseSum()) { + thisCell.setCellFormula("SUM(" + sumArea + ")"); + } + colIndex++; + } + + + //「PFCバランス」出力 + if(cmd.hasOption("with-pfc-balance")) { + rowIndex += 3; + rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex); + } + + //「食品群別摂取量」出力 + if(cmd.hasOption("with-group-sum")) { + rowIndex += 3; + rowIndex = generateGroupSum(calcSheet, csPool, rowIndex); + } + + //ブック出力 + FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName); + outputWorkbook.setActiveSheet(0); + calcSheet.setForceFormulaRecalculation(true); + outputWorkbook.setSelectedTab(0); + outputWorkbook.write(outputXlsxFile); + outputWorkbook.close(); + + } catch (Exception e) { + // TODO 自動生成された catch ブロック + e.printStackTrace(); + } + } + + + + //PFCバランス + private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex) { + Row pfbBalanceRow1 = calcSheet.createRow(rowIndex); + pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%)"); + calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); + pfbBalanceRow1.createCell(3).setCellValue("P"); + pfbBalanceRow1.createCell(4).setCellValue("F"); + pfbBalanceRow1.createCell(5).setCellValue("C"); + + rowIndex++; + Row pfbBalanceRow2 = calcSheet.createRow(rowIndex); + Cell pCell = pfbBalanceRow2.createCell(3); + pCell.setCellStyle(csPool.getCellStyle("0")); + pCell.setCellFormula("SUM_P*4*100/(SUM_P*4+SUM_F*9+SUM_C*4)"); + Cell fCell = pfbBalanceRow2.createCell(4); + fCell.setCellStyle(csPool.getCellStyle("0")); + fCell.setCellFormula("SUM_F*9*100/(SUM_P*4+SUM_F*9+SUM_C*4)"); + Cell cCell = pfbBalanceRow2.createCell(5); + cCell.setCellStyle(csPool.getCellStyle("0")); + cCell.setCellFormula("SUM_C*4*100/(SUM_P*4+SUM_F*9+SUM_C*4)"); + + return rowIndex; + } + + + + //群別摂取量 + private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex) { + + List groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類", + "種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類", + "油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類"); + + Row groupRow = calcSheet.createRow(rowIndex); + groupRow.createCell(1).setCellValue("食品群"); + groupRow.createCell(3).setCellValue("摂取量(g)"); + rowIndex++; + + for(int i = 1; i <= 18; i++,rowIndex++) { + Row thisRow = calcSheet.createRow(rowIndex); + thisRow.createCell(1).setCellValue(i); + thisRow.createCell(2).setCellValue(groupName.get(i)); + Cell cCell = thisRow.createCell(3); + cCell.setCellStyle(csPool.getCellStyle("")); + cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)"); + + if(i == 6) { + rowIndex++; + thisRow = calcSheet.createRow(rowIndex); + thisRow.createCell(2).setCellValue("うち　緑黄色野菜"); + Cell bcvCell = thisRow.createCell(3); + bcvCell.setCellStyle(csPool.getCellStyle("0")); + bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)"); + + } + + + } + + return rowIndex; + } + + + + + + + + + + +} diff --git a/src/main/java/jp/satomichan/nucalgen/NutritionColumn.java b/src/main/java/jp/satomichan/nucalgen/NutritionColumn.java new file mode 100644 index 0000000..34d7ee3 --- /dev/null +++ b/src/main/java/jp/satomichan/nucalgen/NutritionColumn.java @@ -0,0 +1,87 @@ +package jp.satomichan.nucalgen; + +public class NutritionColumn { + private String name; + private String dispName; + private String format; + private String unit; + private boolean useRawValue; + private String alias; + private boolean isUseSum; + + + public String getName() { + return name; + } + + public void setName(String name) { + this.name = name; + } + + public void setName(Object name) { + this.name = (String) name; + } + + + public String getDispName() { + return dispName; + } + + public void setDispName(String disp_name) { + this.dispName = disp_name; + } + + + public String getFormat() { + return format; + } + + public void setFormat(String format) { + this.format = format; + } + + + public String getUnit() { + return unit; + } + + public void setUnit(String unit) { + this.unit = unit; + } + + public boolean isUseRawValue() { + return useRawValue; + } + + public void setUseRawValue(boolean useRawValue) { + this.useRawValue = useRawValue; + } + + + public String getAlias() { + return alias; + } + + public void setAlias(String alias) { + this.alias = alias; + } + + public boolean isUseSum() { + return isUseSum; + } + + public void setUseSum(boolean isUseSum) { + this.isUseSum = isUseSum; + } + + public String toString() { + String ret = "name={" + name + "} disp_name={" + dispName + + "} format={" + format + "} unit={" + unit + "} useRawValue={" + useRawValue + "}"; + + return ret; + } + + + + +} diff --git a/src/main/java/jp/satomichan/nucalgen/NutritionColumnHolder.java b/src/main/java/jp/satomichan/nucalgen/NutritionColumnHolder.java new file mode 100644 index 0000000..e20d799 --- /dev/null +++ b/src/main/java/jp/satomichan/nucalgen/NutritionColumnHolder.java @@ -0,0 +1,58 @@ +package jp.satomichan.nucalgen; + +import java.util.ArrayList; +import java.util.List; + +import org.apache.commons.configuration.XMLConfiguration; + +public class NutritionColumnHolder { + + private List nutritionColumnList; + + List getNutritionColumnList() { + return this.nutritionColumnList; + } + + void addNutritionColumn(NutritionColumn aNutritionColumn) { + this.nutritionColumnList.add(aNutritionColumn); + } + + NutritionColumnHolder(XMLConfiguration aConfig){ + this.nutritionColumnList = new ArrayList(); + + List names = aConfig.getList("cols.column.name"); + List dispNames = aConfig.getList("cols.column.disp_name"); + List aliases = aConfig.getList("cols.column.alias"); + List formats = aConfig.getList("cols.column.format"); + List units = aConfig.getList("cols.column.unit"); + List useRawValue = aConfig.getList("cols.column.use_raw_value"); + List useSum = aConfig.getList("cols.column.use_sum"); + + for (Object aName : names) { + NutritionColumn nc = new NutritionColumn(); + nc.setName((String) aName); + nc.setDispName((String) dispNames.get(names.indexOf(aName))); + nc.setAlias((String) aliases.get(names.indexOf(aName))); + nc.setFormat((String) formats.get(names.indexOf(aName))); + nc.setUnit((String) units.get(names.indexOf(aName))); + nc.setUseRawValue(((String)useRawValue.get(names.indexOf(aName))).equalsIgnoreCase("true")); + nc.setUseSum(((String)useSum.get(names.indexOf(aName))).equalsIgnoreCase("true")); + + this.addNutritionColumn(nc); + } + + } + + + public String toString() { + String ret = ""; + for(NutritionColumn aColumn : this.getNutritionColumnList()) { + ret += aColumn + "\n"; + } + + return ret; + } + + + +} \ No newline at end of file diff --git a/src/main/java/jp/satomichan/nucalgen/package-info.java b/src/main/java/jp/satomichan/nucalgen/package-info.java new file mode 100644 index 0000000..dbb4d71 --- /dev/null +++ b/src/main/java/jp/satomichan/nucalgen/package-info.java @@ -0,0 +1 @@ +package jp.satomichan.nucalgen; \ No newline at end of file -- 2.43.0