List of usage examples for org.apache.poi.ss.util CellAddress CellAddress
public CellAddress(Cell cell)
From source file:excel.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { try (Workbook wb = new XSSFWorkbook()) { CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet();//from www .j a v a 2s . co 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.setBold(true); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setAddress(new CellAddress("C3")); try (FileOutputStream out = new FileOutputStream("comments.xlsx")) { wb.write(out); } } }
From source file:org.keyboardplaying.xtt.xlsx.XlsxNormalizer.java
License:Apache License
private void normalizeSheet(XSSFSheet sheet, String activeRange) { sheet.setZoom(ZOOM_100);/* w ww . j a v a 2 s . co m*/ sheet.setDisplayGridlines(false); sheet.setSelected(false); PaneInformation pane = sheet.getPaneInformation(); if (pane == null) { /* Reset cell */ sheet.setActiveCell(new CellAddress(activeRange)); /* Reset view */ sheet.getCTWorksheet().getSheetViews().getSheetViewArray(0) .setTopLeftCell(CellAddress.A1.formatAsString()); } else { /* Reset cell */ sheet.createFreezePane(0, 0); // Remove panes sheet.setActiveCell(new CellAddress(activeRange)); sheet.createFreezePane(pane.getVerticalSplitLeftColumn(), pane.getHorizontalSplitTopRow()); // Reset panes /* Reset view */ sheet.showInPane(pane.getHorizontalSplitPosition(), pane.getVerticalSplitPosition()); } }
From source file:org.keyboardplaying.xtt.xlsx.XlsxNormalizerTest.java
License:Apache License
private void controlSheet(XSSFWorkbook workbook, String range, int sheetIndex) { XSSFSheet sheet = workbook.getSheetAt(sheetIndex); if (sheet.getPaneInformation() == null) { // Doesn't work with panes assertEquals(new CellAddress(range), sheet.getActiveCell()); }//from w w w . jav a 2s. c om assertEquals(0, sheet.getTopRow()); assertEquals(0, sheet.getLeftCol()); assertFalse(sheet.isDisplayGridlines()); assertFalse(sheet.isSelected()); }
From source file:uk.ac.manchester.cs.owl.semspreadsheets.ui.SheetTable.java
License:BSD License
protected void paintComponent(Graphics g) { try {/* w w w.j av a 2s. c om*/ super.paintComponent(g); } catch (XmlValueDisconnectedException e) { logger.warn("XmlValueDisconnectedException whilst repainting table"); } Color oldColor = g.getColor(); Graphics2D g2 = (Graphics2D) g; Stroke oldStroke = g2.getStroke(); g2.setStroke(stroke); for (OntologyTermValidation ontologyTermValidation : workbookManager.getOntologyManager() .getOntologyTermValidations()) { if (ontologyTermValidation.getRange().getSheet().equals(sheet)) { Range validation = ontologyTermValidation.getRange(); OntologyTermValidationDescriptor validationDescriptor = ontologyTermValidation .getValidationDescriptor(); if (!validationDescriptor.definesLiteral() && validationDescriptor.getTerms().isEmpty()) { g.setColor(emptyValidationColour); } else { g.setColor(validationAppliedColour); } Rectangle startRect = getCellRect(validation.getFromRow(), validation.getFromColumn(), false); Rectangle endRect = getCellRect(validation.getToRow(), validation.getToColumn(), false); int x1 = startRect.x + 1; int y1 = startRect.y + 1; int width = endRect.width + endRect.x - startRect.x - 2; int height = endRect.y + endRect.height - startRect.y - 2; Rectangle rect = new Rectangle(x1, y1, width, height); Composite oldComposite = g2.getComposite(); g2.setComposite(alphaComposite2); g2.drawRoundRect(rect.x, rect.y, rect.width, rect.height, 5, 5); g2.setComposite(alphaComposite); g2.fill(rect); g2.setComposite(oldComposite); } } Sheet sheet = workbookManager.getSelectionModel().getSelectedRange().getSheet(); if (sheet != null) { for (String entry : workbookManager.getLinkedCells(sheet.getName())) { Range validation; String cellString = entry.split(",")[0]; Color color; String test = entry.split(",")[1]; if (test.equals("BLUE")) { color = Color.BLUE; } else if (test.equals("RED")) { color = Color.RED; } else if (test.equals("MAGENTA")) { color = Color.MAGENTA; } else if (test.equals("ORANGE")) { color = Color.ORANGE; } else if (test.equals("CYAN")) { color = Color.CYAN; } else if (test.equals("GRAY")) { color = Color.GRAY; } else { color = Color.PINK; } int noOfColumns = Integer.parseInt(entry.split(",")[2]); CellAddress da = new CellAddress(cellString); for (int i = 0; i < noOfColumns; i++) { if (sheet.getCellAt(da.getColumn() + i, da.getRow()) == null) { sheet.addCellAt(da.getColumn() + i, da.getRow()); } } validation = new Range(sheet, da.getColumn(), da.getRow(), da.getColumn() + noOfColumns - 1, da.getRow()); g.setColor(color); Rectangle startRect = getCellRect(validation.getFromRow(), validation.getFromColumn(), false); Rectangle endRect = getCellRect(validation.getToRow(), validation.getToColumn(), false); int x1 = startRect.x + 1; int y1 = startRect.y + 1; int width = endRect.width + endRect.x - startRect.x - 2; int height = endRect.y + endRect.height - startRect.y - 2; Rectangle rect = new Rectangle(x1, y1, width, height); Composite oldComposite = g2.getComposite(); g2.setComposite(alphaComposite2); g2.drawRoundRect(rect.x, rect.y, rect.width, rect.height, 5, 5); g2.setComposite(alphaComposite); g2.setComposite(oldComposite); } } g2.setStroke(oldStroke); g.setColor(oldColor); }
From source file:utilities.SmapSheetXMLHandler.java
License:Apache License
@Override public void endElement(String uri, String localName, String qName) throws SAXException { if (uri != null && !uri.equals(NS_SPREADSHEETML)) { return;/*from ww w . j a va2s.c om*/ } String thisStr = null; // v => contents of a cell if (isTextTag(localName)) { vIsOpen = false; // Process the value contents as required, now we have it all switch (nextDataType) { case BOOLEAN: char first = value.charAt(0); thisStr = first == '0' ? "FALSE" : "TRUE"; break; case ERROR: thisStr = "ERROR:" + value; break; case FORMULA: if (formulasNotResults) { thisStr = formula.toString(); } else { String fv = value.toString(); if (this.formatString != null) { try { // Try to use the value as a formattable number double d = Double.parseDouble(fv); thisStr = formatter.formatRawCellContents(d, this.formatIndex, this.formatString); } catch (NumberFormatException e) { // Formula is a String result not a Numeric one thisStr = fv; } } else { // No formatting applied, just do raw value in all cases thisStr = fv; } } break; case INLINE_STRING: // TODO: Can these ever have formatting on them? XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); thisStr = rtsi.toString(); break; case SST_STRING: String sstIndex = value.toString(); try { int idx = Integer.parseInt(sstIndex); RichTextString rtss = sharedStringsTable.getItemAt(idx); thisStr = rtss.toString(); } catch (NumberFormatException ex) { logger.log(POILogger.ERROR, "Failed to parse SST index '" + sstIndex, ex); } break; case NUMBER: String n = value.toString(); if (this.formatString != null && n.length() > 0) { // smap Check for date boolean isDate = false; double d = Double.parseDouble(n); if (DateUtil.isValidExcelDate(d)) { ExcelNumberFormat nf = ExcelNumberFormat.from(style); if (nf != null) { isDate = DateUtil.isADateFormat(nf); } } if (isDate) { try { Date dv = DateUtil.getJavaDate(d); thisStr = sdf.format(dv); } catch (Exception e) { } } else { thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString); } } else thisStr = n; break; default: thisStr = "(TODO: Unexpected type: " + nextDataType + ")"; break; } // Do we have a comment for this cell? checkForEmptyCellComments(EmptyCellCommentsCheckType.CELL); XSSFComment comment = comments != null ? comments.findCellComment(new CellAddress(cellRef)) : null; // Output output.cell(cellRef, thisStr, comment, nextDataType, formatString); } else if ("f".equals(localName)) { fIsOpen = false; } else if ("is".equals(localName)) { isIsOpen = false; } else if ("row".equals(localName)) { // Handle any "missing" cells which had comments attached checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_ROW); // Finish up the row output.endRow(rowNum); // some sheets do not have rowNum set in the XML, Excel can read them so we should try to read them as well nextRowNum = rowNum + 1; } else if ("sheetData".equals(localName)) { // Handle any "missing" cells which had comments attached checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_SHEET_DATA); // indicate that this sheet is now done output.endSheet(); } else if ("oddHeader".equals(localName) || "evenHeader".equals(localName) || "firstHeader".equals(localName)) { hfIsOpen = false; output.headerFooter(headerFooter.toString(), true, localName); } else if ("oddFooter".equals(localName) || "evenFooter".equals(localName) || "firstFooter".equals(localName)) { hfIsOpen = false; output.headerFooter(headerFooter.toString(), false, localName); } }
From source file:utilities.SmapSheetXMLHandler.java
License:Apache License
/** * Do a check for, and output, comments in otherwise empty cells. *///from ww w.j a v a 2 s . co m private void checkForEmptyCellComments(EmptyCellCommentsCheckType type) { if (commentCellRefs != null && !commentCellRefs.isEmpty()) { // If we've reached the end of the sheet data, output any // comments we haven't yet already handled if (type == EmptyCellCommentsCheckType.END_OF_SHEET_DATA) { while (!commentCellRefs.isEmpty()) { outputEmptyCellComment(commentCellRefs.remove()); } return; } // At the end of a row, handle any comments for "missing" rows before us if (this.cellRef == null) { if (type == EmptyCellCommentsCheckType.END_OF_ROW) { while (!commentCellRefs.isEmpty()) { if (commentCellRefs.peek().getRow() == rowNum) { outputEmptyCellComment(commentCellRefs.remove()); } else { return; } } return; } else { throw new IllegalStateException( "Cell ref should be null only if there are only empty cells in the row; rowNum: " + rowNum); } } CellAddress nextCommentCellRef; do { CellAddress cellRef = new CellAddress(this.cellRef); CellAddress peekCellRef = commentCellRefs.peek(); if (type == EmptyCellCommentsCheckType.CELL && cellRef.equals(peekCellRef)) { // remove the comment cell ref from the list if we're about to handle it alongside the cell content commentCellRefs.remove(); return; } else { // fill in any gaps if there are empty cells with comment mixed in with non-empty cells int comparison = peekCellRef.compareTo(cellRef); if (comparison > 0 && type == EmptyCellCommentsCheckType.END_OF_ROW && peekCellRef.getRow() <= rowNum) { nextCommentCellRef = commentCellRefs.remove(); outputEmptyCellComment(nextCommentCellRef); } else if (comparison < 0 && type == EmptyCellCommentsCheckType.CELL && peekCellRef.getRow() <= rowNum) { nextCommentCellRef = commentCellRefs.remove(); outputEmptyCellComment(nextCommentCellRef); } else { nextCommentCellRef = null; } } } while (nextCommentCellRef != null && !commentCellRefs.isEmpty()); } }