From 157cca3a38bee5fcb31d0e7b58ea01d728489e7f Mon Sep 17 00:00:00 2001 From: satomichan Date: Sat, 10 Jul 2021 14:45:27 +0900 Subject: [PATCH] =?utf8?q?=E3=81=B2=E3=81=A8=E3=81=BE=E3=81=9A=E5=AE=9F?= =?utf8?q?=E8=A3=85=E3=81=A7=E3=81=8D=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- nucalgen/pom.xml | 8 + .../satomichan/nucalgen/NamedAreaStore.java | 10 +- .../java/jp/satomichan/nucalgen/Nucalgen.java | 420 +++++++++++++----- 3 files changed, 321 insertions(+), 117 deletions(-) diff --git a/nucalgen/pom.xml b/nucalgen/pom.xml index 7bf56ff..fe18332 100644 --- a/nucalgen/pom.xml +++ b/nucalgen/pom.xml @@ -37,6 +37,14 @@ commons-digester 2.1 + + + + org.apache.commons + commons-lang3 + 3.12.0 + + diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java index 095499e..2f2f679 100644 --- a/nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/NamedAreaStore.java @@ -4,7 +4,9 @@ import java.util.HashMap; import java.util.Map; import java.util.Map.Entry; import java.util.Set; +import java.util.regex.Matcher; +import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.util.CellReference; public class NamedAreaStore { @@ -33,7 +35,7 @@ public class NamedAreaStore { String save(String areaname, int row1, int col1, int row2, int col2) { String area = getAreaString(row1, col1, row2, col2); - + areaname = Matcher.quoteReplacement(areaname); this.namedAreaMap.put(areaname, area); return area; @@ -45,7 +47,13 @@ public class NamedAreaStore { } + String save(String areaname, Cell cell) { + return this.save(areaname, cell.getRowIndex(), cell.getColumnIndex()); + } + + String load(String areaName) { + areaName = Matcher.quoteReplacement(areaName); return this.namedAreaMap.get(areaName); } diff --git a/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java index 941f552..2068900 100644 --- a/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java +++ b/nucalgen/src/main/java/jp/satomichan/nucalgen/Nucalgen.java @@ -13,6 +13,7 @@ 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.lang.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; @@ -39,6 +40,7 @@ public class Nucalgen { options.addOption(Option.builder("o").required().hasArg().longOpt("output").build()); options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build()); options.addOption(Option.builder("add").hasArgs().longOpt("addition").build()); + options.addOption(Option.builder("sheets").hasArgs().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("processed").longOpt("use-processed-table").build()); @@ -69,162 +71,329 @@ public class Nucalgen { //「栄養価計算」シート生成 - Sheet calcSheet = outputWorkbook.createSheet("栄養価計算"); - outputWorkbook.setSheetOrder("栄養価計算", 0); - if(cmd.hasOption("set-protect")) { - calcSheet.protectSheet(""); + CellStylePool csPool = new CellStylePool(outputWorkbook); + List usedTableList = new ArrayList(); + List sheetNameList = new ArrayList(); + + + String[] sheets = cmd.getOptionValues("sheets"); + if(sheets != null && sheets.length >= 2) { + //表が複数 + for(int si = 0; si < sheets.length - 1; si++) { + final String sheetName = sheets[si]; + generateCalculationSheet(outputWorkbook, sheetName, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList); + outputWorkbook.setSheetOrder(sheetName, si); + sheetNameList.add(sheetName); + } + final String sumSheetName = sheets[sheets.length - 1]; + generateSumSheet(outputWorkbook, sumSheetName, sheetNameList, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList); + outputWorkbook.setSheetOrder(sumSheetName, sheets.length - 1); + + }else { + //表がひとつ + final String sheetName = (sheets != null) ? sheets[0] : "栄養価計算"; + generateCalculationSheet(outputWorkbook, sheetName, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList); + outputWorkbook.setSheetOrder(sheetName, 0); + sheetNameList.add(sheetName); + } + + + //未使用表シート削除 + for(int si = outputWorkbook.getNumberOfSheets() - 1 ; si >= 1 ; si--) { + String sheetName = outputWorkbook.getSheetName(si); + boolean used = false; + for(String usedTable : usedTableList) { + if(usedTable.equals(sheetName)) { + used = true; + } + } + if(!used) { + outputWorkbook.removeSheetAt(si); + } } - calcSheet.setColumnWidth(COL_INDEX_START -2, 10240); //C「食品名」列 - calcSheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1, - COL_INDEX_START -3, COL_INDEX_START -3)); //B2:B3「食品番号」セル + + //ブック出力 + FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName); + outputWorkbook.setActiveSheet(0); + + outputWorkbook.setSelectedTab(0); + outputWorkbook.write(outputXlsxFile); + outputWorkbook.close(); + + } catch (Exception e) { + // TODO 自動生成された catch ブロック + e.printStackTrace(); + } + } - CellStylePool csPool = new CellStylePool(outputWorkbook); - //「タイトル」行 - Row titleRow = calcSheet.createRow(ROW_INDEX_START -2); - titleRow.createCell(COL_INDEX_START -3).setCellValue("食品番号"); - titleRow.createCell(COL_INDEX_START -2).setCellValue("食品名"); - titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量"); - int colIndex = COL_INDEX_START; - for(NutritionColumn aColumn : nch.getNutritionColumnList()) { - titleRow.createCell(colIndex).setCellValue(aColumn.getDispName()); - colIndex++; - } - //「単位」行 - Row unitRow = calcSheet.createRow(ROW_INDEX_START -1); - unitRow.createCell(COL_INDEX_START -2).setCellValue("単位"); - unitRow.createCell(COL_INDEX_START -1).setCellValue("g"); + + + + + + //「栄養価計算」シート生成 + private static void generateCalculationSheet(Workbook book, String sheetName, NamedAreaStore namedArea, boolean setProtect, + String[] additionOptionValues, final int lines, + NutritionColumnHolder nch, CellStylePool csPool, List usedTableList) { + + + Sheet calcSheet = book.createSheet(sheetName); + + if(setProtect) { + calcSheet.protectSheet(""); + } + calcSheet.setColumnWidth(COL_INDEX_START -2, 10240); //C「食品名」列 + calcSheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1, + COL_INDEX_START -3, COL_INDEX_START -3)); //B2:B3「食品番号」セル + + + //「タイトル」行 + Row titleRow = calcSheet.createRow(ROW_INDEX_START -2); + titleRow.createCell(COL_INDEX_START -3).setCellValue("食品番号"); + titleRow.createCell(COL_INDEX_START -2).setCellValue("食品名"); + titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量"); + int colIndex = COL_INDEX_START; + for(NutritionColumn aColumn : nch.getNutritionColumnList()) { + titleRow.createCell(colIndex).setCellValue(aColumn.getDispName()); + colIndex++; + } + + //「単位」行 + Row unitRow = calcSheet.createRow(ROW_INDEX_START -1); + unitRow.createCell(COL_INDEX_START -2).setCellValue("単位"); + unitRow.createCell(COL_INDEX_START -1).setCellValue("g"); + colIndex = COL_INDEX_START; + for(NutritionColumn aColumn : nch.getNutritionColumnList()) { + unitRow.createCell(colIndex).setCellValue(aColumn.getUnit()); + colIndex++; + } + + //「栄養計算」行 + int rowIndex = ROW_INDEX_START; + for(int i = rowIndex; i < lines + ROW_INDEX_START; i++,rowIndex++) { + Row thisRow = calcSheet.createRow(rowIndex); + + //「食品名」 + thisRow.createCell(COL_INDEX_START -3).setCellStyle(csPool.getCellStyle("00000", false)); + thisRow.createCell(COL_INDEX_START -2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + + ",成分表!$B$13:$BL$2500,3,FALSE),\"\")"); + thisRow.createCell(COL_INDEX_START -1).setCellStyle(csPool.getCellStyle("", false)); + colIndex = COL_INDEX_START; for(NutritionColumn aColumn : nch.getNutritionColumnList()) { - unitRow.createCell(colIndex).setCellValue(aColumn.getUnit()); + Cell thisCell = thisRow.createCell(colIndex); + thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); + + if(aColumn.getFormula().length() >= 1) { + //「計算式」列 + String formula = aColumn.getFormula(); + for(String aAlias : nch.getNutritionAliasList()) { + formula = replaceFormula(formula, aAlias, rowIndex, COL_INDEX_START + nch.indexOf(aAlias)); + } + thisCell.setCellFormula(formula); + + + } else { + //通常の栄養素の列 + String div100 = aColumn.isUseRawValue() ? "" : "/ 100 * $D" + (rowIndex + 1); + 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 + ",\"\")"); + } + colIndex++; + + usedTableList.add(aColumn.getTable()); + usedTableList.add(sheetName); } - //「栄養計算」行 - List usedTableList = new ArrayList(); - int rowIndex = ROW_INDEX_START; - for(int i = rowIndex; i < lines + ROW_INDEX_START; 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$13:$BL$2500,3,FALSE),\"\")"); - thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false)); - - colIndex = COL_INDEX_START; - for(NutritionColumn aColumn : nch.getNutritionColumnList()) { - Cell thisCell = thisRow.createCell(colIndex); - thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); - - if(aColumn.getFormula().length() >= 1) { - //「計算式」列 - String formula = aColumn.getFormula(); - for(String aAlias : nch.getNutritionAliasList()) { - formula = replaceFormula(formula, aAlias, rowIndex, COL_INDEX_START + nch.indexOf(aAlias)); - } - thisCell.setCellFormula(formula); + } - } else { - //通常の栄養素の列 - String div100 = aColumn.isUseRawValue() ? "" : "/ 100 * $D" + (rowIndex + 1); - 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 + ",\"\")"); - } + //摂取量 範囲を記憶 + namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1); - colIndex++; - usedTableList.add(aColumn.getTable()); - } + //「合計」行 + Row sumRow = calcSheet.createRow(rowIndex); + sumRow.createCell(COL_INDEX_START -3).setCellValue("合計"); + calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, COL_INDEX_START -3, COL_INDEX_START -2)); + + //「摂取量」合計 + Cell intakeSumCell = sumRow.createCell(COL_INDEX_START -1); + intakeSumCell.setCellFormula("SUM(" + namedArea.load("AREA_INTAKE") + ")"); + namedArea.save("SUM_INTAKE", intakeSumCell); + + colIndex = COL_INDEX_START; + for(NutritionColumn aColumn : nch.getNutritionColumnList()) { + Cell thisCell = sumRow.createCell(colIndex); + //範囲を記憶 + String areaName = aColumn.getAlias().length() >= 1 ? ("AREA_" + aColumn.getAlias()) : ("AREAID_" + aColumn.getName()); + namedArea.save(areaName, ROW_INDEX_START, colIndex, rowIndex -1, colIndex); + + if(aColumn.isUseSum()) { + String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName()); + namedArea.save(sumName, thisCell); } + thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); + if(aColumn.isUseSum()) { + thisCell.setCellFormula("SUM(" + + NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) + + ")"); + } + colIndex++; + } - //摂取量 範囲を記憶 - namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1); + + //「付加行」出力 + if(additionOptionValues != null) { + for(String aAdditionFileName : additionOptionValues) { + rowIndex += 2; + rowIndex = generateAddition(aAdditionFileName, calcSheet, null, csPool, rowIndex, namedArea); + } + } + + calcSheet.setForceFormulaRecalculation(true); + } - //「合計」行 - Row sumRow = calcSheet.createRow(rowIndex); - sumRow.createCell(1).setCellValue("合計"); - calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3)); - colIndex = COL_INDEX_START; - for(NutritionColumn aColumn : nch.getNutritionColumnList()) { - Cell thisCell = sumRow.createCell(colIndex); - //範囲を記憶(alias あれば設定) - if(aColumn.getAlias().length() > 0) { - namedArea.save("AREA_" + aColumn.getAlias(), ROW_INDEX_START, colIndex, rowIndex -1, colIndex); + + + + //集計用シート生成 + private static void generateSumSheet(Workbook book, String sheetName, List sheetNameList, NamedAreaStore namedArea, boolean setProtect, + String[] additionOptionValues, final int lines, + NutritionColumnHolder nch, CellStylePool csPool, List usedTableList) { - if(aColumn.isUseSum()) { - namedArea.save("SUM_" + aColumn.getAlias(), rowIndex, colIndex); - } - } + usedTableList.add(sheetName); + Sheet sheet = book.createSheet(sheetName); + + if(setProtect) { + sheet.protectSheet(""); + } + + //「表」 + sheet.setColumnWidth(COL_INDEX_START -2, 10240); //C + sheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1, + COL_INDEX_START -3, COL_INDEX_START -2)); //B2:C3「表」セル + + + //「タイトル」行 + Row titleRow = sheet.createRow(ROW_INDEX_START -2); + titleRow.createCell(COL_INDEX_START -3).setCellValue("表"); + titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量"); + int colIndex = COL_INDEX_START; + for(NutritionColumn aColumn : nch.getNutritionColumnList()) { + titleRow.createCell(colIndex).setCellValue(aColumn.getDispName()); + colIndex++; + } + + //「単位」行 + Row unitRow = sheet.createRow(ROW_INDEX_START -1); + unitRow.createCell(COL_INDEX_START -2).setCellValue("単位"); + unitRow.createCell(COL_INDEX_START -1).setCellValue("g"); + colIndex = COL_INDEX_START; + for(NutritionColumn aColumn : nch.getNutritionColumnList()) { + unitRow.createCell(colIndex).setCellValue(aColumn.getUnit()); + colIndex++; + } + + //表ごとの小計 + int rowIndex = ROW_INDEX_START; + for(String aSheetName : sheetNameList) { + Row thisRow = sheet.createRow(rowIndex); + + sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, + COL_INDEX_START -3, COL_INDEX_START -2)); //B:C「表」セル + //「表」列 + thisRow.createCell(COL_INDEX_START -3).setCellValue(aSheetName); + + //「摂取量」列 + thisRow.createCell(COL_INDEX_START -1).setCellFormula(aSheetName + "!" + namedArea.load("SUM_INTAKE")); + + + //「表ごとの小計」列 + colIndex = COL_INDEX_START; + for(NutritionColumn aColumn : nch.getNutritionColumnList()) { + Cell thisCell = thisRow.createCell(colIndex); thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); if(aColumn.isUseSum()) { - thisCell.setCellFormula("SUM(" + - NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) + - ")"); + String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName()); + String formula = aSheetName + "!" + namedArea.load(sumName); + thisCell.setCellFormula(formula); } + colIndex++; - } - - //「付加行」出力 - String[] additionOptionValues = cmd.getOptionValues("addition"); - if(additionOptionValues != null) { - for(String aAdditionFileName : additionOptionValues) { - rowIndex += 2; - rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedArea); - } } + rowIndex++; + } - //未使用表シート削除 - for(int si = outputWorkbook.getNumberOfSheets() - 1 ; si >= 1 ; si--) { - String sheetName = outputWorkbook.getSheetName(si); - boolean used = false; - for(String usedTable : usedTableList) { - if(usedTable.equals(sheetName)) { - used = true; - } - } - if(!used) { - outputWorkbook.removeSheetAt(si); - } + + //摂取量 範囲を記憶 + namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1); + + //「合計」行 + Row sumRow = sheet.createRow(rowIndex); + sumRow.createCell(COL_INDEX_START -3).setCellValue("合計"); + sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, COL_INDEX_START -3, COL_INDEX_START -2)); + + Cell intakeSumCell = sumRow.createCell(COL_INDEX_START -1); + intakeSumCell.setCellFormula("SUM(" + namedArea.load("AREA_INTAKE") + ")"); + + + colIndex = COL_INDEX_START; + for(NutritionColumn aColumn : nch.getNutritionColumnList()) { + Cell thisCell = sumRow.createCell(colIndex); + + //範囲を記憶 + String areaName = aColumn.getAlias().length() >= 1 ? ("AREA_" + aColumn.getAlias()) : ("AREAID_" + aColumn.getName()); + namedArea.save(areaName, ROW_INDEX_START, colIndex, rowIndex -1, colIndex); + + if(aColumn.isUseSum()) { + String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName()); + namedArea.save(sumName, thisCell); } - //ブック出力 - 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(); + thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat())); + if(aColumn.isUseSum()) { + thisCell.setCellFormula("SUM(" + + NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) + + ")"); + } + colIndex++; } - } + + //「付加行」出力 + if(additionOptionValues != null) { + for(String aAdditionFileName : additionOptionValues) { + rowIndex += 2; + rowIndex = generateAddition(aAdditionFileName, sheet, sheetNameList, csPool, rowIndex, namedArea); + } + } + + sheet.setForceFormulaRecalculation(true); - - - + } //「付加行」生成 - private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex, + private static int generateAddition(String fileName, Sheet calcSheet, List sheetNameList, CellStylePool csPool, int rowIndex, NamedAreaStore namedArea) { AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName)); + int cellCounter = 0; for(AcRow acRow : ac.getRows()) { //行ごとのループ Row thisRow = calcSheet.createRow(rowIndex); int colIndex = 0; @@ -232,6 +401,12 @@ public class Nucalgen { for(AcCell acCell : acRow.getCells()) { //セルごとのループ Cell thisCell = thisRow.createCell(colIndex); + cellCounter++; + if(sheetNameList == null) { + String areaName = "ADDITION_" + fileName + "_" + cellCounter; + namedArea.save(areaName, rowIndex, colIndex); + } + //alias 「付加行」内の別名定義(制約:右方・下方のセルからしか参照できない) if(acCell.getAlias() != null) { namedArea.save(acCell.getAlias(), rowIndex, colIndex); @@ -240,11 +415,24 @@ public class Nucalgen { //formula 計算式 if(acCell.getFormula() != null){ String formula = acCell.getFormula(); - for(Entry keyValue : namedArea.entrySet()) { - String k = keyValue.getKey(); - String v = keyValue.getValue(); + + if(sheetNameList != null && formula.indexOf("AREA_") != -1) { + //集計シート && 名前付き範囲 AREA_ が対象に含まれる + List sumTarget = new ArrayList(); + for(String aSheetName : sheetNameList) { + String areaName = "ADDITION_" + fileName + "_" + cellCounter; + sumTarget.add(aSheetName + "!" + namedArea.load(areaName)); + } + formula = "SUM(" + StringUtils.join(sumTarget, ",") + ")"; - formula = replaceFormula(formula, k, v); + }else { + //それ以外 + for(Entry keyValue : namedArea.entrySet()) { + String k = keyValue.getKey(); + String v = keyValue.getValue(); + + formula = replaceFormula(formula, k, v); + } } thisCell.setCellFormula(formula); -- 2.43.0