From d3d8e4a3afbaf682d390193578a235dfa94db047 Mon Sep 17 00:00:00 2001 From: satomichan Date: Thu, 21 Jan 2021 09:32:23 +0900 Subject: [PATCH] =?utf8?q?=E3=80=8C=E5=90=8D=E5=89=8D=E4=BB=98=E3=81=8D?= =?utf8?q?=E7=AF=84=E5=9B=B2=E3=80=8D=E3=82=92=E8=A8=AD=E5=AE=9A=E3=81=99?= =?utf8?q?=E3=82=8B=E3=81=AE=E3=82=92=E3=82=84=E3=82=81=E3=81=9F=EF=BC=88?= =?utf8?q?=E3=82=A2=E3=83=89=E3=83=AC=E3=82=B9=E3=81=B9=E3=81=9F=E6=9B=B8?= =?utf8?q?=E3=81=8D=E3=81=B8=E5=A4=89=E6=9B=B4=EF=BC=89=E3=80=82=20Google?= =?utf8?q?=20=E3=82=B9=E3=83=97=E3=83=AC=E3=83=83=E3=83=89=E3=82=B7?= =?utf8?q?=E3=83=BC=E3=83=88=E5=AF=BE=E7=AD=96=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../java/jp/satomichan/nucalgen/Nucalgen.java | 59 +++++++++++-------- 1 file changed, 36 insertions(+), 23 deletions(-) diff --git a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java index 313d755..690d47d 100644 --- a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java +++ b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java @@ -3,7 +3,9 @@ package jp.satomichan.nucalgen; import java.io.FileInputStream; import java.io.FileOutputStream; import java.util.Arrays; +import java.util.HashMap; import java.util.List; +import java.util.Map; import org.apache.commons.cli.CommandLine; import org.apache.commons.cli.CommandLineParser; @@ -18,7 +20,6 @@ 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 { @@ -36,6 +37,8 @@ public class Nucalgen { options.addOption(Option.builder("protect").longOpt("set-protect").build()); options.addOption(Option.builder("r").longOpt("-use-processed-table").build()); + Map namedAreaMap = new HashMap(); + try { CommandLineParser parser = new DefaultParser(); @@ -116,10 +119,11 @@ public class Nucalgen { //摂取量 名前付き範囲 - 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); + String intakeArea = new CellReference(3, 3, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, 3, true, true).formatAsString(); + //XSSFName intakeNamedRangeArea = (XSSFName) outputWorkbook.createName(); + //intakeNamedRangeArea.setNameName("AREA_INTAKE"); + //intakeNamedRangeArea.setRefersToFormula(intakeArea); + namedAreaMap.put("AREA_INTAKE", intakeArea); //「合計」行 @@ -129,24 +133,27 @@ public class Nucalgen { 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(); + String sumTargetArea = new CellReference(3, colIndex, true, true).formatAsString() + ":" + new CellReference(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); + //XSSFName namedRangeArea = (XSSFName) outputWorkbook.createName(); + //namedRangeArea.setNameName("AREA_" + aColumn.getAlias()); + //namedRangeArea.setRefersToFormula(sumTargetArea); + namedAreaMap.put("AREA_" + aColumn.getAlias(), sumTargetArea); if(aColumn.isUseSum()) { - XSSFName namedRangeSum = (XSSFName) outputWorkbook.createName(); - namedRangeSum.setNameName("SUM_" + aColumn.getAlias()); - namedRangeSum.setRefersToFormula(new CellReference(calcSheet.getSheetName(), rowIndex, colIndex, true, true).formatAsString()); + //XSSFName namedRangeSum = (XSSFName) outputWorkbook.createName(); + //namedRangeSum.setNameName("SUM_" + aColumn.getAlias()); + String sumArea = new CellReference(rowIndex, colIndex, true, true).formatAsString(); + //namedRangeSum.setRefersToFormula(sumArea); + namedAreaMap.put("SUM_" + aColumn.getAlias(), sumArea); } } thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); if(aColumn.isUseSum()) { - thisCell.setCellFormula("SUM(" + sumArea + ")"); + thisCell.setCellFormula("SUM(" + sumTargetArea + ")"); } colIndex++; } @@ -155,13 +162,13 @@ public class Nucalgen { //「PFCバランス」出力 if(cmd.hasOption("with-pfc-balance")) { rowIndex += 3; - rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex); + rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex, namedAreaMap); } //「食品群別摂取量」出力 if(cmd.hasOption("with-group-sum")) { rowIndex += 3; - rowIndex = generateGroupSum(calcSheet, csPool, rowIndex); + rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap); } //ブック出力 @@ -181,7 +188,7 @@ public class Nucalgen { //PFCバランス - private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex) { + private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map _namedAreaMap) { Row pfbBalanceRow1 = calcSheet.createRow(rowIndex); pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%)"); calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2)); @@ -189,17 +196,20 @@ public class Nucalgen { pfbBalanceRow1.createCell(4).setCellValue("F"); pfbBalanceRow1.createCell(5).setCellValue("C"); + final String sumPfc = "(" + _namedAreaMap.get("SUM_P") + "*4+" + _namedAreaMap.get("SUM_F") + "*9+" + _namedAreaMap.get("SUM_C") + "*4)"; + 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)"); + pCell.setCellFormula(_namedAreaMap.get("SUM_P") + "*4*100/" + sumPfc); + 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)"); + fCell.setCellFormula(_namedAreaMap.get("SUM_F") + "*9*100/" + sumPfc); 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)"); + cCell.setCellFormula(_namedAreaMap.get("SUM_C") + "*4*100/" + sumPfc); return rowIndex; } @@ -207,12 +217,14 @@ public class Nucalgen { //群別摂取量 - private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex) { + private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map _namedAreaMap) { List groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類", "種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類", "油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類"); + + Row groupRow = calcSheet.createRow(rowIndex); groupRow.createCell(1).setCellValue("食品群"); groupRow.createCell(3).setCellValue("摂取量(g)"); @@ -224,7 +236,8 @@ public class Nucalgen { thisRow.createCell(2).setCellValue(groupName.get(i)); Cell cCell = thisRow.createCell(3); cCell.setCellStyle(csPool.getCellStyle("")); - cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)"); + //cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)"); + cCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_GROUP") + ", " + i + ", " + _namedAreaMap.get("AREA_INTAKE") + ")"); if(i == 6) { rowIndex++; @@ -232,8 +245,8 @@ public class Nucalgen { thisRow.createCell(2).setCellValue("うち　緑黄色野菜"); Cell bcvCell = thisRow.createCell(3); bcvCell.setCellStyle(csPool.getCellStyle("0")); - bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)"); - + //bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)"); + bcvCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_BRIGHT_COLORED_VEGETABLE") + ", 1, " + _namedAreaMap.get("AREA_INTAKE") + ")"); } -- 2.43.0