FlexCelPdfExport vertically compresses content

[Wilson_James]

I'm using FlexCel on a Mac with an M1 processor. When I produce a PDF using FlexCelPdfExport, the content is vertically compressed. To reproduce:

1. Create a new Excel file.
2. Set cell A1 size to 100 pixels by 100 pixels.
3. Set cell A1 background color to green. Observe that the cell is a square.
4. Save as test.xslx.
5. Run the following code (adjust path as required):

var xls = new FlexCel.XlsAdapter.XlsFile("/Users/jw/test.xlsx");
using (var pdf = new FlexCel.Render.FlexCelPdfExport(xls, true))
	pdf.Export("/Users/jw/test.pdf");

Open text.pdf and observe that the green rectangle is significantly wider than it is high. In my test, the aspect ratio was about 6/5.

Please advise.

[adrian]

Hi,
Can you verify that you have the "Normal" font installed in the mac? (If using a modern Excel that would be "Aptos", if not "Calibri").

The problem is: For row height, Excel uses a simple unit: a twip (1/20 of a point, which is 1/72 of an inch). See Understanding Excel measurement units | FlexCel Studio for the .NET Framework documentation

This being a multiple of inches, will be the same no matter the font you are using.
But, column widths are stored in the file as "how many 0's you can fit in a column in the 'normal' font", which, depends in the font you are using. (see again the link above)

If you don't have the normal font installed in the machine where you are generating the PDF, FlexCel will replace it with a different font, which has different metrics, and you will likely end up with a different measurement for the width, while the rows will stay the same size. This will lead to a different aspect ratio.

Note: Normal font is the font that appears if you click in "Cell styles"

Whatever you set there will be used for column and row headings, as base font for the workbook, and also used to measure how wide columns are. You can easily see that if you change the size of that font, the columns will grow wider (while still saying the same number).

Note that "Aptos" (the default font in newer Excel's) is a complex font to use, because ms isn't distributing it with Windows (at least up to where I know, I don't know if that changed now). See Cloud fonts | FlexCel Studio for the .NET Framework documentation

You need to either install aptos in the mac, or use a different "normal" font that is installed. Note that the pdf generated will have the font embedded, so it is not a problem for the pdf, but for the xlsx it is.

[Wilson_James]

I confirm that Aptos and Calibri are listed in Excel's drop-down list of fonts. Aptos Narrow is indicated as the font for the "Normal" cell style. Neither Aptos nor Calibri are listed in my Mac's Font Book, and they are not available in any of the non-Microsoft apps I checked. I changed the "Normal" font to Helvetica and produced the PDF again. This time the aspect ration is 1.05/1, which I can live with. I installed the Aptos font in Font Book and tried my earlier test with the same results, but I'm going to reboot (perhaps there's some sort of font registration that occurs during startup). I'll report the results.

[Wilson_James]

I did other tests setting the "Normal" font with the following aspect ratio results.

1. Aptos Narrow (the default): 1.08:1
2. Courier New: 1.16:1
3. Arial: 1.06:1
4. Times New Roman: 1.08:1
5. Helvetica: 1.05:1

[adrian]

Getting a 1.0 aspect ratio, given the limitations not really possible. Not even for Excel. I for example created a file in Excel for Windows with A1 = 100px x 100px:

If I save this file and open it in Excel for Mac, I get:

As you can see, the same file, has a different aspect ratio when opened in a mac (no FlexCel involved, only Excel). You can easily verify it. But it gets worse: If you change the zoom of the Windows Excel, you might get a different result. Which will be also different when you do a print preview (and will depend on the physical printer connected)

For example: In the screen (100% zoom), if I screenshot and actually measure the square, I get an aspect ratio of 1:

But if I do a print preview and measure, the width and height aren't the same (All with the same Windows Excel):

You can see some more discussion of the issue here: FlexCel API Developer Guide | FlexCel Studio for the .NET Framework documentation

And we have ways in FlexCel to fine-tune the aspect ratio so you could get a 1.0 aspect ratio: You can use HeightCorrection and WidthCorrection: ExcelFile.HeightCorrection Property | FlexCel Studio for the .NET Framework documentation

But as you might guess, if you "fix" HeightCorrect so it is ok in your machine, it will break in others. We calculated the defaul "Height/Width"CorrectValue so FlexCel mimics an average of about 20 different physical printers we tried. In all of them we printed a square, saw what Excel printed, and calculated the exact amount needed so the square would be a square. Then we averaged all values to get the correction we use: Being an average it isn't what any printer needed 100% exactly, but it is the one that approximates more 600dpi printers.

But I woudn't touch those corrections if not really needed: as said the default is the result of measuring a lot of real printers and getting what was best for most. You might fix it for some, but will break it for others.

By the way, to know if you are actually using aptos or not in the PDF, you can open the file in acrobat, press ctrl-D and look at the fonts tab:

If the font you want is not in the list, then the OS is likely not finding it.

[Wilson_James]

Thanks for all the explanation. The situation that prompted my original post was that my company's logo was vertically compressed in invoices (i.e., squished). See attached PDF with other logos that I put in Excel and then used FlexCelPdfExport to produce a PDF. While I have found FlexCel indispensable for over a decade, I cannot use it for PDF production that includes company logos.

logos.pdf (303.8 KB)

[adrian]

As discussed, there are 2 issues here:

1. A missing "Normal" font. This can lead to big distortions, because, while the row height is constant, the column width can change a lot if we use a substitute font. One thing you can do to mitigate this problem is to use FlexCelTrace (see https://doc.tmssoftware.com/flexcel/net/samples/csharp/netframework/api/handling-errors/index.html ) and warn the user if a font is missing. The other might be to use arial as normal font, as that (or the virtually identical helvetica) is normally available everywhere. Or you might embed the fonts you are using in your app (this can be kind of tricky as it is different for windows than for linux or macos, but if you are interested, I can send some example on how to embed the fonts)

My guess is that the file you sent has this first issue, as the logo looks too compressed?

3. Due to using the silly "how many 0s you can fit in a cell" as unit of measurement, it is not easy to get an exact 1.0 aspect ratio (not with FlexCel, and not with Excel only). How many 0s you can fit can change depending in the Font engine (mac for example draws characters a little bolder), the kerning, the resolution, font hinting, etc. But, this should be very small, and not normally be a problem. After all, millions of people use Excel everyday, and most are not even aware of the issue that the logos might have a little distorted aspect ratio.

To make the point more clear, I created a file in Excel, gave cell A1 a width and height of 1000 px, and exported the file to pdf both with Excel and FlexCel. Here are the results:
Excel:

Aspect ratio: 1.07
FlexCel:

Aspect ratio: 1.01

So we got a better aspect ratio than Excel (this is all in windows btw). And different from Excel, you could further make this aspect ratio even nearer 1 by changing Width/HeightCorrect in the machine you are running the app. The good thing is that the generated PDFs don't have this issue, so if you can generate it correctly, it will have a exact 1.0 ratio wherever.

I attach the files I used below, but note that you might get different numbers from me. (I am using 200% screen resolution here, or 192 dpi)

Book1_XL.pdf (17.1 KB)
book1_FL.pdf (10.6 KB)
Book1_original.xlsx (9.8 KB)

[Wilson_James]

Thanks again for more tests and explanation. As far as I can tell, my present issue with FlexCelPdfExport accurately rendering logos is explainable but not correctable. I'm at a conference through Friday with little time to test, but I would be happy to do a Zoom session as early as late next week to test various scenarios real-time and then write up guidance for other developers. I assume you can access my email if you want to contact me directly. (By the way, I have xScope for Mac, which enables quick and precise size measurements.)