List of usage examples for org.apache.poi.ss.usermodel CellStyle setDataFormat
void setDataFormat(short fmt);
From source file:de.alpharogroup.export.excel.poi.ExcelPoiFactory.java
License:Open Source License
/** * Creates a new CellStyle with the given date format. * * @param workbook//from ww w.j a v a2 s . c o m * the workbook * @param dateFormat * the date format * @return the cell style */ public static CellStyle newDateCellStyle(final Workbook workbook, final String dateFormat) { final CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat(dateFormat)); return dateCellStyle; }
From source file:de.enerko.reports2.engine.Report.java
License:Apache License
private CellStyle getFormat(final Workbook workbook, final Cell cell, String type, String value) { String format = (String) DATE_FORMATS_EXCEL.get(type.toLowerCase()); // Type is number if (format == null) { final String[] hlp = value.split("@@"); format = hlp.length > 1 ? hlp[1] : "0.00####"; }/*from w ww. j a va 2 s . com*/ CellStyle cellStyle = formatCache.get(format); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.createDataFormat().getFormat(format)); formatCache.put(format, cellStyle); } return cellStyle; }
From source file:de.fme.alfresco.repo.web.scripts.datalist.DataListDownloadWebScript.java
License:Open Source License
@SuppressWarnings("deprecation") @Override/* w w w.java2s. co m*/ protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties) throws IOException { NodeRef list = (NodeRef) resource; List<NodeRef> items = getItems(list); // Our various formats DataFormat formatter = workbook.createDataFormat(); CreationHelper createHelper = workbook.getCreationHelper(); CellStyle styleInt = workbook.createCellStyle(); styleInt.setDataFormat(formatter.getFormat("0")); CellStyle styleDate = workbook.createCellStyle(); styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd")); CellStyle styleDouble = workbook.createCellStyle(); styleDouble.setDataFormat(formatter.getFormat("General")); CellStyle styleNewLines = workbook.createCellStyle(); styleNewLines.setWrapText(true); CellStyle hlink_style = workbook.createCellStyle(); Font hlink_font = workbook.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font); // Export the items int rowNum = 1, colNum = 0; for (NodeRef item : items) { Row r = sheet.createRow(rowNum); colNum = 0; for (QName prop : properties) { Cell c = r.createCell(colNum); Serializable val = nodeService.getProperty(item, prop); if (val == null) { // Is it an association, or just missing? List<AssociationRef> assocs = nodeService.getTargetAssocs(item, prop); Set<QName> qnames = new HashSet<QName>(1, 1.0f); qnames.add(prop); List<ChildAssociationRef> childAssocs = nodeService.getChildAssocs(item, qnames); if (assocs.size() > 0) { StringBuffer text = new StringBuffer(); int lines = 1; for (AssociationRef ref : assocs) { NodeRef child = ref.getTargetRef(); QName type = nodeService.getType(child); if (ContentModel.TYPE_PERSON.equals(type)) { if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_FIRSTNAME)); text.append(" "); text.append(nodeService.getProperty(child, ContentModel.PROP_LASTNAME)); } else if (ContentModel.TYPE_CONTENT.equals(type)) { // TODO Link to the content if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE)); text.append(") "); /*MessageFormat.format(CONTENT_DOWNLOAD_PROP_URL, new Object[] { child.getStoreRef().getProtocol(), child.getStoreRef().getIdentifier(), child.getId(), URLEncoder.encode((String)nodeService.getProperty(child, ContentModel.PROP_TITLE)), URLEncoder.encode(ContentModel.PROP_CONTENT.toString()) }); */ /*currently only one link per cell possible * Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); *link.setAddress("http://poi.apache.org/"); *c.setHyperlink(link); *c.setCellStyle(hlink_style);*/ } else if (ApplicationModel.TYPE_FILELINK.equals(type)) { NodeRef linkRef = (NodeRef) nodeService.getProperty(child, ContentModel.PROP_LINK_DESTINATION); if (linkRef != null) { if (text.length() > 0) { text.append('\n'); lines++; } text.append("link to: "); try { text.append(nodeService.getProperty(linkRef, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(linkRef, ContentModel.PROP_TITLE)); text.append(") "); } catch (Exception e) { text.append(nodeService.getProperty(child, ContentModel.PROP_NAME)); text.append(" ("); text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE)); text.append(") "); } } } else { System.err.println("TODO: handle " + type + " for " + child); } } String v = text.toString(); c.setCellValue(v); if (lines > 1) { c.setCellStyle(styleNewLines); r.setHeightInPoints(lines * sheet.getDefaultRowHeightInPoints()); } } else if (childAssocs.size() > 0) { StringBuffer text = new StringBuffer(); for (ChildAssociationRef childAssociationRef : childAssocs) { NodeRef child = childAssociationRef.getChildRef(); QName type = nodeService.getType(child); if (type.equals(ForumModel.TYPE_FORUM)) { List<ChildAssociationRef> topics = nodeService.getChildAssocs(child); if (topics.size() > 0) { ChildAssociationRef topicRef = topics.get(0); List<ChildAssociationRef> comments = nodeService .getChildAssocs(topicRef.getChildRef()); for (ChildAssociationRef commentChildRef : comments) { NodeRef commentRef = commentChildRef.getChildRef(); ContentData data = (ContentData) nodeService.getProperty(commentRef, ContentModel.PROP_CONTENT); TemplateContentData contentData = new TemplateContentData(data, ContentModel.PROP_CONTENT); String commentString = ""; try { commentString = contentData.getContentAsText(commentRef, -1); } catch (Exception e) { logger.warn("failed to extract content for nodeRef " + commentRef, e); } String creator = (String) nodeService.getProperty(commentRef, ContentModel.PROP_CREATOR); NodeRef person = personService.getPerson(creator, false); if (person != null) { creator = nodeService.getProperty(person, ContentModel.PROP_FIRSTNAME) + " " + nodeService.getProperty(person, ContentModel.PROP_LASTNAME); } Date created = (Date) nodeService.getProperty(commentRef, ContentModel.PROP_CREATED); text.append(creator).append(" (") .append(DateFormatUtils.format(created, "yyyy-MM-dd")) .append("):\n "); text.append(commentString).append("\n"); } } } } String v = text.toString(); c.setCellValue(v); c.setCellStyle(styleNewLines); } else { // This property isn't set c.setCellType(Cell.CELL_TYPE_BLANK); } } else { // Regular property, set if (val instanceof String) { c.setCellValue((String) val); c.setCellStyle(styleNewLines); } else if (val instanceof Date) { c.setCellValue((Date) val); c.setCellStyle(styleDate); } else if (val instanceof Integer || val instanceof Long) { double v = 0.0; if (val instanceof Long) v = (double) (Long) val; if (val instanceof Integer) v = (double) (Integer) val; c.setCellValue(v); c.setCellStyle(styleInt); } else if (val instanceof Float || val instanceof Double) { double v = 0.0; if (val instanceof Float) v = (double) (Float) val; if (val instanceof Double) v = (double) (Double) val; c.setCellValue(v); c.setCellStyle(styleDouble); } else { // TODO System.err.println("TODO: handle " + val.getClass().getName() + " - " + val); } } colNum++; } rowNum++; } // Sensible column widths please! colNum = 0; for (QName prop : properties) { try { sheet.autoSizeColumn(colNum); } catch (IllegalArgumentException e) { sheet.setColumnWidth(colNum, 40 * 256); } colNum++; } }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.ExcelStylesCreator.java
License:Open Source License
private static CellStyle getDateStyle(Workbook workbook, CellStyle dataStyle) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.cloneStyleFrom(dataStyle); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("m/d/yy")); return cellStyle; }
From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformer.java
License:Open Source License
/**{@inheritDoc}**/ @Override/*from w w w. j a v a 2 s . co m*/ public void transform(List<?> sourceList, OutputStream out, TypeOfBuildingBlock typeOfBuildingBlock) { ColumnStructure[] columns = tableStructure.getColumns(); try { Workbook workbook = createWorkbook(); Sheet sheet = workbook.createSheet(); configSheetName(sheet, typeOfBuildingBlock); Map<IteraExcelStyle, CellStyle> createdStyles = ExcelStylesCreator.createStyles(workbook); CellStyle headerStyle = createdStyles.get(IteraExcelStyle.HEADER); CellStyle dataStyle = createdStyles.get(IteraExcelStyle.DATA); CellStyle dataDateStyle = createdStyles.get(IteraExcelStyle.DATA_DATE); // Create cell style for numbers CellStyle numCellStyle = workbook.createCellStyle(); numCellStyle.cloneStyleFrom(dataStyle); short numFormatIndex = workbook.createDataFormat().getFormat("0.00"); numCellStyle.setDataFormat(numFormatIndex); Row headerRow = sheet.createRow(0); int nextCol = 0; for (ColumnStructure columnStructure : columns) { Cell headerCell = headerRow.createCell(nextCol); headerCell.setCellValue(columnStructure.getColumnHeader()); headerCell.setCellStyle(headerStyle); nextCol++; } int nextRow = 1; for (Object obj : sourceList) { if (obj instanceof BuildingBlock) { BuildingBlock bb = (BuildingBlock) obj; // skip virutal root element if (bb instanceof AbstractHierarchicalEntity<?>) { AbstractHierarchicalEntity<?> hierarchicalEntity = (AbstractHierarchicalEntity<?>) bb; if (hierarchicalEntity.isTopLevelElement()) { continue; } } Row row = sheet.createRow(nextRow); nextCol = 0; for (ColumnStructure columnStructure : columns) { Cell cell = row.createCell(nextCol); Object resolvedValue = columnStructure.resolveValue(bb); if (resolvedValue instanceof Date) { cell.setCellStyle(dataDateStyle); cell.setCellValue((Date) resolvedValue); } else if (resolvedValue instanceof Number) { cell.setCellStyle(numCellStyle); double doubleValue = ((Number) resolvedValue).doubleValue(); cell.setCellValue(doubleValue); } else { cell.setCellStyle(dataStyle); cell.setCellValue(String.valueOf(resolvedValue)); } ++nextCol; } ++nextRow; } } // auto format nextCol = 0; for (int col = 0; col < columns.length; col++) { sheet.autoSizeColumn(col); int columnCharWidth = sheet.getColumnWidth(col) / 256; if (columnCharWidth > MAX_COLUM_CHAR_WIDTH) { sheet.setColumnWidth(col, MAX_COLUM_CHAR_WIDTH * 256); } } workbook.write(out); out.flush(); } catch (IOException e) { e.printStackTrace(); } }
From source file:de.iteratec.iteraplan.businesslogic.service.legacyExcel.ExcelImportServiceAttributesIntegrationTest.java
License:Open Source License
@Test public void testImportDateAttribute() { // create attribute type and sample building block AttributeTypeGroup atg = testDataHelper.createAttributeTypeGroup("myTAG", ""); DateAT dateType = testDataHelper.createDateAttributeType("MyDateType", "", atg); testDataHelper.assignAttributeTypeToAllAvailableBuildingBlockTypes(dateType); InformationSystem is = testDataHelper.createInformationSystem("myIS"); InformationSystemRelease isr = testDataHelper.createInformationSystemRelease(is, "1.0"); // create excel cells with import data final HSSFWorkbook workbook = new HSSFWorkbook(); final CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); final HSSFSheet sheet = workbook.createSheet(); final HSSFRow row = sheet.createRow(0); final Cell cell1 = row.createCell(0); cell1.setCellValue(createDate(2010, 12, 31)); cell1.setCellStyle(dateStyle); // need to do this so our import routines can parse it back as a date value Map<String, Cell> attributes = new HashMap<String, Cell>(); attributes.put(dateType.getName(), cell1); LandscapeData landscapeData = new LandscapeData(); landscapeData.addAttributes(isr, attributes); landscapeData.setLocale(Locale.GERMAN); excelImportService.importLandscapeData(landscapeData); InformationSystemRelease isrLoadedFromDb = isrService.loadObjectById(isr.getId()); String loadedAv = isrLoadedFromDb.getAttributeValue(dateType.getName(), Locale.GERMAN); assertEquals("31.12.2010", loadedAv); // following call failed before ITERAPLAN-170 was fixed @SuppressWarnings("unused") AttributeType at = attributeTypeService.getAttributeTypeByName(dateType.getName()); }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void setupStyle(Cell cell, int row) { CellStyle style = cell.getCellStyle(); // cell has its own style and not the default style if (reuseExistingStyles) { // we have to reuse the existing style if (reuseExistingStylesAlternating) { // we have to reuse the style from the even/odd row if (isFirstRow(row)) { // we are in the first row, memorize the style if (style.getIndex() > 0) { // only if the cell does not use the default style oddRowColumnStyleMap.put(cell.getColumnIndex(), style); }/* ww w .j av a 2 s . c o m*/ } else if (isSecondRow(row)) { // we are in the first row, memorize the style if (style.getIndex() > 0) { // only if the cell does not use the default style evenRowColumnStyleMap.put(cell.getColumnIndex(), style); } } else if (isEvenDataRow(row)) { // reference to the previously memorized style for even rows CellStyle s = evenRowColumnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } else { // reference to the previously memorized style for even rows CellStyle s = oddRowColumnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } } else { // we take the style from the last row if (isFirstRow(row)) { // memorize the style for reuse in all other rows if (style.getIndex() > 0) { // only if the cell does not use the default style columnStyleMap.put(cell.getColumnIndex(), style); } } else { // set the style from the previous row CellStyle s = columnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } } } else { Short formatIndex = cellFormatMap.get(cell.getColumnIndex()); if (formatIndex != null) { if ((style.getIndex() == 0) || (style.getDataFormat() != formatIndex)) { // this is the default style or the current format differs from the given format // we need our own style for this style = columnStyleMap.get(cell.getColumnIndex()); if (style == null) { style = workbook.createCellStyle(); style.setDataFormat(formatIndex.shortValue()); columnStyleMap.put(cell.getColumnIndex(), style); } cell.setCellStyle(style); } } } }
From source file:de.jpaw.bonaparte.poi.BaseExcelComposer.java
License:Apache License
private CellStyle getCachedCellStyle(int decimals) { if (decimals < 0 || decimals > MAX_DECIMALS) return null; // no application format if (csBigDecimal[decimals] == null) { CellStyle newStyle = xls.createCellStyle(); newStyle.setDataFormat(xlsDataFormat.getFormat(BIGDECIMAL_FORMATS[decimals])); csBigDecimal[decimals] = newStyle; return newStyle; } else {//from w w w.j a v a 2 s . co m return csBigDecimal[decimals]; } }
From source file:de.ks.idnadrev.expimp.xls.ReflectionColumn.java
License:Apache License
@Override public CellStyle getCellStyle(SXSSFWorkbook workbook) { CreationHelper creationHelper = workbook.getCreationHelper(); if (LocalDateTime.class.isAssignableFrom(fieldType)) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss")); return cellStyle; } else if (LocalDate.class.isAssignableFrom(fieldType)) { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy/mm/dd")); return cellStyle; }/*w ww .j av a 2s .c o m*/ return null; }
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLSX.java
License:Open Source License
/** * create a library of cell styles/* w w w.j a v a 2 s .c om*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); DataFormat df = wb.createDataFormat(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("header_date", style); Font font1 = wb.createFont(); font1.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font1); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBoldweight(Font.BOLDWEIGHT_BOLD); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("dd.MM.yyyy")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(CellStyle.ALIGN_LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }