In Streaming XSSFWorkbook make part of cell content to bold using apache poi-CodePudding

I am trying following two sets of code to make part of the cell content bold/italic in streaming XSSFWorkbook that is SXSSFWorkbook. But it is not working. Any help in this regard is appreciated.

The cell content should look like following

This is sample content

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

    XSSFFont fontPlain = (XSSFFont) wb.createFont();

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

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

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

    cell.setCellValue(cell1Value);
    wb.write(new FileOutputStream("BoldFile.xlsx"));
} catch (IOException e) {
    e.printStackTrace();
}

Also following code does the same. The cell content should look like following

Hello World

try (SXSSFWorkbook wb = new SXSSFWorkbook()) {
    SXSSFSheet sheet = wb.createSheet("With Rich Text");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    
    Font fontBold = wb.createFont();
    fontBold.setBold(true);
    
    String value = "Hello World";
    String subValue = "World";
    
    cell.setCellValue(value);
    RichTextString rts = cell.getRichStringCellValue();
    rts.applyFont(value.indexOf(subValue), value.length(), fontBold);
    cell.setCellValue(rts);

    wb.write(new FileOutputStream("BoldFile.xlsx"));
    
} catch (IOException e) {
    e.printStackTrace();
}

CodePudding user response：

You should always create the RichTextString using CreationHelper.createRichTextString(java.lang.String text) where CreationHelper comes from Workbook.getCreationHelper(). So you can be sure, that the created RichTextString always is of same type as the workbook. For example HSSFRichTextString from a HSSFCreationHelper, XSSFRichTextString from a XSSFCreationHelper and XSSFRichTextString for streaming SXSSF from a SXSSFCreationHelper. When created using the constructors that is not always guaranteed.

But using SXSSF there is an issue when SXSSFWorkbook uses inline strings instead of a shared strings table. This is the default in SXSSF because inline strings are better usable in the streaming approach. But Apache POI fails to create rich text content in inline strings. It only creates rich text content in the shared strings table, which is the default in Excel.

So using Workbook workbook = new SXSSFWorkbook(); no rich text cell content can be created. One needs using Workbook workbook = new SXSSFWorkbook(new XSSFWorkbook(), 100, false, true); which tells to create a SXSSFWorkbook from a new empty XSSFWorkbook, using the default rowAccessWindowSize of 100, not to use gzip compression for temporary files and to use a shared strings table.

Following code will work for all kinds of Workbook and create rich text cell content.

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

class CreateExcelRichTextString {

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

  //Workbook workbook = new XSSFWorkbook(); FileOutputStream out = new FileOutputStream("./Excel.xlsx");
  //Workbook workbook = new HSSFWorkbook(); FileOutputStream out = new FileOutputStream("./Excel.xls");
  
  //Workbook workbook = new SXSSFWorkbook(); FileOutputStream out = new FileOutputStream("./Excel.xlsx");
  Workbook workbook = new SXSSFWorkbook(new XSSFWorkbook(), 100, false, true); FileOutputStream out = new FileOutputStream("./Excel.xlsx");

  CreationHelper creationHelper = workbook.getCreationHelper();

  Font font = workbook.createFont(); // default font
  Font fontBold = workbook.createFont();
  fontBold.setBold(true);
  Font fontItalic  = workbook.createFont();
  fontItalic.setItalic(true);

  String text = "This is some sample content.";
  RichTextString richTextString = creationHelper.createRichTextString(text);
  String word = "some";
  int startIndex = text.indexOf(word);
  int endIndex = startIndex   word.length();
  richTextString.applyFont(startIndex, endIndex, fontBold);
  word = "sample";
  startIndex = text.indexOf(word);
  endIndex = startIndex   word.length();
  richTextString.applyFont(startIndex, endIndex, fontItalic);

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

  workbook.write(out);
  out.close();
  workbook.close();
  if (workbook instanceof SXSSFWorkbook) ((SXSSFWorkbook)workbook).dispose();
 }
}

Conclusion: Stop thinking SXSSF is the better XSSF because of using streaming approach to save memory usage. It is not. It has disadvantages of it's own. The structure of spreadsheet application files is nothing what is good to stream mass data in.