public void putCurrencyFormat(final ContentProvider sheetProvider, final ExportColumn exportColumn) { sheetProvider.putFormat(exportColumn, "#,##0.00;[Red]-#,##0.00"); exportColumn.setWidth(12); } }
public CellStyle ensureAndGetCellStyle() { CellStyle cellStyle = this.poiCell.getCellStyle(); if (cellStyle == null) { cellStyle = styleProvider.getWorkbook().createCellStyle(); } return cellStyle; }
/** * @param value * @param property * @return this for chaining. */ public ExportCell setValue(final Object value, final String property) { styleProvider.setValue(this, value, property); return this; }
/** * If the sheet has not its own StyleProvider then the given StyleProvider will be used (if not null). * * @param contentProvider Can be null. */ public void updateStyles(final ContentProvider contentProvider) { ContentProvider cp = this.contentProvider; if (cp == null) { cp = contentProvider; } if (cp == null) { return; } cp.updateRowStyle(this); for (final ExportCell cell : getCells()) { if (cell != null) { cp.updateCellStyle(cell); } } }
@Override public void onClick() { checkAdminUser(); final List<DatabaseUpdateDO> updateEntries = myDatabaseUpdater.getUpdateHistory(); final ExportWorkbook workbook = new ExportWorkbook(); final ExportSheet sheet = workbook.addSheet("Update history"); sheet.getContentProvider().setColWidths(new int[] { 20, 10, 20, 15, 50, 20 }); sheet.getContentProvider().putFormat(java.sql.Timestamp.class, "YYYY-MM-DD hh:mm:ss"); sheet.setPropertyNames( new String[] { "regionId", "versionString", "updateDate", "executedBy.username", "description", "executionResult" }); sheet.addRow().setValues("region id", "version", "update date", "executed by", "description", "execution result"); sheet.addRows(updateEntries); final String filename = "ProjectForge-UpdateHistory_" + DateHelper.getDateAsFilenameSuffix(new Date()) + ".xls"; final byte[] xls = workbook.getAsByteArray(); DownloadUtils.setDownloadTarget(xls, filename); }
employeeSheet.setColumnWidth(6, 20 * 256); final ContentProvider provider = employeeSheet.getContentProvider(); provider.putFormat("STUNDEN", "0.00;[Red]-0.00"); final ExportRow employeeRow = employeeSheet.addRow(); employeeRow.addCell(0, ThreadLocalUserContext.getLocalizedString("fibu.employee")); sheetProvider.putFormat("STUNDEN", "0.00"); sheetProvider.putFormat("BRUTTO_MIT_AG", "#,##0.00;[Red]-#,##0.00"); sheetProvider.putFormat("KORREKTUR", "#,##0.00;[Red]-#,##0.00"); sheetProvider.putFormat("SUMME", "#,##0.00;[Red]-#,##0.00"); sheetProvider.putFormat("KOST1", "#"); sheetProvider.putFormat("KOST2", "#"); sheetProvider.putFormat("KONTO", "#"); sheetProvider.putFormat("GEGENKONTO", "#"); sheetProvider.putFormat("DATUM", "dd.MM.yyyy"); sheetProvider.putColWidth(ci, colWidths[ci]);
final ExportWorkbook workbook = new ExportWorkbook(); final ExportSheet sheet = workbook.addSheet("Test"); sheet.getContentProvider().setColWidths(20, 20, 20); sheet.addRow().setValues("Type", "Precision", "result"); sheet.addRow().setValues("Java output", ".", "Tue Sep 28 00:27:10 UTC 2010");
private void addHeadRowCell(final ExportRow headRow, final ExportColumn col, final String[] colNames, final int idx) { headRow.addCell(idx, col.getTitle()); colNames[idx] = col.getName(); contentProvider.putColWidth(idx, col.getWidth()); }
public static void main(String... args) throws IOException { final ExportWorkbook workbook = new ExportWorkbook(); ExportSheet sheet = workbook.addSheet("Data types"); sheet.getContentProvider().setColWidths(20, 20); sheet.addRow().setValues("Type", "result"); sheet.addRow().setValues("String", "This is a text."); sheet.addRow().setValues("int", 1234); sheet.addRow().setValues("BigDecimal", new BigDecimal("1042.3873")); Date date = new Date(); sheet.addRow().setValues("Date", date); sheet.addRow().setValues("SQL-Date", new java.sql.Date(date.getTime())); sheet.addRow().setValues("Timestamp", new Timestamp(date.getTime())); sheet = workbook.addSheet("Own data types"); sheet.getContentProvider().setColWidths(20, 20).putFormat(Currency.class, "#,##0.00;[Red]-#,##0.00"); sheet.addRow().setValues("Type", "result"); sheet.addRow().setValues("Currency", new Currency("1023.873").getValue()); sheet.addRow().setValues("Currency", new Currency("-10").getValue()); final File file = new File("target/test-excel.xls"); log.info("Writing Excel test sheet to work directory: " + file.getAbsolutePath()); workbook.write(new FileOutputStream(file)); } }
putCurrencyFormat(sheetProvider, exportColumn); } else if (type == PropertyType.DATE) { sheetProvider.putFormat(exportColumn, "MM/dd/yyyy"); } else if (type == PropertyType.DATE_TIME) { sheetProvider.putFormat(exportColumn, "MM/dd/yyyy HH:mm"); } else if (type == PropertyType.DATE_TIME_SECONDS) { sheetProvider.putFormat(exportColumn, "MM/dd/yyyy HH:mm:ss"); } else if (type == PropertyType.DATE_TIME_MILLIS) { sheetProvider.putFormat(exportColumn, "MM/dd/yyyy HH:mm:ss.fff"); } else if (type == PropertyType.UNSPECIFIED) { if (java.sql.Date.class.isAssignableFrom(field.getType()) == true) { sheetProvider.putFormat(exportColumn, "MM/dd/yyyy"); } else if (java.util.Date.class.isAssignableFrom(field.getType()) == true) { sheetProvider.putFormat(exportColumn, "MM/dd/yyyy HH:mm"); } else if (java.lang.Integer.class.isAssignableFrom(field.getType()) == true) { exportColumn.setWidth(10);
/** * Sets the data format of the poi cell. Use this method only if you modify existing cells of an existing workbook (loaded from file). * * @param dataFormat * @return this for chaining. */ public ExportCell setDataFormat(final String dataFormat) { final CellStyle cellStyle = ensureAndGetCellStyle(); final short df = styleProvider.getWorkbook().getDataFormat(dataFormat); cellStyle.setDataFormat(df); return this; } }
/** * @see org.projectforge.export.MyExcelExporter#putFieldFormat(ContentProvider, * java.lang.reflect.Field, org.projectforge.common.anots.PropertyInfo, ExportColumn) */ @Override public void putFieldFormat(final ContentProvider sheetProvider, final Field field, final PropertyInfo propInfo, final ExportColumn exportColumn) { if ("month".equals(field.getName()) == true) { sheetProvider.putFormat(exportColumn, "mmm"); exportColumn.setWidth(6); } else if ("year".equals(field.getName()) == true) { sheetProvider.putFormat(exportColumn, "#"); exportColumn.setWidth(6); } else { super.putFieldFormat(sheetProvider, field, propInfo, exportColumn); } }
/** * Excel shares the cell formats and the number of cell formats is limited. This method uses a new cell style!<br/> * Don't forget to call #setCellStyle(CellStyle) if you want to apply this cloned one. * * @return */ public CellStyle cloneCellStyle() { final CellStyle cellStyle = styleProvider.getWorkbook().createCellStyle(); final CellStyle origCellStyle = this.poiCell.getCellStyle(); if (origCellStyle != null) { cellStyle.cloneStyleFrom(origCellStyle); } return cellStyle; }
sheetProvider.putFormat(exportColumn, DateFormats.getExcelFormatString(DateFormatType.DATE)); exportColumn.setWidth(10); } else if (type == PropertyType.DATE_TIME) { sheetProvider.putFormat(exportColumn, DateFormats.getExcelFormatString(DateFormatType.DATE_TIME_MINUTES)); exportColumn.setWidth(10); } else if (type == PropertyType.DATE_TIME_SECONDS) { sheetProvider.putFormat(exportColumn, DateFormats.getExcelFormatString(DateFormatType.DATE_TIME_SECONDS)); exportColumn.setWidth(16); } else if (type == PropertyType.DATE_TIME_MILLIS) { sheetProvider.putFormat(exportColumn, DateFormats.getExcelFormatString(DateFormatType.DATE_TIME_MILLIS)); exportColumn.setWidth(18); } else if (type == PropertyType.UNSPECIFIED) { if (java.sql.Date.class.isAssignableFrom(field.getType()) == true) { sheetProvider.putFormat(exportColumn, DateFormats.getExcelFormatString(DateFormatType.DATE)); exportColumn.setWidth(10); } else if (java.util.Date.class.isAssignableFrom(field.getType()) == true) { sheetProvider.putFormat(exportColumn, DateFormats.getExcelFormatString(DateFormatType.DATE_TIME_MINUTES)); exportColumn.setWidth(16); } else {
sheetProvider.putFormat(Col.UNASSIGNEDHOURS, "0.00"); sheetProvider.putFormat(Col.MONDAYHOURS, "0.00"); sheetProvider.putFormat(Col.TUESDAYHOURS, "0.00"); sheetProvider.putFormat(Col.WEDNESDAYHOURS, "0.00"); sheetProvider.putFormat(Col.THURSDAYHOURS, "0.00"); sheetProvider.putFormat(Col.FRIDAYHOURS, "0.00"); sheetProvider.putFormat(Col.WEEKENDHOURS, "0.00");
sheetProvider.putFormat(MyXlsContentProvider.FORMAT_CURRENCY, PosCol.NETSUM, PosCol.INVOICED, PosCol.TO_BE_INVOICED); sheetProvider .putFormat(DateFormats.getExcelFormatString(DateFormatType.DATE), PosCol.DATE_OF_OFFER, PosCol.DATE_OF_ENTRY, PosCol.PERIOD_OF_PERFORMANCE_BEGIN, PosCol.PERIOD_OF_PERFORMANCE_END); Map<PosCol, BigDecimal> istSumMap = createIstSumMap();
ExportSheet sheet = xls.addSheet(sheetTitle); ContentProvider sheetProvider = sheet.getContentProvider(); sheetProvider.putFormat(MyXlsContentProvider.FORMAT_CURRENCY, OrderCol.NETSUM, OrderCol.INVOICED, OrderCol.TO_BE_INVOICED); sheetProvider.putFormat(DateFormats.getExcelFormatString(DateFormatType.DATE), OrderCol.DATE_OF_ENTRY, OrderCol.DATE_OF_OFFER, OrderCol.ORDER_DATE); sheet.createFreezePane(1, 1); sheet.setColumns(columns); sheet = xls.addSheet(sheetTitle); sheetProvider = sheet.getContentProvider(); sheetProvider.putFormat(MyXlsContentProvider.FORMAT_CURRENCY, PosCol.NETSUM, PosCol.INVOICED, PosCol.TO_BE_INVOICED); sheetProvider.putFormat(DateFormats.getExcelFormatString(DateFormatType.DATE), PosCol.DATE_OF_OFFER, PosCol.DATE_OF_ENTRY, PosCol.PERIOD_OF_PERFORMANCE_BEGIN, PosCol.PERIOD_OF_PERFORMANCE_END); sheet = xls.addSheet(sheetTitle); sheetProvider = sheet.getContentProvider(); sheetProvider.putFormat(MyXlsContentProvider.FORMAT_CURRENCY, PaymentsCol.AMOUNT); sheet.createFreezePane(1, 1); sheet.setColumns(columns);