public static void main(String[] args) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow((short) 2); XSSFCell cell = row.createCell(1); XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); XSSFFont font1 = wb.createFont(); font1.setBold(true); font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); rt.applyFont(0, 10, font1); XSSFFont font2 = wb.createFont(); font2.setItalic(true); font2.setUnderline(XSSFFont.U_DOUBLE); font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0))); rt.applyFont(10, 19, font2); XSSFFont font3 = wb.createFont(); font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255))); rt.append(" Jumped over the lazy dog", font3); cell.setCellValue(rt); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx"); wb.write(fileOut); fileOut.close(); }
/** * Set a string value for the cell. * * @param str value to set the cell to. For formulas we'll set the 'pre-evaluated result string, * for String cells we'll set its value. For other types we will change the cell to a string * cell and set its value. If value is null then we will change the cell to a Blank cell. */ public void setCellValue(RichTextString str) { if (str == null || str.getString() == null) { setCellType(Cell.CELL_TYPE_BLANK); return; } int cellType = getCellType(); switch (cellType) { case Cell.CELL_TYPE_FORMULA: _cell.setV(str.getString()); _cell.setT(STCellType.STR); break; default: if (_cell.getT() == STCellType.INLINE_STR) { // set the 'pre-evaluated result _cell.setV(str.getString()); } else { _cell.setT(STCellType.S); XSSFRichTextString rt = (XSSFRichTextString) str; rt.setStylesTableReference(_stylesSource); int sRef = _sharedStringSource.addEntry(rt.getCTRst()); _cell.setV(Integer.toString(sRef)); } break; } }
/** Test setText single paragraph to ensure backwards compatibility */ public void testSetTextSingleParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFDrawing drawing = sheet.createDrawingPatriarch(); XSSFTextBox shape = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.createFont(); font.setColor(new XSSFColor(new Color(0, 255, 255))); font.setFontName("Arial"); rt.applyFont(font); shape.setText(rt); List<XSSFTextParagraph> paras = shape.getTextParagraphs(); assertEquals(1, paras.size()); assertEquals("Test String", paras.get(0).getText()); List<XSSFTextRun> runs = paras.get(0).getTextRuns(); assertEquals(1, runs.size()); assertEquals("Arial", runs.get(0).getFontFamily()); Color clr = runs.get(0).getFontColor(); assertArrayEquals( new int[] {0, 255, 255}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()}); assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb)); }
/** * ensure that font and color rich text attributes defined in a XSSFRichTextString are passed to * XSSFSimpleShape. * * <p>See Bugzilla 54969. */ public void testRichTextFontAndColor() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFDrawing drawing = sheet.createDrawingPatriarch(); XSSFTextBox shape = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.createFont(); font.setColor(new XSSFColor(new Color(0, 128, 128))); font.setFontName("Arial"); rt.applyFont(font); shape.setText(rt); CTTextParagraph pr = shape.getCTShape().getTxBody().getPArray(0); assertEquals(1, pr.sizeOfRArray()); CTTextCharacterProperties rPr = pr.getRArray(0).getRPr(); assertEquals("Arial", rPr.getLatin().getTypeface()); assertArrayEquals( new byte[] {0, (byte) 128, (byte) 128}, rPr.getSolidFill().getSrgbClr().getVal()); assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb)); }
/** * Set the cells type (numeric, formula or string) * * @throws IllegalArgumentException if the specified cell type is invalid * @see #CELL_TYPE_NUMERIC * @see #CELL_TYPE_STRING * @see #CELL_TYPE_FORMULA * @see #CELL_TYPE_BLANK * @see #CELL_TYPE_BOOLEAN * @see #CELL_TYPE_ERROR */ public void setCellType(int cellType) { int prevType = getCellType(); if (isPartOfArrayFormulaGroup()) { notifyArrayFormulaChanging(); } if (prevType == CELL_TYPE_FORMULA && cellType != CELL_TYPE_FORMULA) { getSheet().getWorkbook().onDeleteFormula(this); } switch (cellType) { case CELL_TYPE_BLANK: setBlank(); break; case CELL_TYPE_BOOLEAN: String newVal = convertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING; _cell.setT(STCellType.B); _cell.setV(newVal); break; case CELL_TYPE_NUMERIC: _cell.setT(STCellType.N); break; case CELL_TYPE_ERROR: _cell.setT(STCellType.E); break; case CELL_TYPE_STRING: if (prevType != CELL_TYPE_STRING) { String str = convertCellValueToString(); XSSFRichTextString rt = new XSSFRichTextString(str); rt.setStylesTableReference(_stylesSource); int sRef = _sharedStringSource.addEntry(rt.getCTRst()); _cell.setV(Integer.toString(sRef)); } _cell.setT(STCellType.S); break; case CELL_TYPE_FORMULA: if (!_cell.isSetF()) { CTCellFormula f = CTCellFormula.Factory.newInstance(); f.setStringValue("0"); _cell.setF(f); if (_cell.isSetT()) _cell.unsetT(); } break; default: throw new IllegalArgumentException("Illegal cell type: " + cellType); } if (cellType != CELL_TYPE_FORMULA && _cell.isSetF()) { _cell.unsetF(); } }
private String convertCellValueToString() { int cellType = getCellType(); switch (cellType) { case CELL_TYPE_BLANK: return ""; case CELL_TYPE_BOOLEAN: return TRUE_AS_STRING.equals(_cell.getV()) ? "TRUE" : "FALSE"; case CELL_TYPE_STRING: int sstIndex = Integer.parseInt(_cell.getV()); XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.getEntryAt(sstIndex)); return rt.getString(); case CELL_TYPE_NUMERIC: case CELL_TYPE_ERROR: return _cell.getV(); case CELL_TYPE_FORMULA: // should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator // just use cached formula result instead break; default: throw new IllegalStateException("Unexpected cell type (" + cellType + ")"); } cellType = getBaseCellType(false); String textValue = _cell.getV(); switch (cellType) { case CELL_TYPE_BOOLEAN: if (TRUE_AS_STRING.equals(textValue)) { return "TRUE"; } if (FALSE_AS_STRING.equals(textValue)) { return "FALSE"; } throw new IllegalStateException( "Unexpected boolean cached formula value '" + textValue + "'."); case CELL_TYPE_STRING: case CELL_TYPE_NUMERIC: case CELL_TYPE_ERROR: return textValue; } throw new IllegalStateException("Unexpected formula result type (" + cellType + ")"); }
/** * Get the value of the cell as a XSSFRichTextString * * <p>For numeric cells we throw an exception. For blank cells we return an empty string. For * formula cells we return the pre-calculated value if a string, otherwise an exception * * @return the value of the cell as a XSSFRichTextString */ public XSSFRichTextString getRichStringCellValue() { int cellType = getCellType(); XSSFRichTextString rt; switch (cellType) { case CELL_TYPE_BLANK: rt = new XSSFRichTextString(""); break; case CELL_TYPE_STRING: if (_cell.getT() == STCellType.INLINE_STR) { if (_cell.isSetIs()) { // string is expressed directly in the cell definition instead of implementing the // shared string table. rt = new XSSFRichTextString(_cell.getIs()); } else if (_cell.isSetV()) { // cached result of a formula rt = new XSSFRichTextString(_cell.getV()); } else { rt = new XSSFRichTextString(""); } } else if (_cell.getT() == STCellType.STR) { // cached formula value rt = new XSSFRichTextString(_cell.isSetV() ? _cell.getV() : ""); } else { if (_cell.isSetV()) { int idx = Integer.parseInt(_cell.getV()); rt = new XSSFRichTextString(_sharedStringSource.getEntryAt(idx)); } else { rt = new XSSFRichTextString(""); } } break; case CELL_TYPE_FORMULA: checkFormulaCachedValueType(CELL_TYPE_STRING, getBaseCellType(false)); rt = new XSSFRichTextString(_cell.isSetV() ? _cell.getV() : ""); break; default: throw typeMismatch(CELL_TYPE_STRING, cellType, false); } rt.setStylesTableReference(_stylesSource); return rt; }
/** * Chooses a new boolean value for the cell when its type is changing. * * <p>Usually the caller is calling setCellType() with the intention of calling * setCellValue(boolean) straight afterwards. This method only exists to give the cell a somewhat * reasonable value until the setCellValue() call (if at all). TODO - perhaps a method like * setCellTypeAndValue(int, Object) should be introduced to avoid this */ private boolean convertCellValueToBoolean() { int cellType = getCellType(); if (cellType == CELL_TYPE_FORMULA) { cellType = getBaseCellType(false); } switch (cellType) { case CELL_TYPE_BOOLEAN: return TRUE_AS_STRING.equals(_cell.getV()); case CELL_TYPE_STRING: int sstIndex = Integer.parseInt(_cell.getV()); XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.getEntryAt(sstIndex)); String text = rt.getString(); return Boolean.parseBoolean(text); case CELL_TYPE_NUMERIC: return Double.parseDouble(_cell.getV()) != 0; case CELL_TYPE_ERROR: case CELL_TYPE_BLANK: return false; } throw new RuntimeException("Unexpected cell type (" + cellType + ")"); }
/** * Get the value of the cell as a string * * <p>For numeric cells we throw an exception. For blank cells we return an empty string. For * formulaCells that are not string Formulas, we throw an exception * * @return the value of the cell as a string */ public String getStringCellValue() { XSSFRichTextString str = getRichStringCellValue(); return str == null ? null : str.getString(); }
/** Creates a new XSSFRichTextString for you. */ public XSSFRichTextString createRichTextString(String text) { XSSFRichTextString rt = new XSSFRichTextString(text); rt.setStylesTableReference(workbook.getStylesSource()); return rt; }
public void testNew() throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); // multiple calls of createDrawingPatriarch should return the same instance of XSSFDrawing XSSFDrawing dr1 = sheet.createDrawingPatriarch(); XSSFDrawing dr2 = sheet.createDrawingPatriarch(); assertSame(dr1, dr2); List<POIXMLDocumentPart> rels = sheet.getRelations(); assertEquals(1, rels.size()); assertTrue(rels.get(0) instanceof XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing) rels.get(0); String drawingId = drawing.getPackageRelationship().getId(); // there should be a relation to this drawing in the worksheet assertTrue(sheet.getCTWorksheet().isSetDrawing()); assertEquals(drawingId, sheet.getCTWorksheet().getDrawing().getId()); XSSFConnector c1 = drawing.createConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2)); c1.setLineWidth(2.5); c1.setLineStyle(1); XSSFShapeGroup c2 = drawing.createGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5)); assertNotNull(c2); XSSFSimpleShape c3 = drawing.createSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); c3.setText(new XSSFRichTextString("Test String")); c3.setFillColor(128, 128, 128); XSSFTextBox c4 = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6)); XSSFRichTextString rt = new XSSFRichTextString("Test String"); rt.applyFont(0, 5, wb.createFont()); rt.applyFont(5, 6, wb.createFont()); c4.setText(rt); c4.setNoFill(true); assertEquals(4, drawing.getCTDrawing().sizeOfTwoCellAnchorArray()); List<XSSFShape> shapes = drawing.getShapes(); assertEquals(4, shapes.size()); assertTrue(shapes.get(0) instanceof XSSFConnector); assertTrue(shapes.get(1) instanceof XSSFShapeGroup); assertTrue(shapes.get(2) instanceof XSSFSimpleShape); assertTrue(shapes.get(3) instanceof XSSFSimpleShape); // // Save and re-load it wb = XSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); // Check dr1 = sheet.createDrawingPatriarch(); CTDrawing ctDrawing = dr1.getCTDrawing(); // Connector, shapes and text boxes are all two cell anchors assertEquals(0, ctDrawing.sizeOfAbsoluteAnchorArray()); assertEquals(0, ctDrawing.sizeOfOneCellAnchorArray()); assertEquals(4, ctDrawing.sizeOfTwoCellAnchorArray()); shapes = dr1.getShapes(); assertEquals(4, shapes.size()); assertTrue(shapes.get(0) instanceof XSSFConnector); assertTrue(shapes.get(1) instanceof XSSFShapeGroup); assertTrue(shapes.get(2) instanceof XSSFSimpleShape); assertTrue(shapes.get(3) instanceof XSSFSimpleShape); // // Ensure it got the right namespaces String xml = ctDrawing.toString(); assertTrue( xml.contains( "xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\"")); assertTrue(xml.contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\"")); assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb)); }
/** Test addNewTextParagraph using RichTextString */ public void testAddNewTextParagraphWithRTS() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFDrawing drawing = sheet.createDrawingPatriarch(); XSSFTextBox shape = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String"); XSSFFont font = wb.createFont(); font.setColor(new XSSFColor(new Color(0, 255, 255))); font.setFontName("Arial"); rt.applyFont(font); XSSFFont midfont = wb.createFont(); midfont.setColor(new XSSFColor(new Color(0, 255, 0))); rt.applyFont(5, 14, midfont); // set the text "Rich Text" to be green and the default font XSSFTextParagraph para = shape.addNewTextParagraph(rt); // Save and re-load it wb = XSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); // Check drawing = sheet.createDrawingPatriarch(); List<XSSFShape> shapes = drawing.getShapes(); assertEquals(1, shapes.size()); assertTrue(shapes.get(0) instanceof XSSFSimpleShape); XSSFSimpleShape sshape = (XSSFSimpleShape) shapes.get(0); List<XSSFTextParagraph> paras = sshape.getTextParagraphs(); assertEquals( 2, paras .size()); // this should be 2 as XSSFSimpleShape creates a default paragraph (no text), // and then we add a string to that. List<XSSFTextRun> runs = para.getTextRuns(); assertEquals(3, runs.size()); // first run properties assertEquals("Test ", runs.get(0).getText()); assertEquals("Arial", runs.get(0).getFontFamily()); Color clr = runs.get(0).getFontColor(); assertArrayEquals( new int[] {0, 255, 255}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()}); // second run properties assertEquals("Rich Text", runs.get(1).getText()); assertEquals(XSSFFont.DEFAULT_FONT_NAME, runs.get(1).getFontFamily()); clr = runs.get(1).getFontColor(); assertArrayEquals( new int[] {0, 255, 0}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()}); // third run properties assertEquals(" String", runs.get(2).getText()); assertEquals("Arial", runs.get(2).getFontFamily()); clr = runs.get(2).getFontColor(); assertArrayEquals( new int[] {0, 255, 255}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()}); assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb)); }