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));
}
