Autosize Shape to Fit the Text

FLX TMS FlexCel for VCL & FMX
1
Hi,
In VBA Excel, it is possible customize the size of the shape to fit to containg text.
Aproxinatelly In this way:
"..TextFrame2.AutoSize   := msoAutoSizeShapeToFitText"
More info here:

With FlexCel can I fill the Shape with the text and change font properties.too.
But can't FIT the Shape, resp. dimensions of the Shape to the new text.
is it possible to do this using Flexcel?
Thank you in advance
Miro
2

Hi,

You can set the same property in the Excel file with the code:


ShapeOptions1 := TShapeProperties_Create;
...
  ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fFitShapeToText, true);

But this will only affect the Excel file when you open it in Excel. When eporting to pdf etc with FlexCel, FlexCel doesn't autofit shapes to text.
3
I tried that. But it doesn't work for me.
I create an excel file, including shape. Everything works, but Shape doesn't fit to the text. I just want to open result in Excel.
No need Pdf or HTML report.
I've also played with WordWrap, but it doesn't go anyway.

4

Hi,

The problem here is that Excel only modifies the shape size when you edit the text in Excel. If you take an xlsx file with a shape with some text, manually edit it and add more text, the shape size won't be changed when you open it in Excel. If you add a character to the text in Excel, it will resize again.

As FlexCel is not autofitting the shape, and Excel neither (because you aren't modifying its text with Excel), the shape size is never recalculated.

I think that the only viable alternative here is to have FlexCel calculate the shape size before saving. You can also keep this property on so if a user later edits the text in Excel it keeps adapting the size, but it has to be saved with the correct size the first time.

To autofit the shape, FlexCel has a method that is actually designed to autofit comment boxes, but you can use it for shapes too: http://www.tmssoftware.biz/flexcel/doc/vcl/api/FlexCel.XlsAdapter/TXlsFile/AutofitComment.html

I've tried this code and it seems to work:


program Project63;


{$APPTYPE CONSOLE}


{$R *.res}


uses
  System.SysUtils, VCL.FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Render, UITypes;


const
  Text = 'It was the best of times, it was the worst of times, it was the age of wisdom, ' +
         'it was the age of foolishness, it was the epoch of belief, it was the epoch of incredulity,' +
         'it was the season of Light, it was the season of Darkness, it was the spring of hope, ' +
         'it was the winter of despair.';
var
  xls: TXlsFile;
  ShapeOptions1: IShapeProperties;
begin
  xls := TXlsFile.Create(1, TExcelFileFormat.v2019, true);
  try
    ShapeOptions1 := TShapeProperties_Create;
    ShapeOptions1.Anchor := TClientAnchor.Create(TFlxAnchorType.MoveAndDontResize, 2, 161, 3, 384, 6, 242, 5, 497);
    ShapeOptions1.ShapeType := TShapeType.Rectangle;
    ShapeOptions1.ObjectType := TObjectType.MicrosoftOfficeDrawing;
    ShapeOptions1.ShapeName := 'Rectangle 1';
    ShapeOptions1.Text := Text;
    ShapeOptions1.TextFlags := 530;
    ShapeOptions1.RotateTextWithShape := true;
    ShapeOptions1.ShapeThemeFont := TShapeFont_Create(TFontScheme.Minor, TDrawingColor.FromTheme(TThemeColor.Light1));
    ShapeOptions1.Print := true;
    ShapeOptions1.Visible := true;
    ShapeOptions1.ShapeGeometry := '<?xml version="1.0" encoding="utf-8" standalone="yes"?><a:shapeGeom xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"><a:prstGeom'
    + ' prst="rect"><a:avLst /></a:prstGeom></a:shapeGeom>';
    ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fFitShapeToText, true);
    ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fillColor, 12874308);
    ShapeOptions1.ShapeOptions.SetValue(TShapeOption.fFilled, true);
    ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineColor, 9392687);
    ShapeOptions1.ShapeOptions.SetValue(TShapeOption.lineWidth, 12700);
    ShapeOptions1.ShapeOptions.SetValue(TShapeOption.wzName, 'Rectangle 1');


    ShapeOptions1.Anchor := xls.AutofitComment(Text, 4.0/3.0, false, 1.0, 0, ShapeOptions1.Anchor);






    xls.AddAutoShape(ShapeOptions1);


    xls.Save('test.xlsx');


  finally
    xls.Free;
  end;
end.

Note that the shape is still "fit shape to text", so if you open the generated file in Excel and edit the text and say type an space, Excel fill refit the shape again. But it won't resize it when you open the file.

Finally, this fits the text in a box, so it is good for a rectangle. If you have a more complex shape like a star, the text inside still is in a rectangle, but you have to account for the extra space between the rectangle containing the text and the bounds of the star itself.
5
Line of code with xls.AutofitComment did its job.
Thank you very much.
Miro