List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat
public String getFormat(short index)
From source file:org.databene.formats.xls.BeanXLSWriter.java
License:Open Source License
private void writeHeaderRow(E bean, HSSFSheet sheet) { HSSFRow headerRow = sheet.createRow(0); for (int i = 0; i < beanProperties.size(); i++) { PropFormat prop = beanProperties.get(i); // write column header String componentName = prop.getName(); headerRow.createCell(i).setCellValue(new HSSFRichTextString(componentName)); // apply pattern if (prop.getPattern() != null) { HSSFDataFormat dataFormat = workbook.createDataFormat(); CellStyle columnStyle = workbook.createCellStyle(); columnStyle.setDataFormat(dataFormat.getFormat(prop.getPattern())); sheet.setDefaultColumnStyle(i, columnStyle); }/* w ww .ja v a 2s . c o m*/ } }
From source file:org.databene.platform.xls.XLSEntityExporter.java
License:Open Source License
private void createWorkbook() { this.workbook = new HSSFWorkbook(); this.dateCellStyle = workbook.createCellStyle(); HSSFDataFormat format = workbook.createDataFormat(); short dateFormat = format.getFormat(getDatePattern()); this.dateCellStyle.setDataFormat(dateFormat); }
From source file:org.databene.platform.xls.XLSEntityExporter.java
License:Open Source License
private void writeHeaderRow(Entity entity, HSSFSheet sheet) { HSSFRow headerRow = sheet.createRow(0); int colnum = 0; for (Map.Entry<String, Object> component : getComponents(entity)) { String componentName = component.getKey(); headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName)); ComponentDescriptor cd = entity.descriptor().getComponent(componentName); PrimitiveType primitiveType; if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor) primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType(); else//w w w . ja v a2 s .c om throw new UnsupportedOperationException("Can only export simple type attributes, " + "failed to export " + entity.type() + '.' + cd.getName()); Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class); String formatString = null; if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern(); else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern(); else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern(); else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern(); else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern(); if (formatString != null) { HSSFDataFormat dataFormat = workbook.createDataFormat(); CellStyle columnStyle = workbook.createCellStyle(); columnStyle.setDataFormat(dataFormat.getFormat(formatString)); sheet.setDefaultColumnStyle(colnum, columnStyle); } colnum++; } }
From source file:org.devgateway.eudevfin.sheetexp.poi.PoiObjectCreator.java
License:Open Source License
private CellStyle createBodyDateCellStyle() { final CellStyle style = this.workbook.createCellStyle(); final Font font = this.workbook.createFont(); style.setFont(font);// w w w . j a va 2s. com final DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, this.locale); final String pattern = ((SimpleDateFormat) df).toPattern(); final HSSFDataFormat hssfDataFormat = this.workbook.createDataFormat(); style.setDataFormat(hssfDataFormat.getFormat(pattern)); return style; }
From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor) throws IOException { final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFDataFormat dateFormat = workbook.createDataFormat(); final HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000")); try {/*from ww w .j a va 2 s . c o m*/ monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size()); try { monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook); monitor.worked(1); final HSSFSheet sheet = createSheet(events, workbook, columns); monitor.worked(1); monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents); for (int i = 0; i < events.size(); i++) { final HSSFRow row = sheet.createRow(i + 1); final Event e = events.get(i); for (int j = 0; j < columns.size(); j++) { final Field field = columns.get(j); final ExcelCell cell = new ExcelCell(row, j, dateCellStyle); field.render(e, cell); } monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } sheet.setRepeatingRows(new CellRangeAddress(0, 1, -1, -1)); monitor.setTaskName("Auto sizing"); for (int i = 0; i < columns.size(); i++) { monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader())); sheet.autoSizeColumn(i); monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } } finally { monitor.subTask(Messages.ExportImpl_Progress_CloseFile); if (workbook != null) { makeDocInfo(workbook); final FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); } monitor.worked(1); } } finally { monitor.done(); } return Status.OK_STATUS; }
From source file:org.extremecomponents.table.view.ExtendXlsView.java
License:Apache License
private Map initStyles(HSSFWorkbook wb, short fontHeight) { Map result = new HashMap(); HSSFCellStyle titleStyle = wb.createCellStyle(); HSSFCellStyle textStyle = wb.createCellStyle(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFCellStyle numericStyle = wb.createCellStyle(); HSSFCellStyle numericStyleBold = wb.createCellStyle(); HSSFCellStyle moneyStyle = wb.createCellStyle(); HSSFCellStyle moneyStyleBold = wb.createCellStyle(); HSSFCellStyle percentStyle = wb.createCellStyle(); HSSFCellStyle percentStyleBold = wb.createCellStyle(); result.put("titleStyle", titleStyle); result.put("textStyle", textStyle); result.put("boldStyle", boldStyle); result.put("numericStyle", numericStyle); result.put("numericStyleBold", numericStyleBold); result.put("moneyStyle", moneyStyle); result.put("moneyStyleBold", moneyStyleBold); result.put("percentStyle", percentStyle); result.put("percentStyleBold", percentStyleBold); HSSFDataFormat format = wb.createDataFormat(); // Global fonts HSSFFont font = wb.createFont();/* w ww .j a v a2s. c om*/ font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setColor(HSSFColor.BLACK.index); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints(fontHeight); HSSFFont fontBold = wb.createFont(); fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontBold.setColor(HSSFColor.BLACK.index); fontBold.setFontName(HSSFFont.FONT_ARIAL); fontBold.setFontHeightInPoints(fontHeight); // Money Style moneyStyle.setFont(font); moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyle.setDataFormat(format.getFormat(moneyFormat)); // Money Style Bold moneyStyleBold.setFont(fontBold); moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyleBold.setDataFormat(format.getFormat(moneyFormat)); // Percent Style percentStyle.setFont(font); percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyle.setDataFormat(format.getFormat(percentFormat)); // Percent Style Bold percentStyleBold.setFont(fontBold); percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyleBold.setDataFormat(format.getFormat(percentFormat)); // Standard Numeric Style numericStyle.setFont(font); numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Standard Numeric Style Bold numericStyleBold.setFont(fontBold); numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Title Style titleStyle.setFont(font); titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBottomBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setLeftBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setRightBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setTopBorderColor(HSSFColor.BLACK.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Standard Text Style textStyle.setFont(font); textStyle.setWrapText(true); // Standard Text Style boldStyle.setFont(fontBold); boldStyle.setWrapText(true); return result; }
From source file:org.mili.core.text.transformation.ExcelTransformator.java
License:Apache License
/** * Transforms./*from www .j a va2s .co m*/ * * @param from from table * @param params the params * @return the HSSF workbook */ public HSSFWorkbook transform(Table from, Object... params) { if (from.getRowSize() == 0) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFRow r = sheet.createRow(0); HSSFCell c = r.createCell((short) (0)); c.setCellValue("Keine Daten vorhanden !"); return wb; } String fsInt = "#,###,##0"; String fsFloat = "#,###,##0.000"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFFont headFont = wb.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFont(headFont); HSSFCellStyle numberStyle = wb.createCellStyle(); HSSFRow headerRow = sheet.createRow(0); short z = 0; for (int i = 0, n = from.getColSize(); i < n; i++) { Col col = from.getCol(i); HSSFCell headerCell = headerRow.createCell((short) (z)); headerCell.setCellStyle(headStyle); headerCell.setCellValue(col.getName()); z++; } for (int i = 0, n = from.getRowSize(); i < n; i++) { Row row = from.getRow(i); HSSFRow contentRow = sheet.createRow(i + 1); short j = 0; for (int ii = 0, nn = from.getColSize(); ii < nn; ii++) { Col col = from.getCol(ii); Object o = row.getValue(ii); HSSFCell contentCell = contentRow.createCell((short) (j)); String value = o == null ? "" : String.valueOf(o); if (o instanceof Number) { if (o instanceof Integer) { numberStyle.setDataFormat(format.getFormat(fsInt)); contentCell.setCellValue(Integer.parseInt(value)); contentCell.setCellStyle(numberStyle); } else if (o instanceof Float) { numberStyle.setDataFormat(format.getFormat(fsFloat)); contentCell.setCellValue(Float.parseFloat(value)); contentCell.setCellStyle(numberStyle); } } else { contentCell.setCellValue(value); } j++; } } return wb; }
From source file:org.openmicroscopy.shoola.util.file.ExcelWriter.java
License:Open Source License
/** Creates the default styles. */ private void createStyles() { HSSFCellStyle style;//from www . ja va2s .c o m Iterator<String> fontIterator = fontMap.keySet().iterator(); String fontName; while (fontIterator.hasNext()) { fontName = fontIterator.next(); style = workbook.createCellStyle(); style.setFont(fontMap.get(fontName)); styleMap.put(fontName, style); } HSSFDataFormat df; style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); df = workbook.createDataFormat(); style.setDataFormat(df.getFormat("#.##")); styleMap.put(TWODECIMALPOINTS, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); df = workbook.createDataFormat(); style.setDataFormat(df.getFormat("0")); styleMap.put(INTEGER, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); styleMap.put(CELLBORDER_UNDERLINE, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); styleMap.put(CELLBORDER_TOPLINE, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); styleMap.put(CELLBORDER_UNDERLINE_TOPLINE, style); }
From source file:org.openscada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor) throws IOException { final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFDataFormat dateFormat = workbook.createDataFormat(); final HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000")); try {// w w w . j a v a2 s . c o m monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size()); try { monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook); monitor.worked(1); final HSSFSheet sheet = createSheet(events, workbook, columns); monitor.worked(1); monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents); for (int i = 0; i < events.size(); i++) { final HSSFRow row = sheet.createRow(i + 1); final Event e = events.get(i); for (int j = 0; j < columns.size(); j++) { final Field field = columns.get(j); final ExcelCell cell = new ExcelCell(row, j, dateCellStyle); field.render(e, cell); } monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } workbook.setRepeatingRowsAndColumns(0, -1, -1, 0, 1); monitor.setTaskName("Auto sizing"); for (int i = 0; i < columns.size(); i++) { monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader())); sheet.autoSizeColumn(i); monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } } finally { monitor.subTask(Messages.ExportImpl_Progress_CloseFile); if (workbook != null) { makeDocInfo(workbook); final FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); } monitor.worked(1); } } finally { monitor.done(); } return Status.OK_STATUS; }
From source file:org.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Creates an Excel document with a given column name list, and column data list. * The String objects in the column name list are used as Map keys to look up the corresponding * column header and data. The column data to be exported is a List of Map objects where * the first Map element contains column headers, and the rest has all the column data. * @param workBookName a String object as Excel file name * @param workSheetName a String object as the name of the Excel sheet * @param columnNameList a List of String objects as column names, they usually correspond to entity field names * @param data a List of Map objects to be exported where the first Map element contains column headers, * and the rest has all the column data. * @throws IOException if an error occurs *//*from w w w. ja v a 2 s . c o m*/ public static void saveToExcel(final String workBookName, final String workSheetName, final List<String> columnNameList, final List<Map<String, Object>> data) throws IOException { if (StringUtils.isEmpty(workBookName)) { throw new IllegalArgumentException("Argument workBookName can't be empty"); } if (StringUtils.isEmpty(workSheetName)) { throw new IllegalArgumentException("Argument workSheetName can't be empty"); } if (columnNameList == null || columnNameList.isEmpty()) { throw new IllegalArgumentException("Argument columnNameList can't be empty"); } // the data list should have at least one element for the column headers if (data == null || data.isEmpty()) { throw new IllegalArgumentException("Argument data can't be empty"); } FileOutputStream fileOut = new FileOutputStream(new File(workBookName)); assert fileOut != null; HSSFWorkbook workBook = new HSSFWorkbook(); assert workBook != null; HSSFSheet workSheet = workBook.createSheet(workSheetName); assert workSheet != null; // create the header row HSSFRow headerRow = workSheet.createRow(0); assert workSheet != null; HSSFFont headerFont = workBook.createFont(); assert headerFont != null; headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(HSSFColor.BLACK.index); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); assert headerCellStyle != null; headerCellStyle.setFont(headerFont); // the first data list element should always be the column header map Map<String, Object> columnHeaderMap = data.get(0); if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = headerRow.createCell(i); assert cell != null; cell.setCellStyle(headerCellStyle); Object columnHeaderTitle = columnHeaderMap.get(columnNameList.get(i)); if (columnHeaderTitle != null) { cell.setCellValue(new HSSFRichTextString(columnHeaderTitle.toString())); } } } // create data rows // column data starts from the second element if (data.size() > 1) { // Create the style used for dates. HSSFCellStyle dateCellStyle = workBook.createCellStyle(); String dateFormat = "mm/dd/yyyy hh:mm:ss"; HSSFDataFormat hsfDateFormat = workBook.createDataFormat(); short dateFormatIdx = hsfDateFormat.getFormat(dateFormat); if (dateFormatIdx == -1) { Debug.logWarning("Date format [" + dateFormat + "] could be found or created, try one of the pre-built instead:" + HSSFDataFormat.getBuiltinFormats(), MODULE); } dateCellStyle.setDataFormat(dateFormatIdx); for (int dataRowIndex = 1; dataRowIndex < data.size(); dataRowIndex++) { Map<String, Object> rowDataMap = data.get(dataRowIndex); if (rowDataMap == null) { continue; } HSSFRow dataRow = workSheet.createRow(dataRowIndex); assert dataRow != null; for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = dataRow.createCell(i); assert cell != null; Object cellData = rowDataMap.get(columnNameList.get(i)); if (cellData != null) { // Note: dates are actually numeric values in Excel and so the cell need to have // a special style set so it actually displays as a date if (cellData instanceof Calendar) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Calendar) cellData); } else if (cellData instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) cellData); } else if (cellData instanceof BigDecimal) { cell.setCellValue(((BigDecimal) cellData).doubleValue()); } else if (cellData instanceof Double) { cell.setCellValue(((Double) cellData).doubleValue()); } else if (cellData instanceof Integer) { cell.setCellValue(((Integer) cellData).doubleValue()); } else if (cellData instanceof BigInteger) { cell.setCellValue(((BigInteger) cellData).doubleValue()); } else { cell.setCellValue(new HSSFRichTextString(cellData.toString())); } } } } } // auto size the column width if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { workSheet.autoSizeColumn(i); } } // create the Excel file workBook.write(fileOut); fileOut.close(); }