List of usage examples for org.apache.poi.ss.usermodel Workbook createCellStyle
CellStyle createCellStyle();
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * /*from w w w . j ava2s.c o m*/ * method name : createStyles * @param wb * @return * TeachingSurveyExcelImpl * return type : Map<String,CellStyle> * * purpose : Creating Styles for Excell sheet cells * * Date : Mar 16, 2016 1:25:00 PM */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; /*** TITLE ***/ Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put(TITLE, style); /*** SUB-HEADER ***/ Font subHeaderFont = wb.createFont(); subHeaderFont.setFontHeightInPoints((short) 9); subHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style = wb.createCellStyle(); style.setFont(subHeaderFont); style.setWrapText(true); styles.put(SUB_HEADER, style); /*** MAX. TWO DIGIT DECIMAL VALUE ***/ style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put(FORMULA_1, style); return styles; }
From source file:onl.netfishers.netshot.RestService.java
License:Open Source License
@GET @Path("reports/export") @RolesAllowed("readonly") @Produces({ "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }) public Response getDataXLSX(@Context HttpServletRequest request, @DefaultValue("-1") @QueryParam("group") long group, @DefaultValue("false") @QueryParam("interfaces") boolean exportInterfaces, @DefaultValue("false") @QueryParam("inventory") boolean exportInventory, @DefaultValue("xlsx") @QueryParam("format") String fileFormat) throws WebApplicationException { logger.debug("REST request, export data."); User user = (User) request.getSession().getAttribute("user"); if (fileFormat.compareToIgnoreCase("xlsx") == 0) { String fileName = String.format("netshot-export_%s.xlsx", (new SimpleDateFormat("yyyyMMdd-HHmmss")).format(new Date())); Session session = Database.getSession(); try {/*from w ww . ja v a 2 s . co m*/ Workbook workBook = new XSSFWorkbook(); Row row; Cell cell; CreationHelper createHelper = workBook.getCreationHelper(); CellStyle datetimeCellStyle = workBook.createCellStyle(); datetimeCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm")); CellStyle dateCellStyle = workBook.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd")); Sheet summarySheet = workBook.createSheet("Summary"); row = summarySheet.createRow(0); row.createCell(0).setCellValue("Netshot version"); row.createCell(1).setCellValue(Netshot.VERSION); row = summarySheet.createRow(1); row.createCell(0).setCellValue("Exported by"); row.createCell(1).setCellValue(user.getName()); row = summarySheet.createRow(2); row.createCell(0).setCellValue("Date and time"); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(datetimeCellStyle); row = summarySheet.createRow(4); row.createCell(0).setCellValue("Selected Group"); Query query; if (group == -1) { query = session.createQuery("select d from Device d"); row.createCell(1).setCellValue("None"); } else { query = session.createQuery("select d from Device d join d.ownerGroups g where g.id = :id") .setLong("id", group); DeviceGroup deviceGroup = (DeviceGroup) session.get(DeviceGroup.class, group); row.createCell(1).setCellValue(deviceGroup.getName()); } Sheet deviceSheet = workBook.createSheet("Devices"); row = deviceSheet.createRow(0); row.createCell(0).setCellValue("ID"); row.createCell(1).setCellValue("Name"); row.createCell(2).setCellValue("Management IP"); row.createCell(3).setCellValue("Domain"); row.createCell(4).setCellValue("Network Class"); row.createCell(5).setCellValue("Family"); row.createCell(6).setCellValue("Creation"); row.createCell(7).setCellValue("Last Change"); row.createCell(8).setCellValue("Software"); row.createCell(9).setCellValue("End of Sale Date"); row.createCell(10).setCellValue("End Of Life Date"); int yDevice = 1; @SuppressWarnings("unchecked") List<Device> devices = query.list(); for (Device device : devices) { row = deviceSheet.createRow(yDevice++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(device.getName()); row.createCell(2).setCellValue(device.getMgmtAddress().getIp()); row.createCell(3).setCellValue(device.getMgmtDomain().getName()); row.createCell(4).setCellValue(device.getNetworkClass().toString()); row.createCell(5).setCellValue(device.getFamily()); cell = row.createCell(6); cell.setCellValue(device.getCreatedDate()); cell.setCellStyle(datetimeCellStyle); cell = row.createCell(7); cell.setCellValue(device.getChangeDate()); cell.setCellStyle(datetimeCellStyle); row.createCell(8).setCellValue(device.getSoftwareVersion()); if (device.getEosDate() != null) { cell = row.createCell(9); cell.setCellValue(device.getEosDate()); cell.setCellStyle(dateCellStyle); } if (device.getEolDate() != null) { cell = row.createCell(10); cell.setCellValue(device.getEolDate()); cell.setCellStyle(dateCellStyle); } } if (exportInterfaces) { Sheet interfaceSheet = workBook.createSheet("Interfaces"); row = interfaceSheet.createRow(0); row.createCell(0).setCellValue("Device ID"); row.createCell(1).setCellValue("Virtual Device"); row.createCell(2).setCellValue("Name"); row.createCell(3).setCellValue("Description"); row.createCell(4).setCellValue("VRF"); row.createCell(5).setCellValue("MAC Address"); row.createCell(6).setCellValue("Enabled"); row.createCell(7).setCellValue("Level 3"); row.createCell(8).setCellValue("IP Address"); row.createCell(9).setCellValue("Mask Length"); row.createCell(10).setCellValue("Usage"); int yInterface = 1; for (Device device : devices) { for (NetworkInterface networkInterface : device.getNetworkInterfaces()) { if (networkInterface.getIpAddresses().size() == 0) { row = interfaceSheet.createRow(yInterface++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(networkInterface.getVirtualDevice()); row.createCell(2).setCellValue(networkInterface.getInterfaceName()); row.createCell(3).setCellValue(networkInterface.getDescription()); row.createCell(4).setCellValue(networkInterface.getVrfInstance()); row.createCell(5).setCellValue(networkInterface.getMacAddress()); row.createCell(6).setCellValue(networkInterface.isEnabled()); row.createCell(7).setCellValue(networkInterface.isLevel3()); row.createCell(8).setCellValue(""); row.createCell(9).setCellValue(""); row.createCell(10).setCellValue(""); } for (NetworkAddress address : networkInterface.getIpAddresses()) { row = interfaceSheet.createRow(yInterface++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(networkInterface.getVirtualDevice()); row.createCell(2).setCellValue(networkInterface.getInterfaceName()); row.createCell(3).setCellValue(networkInterface.getDescription()); row.createCell(4).setCellValue(networkInterface.getVrfInstance()); row.createCell(5).setCellValue(networkInterface.getMacAddress()); row.createCell(6).setCellValue(networkInterface.isEnabled()); row.createCell(7).setCellValue(networkInterface.isLevel3()); row.createCell(8).setCellValue(address.getIp()); row.createCell(9).setCellValue(address.getPrefixLength()); row.createCell(10).setCellValue(address.getAddressUsage() == null ? "" : address.getAddressUsage().toString()); } } } } if (exportInventory) { Sheet inventorySheet = workBook.createSheet("Inventory"); row = inventorySheet.createRow(0); row.createCell(0).setCellValue("Device ID"); row.createCell(1).setCellValue("Slot"); row.createCell(2).setCellValue("Part Number"); row.createCell(3).setCellValue("Serial Number"); int yInventory = 1; for (Device device : devices) { for (Module module : device.getModules()) { row = inventorySheet.createRow(yInventory++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(module.getSlot()); row.createCell(2).setCellValue(module.getPartNumber()); row.createCell(3).setCellValue(module.getSerialNumber()); } } } ByteArrayOutputStream output = new ByteArrayOutputStream(); workBook.write(output); workBook.close(); return Response.ok(output.toByteArray()) .header("Content-Disposition", "attachment; filename=" + fileName).build(); } catch (IOException e) { logger.error("Unable to write the resulting file.", e); throw new WebApplicationException("Unable to write the resulting file.", javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR); } catch (Exception e) { logger.error("Unable to generate the report.", e); throw new WebApplicationException("Unable to generate the report.", javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR); } finally { session.close(); } } logger.warn("Invalid requested file format."); throw new WebApplicationException("The requested file format is invalid or not supported.", javax.ws.rs.core.Response.Status.BAD_REQUEST); }
From source file:opn.greenwebs.HyperlinkExample.java
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); //cell style for hyperlinks //by default hyperlinks are blue and underlined CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font);// ww w . j av a 2 s .c om Cell cell; Sheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell((short) 0); cell.setCellValue("URL Link"); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a file in the current directory cell = sheet.createRow(1).createCell((short) 0); cell.setCellValue("File Link"); link = createHelper.createHyperlink(Hyperlink.LINK_FILE); link.setAddress("link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //e-mail link cell = sheet.createRow(2).createCell((short) 0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a place in this workbook //create a target sheet and cell Sheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell((short) 0); cell.setCellValue("Worksheet Link"); Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link2.setAddress("'Target Sheet'!A1"); cell.setHyperlink(link2); cell.setCellStyle(hlink_style); FileOutputStream out = new FileOutputStream("hyperinks.xlsx"); wb.write(out); out.close(); }
From source file:org.activityinfo.server.report.renderer.excel.ExcelPivotTableRenderer.java
License:Open Source License
@Override public void render(Workbook book, PivotTableReportElement element) { /* Generate the actual pivot table data */ final PivotTableData table = element.getContent().getData(); /* Generate the excel sheet */ new BaseExcelTableRenderer<PivotTableReportElement, PivotTableData.Axis>(book, element) { @Override// ww w . j a v a 2 s .c om public List<FilterDescription> generateFilterDescriptions() { return element.getContent().getFilterDescriptions(); } private CellStyle[] rowHeaderStyles; @Override public void generate() { /* Initialize Cell Styles */ initColHeaderStyles(table.getRootColumn()); initRowHeaderStyles(); /* Generate the column headers */ generateColumnHeaders(1, table.getRootColumn()); int headerHeight = rowIndex; /* Create the rows */ // row headers are in column 1 sheet.setColumnWidth(0, 40 * 256); generateRows(table.getRootRow().getChildList(), 0); /* Finalize the sheet */ sheet.setRowSumsBelow(false); sheet.createFreezePane(1, headerHeight); } protected void initRowHeaderStyles() { int depth = table.getRootRow().getDepth(); rowHeaderStyles = new CellStyle[depth]; for (int i = 0; i != depth; ++i) { CellStyle style = book.createCellStyle(); style.setIndention((short) i); style.setWrapText(true); Font font = createBaseFont(); if (i + 1 != depth) { /* Has sub headers */ font.setBoldweight(Font.BOLDWEIGHT_BOLD); } else { font.setBoldweight(Font.BOLDWEIGHT_NORMAL); } style.setFont(font); rowHeaderStyles[i] = style; } } protected void generateRows(List<PivotTableData.Axis> rows, int indent) { for (PivotTableData.Axis pivotRow : rows) { Row row = sheet.createRow(rowIndex++); Cell headerCell = row.createCell(0); headerCell.setCellValue(factory.createRichTextString(pivotRow.getLabel())); headerCell.setCellStyle(rowHeaderStyles[indent]); if (pivotRow.isLeaf()) { for (Entry<PivotTableData.Axis, Integer> entry : colIndexMap.entrySet()) { PivotTableData.Cell pivotCell = pivotRow.getCell(entry.getKey()); if (pivotCell != null) { Cell cell = row.createCell(entry.getValue()); cell.setCellValue(pivotCell.getValue()); } } } else { int groupStart = rowIndex; generateRows(pivotRow.getChildList(), indent + 1); int groupEnd = rowIndex; sheet.groupRow(groupStart, groupEnd); } } } }; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SpreadSheetFormatOptions.java
License:Open Source License
public static CellStyle createCellStyle(Workbook workbook, cfStructData _struct) throws Exception { CellStyle style = workbook.createCellStyle(); if (_struct.containsKey("alignment")) { String v = _struct.getData("alignment").getString(); Short s = lookup_alignment.get(v); if (s == null) { throw new Exception("invalid parameter for 'alignment' (" + v + ")"); } else/* ww w. j av a2 s . c om*/ style.setAlignment(s); } if (_struct.containsKey("bottomborder")) { String v = _struct.getData("bottomborder").getString(); Short s = lookup_border.get(v); if (s == null) { throw new Exception("invalid parameter for 'bottomborder' (" + v + ")"); } else style.setBorderBottom(s); } if (_struct.containsKey("topborder")) { String v = _struct.getData("topborder").getString(); Short s = lookup_border.get(v); if (s == null) { throw new Exception("invalid parameter for 'topborder' (" + v + ")"); } else style.setBorderTop(s); } if (_struct.containsKey("leftborder")) { String v = _struct.getData("leftborder").getString(); Short s = lookup_border.get(v); if (s == null) { throw new Exception("invalid parameter for 'leftborder' (" + v + ")"); } else style.setBorderLeft(s); } if (_struct.containsKey("rightborder")) { String v = _struct.getData("rightborder").getString(); Short s = lookup_border.get(v); if (s == null) { throw new Exception("invalid parameter for 'rightborder' (" + v + ")"); } else style.setBorderRight(s); } if (_struct.containsKey("bottombordercolor")) { String v = _struct.getData("bottombordercolor").getString(); Short s = lookup_colors.get(v); if (s == null) { throw new Exception("invalid parameter for 'bottombordercolor' (" + v + ")"); } else style.setBottomBorderColor(s); } if (_struct.containsKey("topbordercolor")) { String v = _struct.getData("topbordercolor").getString(); Short s = lookup_colors.get(v); if (s == null) { throw new Exception("invalid parameter for 'topbordercolor' (" + v + ")"); } else style.setTopBorderColor(s); } if (_struct.containsKey("leftbordercolor")) { String v = _struct.getData("leftbordercolor").getString(); Short s = lookup_colors.get(v); if (s == null) { throw new Exception("invalid parameter for 'leftbordercolor' (" + v + ")"); } else style.setLeftBorderColor(s); } if (_struct.containsKey("rightbordercolor")) { String v = _struct.getData("rightbordercolor").getString(); Short s = lookup_colors.get(v); if (s == null) { throw new Exception("invalid parameter for 'rightbordercolor' (" + v + ")"); } else style.setRightBorderColor(s); } if (_struct.containsKey("fillpattern")) { String v = _struct.getData("fillpattern").getString(); Short s = lookup_fillpatten.get(v); if (s == null) { throw new Exception("invalid parameter for 'fillpattern' (" + v + ")"); } else style.setFillPattern(s); } if (_struct.containsKey("fgcolor")) { String v = _struct.getData("fgcolor").getString(); Short s = lookup_colors.get(v); if (s == null) { throw new Exception("invalid parameter for 'fgcolor' (" + v + ")"); } else style.setFillForegroundColor(s); } if (_struct.containsKey("bgcolor")) { String v = _struct.getData("bgcolor").getString(); Short s = lookup_colors.get(v); if (s == null) { throw new Exception("invalid parameter for 'bgcolor' (" + v + ")"); } else style.setFillBackgroundColor(s); } if (_struct.containsKey("textwrap")) { Boolean b = _struct.getData("textwrap").getBoolean(); style.setWrapText(b); } if (_struct.containsKey("hidden")) { Boolean b = _struct.getData("hidden").getBoolean(); style.setHidden(b); } if (_struct.containsKey("locked")) { Boolean b = _struct.getData("locked").getBoolean(); style.setLocked(b); } if (_struct.containsKey("indent")) { style.setIndention((short) _struct.getData("indent").getInt()); } if (_struct.containsKey("rotation")) { style.setRotation((short) _struct.getData("rotation").getInt()); } if (_struct.containsKey("dateformat")) { style.setDataFormat(workbook.createDataFormat().getFormat(_struct.getData("dateformat").getString())); } // Manage the fonts Font f = workbook.createFont(); if (_struct.containsKey("strikeout")) { f.setStrikeout(true); } if (_struct.containsKey("bold")) { Boolean b = _struct.getData("bold").getBoolean(); f.setBoldweight(b ? Font.BOLDWEIGHT_BOLD : Font.BOLDWEIGHT_NORMAL); } if (_struct.containsKey("underline")) { String v = _struct.getData("underline").getString(); Byte b = lookup_underline.get(v); if (b == null) { throw new Exception("invalid parameter for 'underline' (" + v + ")"); } else f.setUnderline(b); } if (_struct.containsKey("color")) { String v = _struct.getData("color").getString(); Short s = lookup_colors.get(v); if (s == null) { throw new Exception("invalid parameter for 'color' (" + v + ")"); } else f.setColor(s); } if (_struct.containsKey("fontsize")) { int s = _struct.getData("fontsize").getInt(); f.setFontHeightInPoints((short) s); } if (_struct.containsKey("font")) { f.setFontName(_struct.getData("font").getString()); } style.setFont(f); return style; }
From source file:org.alfresco.repo.web.scripts.datalist.DataListDownloadWebScript.java
License:Open Source License
@Override protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties) throws IOException { NodeRef list = (NodeRef) resource;/*from ww w . j a va2 s.c o m*/ List<NodeRef> items = getItems(list); // Our various formats DataFormat formatter = workbook.createDataFormat(); 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); // 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); 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_USERNAME)); } 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_TITLE)); } 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 { // This property isn't set c.setCellType(Cell.CELL_TYPE_BLANK); } } else { // Regular property, set if (val instanceof String) { c.setCellValue((String) val); } 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) { sheet.autoSizeColumn(colNum); colNum++; } }
From source file:org.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.//from ww w .j a va 2 s . com */ 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].*)"); String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER); CSVStrategy reqCSVstrategy = null; if (delimiterParam != null && !delimiterParam.isEmpty()) { reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED); } // 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(dictionaryService) != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(dictionaryService); descriptions[i] = pd.getDescription(dictionaryService); } 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, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy()); 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); sheet.createFreezePane(0, 1); 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:org.apache.metamodel.excel.ExcelUpdateCallback.java
License:Apache License
/** * Creates a new cell style in the spreadsheet * //from www.j a va 2 s. c o m * @return */ public CellStyle createCellStyle() { Workbook workbook = getWorkbook(true); return workbook.createCellStyle(); }
From source file:org.centralperf.helper.view.ExcelOOXMLView.java
License:Open Source License
/** * @see AbstractPOIExcelView#buildExcelDocument(Map, Workbook, HttpServletRequest, HttpServletResponse) *//* w w w . ja v a2 s . c o m*/ @Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { log.debug("Generating Excel report from run samples"); // Set the headers response.setHeader("Content-Type", "application/octet-stream"); response.setHeader("Content-Disposition", "attachment; filename=central_perf_result.xlsx"); // get data model which is passed by the Spring container Run run = (Run) model.get("run"); // Set run summary informations setCellValueByName(PROJECT_NAME_CELL_NAME, run.getProject().getName(), workbook); setCellValueByName(RUN_LABEL_CELL_NAME, run.getLabel(), workbook); setCellValueByName(RUN_DESCRIPTION_CELL_NAME, run.getComment(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(GENERATED_ON_CELL_NAME, "" + unixTimestamp2ExcelTimestampconvert(new Date().getTime()), workbook); // Populate data sheet XSSFSheet dataSheet = (XSSFSheet) workbook.getSheet(DATA_SHEET_NAME); // Set date style for first column CellStyle dateStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd")); dataSheet.setDefaultColumnStyle(0, dateStyle); // Add samples for (int i = 0; i < run.getSamples().size(); i++) { Sample sample = run.getSamples().get(i); XSSFRow dataRow = dataSheet.createRow(i + 1); if (sample.getTimestamp() != null) { dataRow.createCell(0) .setCellValue(unixTimestamp2ExcelTimestampconvert(sample.getTimestamp().getTime())); dataRow.createCell(1).setCellValue(sample.getElapsed()); dataRow.createCell(2).setCellValue(sample.getSampleName()); dataRow.createCell(3).setCellValue(sample.getStatus()); dataRow.createCell(4).setCellValue(sample.getReturnCode()); dataRow.createCell(5).setCellValue(sample.getSizeInOctet()); dataRow.createCell(6).setCellValue(sample.getGrpThreads()); dataRow.createCell(7).setCellValue(sample.getAllThreads()); dataRow.createCell(8).setCellValue(sample.getLatency()); } } // Return generated sheet OutputStream outStream = null; try { outStream = response.getOutputStream(); workbook.write(outStream); outStream.flush(); } finally { outStream.close(); } }
From source file:org.cerberus.service.export.ExportServiceFactory.java
License:Open Source License
private void createReportByTagExport(Workbook workbook) { //handles the export of the execution by tag data HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>(); HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>(); List<String> mapCountries = new ArrayList<String>(); List<CellStyle> stylesList = new LinkedList<CellStyle>(); if (exportOptions.contains("chart") || exportOptions.contains("list")) { //then we need to create the default colors for each cell HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); CellStyle okStyle = workbook.createCellStyle(); // get the color which most closely matches the color you want to use // code to get the style for the cell goes here okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex()); okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //okStyle.setFont(); stylesList.add(okStyle);/*from w w w. j a v a2 s .co m*/ } for (TestCaseExecution execution : (List<TestCaseExecution>) list) { //check if the country and application shows if (exportOptions.contains("chart") || exportOptions.contains("summary")) { String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " " + execution.getEnvironment(); SummaryStatisticsDTO stats; String status = execution.getControlStatus(); if (summaryMap.containsKey(keySummaryTable)) { stats = summaryMap.get(keySummaryTable); } else { stats = new SummaryStatisticsDTO(); stats.setApplication(execution.getApplication()); stats.setCountry(execution.getCountry()); stats.setEnvironment(execution.getEnvironment()); } stats.updateStatisticByStatus(status); summaryMap.put(keySummaryTable, stats); //updates the map } if (exportOptions.contains("list")) { if (exportOptions.contains("filter")) { //filter active } else { //all data is saved } HashMap<String, List<TestCaseExecution>> listExecution; List<TestCaseExecution> testCaseList; String testKey = execution.getTest(); String testCaseKey = execution.getTestCase(); if (mapList.containsKey(testKey)) { listExecution = mapList.get(testKey); } else { listExecution = new HashMap<String, List<TestCaseExecution>>(); } if (listExecution.containsKey(testCaseKey)) { testCaseList = listExecution.get(testCaseKey); } else { testCaseList = new ArrayList<TestCaseExecution>(); } testCaseList.add(execution); listExecution.put(testCaseKey, testCaseList); mapList.put(testKey, listExecution); if (mapCountries.indexOf(execution.getCountry()) == -1) { mapCountries.add(execution.getCountry()); } } } int rowCount = -1; //Create a blank sheet Sheet sheet = workbook.createSheet("Report by Tag"); sheet.getPrintSetup().setLandscape(true); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); //ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); sheet.setFitToPage(true); sheet.setColumnWidth(0, 9000); if (exportOptions.contains("chart")) { SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap); Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Report By Status"); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Status"); row.createCell(1).setCellValue("Total"); row.createCell(2).setCellValue("Percentage"); row = sheet.createRow(++rowCount); CellStyle okStyle = stylesList.get(0); Cell cellOk = row.createCell(0); cellOk.setCellValue("OK"); cellOk.setCellStyle(okStyle); row.createCell(1).setCellValue(sumsTotal.getOk()); row.createCell(2).setCellValue(sumsTotal.getPercOk()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("KO"); row.createCell(1).setCellValue(sumsTotal.getKo()); row.createCell(2).setCellValue(sumsTotal.getPercKo()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("FA"); row.createCell(1).setCellValue(sumsTotal.getFa()); row.createCell(2).setCellValue(sumsTotal.getPercFa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NA"); row.createCell(1).setCellValue(sumsTotal.getNa()); row.createCell(2).setCellValue(sumsTotal.getPercNa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NE"); row.createCell(1).setCellValue(sumsTotal.getNe()); row.createCell(2).setCellValue(sumsTotal.getPercNe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("PE"); row.createCell(1).setCellValue(sumsTotal.getPe()); row.createCell(2).setCellValue(sumsTotal.getPercPe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("CA"); row.createCell(1).setCellValue(sumsTotal.getCa()); row.createCell(2).setCellValue(sumsTotal.getPercCa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(sumsTotal.getTotal()); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("summary")) { //draw the table with data Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Summary Table"); //start creating data row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Application"); row.createCell(1).setCellValue("Country"); row.createCell(2).setCellValue("Environment"); row.createCell(3).setCellValue("OK"); row.createCell(4).setCellValue("KO"); row.createCell(5).setCellValue("FA"); row.createCell(6).setCellValue("NA"); row.createCell(7).setCellValue("NE"); row.createCell(8).setCellValue("PE"); row.createCell(9).setCellValue("CA"); row.createCell(10).setCellValue("NOT OK"); row.createCell(11).setCellValue("Total"); /*temporary styles*/ CellStyle styleBlue = workbook.createCellStyle(); CellStyle styleGreen = workbook.createCellStyle(); HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(66, 139, 202); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here styleBlue.setFillForegroundColor(palIndex); styleBlue.setFillPattern(CellStyle.SPARSE_DOTS); HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0); styleGreen.setFillForegroundColor(myColorGreen.getIndex()); styleGreen.setFillPattern(CellStyle.SPARSE_DOTS); int startRow = (rowCount + 2); TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>( summaryMap); for (String key : sortedSummaryMap.keySet()) { row = sheet.createRow(++rowCount); SummaryStatisticsDTO sumStats = summaryMap.get(key); //application row.createCell(0).setCellValue((String) sumStats.getApplication()); //country row.createCell(1).setCellValue((String) sumStats.getCountry()); //environment row.createCell(2).setCellValue((String) sumStats.getEnvironment()); //OK row.createCell(3).setCellValue(sumStats.getOk()); //KO row.createCell(4).setCellValue(sumStats.getKo()); //FA row.createCell(5).setCellValue(sumStats.getFa()); //NA row.createCell(6).setCellValue(sumStats.getNa()); //NE row.createCell(7).setCellValue(sumStats.getNe()); //PE row.createCell(8).setCellValue(sumStats.getPe()); //CA row.createCell(9).setCellValue(sumStats.getCa()); int rowNumber = row.getRowNum() + 1; //NOT OK //row.createCell(11).setCellValue(sumStats.getNotOkTotal()); row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")"); //row.createCell(12).setCellValue(sumStats.getTotal()); if (sumStats.getOk() == sumStats.getTotal()) { for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleGreen); } } } //TODO:FN percentages missing //Total row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(""); row.createCell(2).setCellValue(""); //OK row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")"); //KO row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")"); //FA row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")"); //NA row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")"); //NE row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")"); //PE row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")"); //CA row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")"); int rowNumberTotal = row.getRowNum() + 1; //NOT OK row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")"); for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleBlue); } //add some empty rows sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("list")) { //exports the data from test cases' executions Row r = sheet.createRow(++rowCount); r.createCell(0).setCellValue("Test"); r.createCell(1).setCellValue("Test Case"); r.createCell(2).setCellValue("Description"); r.createCell(3).setCellValue("Application"); r.createCell(4).setCellValue("Environment"); r.createCell(5).setCellValue("Browser"); //creates the country list Collections.sort(mapCountries);//sorts the list of countries int startIndexForCountries = 6; for (String country : mapCountries) { r.createCell(startIndexForCountries).setCellValue(country); startIndexForCountries++; } TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>( mapList); rowCount++; for (String keyMapList : sortedKeys.keySet()) { rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries); } } }