I am accumulating the column widths (in pixels) needed as I fill the cells. I then need to set the column widths, after the sheet has been filled.
In the help, I see that TFlxSetColWidth(int32, int32) needs the column index, and a width in Excel internal units. It tells me:
Use TFlxConsts.ColMult to convert the internal
units to pixels.
I do not find the ColMult member in that class. Using ApiMate, I see that it suggests using TFlxSetColWidth(int32, int32, int32), where the first two parameters are column numbers, and the third is again Excel internal units. ApiMate, though, simply uses a multiplier of 256. However, this comes out very wrong. Is SetColWidth with that multiplier expecting to use a width in characters, rather than Excel internal units?
Use TExcelMetrics.ColMult instead. ColMult was originallly in TFlxConts because it was just a constant (256.0 / 7) but it was deprecated ages ago for a method which knows the file where it is working on (because if you change the default font, colmult changes). Actually it never made it to Delphi (TFlxConsts.ColMult is only in FlexCel.NET, and deprecated). But it seems I forgot to update some docs: I have updated them now.
Note that the result of multiplying by ColMult is always approx, because Excel doesn't have a real definition of what a "pixel" is. I am actually working in a blog post to explain the new HDPI support, and how Excel shows different column widths depending in the dpi settings in Windows.
For example, this is what Excel shows in column width with 100% dpi, in an empty file:
Thanks. To be clear, With TExcelMetrics.ColMult, I should be multiplying the number of characters?
Alternately, can I autosize the column? Or is that either impossible, or worse?
I'm actually surprised that in the two images you linked, the difference is that small. I'm accustomed to making adjustments on an empirical basis, as the measurement functions seem always to be a bit off. But a good sized sheet will let me see fairly quickly whether my adjustment factor is sufficient.
Mos of the problem comes with the "internal units" excel uses to store columns. The definition is: "How many "0"s you can fit in the column in the normal-style font. So if you can write "00000000" in the column, the width in internal units is 8.
So, as fonts change with resolution, the actual size in pixels of "8 internal units" depends in the resolution of the device. But the images I posted are actually something much worse: In them, Excel is changing the internal units of the columns, not just the size in pixels (which of course also changes). And the only difference is the dpi.
But well, to answer the question, yes, you need to multiply or divide by ColMult to convert to and from pixels. From the docs: Multiply by this number to convert the width of a column from pixels to excel internal units.
So to convert from internal units to pixels you actually need to divide.
About autofit, yes, it is fully supported, with all the caveats we've discussed. There is a section in autofitting in the "API Developers guide" (pdf)
You need to use FlexCel.Render (because the autofit engine uses the rendering engine to calculate the actual width of the text), and then call somehting like
xls.AutofitCol(col1, col2, false, 1.2);
To autofit all columns between col1 and col2.
1.2 is "safety margin" to account for the differences we have discussed. Normally leave it at 1.2 or 1.3
after rereading, I am not sure if I really answered the question: You need to multiply the pixels taken by the string you want by TExcelMetrics.ColMult.
You can't just multiply the number of characters, because most fonts are proportional so an "I" will take less space than an "M". Even worse, you might have different fonts inside the cell. So you need to calculate how many pixels the string would be if rendered in the font of the cell (and the fonts inside), then multiply that number by ColMult. Or use autofit, which does all that stuff automatically.
I found my way to the method you clarified in your last reply. It seems to work well enough, and so far, a multiplier of 1.1 seems adequate.
I have sent you an e-mail with a sample spreadsheet which gives evidence of a problem inserting a logo in the upper left. I give details and some code in the e-mail. The oddity is that on two separate worksheets, it shows different widths, and I have been unable to account for the discrepancy.
