FitShapeToText and TextBoxes

I am not sure if there is going to be an answer to this one, but I hope so (and if it is in the documents already I apologize as I have not found it).

This is the same on work sheets and chart sheets. No difference.

When adding a text box I want the surrounding box to be auto-sized to fit the text. I have seen the property TShapeOption.fFitShapeToText which can be added to the ShapeOptions, and when I open the file in Excel the check box shows in the dialogue for editing the text box properties. When I do this with an example from APImate everything is fine, since APImate already knows how big the box actually needs to be because Excel created the file and had sized the box before saving.

My problem is that when making a general procedure to add a text box with variable amounts of text I have not worked out how to measure the size of box needed (I have to specify the anchors, but don't know the size). I can add the box and I can set the fFitShapeToText flag, but when I open the Excel file the box does not autosize. I guess this is because it would need a render to make the measurements and update etc (which Excel handles in interactive mode, but no immediately after opening a file). If I flip the check box off/on it works, but I can not expect the users to do this for every text box.

So is there a way of forcing some form of calculation/rendering so that the text box gets updated

Here you can see the problem if the box has a fill...


The resize flag IS set.

If you have asolution, then I would be a very happy person.

You could try with with TExcelFile.AutofitComment Method | FlexCel Studio for VCL and FireMonkey documentation

It is really designed for autofitting comment boxes, but I don't see why it wouldn't work for a normal textbox.

Thank you for the reply. Not sure this is going to work for me. It looks like it simply calculates the height, whereas I want it to calculate both height and width, as the text box is multi-line, and could be anything from quite narrow to very wide. There is an aspect ratio parameter, but unless I can calculate the height and width, I don't know what that should be.

I am looking a bit further into th render factory to see if there may be more in there I can leverage.


It calculates the height and the width, not sure why you assume it is only the height? The aspect ratio is how "square" you want your box to be. Say, you could fit your text in a square like this (aspect ratio 1/1):

Or you could fit it into a "widescreen" rectangle:

Or into a tall rectangle:

They are all valid solutions to autofitting the text to a shape. If what you want is to autofit the text into a "single" line (but still honoring carriage returns), just use an aspect ratio of 10 or so. This will create a much wider than tall rectangle for a single line, but still wrap it if the line is too long. And it will take care of the carriage returns in the text, so if your text is 3 lines, each line will be autofitted independently.

Ps: This will work for normal sheets, for chart sheets the anchor is in 1/4000 of the size of the sheet, and as the size of the sheet changes with the page size, I don't really think there is a solution. Say, if you have this chart in Excel:

and change the orientation to be portrait, the textbox will change:

>>not sure why you assume it is only the height

Perhaps the fact the code says

rh := TRowHeightCalc.Create(FWorkbook.Globals);

and perhaps it is because it is late here. Sorry if I am misunderstanding

The issue for me is that I don't know what I want the aspect ratio to be. I might be putting on a text box which has 2 lines of 80 characters, or I might be putting on a text box that has 15 lines of 4 characters. And to make it fun there can be a fair amount of formatting. This is a contrived example, but it could be something like this...


Anyway, I will play some more in the morning. 12.30am is too late for me.

Thank as always you for your care and detailed replies.

Well, this is one of those rare cases where you should trust the docs, not the code :slight_smile:
Seriously, the method name in this case is wrong because it is reusing the same code used for autofitting rows, but it does calculate both height and width.
About the multiple fonts, subscripts, etc, don't worry, all of that is covered and much more (if you want to have fun you can add some right-to-left code there, and FlexCel will also handle it fine). This is because AutofitComment is calling the same autofitting code we call to render files, and that code takes care of all that stuff. It renders the string as it would be rendering by an export to pdf, then measures it.

Now, to be 100% exact, your particular case isn't fully covered here. That is because we always assume that you will want to wrap: and from what I gather you don't really want to wrap lines. If you have 2 lines of 80 characters, you want 2 lines of 80 characters, not 4 of 40.

One way you could do it is by manually splitting the lines in #10 and fitting every line, since the lines themselves won't autofit. But let me think about it, I think we can add a parameter to not split lines, and that should be the simplest to do.