Why does not XSSFRichTextString.applyFont() work as it is written in java doc?

Question

I am trying to apply bold font on parts of a string and place it in a cell.

XSSFFont font = workbook.createFont();
font.setBold(true);
XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
XSSFCell c = nextRow.createCell(4);
c.setCellStyle(style);
XSSFRichTextString string = new XSSFRichTextString(report.getSurroundText());
string.applyFont( startIndex, getEndOfWord(startIndex, report.getFoundWord()), font); 
c.setCellValue(string);

This code, as part of my code that produces an .xlsx file and it does produce a not corrupt file, but the text that should be bold is not correct. Instead it highlights from the start of the text to the index I set as end index in the applyFont() method. Basicly for some reason startIndex is ignored.

During debug, both startIndex and the return value of getEndOfWord() is correct.

EDIT:

try(FileOutputStream fileOut = new FileOutputStream(new File(directory.getAbsoluteFile() + File.separator + 
            FilenameUtils.getBaseName(csvFile.getAbsolutePath()) + ".xlsx"));) {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            XSSFSheet sheet = workbook.createSheet("Highlights");
            XSSFRow headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue(firstLine);

            XSSFRow titleRow = sheet.createRow(1);
            titleRow.createCell(0).setCellValue(SCANID);
            titleRow.createCell(1).setCellValue(DOCID);
            titleRow.createCell(2).setCellValue(FOUNDWORD);
            titleRow.createCell(3).setCellValue(OFFSET);
            titleRow.createCell(4).setCellValue(SURROUNDTEXT);

            XSSFFont font = workbook.createFont();
            font.setBold(true);
            XSSFFont deFont = workbook.createFont();
            font.setBold(false);

            int row = 2;
            for (MuiDetailReport report : lst) {
                XSSFRow nextRow = sheet.createRow(row);
                nextRow.createCell(0).setCellValue(report.getScanId());
                nextRow.createCell(1).setCellValue(report.getDocId());
                nextRow.createCell(2).setCellValue(report.getFoundWord());
                if (report.getOffset() != 0) nextRow.createCell(3).setCellValue(report.getOffset());
                else nextRow.createCell(3).setCellValue("");
                if (!report.getFoundWord().isBlank() && !report.getSurroundText().isBlank()) {
                    int startIndex = getStartOfWord(report.getFoundWord(), report.getSurroundText());
                    if (startIndex == -1) nextRow.createCell(4).setCellValue("");
                    else {
                        XSSFCell c = nextRow.createCell(4);
                        XSSFRichTextString string = new XSSFRichTextString(report.getSurroundText());
                        string.applyFont(startIndex, getEndOfWord(startIndex, report.getFoundWord()), font);
                        c.setCellValue(string);
                    }
                } else nextRow.createCell(4).setCellValue("");
                row++;
            }
            workbook.write(fileOut);
        }
        fileOut.flush();
    }

This is my method for creating my .xlsx file. Method parameter: String firstLine, List<MuiDetailReport> lst, File csvFile. Variable with all uppercase characters are static final String

My result is "HellomynameisThad" instead of "HellomynameisThad"

I checked out your code on my system and it works as expected (with hard values set for the indexes). There is only one difference concerning the definition of the font, I used this `XSSFFont boldFont = new XSSFFont();` instead of `workbook.createFont();`. Maybe worth a try... — deHaar, Oct 08 '19 at 10:00
@deHaar how did that work for you? for me there is no available empty constructor for XSSFFont. — Kristóf Horváth, Oct 08 '19 at 10:12
Oh, yes... I didn't overwrite that `XSSFRichTextString` with a cell style that uses the same font, sorry, I should have mentioned that. — deHaar, Oct 08 '19 at 10:15
@ArvindKumarAvinashnew The constructor XSSFFont() is not visible — Kristóf Horváth, Oct 08 '19 at 10:20
You have applied a `CellStyle` with the same font to the cell (the entire one), which means that style will style all the text in the cell. Just don't do that and only set the `XSSFRichTextString` as cell value. — deHaar, Oct 08 '19 at 10:34
@deHaar it did not turn the whole cell into bold. Have you read my post? It turned everything before the index, getEndOfWord() returns, bold. When I try without that step nothing is bold. — Kristóf Horváth, Oct 08 '19 at 10:40
Yes, I have read your post. The `CellStyle` applies to the entire cell and then gets overridden by the rich text conten, which doesn't care about the style of the text before the `startIndex`, but will style the remaining text as desired, which means it makes the desired part bold, no matter if it was bold before and makes the remaining text non bold. — deHaar, Oct 08 '19 at 10:43
@AxelRichter is this better? previously i had minimal code that clearly defined the parameters of my issue. The fact that i have issue with something simple does not make it not answerable, other people understood the problem, but for some reason they cant reproduce my issue. — Kristóf Horváth, Oct 08 '19 at 11:42

score 2 · Accepted Answer · answered Oct 08 '19 at 12:33

2

Let's have a really Minimal, Reproducible Example.

The folowing should result in having the text

HellomynameisThad

in Cell A1.

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

class CreateExcelBoldWord {

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

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

  String fileName = (workbook instanceof XSSFWorkbook)?"Excel.xlsx":"Excel.xls";

  CreationHelper creationHelper = workbook.getCreationHelper();

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

  String text = "HellomynameisThad";
  String word = "name";

  RichTextString richTextString = creationHelper.createRichTextString(text);
  int startIndex = text.indexOf(word);
  int endIndex = startIndex + word.length();
  richTextString.applyFont(startIndex, endIndex, fontBold);

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

  FileOutputStream out = new FileOutputStream(fileName);
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

No?

answered Oct 08 '19 at 12:33

Axel Richter

56,077
6
60
87

yes this example works, can you help me with figuring out what is the difference between this simple example and mine? – Kristóf Horváth Oct 08 '19 at 12:44
@Kristóf Horváth: How shall this be possible without knowing the whole context? Your code snippets both are not complete. And you claim your `startIndex` is correct and `getEndOfWord` returns the correct end index. So if that is true there is not a differnence. – Axel Richter Oct 08 '19 at 12:54
I have same setup except CreationHelper, but i tried that and still no luck – Kristóf Horváth Oct 08 '19 at 12:56
I use the same logic for end and start index as you. – Kristóf Horváth Oct 08 '19 at 12:57
context is super simple report.getSurroundText is your text and report.getFoundWord is your word – Kristóf Horváth Oct 08 '19 at 12:57
I think this had to do with my IDE acting up, I reset my IDE and it worked. – Kristóf Horváth Oct 08 '19 at 13:06

deHaar · Answer 2 · 2019-10-08T10:45:14.007

0

I got it working by not applying a CellStyle to the cell and just setting the XSSFRichTextString as the cell value.

See this example, which assumes an already created XSSFWorkbook and an XSSFSheet:

XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
XSSFFont boldFont = new XSSFFont();
boldFont.setBold(true);
XSSFRichTextString rts = new XSSFRichTextString("BVB BVB BVB");
rts.applyFont(4, 7, boldFont);
cell.setCellValue(rts);

I omitted the part that writes the workbook to the file system, I think yours is working already.

The result of this is a workbook with "BVB BVB BVB" in the first / upper left cell.

This is the part of my pom.xml (maven project) where apache poi dependencies are set, maybe you have an older version of it:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.0</version>
</dependency>

edited Oct 08 '19 at 10:45

answered Oct 08 '19 at 10:38

deHaar

17,687
10
38
51

How the hell are you making new XSSFFont() work? It is not visible for me. – Kristóf Horváth Oct 08 '19 at 10:41
@KristófHorváth I don't know why it is working for me and not for you. Which version of apache poi are you using? See my edit, please... I have provided the versions I use. – deHaar Oct 08 '19 at 10:45
I do not have last one, otherwise same – Kristóf Horváth Oct 08 '19 at 10:47
I added it to my pom and still nothing is bold – Kristóf Horváth Oct 08 '19 at 10:49
@KristófHorváth have you skipped setting the `CellStyle`, too? – deHaar Oct 08 '19 at 10:59
yes i removed that part and the result is without any bold in it. – Kristóf Horváth Oct 08 '19 at 11:01
Still How the hell are you making new XSSFFont() work? For me its not visible. – Kristóf Horváth Oct 08 '19 at 11:08

Why does not XSSFRichTextString.applyFont() work as it is written in java doc?

2 Answers2