Excel row height logic calculation

Question

Using C# and OpenXML, given a font name and its height, I need to calculate the exact row height in pixels in order to draw an Excel spreadsheet in a CAD software.
What I have done so far: I have been able to accurately calculate a column width given the font in the "Normal" style. Column width is given in number of characters and can be translated to pixels according to OpenXML documentation. That's OK.
My only problem is how to calculate row height, which is given in points. My issue so far is to find a precise way, or the logic, behind its calculation. Given a font and its height, how much should be added as margins or paddings so I can calculate the exact how height? Is it based on font line-spacing, height or any other parameter? How much should be added? Is there any factor that should be multiplied? What is the formula behind it?
This answer is no where. I would much appreciate to know the logic behind row height calculation so I can get a precise value in pixels. Thanks!

Answer 1

Hi Fabio

In Excel worksheets, the default row height is determined by the font size. As you increase or decrease the font size for a specific row(s), Excel automatically makes the row taller or shorter.

The height and width attributes in the file are defined as part of ISO 29500. Section 18.3.1.13 covers columns and includes the formula mentioned below. Section 18.3.1.73 describes rows, and there is no formula at play here: “ht (Row Height) Row height measured in point size. There is no margin padding on row height.”

With the default font Calibri 11, the row height is 12.75 points, which is approximately 1/6 inch or 0.4 cm. In practice, with Excel 2013 and later, row height varies depending on the display scaling (DPI) from 15 points on a 100% dpi to 14.3 points on a 200% dpi.

Here is a formula converting Row Height from Points to Pixel (assuming on a machine DPI is set to 96 Dot Per Inch).

DPI = 96 //assuming Dot Per Inch at 96)
PPI = 72 //points per inch
Pixels = Points/PPI * DPI //Row Height in Points divided by PPI times DPI

Here is a Stack Overflow Page on how to determine User’s DPI Settings https://stackoverflow.com/questions/21165986/is-it-possible-to-react-to-users-dpi-settings-in-a-net-application

Let me know if this answered your question.

Hung-Chun Yu
Microsoft Open Specifications

Answer 2

Hi Fabio

Here is the answer I got from the Product group, "For interop purposes though, ISO 29500 specifies only the final resulting ‘ht’ value is written. So there’s no expectation or requirement that all file produces create files with the same values."

Hung-Chun Yu
Microsoft Open Specifications

Answer 3

Hi Fabio

For Font Metrics, please refer to How to: Obtain Font Metrics https://learn.microsoft.com/en-us/dotnet/desktop/winforms/advanced/how-to-obtain-font-metrics?view=netframeworkdesktop-4.8.

DirectWrite (DWrite) https://learn.microsoft.com/en-us/windows/win32/directwrite/direct-write-portal
DWriteCore overview https://learn.microsoft.com/en-us/windows/win32/directwrite/dwritecore-overview

Answer 4

so we need to understand the algorithm to calculate row height, given a font name and its size.

You can find the formula in old KB74299 : INFO: Calculating The Logical Height and Point Size of a Font

(hdc is the Device Context handle where the font has been selected with SelectObject)

Answer 5

Hi @wdh_it

"For interop purposes though, ISO 29500 specifies only the final resulting ‘ht’ value is written. So there’s no expectation or requirement that all file produces create files with the same values." Hence, algorithm will not be described in Microsoft Open Specifications.

Applications can render in a way that is visually appealing or meets the requirements of their application. This does not include mimicking the design choices of another application. Rendering is left for the consuming application as a choice and not prescribed by the standard.”

Following are some resources that should help you get started

.net function called system.drawing.graphics.measurestring https://learn.microsoft.com/en-us/dotnet/api/system.drawing.graphics.measurestring?view=dotnet-plat-ext-5.0

public static Point GetTextSize(Graphics graphics, Font graphicsFont, String text, Int32 width, StringFormat format)  
{  
Point textSize;  
SizeF tempSizeF;  
tempSizeF = graphics.MeasureString(text, graphicsFont, width, format);  
textSize = new Point();  
textSize.X = (int) tempSizeF.Width;  
textSize.Y = (int) tempSizeF.Height;  
return textSize;  
}  
  
Source - https://csharp.hotexamples.com/examples/-/Graphics/MeasureString/php-graphics-measurestring-method-examples.html  

For Font Metrics, please refer to How to: Obtain Font Metrics https://learn.microsoft.com/en-us/dotnet/desktop/winforms/advanced/how-to-obtain-font-metrics?view=netframeworkdesktop-4.8.

DirectWrite (DWrite) https://learn.microsoft.com/en-us/windows/win32/directwrite/direct-write-portal  
  
DWriteCore overview https://learn.microsoft.com/en-us/windows/win32/directwrite/dwritecore-overview  

As @Castorix31 mentioned, you can find the formula in old KB74299 : INFO: Calculating The Logical Height and Point Size of a Font
(hdc is the Device Context handle where the font has been selected with SelectObject)

HungChun Yu
Microsoft Open Specifications