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$12:$BL$2500,3,FALSE),\"\")");
+ thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",成分表!$B$13:$BL$2500,3,FALSE),\"\")");
thisRow.createCell(3).setCellStyle(csPool.getCellStyle("", false));
colIndex = 4;
String div100 = aColumn.isUseRawValue() ? "" : "/ 100 * $D" + (rowIndex + 1);
- thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + "," + aColumn.getTable() + "!$B$12:$BL$2500,MATCH(\"" + aColumn.getName() + "\"," + aColumn.getTable() + "!$B$11:$BL$11,0),FALSE) " + div100 + ",\"\")");
+ 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());
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)";
+ final String sumKiloCalorieCell = _namedAreaMap.get("SUM_KCAL");
rowIndex++;
Row pfbBalanceRow2 = calcSheet.createRow(rowIndex);
Cell pCell = pfbBalanceRow2.createCell(3);
pCell.setCellStyle(csPool.getCellStyle("0"));
- pCell.setCellFormula(_namedAreaMap.get("SUM_P") + "*4*100/" + sumPfc);
+ pCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_P") + "*4*100/" + sumKiloCalorieCell + ",0)");
Cell fCell = pfbBalanceRow2.createCell(4);
fCell.setCellStyle(csPool.getCellStyle("0"));
- fCell.setCellFormula(_namedAreaMap.get("SUM_F") + "*9*100/" + sumPfc);
+ fCell.setCellFormula("ROUND(" + _namedAreaMap.get("SUM_F") + "*9*100/" + sumKiloCalorieCell + ",0)");
+
Cell cCell = pfbBalanceRow2.createCell(5);
cCell.setCellStyle(csPool.getCellStyle("0"));
- cCell.setCellFormula(_namedAreaMap.get("SUM_C") + "*4*100/" + sumPfc);
+ cCell.setCellFormula("100 - (" + new CellReference(cCell.getRowIndex(), 3).formatAsString() + " + "
+ + new CellReference(cCell.getRowIndex(), 4).formatAsString() + ")");
return rowIndex;
}