From 5c8c9b45532b3668857ffc9d254d894432fc6aae Mon Sep 17 00:00:00 2001 From: satomichan Date: Sun, 27 Dec 2020 01:18:20 +0900 Subject: [PATCH] =?utf8?q?2020-12-27=5F=E5=85=AB=E8=A8=82=E5=AF=BE?= =?utf8?q?=E5=BF=9C?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../nucalgen/MoeStdFoodCompTable.java | 111 ++++++++++++------ .../java/jp/satomichan/nucalgen/Nucalgen.java | 13 +- 2 files changed, 78 insertions(+), 46 deletions(-) diff --git a/src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java b/src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java index 9799937..1a39f2d 100644 --- a/src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java +++ b/src/main/java/jp/satomichan/nucalgen/MoeStdFoodCompTable.java @@ -32,60 +32,95 @@ public class MoeStdFoodCompTable { - //「本表」変換 + //変換 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("緑黄色野菜"); + Sheet foodComposithonSheet = outputWorkbook.cloneSheet(0); + outputWorkbook.setSheetName(18, "成分表"); + outputWorkbook.setSheetOrder("成分表", 0); + //Sheet foodComposithonSheet = outputWorkbook.createSheet("成分表"); + + //「成分識別子」行 + Row idRow = foodComposithonSheet.getRow(10); + for(int i = 4; i <= 61; i++) { + Cell idCell = idRow.getCell(i); + idCell.setCellValue(idCell.toString().replaceAll(" ", "")); + } + idRow.createCell(62).setCellValue("GROUP"); + idRow.createCell(63).setCellValue("BRIGHT_COLORED_VEGETABLE"); + int compSheetRowIndex = 11; - for (Row row : mainSheet) { - rowCount++; - if(rowCount < 8) {continue;} + //18 の食品群ごとの処理 + for(int group = 1; group <= 18; group++) { + Sheet thisSheet = outputWorkbook.getSheetAt(group); - for (Cell cell : row) { - String cellString = cell.toString(); + int rowCount = 0; + for (Row thisRow : thisSheet) { + rowCount++; + if(rowCount < 12) {continue;} - cellString = cellString.replaceAll("\\(", ""); - cellString = cellString.replaceAll("\\)", ""); - cellString = cellString.replaceAll("-", "0"); - cellString = cellString.replaceAll("Tr", "0"); + Row compRow = foodComposithonSheet.createRow(compSheetRowIndex); + for (int cellCount = 1; cellCount <= 61; cellCount++) { + Cell thisCell = thisRow.getCell(cellCount); - if(cellString.matches("^[\\d\\.]+$")) { - cell.setCellValue(Double.parseDouble(cellString)); - CellStyle aCellStyle = cell.getCellStyle(); - aCellStyle.setDataFormat((short) 0); - cell.setCellStyle(aCellStyle); - } + String cellString = thisCell.toString(); - } + cellString = cellString.replaceAll("\\(", ""); + cellString = cellString.replaceAll("\\)", ""); + cellString = cellString.replaceAll("-", "0"); + cellString = cellString.replaceAll("Tr", "0"); - int gun = (int) row.getCell(0).getNumericCellValue(); - row.createCell(lastCol + 1).setCellValue(gun); + //セル値・書式 コピー + //Cell compCell = foodComposithonSheet.getRow(compSheetRowIndex).getCell(cellCount); + Cell compCell = compRow.createCell(cellCount); + compCell.setCellValue(cellString); + compCell.setCellStyle(thisCell.getCellStyle()); + compCell.setCellType(thisCell.getCellTypeEnum()); + + if(cellString.matches("^[\\d\\.]+$")) { + compCell.setCellValue(Double.parseDouble(cellString)); + CellStyle aCellStyle = compCell.getCellStyle(); + aCellStyle.setDataFormat((short) 0); + compCell.setCellStyle(aCellStyle); + } - //緑黄色野菜 - 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; + } //CELL + + //食品群（１～１８）書き込み + compRow.createCell(62).setCellValue(group); + + + //緑黄色野菜か？ + if(brightColoredVegetablesXmlFileName.length() > 0) { + boolean isBrightColored = false; + String foodName = thisRow.getCell(3).getStringCellValue(); + for(String aBright : this.brightColoredVegetableList) { + if(foodName.matches(aBright + ".*")) { + isBrightColored = true; + break; + } } - } - if(isBrightColored) { - row.createCell(lastCol + 2).setCellValue(1); + if(isBrightColored) { + compRow.createCell(63).setCellValue(1); + } } - } - } + compSheetRowIndex++; + + } //ROW + + } //GROUP + //元の表 削除 + for(int i = 18; i > 0; i--) { + outputWorkbook.removeSheetAt(i); + } } + + + } diff --git a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java index 8c79e05..b0295c4 100644 --- a/src/main/java/jp/satomichan/nucalgen/Nucalgen.java +++ b/src/main/java/jp/satomichan/nucalgen/Nucalgen.java @@ -41,7 +41,7 @@ public class Nucalgen { CommandLineParser parser = new DefaultParser(); CommandLine cmd = parser.parse(options, args); - final String moeStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table"); + final String mextStdFoodCompTableFileName = 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")); @@ -51,17 +51,14 @@ public class Nucalgen { NutritionColumnHolder nc = new NutritionColumnHolder(config); //Book生成 - Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(moeStdFoodCompTableFileName)); + Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName)); 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("栄養価計算"); @@ -101,7 +98,7 @@ public class Nucalgen { 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(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",成分表!$B$12:$BL$2500,3,FALSE),\"\")"); thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false)); colIndex = 4; @@ -111,7 +108,7 @@ public class Nucalgen { 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 + ",\"\")"); + thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + ",成分表!$B$12:$BL$2500,MATCH(\"" + aColumn.getName() + "\",成分表!$B$11:$BL$11,0),FALSE) " + div100 + ",\"\")"); colIndex++; } -- 2.43.0