List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createDrawingPatriarch
@Override
public XSSFDrawing createDrawingPatriarch()
From source file:net.mcnewfamily.rmcnew.writer.AbstractXlsxWriter.java
License:Open Source License
protected void createTextBox(XSSFSheet xssfSheet, XSSFClientAnchor xssfClientAnchor, String text) { if (xssfSheet != null) { XSSFDrawing xssfDrawing = xssfSheet.createDrawingPatriarch(); XSSFTextBox xssfTextBox = xssfDrawing.createTextbox(xssfClientAnchor); xssfTextBox.setText(new XSSFRichTextString(text)); } else {/*from ww w .j av a2 s.co m*/ throw new IllegalArgumentException("Cannot create drawing on null or empty sheet!"); } }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createEventSheets(final XSSFWorkbook workbook) { // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need CreationHelper helper = workbook.getCreationHelper(); final XSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.RIGHT); final XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold);/*from w w w.j a v a2s . com*/ final XSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(helper.createDataFormat().getFormat("HH:mm:ss")); // Calculate a good window size ResultService resultService = getResultService(); EventRecord firstResult = resultService.getFirstResult(); EventRecord lastResult = resultService.getLastResult(); if (firstResult == null || lastResult == null) { return; } long start = firstResult.getStartTime(); long end = lastResult.getStartTime(); long windowSize = AbstractEventReporter.getWindowSize(start, end, 100); // Well-known window sizes // Keep track of sheets by event name. Note that XLSX truncates sheets to 31 chars, so use 28 chars and ~01, ~02 final Map<String, String> sheetNames = new HashMap<String, String>(31); final Map<String, XSSFSheet> sheets = new HashMap<String, XSSFSheet>(31); final Map<String, AtomicInteger> rowNums = new HashMap<String, AtomicInteger>(31); ResultHandler handler = new ResultHandler() { @Override public boolean processResult(long fromTime, long toTime, Map<String, DescriptiveStatistics> statsByEventName, Map<String, Integer> failuresByEventName) throws Throwable { // Get or create a sheet for each event for (String eventName : statsByEventName.keySet()) { // What sheet name to we use? String sheetName = sheetNames.get(eventName); if (sheetName == null) { sheetName = eventName; if (eventName.length() > 28) { int counter = 1; // Find a sheet name not in use while (true) { sheetName = eventName.substring(0, 28); sheetName = String.format("%s~%02d", sheetName, counter); // Have we used this, yet? if (sheets.containsKey(sheetName)) { // Yes, we have used it. counter++; continue; } // This is unique break; } } sheetNames.put(eventName, sheetName); } // Get and create the sheet, if necessary XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { // Create try { sheet = workbook.createSheet(sheetName); sheets.put(sheetName, sheet); sheet.getHeader().setCenter(title + " - " + eventName); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setLandscape(true); } catch (Exception e) { logger.error("Unable to create workbook sheet for event: " + eventName, e); continue; } // Intro XSSFCell cell = sheet.createRow(0).createCell(0); cell.setCellValue(title + " - " + eventName + ":"); cell.setCellStyle(headerStyle); // Headings XSSFRow row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue("time"); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue("mean"); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue("min"); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue("max"); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue("stdDev"); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue("num"); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue("numPerSec"); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue("fail"); cell = row.createCell(8); cell.setCellStyle(headerStyle); cell.setCellValue("failPerSec"); // Size the columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); } AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { rowNum = new AtomicInteger(2); rowNums.put(eventName, rowNum); } DescriptiveStatistics stats = statsByEventName.get(eventName); Integer failures = failuresByEventName.get(eventName); double numPerSec = (double) stats.getN() / ((double) (toTime - fromTime) / 1000.0); double failuresPerSec = (double) failures / ((double) (toTime - fromTime) / 1000.0); XSSFRow row = sheet.createRow(rowNum.getAndIncrement()); XSSFCell cell; cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(dateStyle); cell.setCellValue(new Date(toTime)); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getN()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numPerSec); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failures); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failuresPerSec); // Leave out values if there is no mean if (Double.isNaN(stats.getMean())) { continue; } cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMean()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMin()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMax()); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getStandardDeviation()); } return true; } }; resultService.getResults(handler, start, windowSize, windowSize, false); // Create charts in the sheets for (String eventName : sheetNames.keySet()) { // Get the sheet name String sheetName = sheetNames.get(eventName); if (sheetName == null) { logger.error("Did not find sheet for event: " + eventName); continue; } // Get the sheet XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { logger.error("Did not find sheet for name: " + sheetName); continue; } // What row did we get up to AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { logger.error("Did not find row number for event: " + sheetName); continue; } // This axis is common to both charts ChartDataSource<Number> xTime = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 0, 0)); // Graph of event times XSSFDrawing drawingTimes = sheet.createDrawingPatriarch(); ClientAnchor anchorTimes = drawingTimes.createAnchor(0, 0, 0, 0, 0, 5, 15, 25); Chart chartTimes = drawingTimes.createChart(anchorTimes); ChartLegend legendTimes = chartTimes.getOrCreateLegend(); legendTimes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataTimes = chartTimes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisTimes = chartTimes.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); bottomAxisTimes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisTimes = chartTimes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Mean ChartDataSource<Number> yMean = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 1, 1)); LineChartSeries yMeanSerie = chartDataTimes.addSeries(xTime, yMean); yMeanSerie.setTitle(title + " - " + eventName + ": Mean (ms)"); // Std Dev ChartDataSource<Number> yStdDev = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 4, 4)); LineChartSeries yStdDevSerie = chartDataTimes.addSeries(xTime, yStdDev); yStdDevSerie.setTitle(title + " - " + eventName + ": Standard Deviation (ms)"); // Plot event times chartTimes.plot(chartDataTimes, bottomAxisTimes, leftAxisTimes); // Graph of event volumes // Graph of event times XSSFDrawing drawingVolumes = sheet.createDrawingPatriarch(); ClientAnchor anchorVolumes = drawingVolumes.createAnchor(0, 0, 0, 0, 0, 25, 15, 35); Chart chartVolumes = drawingVolumes.createChart(anchorVolumes); ChartLegend legendVolumes = chartVolumes.getOrCreateLegend(); legendVolumes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataVolumes = chartVolumes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisVolumes = chartVolumes.getChartAxisFactory() .createCategoryAxis(AxisPosition.BOTTOM); bottomAxisVolumes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisVolumes = chartVolumes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Number per second ChartDataSource<Number> yNumPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 6, 6)); LineChartSeries yNumPerSecSerie = chartDataVolumes.addSeries(xTime, yNumPerSec); yNumPerSecSerie.setTitle(title + " - " + eventName + ": Events per Second"); // Failures per second ChartDataSource<Number> yFailPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 8, 8)); LineChartSeries yFailPerSecSerie = chartDataVolumes.addSeries(xTime, yFailPerSec); yFailPerSecSerie.setTitle(title + " - " + eventName + ": Failures per Second"); // Plot volumes chartVolumes.plot(chartDataVolumes, bottomAxisVolumes, leftAxisVolumes); } }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();/* w ww . j av a 2 s .c o m*/ report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }
From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;/* w w w . jav a 2s .c o m*/ //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }
From source file:org.structr.excel.ToExcelFunction.java
License:Open Source License
public Workbook writeExcel(final List list, final String propertyView, final List<String> properties, final boolean includeHeader, final boolean localizeHeader, final String headerLocalizationDomain, final Locale locale, final Integer maxCellLength, final String overflowMode) throws IOException { final Workbook workbook = new XSSFWorkbook(); final CreationHelper factory = workbook.getCreationHelper(); final XSSFSheet sheet = (XSSFSheet) workbook.createSheet(); final Drawing drawing = sheet.createDrawingPatriarch(); int rowCount = 0; int cellCount = 0; XSSFRow currentRow = null;//from w w w.ja va 2 s . c o m XSSFCell cell = null; if (includeHeader) { currentRow = (XSSFRow) sheet.createRow(rowCount++); cellCount = 0; if (propertyView != null) { final Object obj = list.get(0); if (obj instanceof GraphObject) { for (PropertyKey key : ((GraphObject) obj).getPropertyKeys(propertyView)) { cell = (XSSFCell) currentRow.createCell(cellCount++); String value = key.dbName(); if (localizeHeader) { try { value = LocalizeFunction.getLocalization(locale, value, headerLocalizationDomain); } catch (FrameworkException fex) { logger.warn("to_excel(): Exception", fex); } } cell.setCellValue(value); } } else { cell = (XSSFCell) currentRow.createCell(cellCount++); cell.setCellValue( "Error: Object is not of type GraphObject, can not determine properties of view for header row"); } } else if (properties != null) { for (final String colName : properties) { cell = (XSSFCell) currentRow.createCell(cellCount++); String value = colName; if (localizeHeader) { try { value = LocalizeFunction.getLocalization(locale, value, headerLocalizationDomain); } catch (FrameworkException fex) { logger.warn("to_excel(): Exception", fex); } } cell.setCellValue(value); } } } for (final Object obj : list) { currentRow = (XSSFRow) sheet.createRow(rowCount++); cellCount = 0; if (propertyView != null) { if (obj instanceof GraphObject) { for (PropertyKey key : ((GraphObject) obj).getPropertyKeys(propertyView)) { final Object value = ((GraphObject) obj).getProperty(key); cell = (XSSFCell) currentRow.createCell(cellCount++); writeToCell(factory, drawing, cell, value, maxCellLength, overflowMode); } } else { cell = (XSSFCell) currentRow.createCell(cellCount++); cell.setCellValue( "Error: Object is not of type GraphObject, can not determine properties of object"); } } else if (properties != null) { if (obj instanceof GraphObject) { final GraphObject castedObj = (GraphObject) obj; for (final String colName : properties) { final PropertyKey key = StructrApp.key(obj.getClass(), colName); final Object value = castedObj.getProperty(key); cell = (XSSFCell) currentRow.createCell(cellCount++); writeToCell(factory, drawing, cell, value, maxCellLength, overflowMode); } } else if (obj instanceof Map) { final Map castedObj = (Map) obj; for (final String colName : properties) { final Object value = castedObj.get(colName); cell = (XSSFCell) currentRow.createCell(cellCount++); writeToCell(factory, drawing, cell, value, maxCellLength, overflowMode); } } } } return workbook; }
From source file:org.tiefaces.components.websheet.chart.ChartHelper.java
License:MIT License
/** * initial chart map for XSSF format file. XSSF file is actually the only * format in POI support chart object.// ww w. j a va2 s . c o m * * @param wb * xssf workbook. * @param chartsData * the charts data */ private void initXSSFChartsMap(final XSSFWorkbook wb, final ChartsData chartsData) { initAnchorsMap(wb, chartsData); Map<String, ClientAnchor> anchorMap = chartsData.getChartAnchorsMap(); Map<String, BufferedImage> chartMap = chartsData.getChartsMap(); Map<String, ChartData> chartDataMap = chartsData.getChartDataMap(); chartMap.clear(); chartDataMap.clear(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { XSSFSheet sheet = wb.getSheetAt(i); XSSFDrawing drawing = sheet.createDrawingPatriarch(); List<XSSFChart> charts = drawing.getCharts(); if ((charts != null) && (!charts.isEmpty())) { for (XSSFChart chart : charts) { generateSingleXSSFChart(chart, getChartIdFromParent(chart, sheet.getSheetName()), sheet, anchorMap, chartMap, chartDataMap); } } } }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * clone existing comments into new cell comment. * /* www . j a va 2 s . c om*/ * @param sourceCell * source cell. * @param newCell * target cell. */ public static void cloneComment(final Cell sourceCell, final Cell newCell) { XSSFSheet sheet = (XSSFSheet) newCell.getSheet(); CreationHelper factory = sheet.getWorkbook().getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); XSSFComment sourceComment = (XSSFComment) sourceCell.getCellComment(); // Below code are from POI busy manual. // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = createCommentAnchor(newCell, factory); // Create the comment and set the text+author Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(sourceComment.getString().toString()); comment.setString(str); comment.setAuthor(sourceComment.getAuthor()); // Assign the comment to the cell newCell.setCellComment(comment); comment.setColumn(newCell.getColumnIndex()); comment.setRow(newCell.getRowIndex()); // As POI doesn't has well support for comments, // So we have to use low level api to match the comments. matchCommentSettings(newCell, sourceCell); }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * Creates the or insert comment./*from w ww . j a v a 2s .c om*/ * * @param cell the cell * @param commentStr the comment str */ public static void createOrInsertComment(final Cell cell, final String commentStr) { XSSFSheet sheet = (XSSFSheet) cell.getSheet(); CreationHelper factory = sheet.getWorkbook().getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); Comment comment = cell.getCellComment(); String originStr = ""; if (comment == null) { // Below code are from POI busy manual. // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = createCommentAnchor(cell, factory); // Create the comment and set the text+author comment = drawing.createCellComment(anchor); } else { originStr = comment.getString().getString() + "\n"; } originStr += commentStr; RichTextString str = factory.createRichTextString(originStr); comment.setString(str); comment.setAuthor(""); // Assign the comment to the cell cell.setCellComment(comment); comment.setColumn(cell.getColumnIndex()); comment.setRow(cell.getRowIndex()); }
From source file:org.tiefaces.components.websheet.utility.ChartUtility.java
License:MIT License
/** * Inits the XSSF anchors map for sheet. * * @param anchortMap//from w ww . ja v a 2s . c o m * the anchort map * @param positionMap * the position map * @param sheet * the sheet */ private static void initXSSFAnchorsMapForSheet(final Map<String, ClientAnchor> anchortMap, final Map<String, String> positionMap, final XSSFSheet sheet) { XSSFDrawing drawing = sheet.createDrawingPatriarch(); CTDrawing ctDrawing = drawing.getCTDrawing(); if (ctDrawing.sizeOfTwoCellAnchorArray() <= 0) { return; } List<CTTwoCellAnchor> alist = ctDrawing.getTwoCellAnchorList(); for (int j = 0; j < alist.size(); j++) { CTTwoCellAnchor ctanchor = alist.get(j); String singleChartId = getAnchorAssociateChartId(ctanchor); if (singleChartId != null) { String chartId = sheet.getSheetName() + "!" + singleChartId; int dx1 = (int) ctanchor.getFrom().getColOff(); int dy1 = (int) ctanchor.getFrom().getRowOff(); int dx2 = (int) ctanchor.getTo().getColOff(); int dy2 = (int) ctanchor.getTo().getRowOff(); int col1 = ctanchor.getFrom().getCol(); int row1 = ctanchor.getFrom().getRow(); int col2 = ctanchor.getTo().getCol(); int row2 = ctanchor.getTo().getRow(); anchortMap.put(chartId, new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)); positionMap.put(WebSheetUtility.getFullCellRefName(sheet.getSheetName(), row1, col1), chartId); } } }
From source file:org.wise.vle.web.VLEGetXLS.java
License:Open Source License
/** * Set the comment for a cell/*ww w . j av a 2 s .co m*/ * @param sheet the excel sheet * @param row the excel row * @param column the column index * @param comment the comment string */ private void setCellComment(XSSFSheet sheet, Row row, int column, String comment) { if (row != null) { //get the cell Cell cell = row.getCell(column); if (cell != null) { if (sheet != null) { //make the comment XSSFComment cellComment = sheet.createDrawingPatriarch() .createCellComment(new XSSFClientAnchor()); cellComment.setString(comment); //add the comment to the cell cell.setCellComment(cellComment); } } } }