CODEWITHSUNDEEP
excelformattingapache-poi
Robin
Robin

Reputation: 1746

How to change the content of a cell keeping formatting with Apache POI

I have an Excel document where I need to change the value of a single character inside a cell but keeping the formatting.

Using setCellValue, it properly changes the value but completely removes formatting.

Looking at POI documentation, it seems like there is no method to do that out of the box.

Upvotes: 0

Views: 115

Answers (1)

Robin
Robin

Reputation: 1746

As usual with Excel, not super easy but you need to :

  • Get the text of the cell
  • Keep track of the style on a per-range basis (not on a per-character basis, otherwise italic text look odd)
  • Change the text of the cell applying the previous style
// Copy the style of the different ranges
List<XSSFFont> fonts = new ArrayList<>();
List<Integer> ranges = new ArrayList<>();

String cellText = cell.getStringCellValue();
XSSFRichTextString cellRichText = cell.getRichStringCellValue();

for (int i = 0; i < cellText.length(); i++) {
    XSSFFont font = cellRichText.getFontAtIndex(i);

    fonts.add(font);
    ranges.add(i);

    while (font.equals(cellRichText.getFontAtIndex(i)))
        i++;

    i--;
}

ranges.add(cellText.length());

// Create the new content of the cell
XSSFRichTextString newCellRichText = new XSSFRichTextString(cellText.replace('A', 'B'));

// Apply the style to the new value - In this case, that's
// super easy, there is no need to change the indexes inside
// the `ranges` list as the string has the same size.
for (int i = 0; i < fonts.size(); i++) {
    newCellRichText.applyFont(ranges.get(i), ranges.get(i+1), fonts.get(i));
}

cell.setCellValue(newCellRichText);

Upvotes: 1

Related Questions