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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
options.addOption(Option.builder("protect").longOpt("set-protect").build());
options.addOption(Option.builder("r").longOpt("-use-processed-table").build());
options.addOption(Option.builder("protect").longOpt("set-protect").build());
options.addOption(Option.builder("r").longOpt("-use-processed-table").build());
- 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);
colIndex = 4;
for(NutritionColumn aColumn : nc.getNutritionColumnList()) {
Cell thisCell = sumRow.createCell(colIndex);
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();
- 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);
- 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);
- rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex);
+ rowIndex = generatePfcBalance(calcSheet, csPool, rowIndex, namedAreaMap);
- rowIndex = generateGroupSum(calcSheet, csPool, rowIndex);
+ rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap);
- private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex) {
+ private static int generatePfcBalance(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
Row pfbBalanceRow1 = calcSheet.createRow(rowIndex);
pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%)");
calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
Row pfbBalanceRow1 = calcSheet.createRow(rowIndex);
pfbBalanceRow1.createCell(1).setCellValue("PFCバランス (%)");
calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
rowIndex++;
Row pfbBalanceRow2 = calcSheet.createRow(rowIndex);
Cell pCell = pfbBalanceRow2.createCell(3);
pCell.setCellStyle(csPool.getCellStyle("0"));
rowIndex++;
Row pfbBalanceRow2 = calcSheet.createRow(rowIndex);
Cell pCell = pfbBalanceRow2.createCell(3);
pCell.setCellStyle(csPool.getCellStyle("0"));
- private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex) {
+ private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {
List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
"種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
"油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");
List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
"種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
"油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");
Row groupRow = calcSheet.createRow(rowIndex);
groupRow.createCell(1).setCellValue("食品群");
groupRow.createCell(3).setCellValue("摂取量(g)");
Row groupRow = calcSheet.createRow(rowIndex);
groupRow.createCell(1).setCellValue("食品群");
groupRow.createCell(3).setCellValue("摂取量(g)");
thisRow.createCell(2).setCellValue(groupName.get(i));
Cell cCell = thisRow.createCell(3);
cCell.setCellStyle(csPool.getCellStyle(""));
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") + ")");
thisRow.createCell(2).setCellValue("うち 緑黄色野菜");
Cell bcvCell = thisRow.createCell(3);
bcvCell.setCellStyle(csPool.getCellStyle("0"));
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") + ")");