CODEWITHSUNDEEP
javaapache-poi
AndyW
AndyW

Reputation: 440

Obtaining an Apache POI XSSFRichTextString from a textbox

How do I get an XSSFRichTextString from an Textbox in an excel spreadsheet using POI?

The setText() method is overloaded to set either a String or XSSFRichTextString but the getText() method only returns a String.

My approaches were as follows:-

  1. Change just the text in the textbox but leave the formatting unchanged. I was hoping to get the string and simply change the text but there seems to be no setText method in XSSFRichTextString. It appears that you set the text in the constructor then apply formatting using methods. Using this does put the text in the textbox but it loses all formatting.

  2. Extract the entire XSSFRichTextString, extract the Formatting, create a new RTS with the new text and apply the formatting. The problem is that while there is a setFont(Font object) method the getFont() returns only a short so I cannot seem to get the Font object and change it.

3 My last option is to set the plain text in the Textbox and then programmatically set all the font and formatting elements but this means burying the formatting in the Java code which means re-coding if the user needs to tweak the format instead of just using Excel.

Any suggestions?

Upvotes: 0

Views: 840

Answers (1)

Gagravarr
Gagravarr

Reputation: 48376

Promoting some comments to an answer

You can't get a RichTextString from an Excel textbox. The storage model for formatted text in a textbox was changed from that in plain cells, mostly it seems to support extra features and kinds of formatting. Textbox text is a bit more Word-like in how it's structured / stored

However, there's good news - you can do what you want, and change some specific text in a textbox without changing the formatting!

Firstly, from your XSSFTextBox, call getTextParagraphs() to get the paragraphs

Next, call getTextRuns() to get the individual runs of text sharing the same formatting. Search through those until you find the one(s) containing the text you want to change. Finally, call XSSFTextRun.setText(String) to change the text of that run. The formatting will be unchanged

Upvotes: 2

Related Questions