String length innterms of Excel internal units

FLX TMS FlexCel for VCL & FMX
1

Hi Adrian,


is there a function in TXlsFile that can give me the length of a string in terms of Excel internal units. 
I need find out if a string needs one or two rows in an Excel file.


2

Well, first of all, the usual caveats apply: you can't get the exact length because Excel shows different stuff depending in the resolution. 


But you can get a good approximation. To keep sanity, instead of using the crazy Excel internal units (which are different for the x and y axis, just for starters), it makes sense to convert everything to points (a point is 1/72 of an inch). So the first question would be, which is the width of the column in points?

Once you know the width of the column in points, you need to know the width of the string you want to measure, in a specific font. The code below will do both:


uses ... FlexCel.Pdf...
...
var

  ColWidthInPixels, ColWidthInPoints: double;
  PdfWriter: TPdfWriter;
  TextWidth: TUISize;
  Font: TUIFont;
begin
  ColWidthInPixels := xls.GetColWidth(1, true) / TExcelMetrics.ColMult(xls);
  ColWidthInPoints := ColWidthInPixels / 96.0 * 72.0;




  PdfWriter := TPdfWriter.Create;
  try
    Font := TUIFont.CreateNew('Arial', 12, [TUIFontStyle.fsBold]);
    try
      TextWidth := PdfWriter.MeasureString('hellomylov', Font);
    finally
      Font.Free;
    end;
  finally
    PdfWriter.Free;
  end;

 


Note that I used a PdfWriter to measure the string in points. You could have used Delphi's canvas (as for example here: https://stackoverflow.com/questions/2548132/how-to-get-textwidth-of-string-without-canvas ), or any other method. But FlexCel already has a method in PDFWriter to measure a string in points, so why not use it?

Finally, I used an arbitrary font here (arial 12 bold). I am not sure if you wanted to use a font that you know, or you want to use the font already set in a cell. if that is the case, to get the font at cell (row, col), you could do:

[CODE]
var 
  fmt: TFlxFormat;
  FontStyle: TUIFontStyleSet;
begin
  fmt := xls.GetCellVisibleFormatDef(row, col);
...
    FontStyle := [];
    if (TFlxFontStyles.Bold in fmt.Font.Style) then Include(FontStyle, TUIFontStyle.fsBold);
    if (TFlxFontStyles.Italic in fmt.Font.Style) then Include(FontStyle, TUIFontStyle.fsItalic);

    Font := TUIFont.CreateNew(fmt.Font.Name, fmt.Font.Size20 / 20.0, FontStyle);

3

Thanks a lot Adrian for the comprehensive explanation. 

. 
.