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

Thank you for your answer. Actually, the question is a tricky one... What we really need is a formula to convert font height into row height.

For example:

• Calibri:

Calibri 11 points = 15 row height points.
What is the formula to convert 11 into 15? (in advance I tell you it is not simply a factor. It would be awesome if it were...)

Calibri 36 points = 46.50 row height points.

• Arial:

Arial 11 points = 14.25 row height points. (please notice you need to change "Normal" style to Arial because the height would be lower than the default normal style height)

Arial 36 points = 44.25 row height points

So I hope the question is clearer now. We need a formula, or the logic, behind font height into row height calculation.

Answer 3

Thank you for that. Actually we have tried this also with no success.

This approach, although ugly and imprecise, gave us the closest result:

/// <summary>
/// Given a font name and size, estimates cell height
/// Adds a factor for external leading. Not precise for some fonts or sizes.
/// Points units
/// </summary>
/// <param name="font"></param>
/// <returns></returns>
private static double EstimateCellHeightPoints(this Font font) {
double fontSize = font.FontSize?.Val ?? 0.0;
System.Windows.Media.Typeface typeface = font.ToMediaTypeFace();
double height = fontSize.ToPixels() * typeface.FontFamily.LineSpacing;
height = Math.Round(height * 1.05 + 1).ToPoints();
return height;
}

(Note we developed some extension methods)

The following attempts all failed, using various options and parameters for each one:

System.Drawing.MeasureString

System.Drawing.MeasureCharacterRanges

System.Windows.Media.FormattedText

System.Windows.Forms.TextRenderer.MeasureText

glyphTypeface

and many others ...

So, again, we kindly ask for the formula behind row height calculation. It seems to be a hidden secret I feel we are about to discover. Looking forward to your answer!

Given a font name and its height, what is the formula or logic to calculate an Excel row height.

Answer 4

I appreciate you effort in helping, but we have tried this also in the past.

Let us isolate the question:

What is the formula that translates 11 (for Calibri font height) into 15 (for row height)?

Answer 5

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