List of usage examples for org.apache.poi.ss.usermodel Workbook getCreationHelper
CreationHelper getCreationHelper();
From source file:de.fme.alfresco.repo.web.scripts.datalist.DataListDownloadWebScript.java
License:Open Source License
@SuppressWarnings("deprecation") @Override//from w w w.jav a 2s. c om 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.fme.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java
License:Open Source License
/** * Generates the spreadsheet, based on the properties in the header * and a callback for the body.//w w w . ja v a2 s. co m */ public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status, Map<String, Object> model) throws IOException { Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)"); // Build up the details of the header List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req); String[] headings = new String[propertyDetails.size()]; String[] descriptions = new String[propertyDetails.size()]; boolean[] required = new boolean[propertyDetails.size()]; for (int i = 0; i < headings.length; i++) { Pair<QName, Boolean> property = propertyDetails.get(i); if (property == null || property.getFirst() == null) { headings[i] = ""; required[i] = false; } else { QName column = property.getFirst(); required[i] = property.getSecond(); // Ask the dictionary service nicely for the details PropertyDefinition pd = dictionaryService.getProperty(column); if (pd != null && pd.getTitle() != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(); descriptions[i] = pd.getDescription(); } else { // Nothing friendly found, try to munge the raw qname into // something we can show to a user... String raw = column.getLocalName(); raw = raw.substring(0, 1).toUpperCase() + raw.substring(1); Matcher m = qnameMunger.matcher(raw); if (m.matches()) { headings[i] = m.group(1) + " " + m.group(2); } else { headings[i] = raw; } } } } // Build a list of just the properties List<QName> properties = new ArrayList<QName>(propertyDetails.size()); for (Pair<QName, Boolean> p : propertyDetails) { QName qn = null; if (p != null) { qn = p.getFirst(); } properties.add(qn); } // Output if ("csv".equals(format)) { StringWriter sw = new StringWriter(); CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY); csv.println(headings); populateBody(resource, csv, properties); model.put(MODEL_CSV, sw.toString()); } else { Workbook wb; if ("xlsx".equals(format)) { wb = new XSSFWorkbook(); // TODO Properties } else { wb = new HSSFWorkbook(); // TODO Properties } // Add our header row Sheet sheet = wb.createSheet("Export"); Row hr = sheet.createRow(0); try { sheet.createFreezePane(0, 1); } catch (IndexOutOfBoundsException e) { //https://issues.apache.org/bugzilla/show_bug.cgi?id=51431 & http://stackoverflow.com/questions/6469693/apache-poi-clearing-freeze-split-panes } Font fb = wb.createFont(); fb.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fi = wb.createFont(); fi.setBoldweight(Font.BOLDWEIGHT_BOLD); fi.setItalic(true); CellStyle csReq = wb.createCellStyle(); csReq.setFont(fb); CellStyle csOpt = wb.createCellStyle(); csOpt.setFont(fi); // Populate the header Drawing draw = null; for (int i = 0; i < headings.length; i++) { Cell c = hr.createCell(i); c.setCellValue(headings[i]); if (required[i]) { c.setCellStyle(csReq); } else { c.setCellStyle(csOpt); } if (headings[i].length() == 0) { sheet.setColumnWidth(i, 3 * 250); } else { sheet.setColumnWidth(i, 18 * 250); } if (descriptions[i] != null && descriptions[i].length() > 0) { // Add a description for it too if (draw == null) { draw = sheet.createDrawingPatriarch(); } ClientAnchor ca = wb.getCreationHelper().createClientAnchor(); ca.setCol1(c.getColumnIndex()); ca.setCol2(c.getColumnIndex() + 1); ca.setRow1(hr.getRowNum()); ca.setRow2(hr.getRowNum() + 2); Comment cmt = draw.createCellComment(ca); cmt.setAuthor(""); cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i])); cmt.setVisible(false); c.setCellComment(cmt); } } // Have the contents populated populateBody(resource, wb, sheet, properties); // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); model.put(MODEL_EXCEL, baos.toByteArray()); } }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.DataExportIntroSheetGenerator.java
License:Open Source License
/** * Creates a section of the summary, containing links to one of the types of sheets * /* ww w . ja v a 2 s.c o m*/ * @param sheetContexts list of sheets to link to. Only contains one type of {@link NamedExpression} * @param startAt the row to start at in the introduction sheet * @param sectionHeader the header of the section * * @return the row number that was reached while inserting the links */ private int createSummarySection(List<SheetContext> sheetContexts, int startAt, String sectionHeader) { int rowNr = startAt; Map<IteraExcelStyle, CellStyle> styles = wbContext.getStyles(); Workbook workbook = wbContext.getWb(); CreationHelper createHelper = workbook.getCreationHelper(); // header Sheet introSheet = getIntroductionSheet(); Cell headerCell = introSheet.createRow(rowNr++).createCell(SUMMARY_COL); headerCell.setCellValue(sectionHeader); headerCell.setCellStyle(styles.get(IteraExcelStyle.HEADER)); headerCell.getRow().createCell(SUMMARY_COL + 1).setCellStyle(styles.get(IteraExcelStyle.HEADER)); for (SheetContext sheetContext : sheetContexts) { String sheetName = sheetContext.getSheetName(); String extraInfo = sheetContext.getExpression().getName(); // name is empty, we assume it's a relationship, we need to get the name of the relationship ends if (StringUtils.isEmpty(extraInfo)) { NamedExpression expression = sheetContext.getExpression(); if (expression instanceof RelationshipExpression) { extraInfo = createRelationshipExtrainfo((RelationshipExpression) expression); } } Row entryRow = introSheet.createRow(rowNr++); Cell hyperlinkCell = entryRow.createCell(SUMMARY_COL); hyperlinkCell.setCellValue(sheetName); entryRow.createCell(SUMMARY_COL + 1).setCellValue(extraInfo); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link.setAddress("'" + sheetName + "'!A1"); hyperlinkCell.setHyperlink(link); hyperlinkCell.setCellStyle(styles.get(IteraExcelStyle.HYPERLINK)); } //spacing between sections introSheet.createRow(rowNr++); return rowNr; }
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:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
@Override protected List<Event> parseEvents(Workbook workBook) throws IllegalStateException { evaluator = workBook.getCreationHelper().createFormulaEvaluator(); List<Event> events = new CopyOnWriteArrayList<Event>(); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { Sheet sheet = workBook.getSheetAt(i); if (isValidResultSheet(sheet)) { log.debug("Parsing: {}", sheet.getSheetName()); Row firstRow = sheet.getRow(4); // first row with event results if (firstRow != null) { Cell cell = firstRow.getCell(1); // first cell with event results if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // only parse sheet with content Event event = parseEventDetails(sheet); event.setLive(workBook.getActiveSheetIndex() == i); List<Result> results = new CopyOnWriteArrayList<Result>(); for (int j = 4; j < sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row != null) { Result result = parseResultRow(row, event); if (result.getFirstname() != null && result.getSurname() != null) { results.add(result); }/*from ww w. j a v a 2 s. co m*/ } } if (!results.isEmpty()) { event.setResults(results); events.add(event); } } } } } return events; }
From source file:Documentos.ClaseAlmacenGeneral.java
private static void creandoCelda(Workbook wb, Row row, int column, String strContenido) { CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column);//from ww w . ja v a 2 s . c o m cell.setCellValue(ch.createRichTextString(strContenido)); }
From source file:Documentos.ClaseAlmacenGeneral.java
private static void createCell(Workbook wb, Row row, int column, short halign, short valign, String strContenido, boolean booBorde, boolean booCabecera) { CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column);/*from w w w .j a v a 2s.c o m*/ cell.setCellValue(ch.createRichTextString(strContenido)); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); if (booBorde) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOTTED); cellStyle.setBottomBorderColor((short) 8); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_DOTTED); cellStyle.setLeftBorderColor((short) 8); cellStyle.setBorderRight(HSSFCellStyle.BORDER_DOTTED); cellStyle.setRightBorderColor((short) 8); cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOTTED); cellStyle.setTopBorderColor((short) 8); } if (booCabecera) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setBottomBorderColor((short) 8); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setLeftBorderColor((short) 8); cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setRightBorderColor((short) 8); cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); cellStyle.setTopBorderColor((short) 8); cellStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } cell.setCellStyle(cellStyle); }
From source file:eu.esdihumboldt.hale.app.bgis.ade.common.AbstractAnalyseTable.java
License:Open Source License
/** * Load table to analyse from an Excel file. * //ww w . j ava2 s . c o m * @param location the file location * @throws Exception if an error occurs loading the file */ protected void analyse(URI location) throws Exception { InputStream inp = new BufferedInputStream(location.toURL().openStream()); try { Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); evaluator = wb.getCreationHelper().createFormulaEvaluator(); // the first row represents the header analyseHeader(sheet); // load configuration entries analyseContent(sheet); } finally { // reset evaluator reference evaluator = null; // unclear whether the POI API closes the stream inp.close(); } }
From source file:eu.esdihumboldt.hale.io.xls.AbstractAnalyseTable.java
License:Open Source License
/** * Load table to analyse from an Excel file. * // ww w . ja v a 2 s.c o m * @param location the file location * @param sheetNum number of the sheet that should be loaded (0-based) * @throws Exception if an error occurs loading the file */ protected void analyse(URI location, int sheetNum) throws Exception { InputStream inp = new BufferedInputStream(location.toURL().openStream()); try { Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(sheetNum); evaluator = wb.getCreationHelper().createFormulaEvaluator(); // the first row represents the header analyseHeader(sheet); // load configuration entries analyseContent(sheet); } finally { // reset evaluator reference evaluator = null; // unclear whether the POI API closes the stream inp.close(); } }
From source file:eu.learnpad.ontology.kpi.data.ExcelParser.java
public List<List<String>> getDataTable() throws IOException, InvalidFormatException { List<List<String>> dataTable = new ArrayList<>(); Integer rowNumber = -2;/* w ww . j a va 2 s . co m*/ Workbook wb = WorkbookFactory.create(excelFile); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (Sheet sheet : wb) { if (sheet.getSheetName().equals(SHEETNAME)) { for (Row row : sheet) { //stop with the first empty row if (row.getCell(0) == null) { break; } if (rowNumber >= -1) { rowNumber++; dataTable.add(new ArrayList<String>()); } for (Cell cell : row) { String sheetName = sheet.getSheetName(); String cellRow = "Row:" + cell.getRowIndex(); String cellColumn = "Column:" + cell.getColumnIndex(); Object[] o = new Object[] { sheetName, cellRow, cellColumn }; LOGGER.log(Level.INFO, "Processing: Sheet={0} celladress={1}", o); if (rowNumber <= -1 && cell.getCellType() == Cell.CELL_TYPE_BLANK) { continue; } if (rowNumber == -2 && cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getRichStringCellValue().getString().equals(DATACELLNAME)) { rowNumber = -1; continue; } } //Attributes (column headers) if (rowNumber == 0) { dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString()); } if (rowNumber >= 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { dataTable.get(rowNumber).add(cell.getDateCellValue().toString()); } else { dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue())); break; case Cell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_STRING: dataTable.get(rowNumber).add(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { dataTable.get(rowNumber).add(cell.getDateCellValue().toString()); } else { dataTable.get(rowNumber).add(Double.toString(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: dataTable.get(rowNumber).add(Boolean.toString(cell.getBooleanCellValue())); break; default: dataTable.get(rowNumber).add(""); } break; default: dataTable.get(rowNumber).add(""); } } } } } } return dataTable; }