final ContentProvider contentProvider = new MyContentProvider(xls);
// create a default Date format and currency column
xls.setContentProvider(contentProvider);
final String sheetTitle = PFUserContext.getLocalizedString("timesheet.timesheets");
final ExportSheet sheet = xls.addSheet(sheetTitle);
sheet.createFreezePane(8, 1);
final ExportColumn[] cols = new ExportColumn[] { //
new I18nExportColumn(Col.USER, "timesheet.user", MyXlsContentProvider.LENGTH_USER),
new I18nExportColumn(Col.KUNDE, "fibu.kunde", MyXlsContentProvider.LENGTH_STD),
new I18nExportColumn(Col.PROJEKT, "fibu.projekt", MyXlsContentProvider.LENGTH_STD),
new I18nExportColumn(Col.KOST2, "fibu.kost2", MyXlsContentProvider.LENGTH_KOSTENTRAEGER),
new I18nExportColumn(Col.WEEK_OF_YEAR, "calendar.weekOfYearShortLabel", 4),
new I18nExportColumn(Col.DAY_OF_WEEK, "calendar.dayOfWeekShortLabel", 4),
new I18nExportColumn(Col.START_TIME, "timesheet.startTime", MyXlsContentProvider.LENGTH_DATETIME),
new I18nExportColumn(Col.STOP_TIME, "timesheet.stopTime", MyXlsContentProvider.LENGTH_TIMESTAMP),
new I18nExportColumn(Col.DURATION, "timesheet.duration", MyXlsContentProvider.LENGTH_DURATION),
new I18nExportColumn(Col.HOURS, "hours", MyXlsContentProvider.LENGTH_DURATION),
new I18nExportColumn(Col.LOCATION, "timesheet.location", MyXlsContentProvider.LENGTH_STD),
new I18nExportColumn(Col.TASK_TITLE, "task.title", MyXlsContentProvider.LENGTH_STD),
new I18nExportColumn(Col.REFERENCE, "task.reference", MyXlsContentProvider.LENGTH_STD),
new I18nExportColumn(Col.SHORT_DESCRIPTION, "shortDescription", MyXlsContentProvider.LENGTH_STD),
new I18nExportColumn(Col.DESCRIPTION, "timesheet.description", MyXlsContentProvider.LENGTH_EXTRA_LONG),
new I18nExportColumn(Col.TASK_PATH, "task.path", MyXlsContentProvider.LENGTH_EXTRA_LONG),
new I18nExportColumn(Col.ID, "id", MyXlsContentProvider.LENGTH_ID)};
// column property names
sheet.setColumns(cols);
final ContentProvider sheetProvider = sheet.getContentProvider();
sheetProvider.putFormat(Col.START_TIME, "yyyy-MM-dd HH:mm");
sheetProvider.putFormat(Col.STOP_TIME, "HH:mm");
sheetProvider.putFormat(Col.DURATION, "[h]:mm");
sheetProvider.putFormat(Col.HOURS, "#,##0.00");
sheetProvider.putFormat(Col.ID, "0");
final PropertyMapping mapping = new PropertyMapping();
for (final TimesheetDO timesheet : list) {
final TaskNode node = taskTree.getTaskNodeById(timesheet.getTaskId());
final PFUserDO user = userGroupCache.getUser(timesheet.getUserId());
mapping.add(Col.USER, user.getFullname());
final Kost2DO kost2 = timesheet.getKost2();
String kost2Name = null;
String projektName = null;
String kundeName = null;
if (kost2 != null) {
kost2Name = kost2.getShortDisplayName();
final ProjektDO projekt = kost2.getProjekt();
if (projekt != null) {
projektName = projekt.getName();
final KundeDO kunde = projekt.getKunde();
if (kunde != null) {
kundeName = kunde.getName();
} else {
}
}
}
mapping.add(Col.KOST2, kost2Name);
mapping.add(Col.PROJEKT, projektName);
mapping.add(Col.KUNDE, kundeName);
mapping.add(Col.TASK_TITLE, node.getTask().getTitle());
mapping.add(Col.TASK_PATH, taskFormatter.getTaskPath(timesheet.getTaskId(), null, true, OutputType.PLAIN));
mapping.add(Col.WEEK_OF_YEAR, timesheet.getFormattedWeekOfYear());
mapping.add(Col.DAY_OF_WEEK, dateTimeFormatter.getFormattedDate(timesheet.getStartTime(), DateFormats
.getFormatString(DateFormatType.DAY_OF_WEEK_SHORT)));
final DateHolder startTime = new DateHolder(timesheet.getStartTime());
final DateHolder stopTime = new DateHolder(timesheet.getStopTime());
mapping.add(Col.START_TIME, startTime);
mapping.add(Col.STOP_TIME, stopTime);
final BigDecimal seconds = new BigDecimal(timesheet.getDuration() / 1000); // Seconds
final BigDecimal duration = seconds.divide(new BigDecimal(60 * 60 * 24), 8, RoundingMode.HALF_UP); // Fraction of day (24 hours)
mapping.add(Col.DURATION, duration.doubleValue());
final BigDecimal hours = seconds.divide(new BigDecimal(60 * 60), 2, RoundingMode.HALF_UP);
mapping.add(Col.HOURS, hours.doubleValue());
mapping.add(Col.LOCATION, timesheet.getLocation());
mapping.add(Col.REFERENCE, node.getReference());
mapping.add(Col.SHORT_DESCRIPTION, timesheet.getShortDescription());
mapping.add(Col.DESCRIPTION, timesheet.getDescription());
mapping.add(Col.ID, timesheet.getId());
sheet.addRow(mapping.getMapping(), 0);
}
sheet.setZoom(3, 4); // 75%
return xls.getAsByteArray();
}