Styjun

I want to set the content of cell value with a combination of bold and italic.
like "This is Sample content."

However, this is not working using XSSFRichTextString.

I am using apache poi library version 4.0.1. I have tried making my content bold and italic combination using XSSFRichTextString. I have appended the string using by passing two arguments in method cell1Value.append("sample ", fontBold);i.e. String and Font.

 XSSFRichTextString cell1Value= new XSSFRichTextString("This is ");
 XSSFFont fontBold= wb.createFont();
 fontBold.setBold(true); //set bold
 fontBold.setUnderline(HSSFFont.U_SINGLE);


 XSSFFont fontItalic= wb.createFont();
 fontItalic.setItalic(true); //set italic

 cell1Value.append("sample ",fontBold);
 cell1Value.append("content", fontItalic);

 System.err.println(cell1Value.getCTRst());

 Cell cell1 = row.createCell(0);
 cell1.setCellValue(cell1Value);

I am expecting "sample" to be bold and "content" to be italic. However, the underline is working fine and my "sample" word is underlined properly. Please suggest what I am missing?

import java.io.FileOutputStream;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TextBoldItalic 

 public static void main(String[] args) throws Exception 

 XSSFWorkbook wb = new XSSFWorkbook();
 Sheet sheet = wb.createSheet();
 Row row = sheet.createRow(0);
 Cell cell = row.createCell(0);

 XSSFFont fontBold = wb.createFont();
 fontBold.setBold(true);
 XSSFFont fontItalic = wb.createFont();
 fontItalic.setItalic(true);
 XSSFFont fontBoldItalic = wb.createFont();
 fontBoldItalic.setBold(true);
 fontBoldItalic.setItalic(true);

 XSSFRichTextString cellValue = new XSSFRichTextString();
 cellValue.append("This is ", fontBold);
 cellValue.append("sample ", fontItalic);
 cellValue.append("content", fontBoldItalic);
 cell.setCellValue(cellValue);

 OutputStream fileOut = new FileOutputStream("TextBoldItalic.xlsx");
 wb.write(fileOut);
 wb.close();
 

This code works for me and gives me this result in LibreOffice. OpenOffice is fine, too. No MS Excel here for a test, sorry. Of course tools like this Online-Excel-Viewer will not do it right. So, please try my code and make a report.

As the code looks plausible, just doing the complete run:

The following I have tested with

• org.apache.poi / poi / 3.16


• org.apache.poi / poi-ooxml / 3.16

It worked.

try (XSSFWorkbook wb = new XSSFWorkbook()) 
 XSSFSheet sheet = wb.createSheet("With Rich Text");
 Row row = sheet.createRow(0);
 Cell cell = row.createCell(0);

 XSSFFont fontPlain = wb.createFont();

 XSSFFont fontBoldItalic = wb.createFont();
 fontBoldItalic.setBoldItalic(true);
 fontBoldItalic.setItalic(true);

 XSSFFont fontItalic = wb.createFont();
 fontItalic.setItalic(true);

 XSSFRichTextString cell1Value= new XSSFRichTextString("This is ");
 cell1Value.applyFont(fontPlain);
 cell1Value.append("sample ", fontBoldItalic);
 cell1Value.append("content", fontItalic);

 cell.setCellValue(cell1Value);
 wb.write(new FileOutputStream(xlsxFile));
 catch (IOException e) 
 e.printStackTrace();

My guess is a variable mix-up or something trivial. Maybe the font.

The problem using WPS Spreadsheets is that they claim to be most compatible to Excel but sometimes they totally fail. This time they misinterpret all Boolean font settings (bold, italic, strike) if they explicitly are set true.

Office Open XML provides Boolean elements having val attribute. Example: <b val="true"/> or <b val="false"/> or also <b val="1"/> or <b val="0"/>. But for set bold font having <b/> would be enough. And for not set bold font simply not having the b element at all would be enough.

Apache poi always sets <b val="true"/> for bold and <b val="false"/> for not bold. But WPS Spreadsheets now seems to misinterpret <b val="true"/>. It expects <b/> only.

The following code is the most compatible code for creating rich text strings for Excel. It supports Office Open XML (*.xlsx) as well as BIFF (*.xls) and it corrects the <Boolean val="true"/> to <Boolean/> only.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.xmlbeans.XmlObject;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBooleanProperty;

class CreateExcelRichText 

 static RichTextString createRichTextString(Workbook workbook, String[] textParts, Font[] fonts) 
 CreationHelper creationHelper = workbook.getCreationHelper();
 RichTextString richTextString = creationHelper.createRichTextString(String.join("", textParts));
 int start = 0;
 int end = 0;
 for (int tp = 0; tp < textParts.length; tp ++) 
 Font font = null;
 if (tp < fonts.length) font = fonts[tp];
 end += textParts[tp].length();
 if (font != null) richTextString.applyFont(start, end, font);
 start += textParts[tp].length();
 
 if (richTextString instanceof XSSFRichTextString) 
 //unset val="true" for boolean objects
 XSSFRichTextString xSSFRichTextString = (XSSFRichTextString)richTextString;
 String[] boolenanObjectsToUnset = new String[]"b", "i", "strike";
 for (String boolenanObjectToUnset : boolenanObjectsToUnset) 
 XmlObject[] xmlObjects = xSSFRichTextString.getCTRst().selectPath(
 "declare namespace main='http://schemas.openxmlformats.org/spreadsheetml/2006/main' " +
 ".//main:" + boolenanObjectToUnset);
 for (XmlObject xmlObject : xmlObjects) 
 CTBooleanProperty booleanProperty = (CTBooleanProperty)xmlObject;
 if (booleanProperty.getVal()) booleanProperty.unsetVal();
 
 
 
 return richTextString;
 

 public static void main(String[] args) throws Exception 

 Workbook workbook = new XSSFWorkbook(); 
 //Workbook workbook = new HSSFWorkbook(); 

 Font font = workbook.createFont();

 Font fontBoldItalic = workbook.createFont();
 fontBoldItalic.setBold(true);
 fontBoldItalic.setItalic(true);

 Font fontItalic = workbook.createFont();
 fontItalic.setItalic(true);

 Font fontStrikeout = workbook.createFont();
 fontStrikeout.setStrikeout(true);

 String[] textParts = new String[]"This is ", "Sample ", "content. ", "This is crossed out.";
 Font[] fonts = new Font[]font, fontBoldItalic, fontItalic, fontStrikeout;

 RichTextString richTextString = createRichTextString(workbook, textParts, fonts);

 Sheet sheet = workbook.createSheet();
 sheet.createRow(0).createCell(0).setCellValue(richTextString);

 String fileName = (workbook instanceof XSSFWorkbook)?"Excel.xlsx":"Excel.xls";
 FileOutputStream out = new FileOutputStream(fileName);
 workbook.write(out);
 out.close();
 workbook.close();