import java.io.FileInputStream;
import java.io.FileOutputStream;
+import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
}
//「栄養計算」行
+ List<String> usedTableList = new ArrayList<String>();
int rowIndex = 3;
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());
}
}
- //摂取量 名前付き範囲
+ //摂取量 範囲を記憶
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);
Cell thisCell = sumRow.createCell(colIndex);
String sumTargetArea = new CellReference(3, colIndex, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, colIndex, true, true).formatAsString();
- //名前付き範囲(alias あれば設定)
+ //範囲を記憶(alias あれば設定)
if(aColumn.getAlias().length() > 0) {
- //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());
String sumArea = new CellReference(rowIndex, colIndex, true, true).formatAsString();
- //namedRangeSum.setRefersToFormula(sumArea);
namedAreaMap.put("SUM_" + aColumn.getAlias(), sumArea);
}
}
rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap);
}
+
+ //未使用表シート削除
+ 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);
+ }
+ }
+
//ブック出力
FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName);
outputWorkbook.setActiveSheet(0);