private int addSummaryRow(int currentRow, final int firstFoodRow) {
HSSFCell cell;
final HSSFRow sumRow = sheet.createRow(++currentRow);
cell =
createCell(
PriceColumns.COLUMN_PRICE_PER_SERVING.getColumn(),
"Gesamt:",
sumRow,
borderedBoldCellStyle);
// HSSFCellUtil.setAlignment(cell, workbook, HSSFCellStyle.ALIGN_RIGHT);
if (currentRow > firstFoodRow) {
System.out.println("currentRow=" + currentRow + " firstFoodRow=" + firstFoodRow);
// final HSSFCellStyle sumStyle =
// createBorderedBoldFontCellStyle(workbook);
cell =
createNumberCell(
PriceColumns.COLUMN_TOTAL_PRICE.getColumn(),
0d,
sumRow,
borderedBoldUnderlinedCellStyle);
cell.setCellFormula(
"SUM("
+ getCellRefString(firstFoodRow, PriceColumns.COLUMN_TOTAL_PRICE.getColumn())
+ ":"
+ getCellRefString(currentRow - 1, PriceColumns.COLUMN_TOTAL_PRICE.getColumn())
+ ")");
}
return currentRow;
}
public Map<Long, String> fillSheet() {
final int numberOfOrderer = calcNumberOfOrderer(orderList);
Map<Long, String> foodId2PriceCellRef = new HashMap<Long, String>();
int currentRow = 0;
addHeaderRow(currentRow);
final int firstFoodRow = currentRow + 1;
HSSFCell cell;
for (final OrderListTotalFoodQuantity foodInfo : totalQuantityInfoList) {
// log.error("" + foodInfo);
final HSSFRow bodyRow = sheet.createRow(++currentRow);
createNumberCell(
PriceColumns.COLUMN_NUMBER_OF_SERVINGS.getColumn(),
foodInfo.getTotalQuantity(),
bodyRow,
borderedCellStyle);
createCell(
PriceColumns.COLUMN_FOOD_NAME.getColumn(),
foodInfo.getFood().getName(),
bodyRow,
borderedCellStyle);
// Entweder der Einzelpreis, oder der KG-Preis
createNumberCell(
PriceColumns.COLUMN_SAVED_PRICE.getColumn(),
getPriceAsDouble(foodInfo),
bodyRow,
borderedCurrencyCellStyle);
if (foodInfo.getFood().getPricePerKilo()) {
final Integer averageServingWeight = foodInfo.getFood().getAverageWeightPerServing();
cell =
createCell(
PriceColumns.COLUMN_SAVED_AVERAGE_SERVING_WEIGHT.getColumn(),
0,
bodyRow,
borderedCellStyle);
if (averageServingWeight != null) {
final double d = averageServingWeight.doubleValue() / 1000d;
cell.setCellValue(d);
} else {
createSimpleComment(
cell,
"Gesamtpreis kann nicht berechnet werden! (Kein Durchschnitts-Portionsgewicht gespeichert)",
"Configuration error");
}
// Berech. Einzelpreis auf Basis des Durchschn. gesp.
// Portionsgewichts
cell =
createNumberCell(
PriceColumns.COLUMN_PRECALCULATED_UNIT_PRICE.getColumn(),
0,
bodyRow,
borderedCurrencyCellStyle);
cell.setCellFormula(
getCellRefString(currentRow, PriceColumns.COLUMN_SAVED_PRICE.getColumn())
+ "*"
+ getCellRefString(
currentRow, PriceColumns.COLUMN_SAVED_AVERAGE_SERVING_WEIGHT.getColumn()));
// Gesamtgewicht
cell =
createNumberCell(
PriceColumns.COLUMN_TOTAL_WEIGHT.getColumn(), 0, bodyRow, borderedCellStyle);
cell.setCellFormula(
getCellRefString(currentRow, PriceColumns.COLUMN_NUMBER_OF_SERVINGS.getColumn())
+ "*"
+ getCellRefString(
currentRow, PriceColumns.COLUMN_SAVED_AVERAGE_SERVING_WEIGHT.getColumn()));
} else {
// Berechneter Preis
cell =
createNumberCell(
PriceColumns.COLUMN_PRECALCULATED_UNIT_PRICE.getColumn(),
0,
bodyRow,
borderedCurrencyCellStyle);
cell.setCellFormula(
getCellRefString(currentRow, PriceColumns.COLUMN_SAVED_PRICE.getColumn()));
}
// Gesamtpreis
cell =
createNumberCell(
PriceColumns.COLUMN_TOTAL_PRICE.getColumn(), 0, bodyRow, borderedCurrencyCellStyle);
cell.setCellFormula(
getCellRefString(currentRow, PriceColumns.COLUMN_PRECALCULATED_UNIT_PRICE.getColumn())
+ "*"
+ getCellRefString(currentRow, PriceColumns.COLUMN_NUMBER_OF_SERVINGS.getColumn()));
// Einzelpreis
cell =
createNumberCell(
PriceColumns.COLUMN_PRICE_PER_SERVING.getColumn(),
0,
bodyRow,
borderedCurrencyCellStyle);
// wird auf Basis des Gesamtpreises berechnent (welcher durch
// Benutzereingabe fest überschrieben werden kann)
cell.setCellFormula(
getCellRefString(currentRow, PriceColumns.COLUMN_TOTAL_PRICE.getColumn())
+ "/"
+ getCellRefString(currentRow, PriceColumns.COLUMN_NUMBER_OF_SERVINGS.getColumn()));
// Preis-Umlage
if (foodInfo.getFood().getCommunityFood()) {
cell =
createNumberCell(
PriceColumns.COLUMN_COMMON_FOOD_CONTRIBUTION.getColumn(),
0,
bodyRow,
borderedCurrencyCellStyle);
cell.setCellFormula(
getCellRefString(currentRow, PriceColumns.COLUMN_TOTAL_PRICE.getColumn())
+ "/"
+ numberOfOrderer);
foodId2PriceCellRef.put(
foodInfo.getFood().getId(),
getCellRefString(
currentRow, PriceColumns.COLUMN_COMMON_FOOD_CONTRIBUTION.getColumn(), sheetName));
} else {
foodId2PriceCellRef.put(
foodInfo.getFood().getId(),
getCellRefString(
currentRow, PriceColumns.COLUMN_PRICE_PER_SERVING.getColumn(), sheetName));
}
} // for - alle Speisen
addSummaryRow(currentRow, firstFoodRow);
return foodId2PriceCellRef;
}