Column width, again

I have been working again on dynamic adjustments in column width. I have found that using Canvas.TextWidth(MyString), even when I have ensured that the Font parameters are correctly set, does not yield a very useful figure for Excel. Using your TExcelMetrics.ColMult and a fudge factor is not great, either, as I seem to need a factor of at least 1.3, which seems pretty sloppy. But perhaps that is simply the Excel issues, and can't be improved upon. 

Is there something I have missed which would allow me to determine the needed width based entirely on FlexCel operations?


The main thing here is screen resolution. As said, Excel shows different stuff depending in your screen resolution, and it makes it impossible to have an exact calculation that would work everywhere. Now, that being said, ColMult is very accurate (at leas for print preview of a 600 dpi printer). We calculated it back in 2005 by looking at the physical output of more than a dozen of laser printers, measuring the actual output in paper with an old fashioned ruler, and averaging the results. I remember it because I traveled to australia to do that (among a lot of other stuff of course).

But even when colmult is targeted to high res (600 dpi), differences aren't that big as to justify an 1.3 adjustment factor. There is something else going on. 

And I think you just gave the answer in this line:


Alas, Canvas uses GDI, which isn't either resolution independent. This means, the number of pixels you will get will depend (a lot) in the screen dpi. You can make a simple test:

In an empty project, drop a button and write this code:

  s = 'A little test!';

procedure TForm12.Button1Click(Sender: TObject);


Then run it at different dpi settings. Here with 96dpi (100% scaling in modern windows) I get 56. But if I run it at 150%, I get 89, which makes sense because 56*1.5 = 84. (the difference between 84 and 89 is because the text rendered at a higher resolution is a little bigger due to pixel rounding)

Now, as you can imagine, passing 56*RowMult or 89*RowMult will get completely different results: After all the file generated is the same, and the value you write in the xls/x file shouldn't depend on if you were running at 100% or 150%.

So you can't use the output of Canvas.TextWidth as it. This is one of the reasons FlexCel itself doesn't use Canvas at all, because it uses GDI which is resolution dependent. (The other reason being that canvas is not thread aware, so we would need to use Canvas.Lock a  lot to be sure FlexCel can be used in multithreaded apps). We use GDI+ everywhere.

So my first question would be: Do you have 96dpi (100%) screen scaling in your pc?  But even if you do, your customers might not, so I would use Canvas.Font.PixelsPerInch as an adjustment. This will return 96 if you are running at 100%, 144 if you are running at 150, etc.

If the first question is ok (you are testing at 96 dpi), do you have an example on what cases you need 1.3 adjustment? I've tried the example you sent me last time here using 1.0 (instead of 1.1 which you used and should probably use) and it works fine. Column widths are ok.

Third point: As you know, strings inside a cell might have different fonts. So for example you might have a cell which has a font of arial 8, but inside you select all the text and make it times new roman 12. If you have any rich text inside the cells, then you can't just use Canvas.TextWidth. But XlsFile.AutoFitCol supports rich text too, so you could use that.

Well, this is what I can think of. Your factor 1.3 is highly suspicious because 96/72 = 1.3333, and it looked like ti could be some conversion issue between "windows pixels" (96 per inch) and "everyone else pixels (72 per inch) 
As you can see here:

But looking at the code you sent last time, I could't find a place where you forgot to convert from 96 to 72. On the other hand, the code you sent last time works with 1.0 factor. In any case, I personally prefer to use ExcelMetrics.ColMultDisplay (which gives results in 1/100 of an inch, so I need to think in what I need to convert that to). But this is a personal preference, ColMult should work fine, using a "pixel"of 1/96 of an inch.


OK, you have confirmed my sense that the Canvas will not be my friend. But if that is the case, then my question is, how do I use FlexCel to tell me the width a given string will require. Yes, I know it will be approximate, and will still require an adjustment factor, but if I am using the work you have already done to free FlexCel from the Canvas and GDI, then it seems obvious that my app will benefit. 

It's clearly not worth much time to explore DPI compensation tricks. The impending need for HDPI will only add to that mess. It seams reasonable to think that within FlexCel there may be some sensible approach which depends only on the characters to be placed in the cells.

As to AutoFitCol, as I understand it, that will analyze the entire column, not a range of rows in that column. That makes it impractical for me, given the things I must do for the layout of the sheet.

I will send you a sheet in e-mail which may help to make clear why I am saying that AutoFitCol will not be a solution for me.


I got the file, I'll look at it later today.
But I just wanted to point out that AutoFitCol does allow you to analyze for a range of rows. Just use the overload that has a minrow and maxrow parameter.

Ah, excellent...  I will try that out, when I get a chance....  Still, unless it returns the value it likes, so I can analyze across a few row groups, and take the max, it still won't get me where I need to go.

There is another parameter: MinWidth which you can use to make sure that the width doesn't shrink.

To be clear, you have to use this overload:

   /// <summary>
    /// Autofits a range of columns so they adapt their width to show all the text inside. 
    /// </summary>
    /// <param name="col1">First column to Autofit.</param>
    /// <param name="col2">Last column to Autofit.</param>
    /// <param name="minRow">First row that will be used to autofit the column. If you want for example to autofit a column
    /// based only in the cells from row 3 to 10, you would use 3 as minRow.</param>
    /// <param name="maxRow">Last row that will be used to autofit the column. If you want for example to autofit a column
    /// based only in the cells from row 3 to 10, you would use 10 as maxRow.
    /// <br /><b>Specify any value &lt;=0 here to use all rows for the autofit.</b></param>
    /// <param name="ignoreStrings">When true, strings will not be considered for the autofit. Only numbers will.</param>
    /// <param name="adjustment">You will normally want to set this parameter to 1, which means that autofit will be made with standard measurements.
    /// If you set it to for example 1.1, then columns will be adjusted to 110% percent of what their calculated width was. 
    /// Use this parameter to fine-tune autofiting, if for example columns are too small when opening the file in Excel.</param>
    /// <param name="adjustmentFixed">You will normally set this parameter to 0, which means standard autofit. If you set it to a value, the column will be
    /// made larger by that amount from the calculated autofit. Different from the "adjustment" parameter, this parameter adds a fixed size to the column
    /// and not a percentage. The final size of the column will be:  FinalSize = CalulatedAutoFit * adjustment + adjusmentFixed</param>
    /// <param name="minWidth">Minimum final width for the column to autofit. If the calculated value is less than minWidth, column size will be set to minWidth.
    /// <br/>A negative value on minWidth means the column size will be no smaller than the original width.</param>
    /// <param name="maxWidth">Maximum final width for the column to autofit. If the calculated value is more than maxWidth, column size will be set to maxWidth.
    /// <br/>maxWidth = 0 means no maxWidth.
    /// <br/>A negative value on maxWidth means the column size will be no bigger than the original width.
    /// </param>
    /// <param name="autofitMerged">Specifies which column in a merged cell using more than one column will be used to autofit the merged cell.
    /// If you don't specify this parameter, it will be the last column.</param>
    procedure AutofitCol(const col1: Int32; const col2: Int32; const minRow: Int32; const maxRow: Int32; const ignoreStrings: Boolean; const adjustment: RealNumber; const adjustmentFixed: Int32; const minWidth: Int32; const maxWidth: Int32; const autofitMerged: TAutofitMerged); overload; virtual; abstract;

You can call it many times for different blocks of rows, keeping MinWidth = -1. From the doc above:
   /// <param name="minWidth">Minimum final width for the column to autofit. If the calculated value is less than minWidth, column size will be set to minWidth.
    /// <br/>A negative value on minWidth means the column size will be no smaller than the original width.</param>

So this way you ensure that if no columns are wider than the last autofit, the column width won't change.

I ma studying the possibility to include a MeasureString too which might make the conversion simpler for you: What this should do is the same as AutoFit, but return the value instead of setting the column width. The only thing I see is that it might be a little less efficient: When you autofit a block of rows we setup a font cache, a gdi+ canvas, etc once, and then use it to measure all the strings. With a MeasureString method we would have to setup the graphics each time you call it. But I am not sure that the actual times will matter much. I will investigate.

OK, I need to consider how this might work out in my code. I would have thought the AutoFitCol less efficient than a MeasureString method, but if is likely to be the other way around, then I will have to see whether I can make this approach serve. Thanks!

OK, about autofitMerged: I have a frequent need to merge two or more cells to make a category header float above column headers. And add the complication that depending on user-selected options, the category header may be above only one column, not two or more. From what you wrote in the documentation, it is not clear to me how to achieve what I want. If columns 11 and 12 are both present, and each is too narrow by itself to contain the category header, but the combined width is sufficient, then I do not want the category header to contribute to the autofit. However, if only either column 11 or 12 is present, then it will need to be wide enough to contain the category header.

Further, if I am understanding correctly the parameters used, then to effectively use minWidth, I will need to set minWidth small on the first call, but negative on subsequent calls on the same page, so that I can call AutoFitCol on a rectangular block of sells and get it to either make no change or to only increase width. Is that correct?

Adrian, I have it working as need now, with AutoFitCol. Thanks!

About minwidth: Indeed you would first call it with minWidth = 0, then with a negative value. The first time it will autofit to the block, next times it will only increase the autofit if needed, but never decrease it.

About merged cells: I am not sure if I fully undestand the situation. The main issue with autofitting merged cells is when autofitting rows, not columns, because Excel doesn't autofit them, and even if you do but keep heights automatic, Excel will resize the row back to 1 line when you open the file. This is also the reason we have a section in UsingFlexCelAPI.pdf about autofitting merged cells.

In you case, I assume you know if the header will take 11 and 12, or only 11, or only 12. 
But in the case you have both 11 and 12, which one of them you would like to grow if the combined width isn't enough?  If you want to make 12 wider, then you should autofit column 11 with TAutoFitMergef.OnLastCell. Something like:

           xls := TXlsFile.Create(1, true);
            xls.SetCellValue(1, 1, 'this is some long text to try autofit in a merged cell');
            xls.MergeCells(1, 1, 1, 2);
            xls.AutofitCol(1, 1, false, 1.1, 0, 0, 0, TAutofitMerged.OnLastCell);

IF you prefer to make column 11 wider, use OnFirstCell. And if you want both 11 and 12 to grow the same, use Balanced.

Now, if the header is only in 12 or only in 11, then just autofit that column with the row of the header.

There are layers of confusion here, and when I got into it, I realized that it was probably not an issue, since I only merge cells if I have both 11 and 12. So in practice, for the way I am laying things out, it is not an issue.

As to row merge, I don't use is, and should not need to, so that also should pose no problem.