List of usage examples for org.apache.poi.ss.usermodel CreationHelper createRichTextString
RichTextString createRichTextString(String text);
From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java
License:MIT License
/** * build command list from comment.//from w w w . j a v a2s . c o m * * @param sheet * sheet. * @param sheetRightCol * sheet right column. * @param cell * the cell * @param cList * command list. * @param cellAttributesMap * the cell attributes map * @return command list. */ private List<ConfigCommand> buildCommandList(final Sheet sheet, final int sheetRightCol, final Cell cell, final List<ConfigCommand> cList, final CellAttributesMap cellAttributesMap) { Comment comment = cell.getCellComment(); String text = comment.getString().getString(); String[] commentLines = text.split("\\n"); StringBuilder newComment = new StringBuilder(); boolean changed = false; for (String commentLine : commentLines) { String line = commentLine.trim(); if (ParserUtility.isCommandString(line)) { processCommandLine(sheet, cell, line, cList, sheetRightCol); changed = true; } else if (ParserUtility.isEmptyMethodString(line) || ParserUtility.isMethodString(line)) { processMethodLine(cell, line, cellAttributesMap); changed = true; } else { if (newComment.length() > 0) { newComment.append("\\n" + commentLine); } else { newComment.append(commentLine); } } } if (!changed) { moveCommentToMap(cell, text, cellAttributesMap.getTemplateCommentMap(), true); } else { // reset comment string if changed if (newComment.length() > 0) { moveCommentToMap(cell, newComment.toString(), cellAttributesMap.getTemplateCommentMap(), true); CreationHelper factory = sheet.getWorkbook().getCreationHelper(); RichTextString str = factory.createRichTextString(newComment.toString()); comment.setString(str); } else { // remove cell comment if new comment become empty. cell.removeCellComment(); } } return cList; }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * clone existing comments into new cell comment. * //w w w. j av a 2 s .c o m * @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 w w.j a v a 2 s. c o m*/ * * @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:packtest.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet();//from w ww . j a va 2 s. c o m Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); // comment2.setAddress(new CellAddress("C3")); String fname = "comments.xlsx"; FileOutputStream out = new FileOutputStream(fname); wb.write(out); out.close(); wb.close(); }
From source file:packtest.CreateCell.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper creationHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 0); // Create a cell and put a value in it. Cell cell = row.createCell((short) 0); cell.setCellValue(1);/*w w w . j a v a2s . c o m*/ //numeric value row.createCell(1).setCellValue(1.2); //plain string value row.createCell(2).setCellValue("This is a string cell"); //rich text string RichTextString str = creationHelper.createRichTextString("Apache"); Font font = wb.createFont(); font.setItalic(true); font.setUnderline(Font.U_SINGLE); str.applyFont(font); row.createCell(3).setCellValue(str); //boolean value row.createCell(4).setCellValue(true); //formula row.createCell(5).setCellFormula("SUM(A1:B1)"); //date CellStyle style = wb.createCellStyle(); style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm")); cell = row.createCell(6); cell.setCellValue(new Date()); cell.setCellStyle(style); //hyperlink row.createCell(7).setCellFormula("SUM(A1:B1)"); cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("ooxml-cell.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:poi.xssf.usermodel.examples.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet();/*from w w w . j a va2 s . c om*/ Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setColumn(2); comment2.setRow(2); String fname = "comments.xlsx"; FileOutputStream out = new FileOutputStream(fname); wb.write(out); out.close(); }
From source file:qmul.align.AlignmentTester.java
License:Open Source License
/** * Process a single dialogue//www .ja v a 2 s .c o m * * @param d * the dialogue to process * @param wb * the XLS workbook to write to, or null not to bother * @return a list of {@link Double} scores, one per {@link DialogueWindower} step (e.g. dialogue turn) */ public List<Double> processDialogue(Dialogue d, Workbook wb, HashMap<String, ArrayList<Double>> speakerScores, HashMap<String, String> originalSpks, HashMap<String, ArrayList<Double>> speakerN, MetricsMap spkMetrics, MetricsMap totMetrics, Workbook wbcounts, HashMap<String, HashMap<Object, Integer>> allCounts, HashMap<String, HashMap<Object, Integer>> commonCounts, HashMap<Object, Integer> diaAllCounts, HashMap<Object, Integer> diaCommonCounts) { CreationHelper creationHelper = wb.getCreationHelper(); win.setDialogue(d); sim.reset(); ArrayList<DialogueSpeaker> spks = new ArrayList<DialogueSpeaker>(d.getSpeakers()); Collections.sort(spks); Sheet sheet = (wb == null ? null : wb.createSheet(d.getId().replaceAll(":", "-"))); Sheet sheetcounts = (wbcounts == null ? null : wbcounts.createSheet(d.getId().replaceAll(":", "-"))); int iRow = 0; if (sheet != null) { iRow = writeSheetHeader(creationHelper, sheet, iRow, d, spks); } int iCRow = 0; if (sheetcounts != null) { iCRow = writeSheetHeader(creationHelper, sheet, iCRow, d, spks); } ArrayList<Double> scores = new ArrayList<Double>(); HashSet<X> counted = new HashSet<X>(); do { List<X> left = win.getLeftWindow(); Collections.reverse(left); // windowers return things in dialogue order: we'll look progressively backwards List<X> right = win.getRightWindow(); // System.out.println("lengthS " + left.size() + " " + right.size()); double score = 0.0; double n = 0.0; for (X r : right) { String spkKey = makeSpkKey(r.getSpeaker(), d); String originalSpkKey = ""; if (r.getOriginalSpeaker() != null) { originalSpkKey = r.getOriginalSpeaker().getId(); // fix for the fact that BNC speakers are not currently given SUBdialogue ID in their ID - TODO // change that? Dialogue od = r.getOriginalDialogue(); if ((od == null) && (r instanceof DialogueSentence)) { od = ((DialogueSentence) r).getTurn().getOriginalDialogue(); } String originalDia; if (od != null) { originalDia = od.getId(); } else { originalDia = d.getId().replaceFirst("-\\d+$", ""); } if (!originalSpkKey.contains(originalDia)) { if (!originalDia.contains(":")) { throw new RuntimeException("can't find super-dialogue, no : in " + originalDia); } String originalSuperDia = originalDia.substring(0, originalDia.lastIndexOf(":")); if (originalSpkKey.contains(originalSuperDia)) { originalSpkKey = originalSpkKey.replace(originalSuperDia, originalDia); } else { throw new RuntimeException("spk key without super-dialogue " + spkKey + ", " + originalSpkKey + ", " + originalDia); } } } Row row = (wb == null ? null : sheet.createRow(iRow++)); int iCol = 0; Cell cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_STRING)); if (cell != null) { cell.setCellValue(creationHelper.createRichTextString(r.getSpeaker().getId())); cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING); cell.setCellValue(creationHelper.createRichTextString(originalSpkKey)); // cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING); // cell.setCellValue(creationHelper.createRichTextString(r.getId())); cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING); cell.setCellValue(creationHelper.createRichTextString(r.toString())); row.setHeightInPoints(12); sheet.setColumnWidth(iCol - 1, 2560); } if (!speakerScores.containsKey(spkKey)) { speakerScores.put(spkKey, new ArrayList<Double>()); speakerN.put(spkKey, new ArrayList<Double>()); originalSpks.put(spkKey, originalSpkKey); for (int i = 0; i < win.getLeftWindowSize(); i++) { speakerScores.get(spkKey).add(0.0); speakerN.get(spkKey).add(0.0); } Boolean isTurns = null; if (left.size() > 0) { isTurns = (left.get(0) instanceof DialogueTurn); } else if (right.size() > 0) { isTurns = (right.get(0) instanceof DialogueTurn); } spkMetrics.setNumUnits(spkKey, 0); totMetrics.setNumUnits(d.getId(), (isTurns ? d.numTurns() : d.numSents())); spkMetrics.setNumWords(spkKey, 0); totMetrics.setNumWords(d.getId(), d.numWords()); spkMetrics.setNumTokens(spkKey, 0); totMetrics.setNumTokens(d.getId(), d.numTokens()); } int iLeft = 0; double offset = Double.NaN; boolean gotOffset = false; for (X l : left) { double s = sim.similarity(l, r); // System.out.println("Siml = " + s + " for l:" + l.getId() + " r:" + r.getId()); if ((l.getOriginalId() != null) && (r.getOriginalId() != null) && l.getOriginalId().equals(r.getOriginalId())) { System.out.println("Equal IDs sim = " + s + " for l:" + l.getId() + " " + l.getOriginalId() + " r:" + r.getId() + " " + r.getOriginalId() + " d " + d.getId() + " nturns " + d.numTurns()); } if (wbcounts != null) { if (!counted.contains(l)) { MapUtil.addAll(diaAllCounts, sim.rawCountsA()); MapUtil.addAll(allCounts.get(""), sim.rawCountsA()); MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsA()); counted.add(l); } if (!counted.contains(r)) { MapUtil.addAll(diaAllCounts, sim.rawCountsB()); MapUtil.addAll(allCounts.get(""), sim.rawCountsB()); MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsB()); counted.add(r); } MapUtil.addAll(diaCommonCounts, sim.rawCountsAB()); MapUtil.addAll(commonCounts.get(""), sim.rawCountsAB()); MapUtil.addAll(commonCounts.get(d.getGenre()), sim.rawCountsAB()); } cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); if (cell != null) { cell.setCellValue(s); } score += s; n++; speakerScores.get(spkKey).set(iLeft, speakerScores.get(spkKey).get(iLeft) + s); speakerN.get(spkKey).set(iLeft, speakerN.get(spkKey).get(iLeft) + 1); if (!win.getClass().toString().contains("AllOther")) { // for "all other" windowers, actually // average over "window" iLeft++; } if (!gotOffset) { offset = r.getStartTime() - l.getEndTime(); gotOffset = true; // if (!Double.isNaN(offset)) { // System.out.println("Offset = " + offset + " for l:" + l.getId() + " r:" + r.getId()); // } } } // print number sents/words/tokens iCol += (win.getLeftWindowSize() - left.size() + 1); if (wb != null) { // if we are writing to a workbook cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(r instanceof DialogueTurn ? ((DialogueTurn) r).getSents().size() : 1); cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(r.numWords()); cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(r.numTokens()); } iCol += 1; if (!Double.isNaN(offset)) { cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(offset); } else { iCol++; } double wordRate = (double) (r.getEndTime() - r.getStartTime()) / (double) r.numWords(); if (r.numWords() == 0) { wordRate = Double.NaN; // on some OSs this doesn't happen in the calc above } if (!Double.isNaN(wordRate)) { cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(wordRate); } else { iCol++; } // make sure we counted this one - the first one can get missed if leftWindow empty if ((wbcounts != null) && !counted.contains(r)) { sim.similarity(r, r); MapUtil.addAll(diaAllCounts, sim.rawCountsA()); MapUtil.addAll(allCounts.get(""), sim.rawCountsA()); MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsA()); counted.add(r); } spkMetrics.setNumUnits(spkKey, spkMetrics.getNumUnits(spkKey) + 1); spkMetrics.setNumWords(spkKey, spkMetrics.getNumWords(spkKey) + r.numWords()); spkMetrics.setNumTokens(spkKey, spkMetrics.getNumTokens(spkKey) + r.numTokens()); if (!Double.isNaN(offset)) { spkMetrics.setTurnOffset(spkKey, spkMetrics.getTurnOffset(spkKey) + offset); spkMetrics.setNumTurnOffsets(spkKey, spkMetrics.getNumTurnOffsets(spkKey) + 1); totMetrics.setTurnOffset(d.getId(), totMetrics.getTurnOffset(d.getId()) + offset); totMetrics.setNumTurnOffsets(d.getId(), totMetrics.getNumTurnOffsets(d.getId()) + 1); } if (!Double.isNaN(wordRate)) { spkMetrics.setWordRate(spkKey, spkMetrics.getWordRate(spkKey) + wordRate); spkMetrics.setNumWordRates(spkKey, spkMetrics.getNumWordRates(spkKey) + 1); totMetrics.setWordRate(d.getId(), totMetrics.getWordRate(d.getId()) + wordRate); totMetrics.setNumWordRates(d.getId(), totMetrics.getNumWordRates(d.getId()) + 1); } } scores.add((n == 0.0) ? 0.0 : (score / n)); } while (win.advance()); if (wb != null) { iRow++; for (DialogueSpeaker spk : spks) { String spkKey = makeSpkKey(spk, d); Row row = sheet.createRow(iRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(spk.getId())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(originalSpks.get(spkKey))); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Mean")); for (int i = 0; i < win.getLeftWindowSize(); i++) { if (speakerN.get(spkKey).get(i) > 0) { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC) .setCellValue(speakerScores.get(spkKey).get(i) / speakerN.get(spkKey).get(i)); } else { iCol++; } // System.out // .println("score " + i + " for speaker " + spkKey + "=" + speakerScores.get(spkKey).get(i)); // System.out.println("N " + i + " for speaker " + spkKey + "=" + speakerN.get(spkKey).get(i)); // System.out.println("mean " + i + " for speaker " + spkKey + "=" // + (speakerScores.get(spkKey).get(i) / speakerN.get(spkKey).get(i))); } iCol++; row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getNumUnits(spkKey) / (double) spkMetrics.getNumUnits(spkKey)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getNumWords(spkKey) / (double) spkMetrics.getNumUnits(spkKey)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getNumTokens(spkKey) / (double) spkMetrics.getNumUnits(spkKey)); iCol++; row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getTurnOffset(spkKey) / (double) spkMetrics.getNumTurnOffsets(spkKey)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getWordRate(spkKey) / (double) spkMetrics.getNumWordRates(spkKey)); } } if (wbcounts != null) { iCRow++; ArrayList<Object> keys = new ArrayList<Object>(diaAllCounts.keySet()); Collections.sort(keys, new DescendingComparator<Object>(diaAllCounts)); for (Object key : keys) { Row row = sheetcounts.createRow(iCRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(key.toString())); Cell cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (diaAllCounts.get(key) != null) { cell.setCellValue(diaAllCounts.get(key)); } cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (diaCommonCounts.get(key) != null) { cell.setCellValue(diaCommonCounts.get(key)); } } } return scores; }
From source file:qmul.align.AlignmentTester.java
License:Open Source License
private int writeSheetHeader(CreationHelper creationHelper, Sheet sheet, int iRow, Dialogue d, List<DialogueSpeaker> spks) { int iCol = 0; Row row = sheet.createRow(iRow++);// www . j a v a 2 s. c o m row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("ID")); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString(d.getId())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num speakers")); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(d.numSpeakers()); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num turns")); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(d.numTurns()); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num sents")); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(d.numSents()); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num words")); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(d.numWords()); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num tok words")); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(d.numTokens()); iRow++; row = sheet.createRow(iRow++); iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Speaker")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Orig Speaker")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("First Name")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Last Name")); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Gender")); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Age")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Occupation")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue genre")); for (DialogueSpeaker s : spks) { // HACK to find first original speaker for speaker - will only work with consistent speaker pairing DialogueSpeaker os = null; for (DialogueTurn t : d.getTurns()) { if (t.getSpeaker().equals(s)) { os = t.getOriginalSpeaker(); break; } } row = sheet.createRow(iRow++); iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(s.getId())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(os == null ? "" : os.getId())); s = (os == null ? s : os); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(s.getFirstName())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(s.getLastName())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(s.getGender())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(s.getAge())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(s.getOccupation())); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue( creationHelper.createRichTextString(corpus.getGenreMap().get(s.getId().split(":")[0]))); } iRow++; row = sheet.createRow(iRow++); iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Speaker")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Orig Speaker")); // row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Turn ID")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Transcription")); for (int i = 0; i < getWin().getLeftWindowSize(); i++) { row.createCell(iCol + i, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Val i-" + (i + 1))); } iCol += getWin().getLeftWindowSize() + 1; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num sents")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num words")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Num tok words")); iCol += 1; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Offset time")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Time per word")); return iRow; }
From source file:qmul.align.AlignmentTester.java
License:Open Source License
/** * Print a summary sheet on the (gulp) excel spreadsheet * //from w w w .java 2s. co m * @param wb * @param sheetName * @param speakerScores * @param originalSpks * @param speakerN */ private void printSummarySheet(Workbook wb, String sheetName, HashMap<String, ArrayList<Double>> speakerScores, HashMap<String, String> originalSpks, HashMap<String, ArrayList<Double>> speakerN, MetricsMap spkMetrics, MetricsMap totMetrics, boolean pairedCorpus) { CreationHelper creationHelper = wb.getCreationHelper(); sheetName = (sheetName == null ? "Summary" : shorten(sheetName)); System.out.println("Checking workbook " + wb + " for sheet " + sheetName); Sheet sheet = wb.getSheet(sheetName); if (sheet != null) { System.out.println("Exists, removing sheet " + sheetName); wb.removeSheetAt(wb.getSheetIndex(sheet)); } sheet = wb.createSheet(sheetName); wb.setSheetOrder(sheetName, 0); int iRow = 0; // first general identifying stuff Row row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getCorpus().getId())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getWin().toString())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Similarity Measure")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getSim().toString())); // now header row = sheet.createRow(iRow++); row = sheet.createRow(iRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Speaker")); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Genre")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Orig Speaker")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Orig Genre")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker #units")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue #units")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker #words")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue #words")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker #tokens")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue #tokens")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker avg offset")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue avg offset")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker avg wordrate")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue avg wordrate")); iCol++; for (int i = 0; i < getWin().getLeftWindowSize(); i++) { row.createCell(i + iCol, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Mean i-" + (i + 1))); } // now means per speaker List<String> spks = new ArrayList<String>(speakerScores.keySet()); Collections.sort(spks); List<Double> means = new ArrayList<Double>(); List<Double> nums = new ArrayList<Double>(); for (int i = 0; i < getWin().getLeftWindowSize(); i++) { means.add(0.0); nums.add(0.0); } int nAll = 0; int nMatch = 0; for (String spk : spks) { // System.out.println("org chk [" + originalSpks.get(spk) + "][" + spk + "]"); boolean matching = false; if ((originalSpks.get(spk) != null) && originalSpks.get(spk).contains(":")) { int li = originalSpks.get(spk).lastIndexOf(":"); String pre = originalSpks.get(spk).substring(0, li); String suf = originalSpks.get(spk).substring(li); matching = spk.startsWith(pre) && spk.endsWith(suf); } nAll++; if (!pairedCorpus || matching) { nMatch++; // System.out.println("match " + pre + " " + suf); row = sheet.createRow(iRow++); iCol = 0; String dId = spk.replaceFirst("(.*)_.*", "$1"); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(spk)); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue( creationHelper.createRichTextString(corpus.getGenreMap().get(spk.split(":")[0]))); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(originalSpks.get(spk))); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper .createRichTextString(corpus.getGenreMap().get(originalSpks.get(spk).split(":")[0]))); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumUnits(spk)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumUnits(dId)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumWords(spk)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumWords(dId)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumTokens(spk)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumTokens(dId)); if (Double.isNaN(spkMetrics.getTurnOffset(spk)) || spkMetrics.getNumTurnOffsets(spk) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( spkMetrics.getTurnOffset(spk) / (double) spkMetrics.getNumTurnOffsets(spk)); } if (Double.isNaN(totMetrics.getTurnOffset(dId)) || totMetrics.getNumTurnOffsets(dId) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( totMetrics.getTurnOffset(dId) / (double) totMetrics.getNumTurnOffsets(dId)); } if (Double.isNaN(spkMetrics.getWordRate(spk)) || spkMetrics.getNumWordRates(spk) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC) .setCellValue(spkMetrics.getWordRate(spk) / (double) spkMetrics.getNumWordRates(spk)); } if (Double.isNaN(totMetrics.getWordRate(dId)) || totMetrics.getNumWordRates(dId) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC) .setCellValue(totMetrics.getWordRate(dId) / (double) totMetrics.getNumWordRates(dId)); } iCol++; for (int i = 0; i < speakerScores.get(spk).size(); i++) { if (speakerN.get(spk).get(i) > 0.0) { double mean = speakerScores.get(spk).get(i) / speakerN.get(spk).get(i); row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(mean); means.set(i, means.get(i) + mean); nums.set(i, nums.get(i) + 1); } } } } System.out.println("Matched " + nMatch + " of " + nAll); // and a final row for overall means row = sheet.createRow(iRow++); iCol = 14; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Overall")); for (int i = 0; i < getWin().getLeftWindowSize(); i++) { means.set(i, means.get(i) / nums.get(i)); row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(means.get(i)); } }
From source file:qmul.align.AlignmentTester.java
License:Open Source License
/** * Print a summary sheet on the (gulp) excel spreadsheet *///from w w w .j av a 2s . c om private void printSummaryCountSheet(Workbook wb, String sheetName, HashMap<String, HashMap<Object, Integer>> allCounts, HashMap<String, HashMap<Object, Integer>> commonCounts) { CreationHelper creationHelper = wb.getCreationHelper(); sheetName = (sheetName == null ? "Summary" : shorten(sheetName)); System.out.println("Checking workbook " + wb + " for sheet " + sheetName); Sheet sheet = wb.getSheet(sheetName); if (sheet != null) { System.out.println("Exists, removing sheet " + sheetName); wb.removeSheetAt(wb.getSheetIndex(sheet)); } sheet = wb.createSheet(sheetName); wb.setSheetOrder(sheetName, 0); int iRow = 0; // first general identifying stuff Row row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getCorpus().getId())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getWin().toString())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Similarity Measure")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getSim().toString())); // now header row = sheet.createRow(iRow++); row = sheet.createRow(iRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Type")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Overall count")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Common count")); for (String genre : allCounts.keySet()) { if (genre.isEmpty()) continue; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(genre + " overall count")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(genre + " common count")); } ArrayList<Object> keys = new ArrayList<Object>(allCounts.get("").keySet()); Collections.sort(keys, new DescendingComparator<Object>(allCounts.get(""))); for (Object key : keys) { row = sheet.createRow(iRow++); iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(key.toString())); Cell cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (allCounts.get("").get(key) != null) { cell.setCellValue(allCounts.get("").get(key)); } cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (commonCounts.get("").get(key) != null) { cell.setCellValue(commonCounts.get("").get(key)); } for (String genre : allCounts.keySet()) { if (genre.isEmpty()) continue; cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (allCounts.get(genre).get(key) != null) { cell.setCellValue(allCounts.get(genre).get(key)); } cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (commonCounts.get(genre).get(key) != null) { cell.setCellValue(commonCounts.get(genre).get(key)); } } } }