Excel row height logic calculation

Fabio Almeida 1 Reputation point
2021-02-03T19:20:57.723+00:00

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!

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,573 questions
Office Open Specifications
Office Open Specifications
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Open Specifications: Technical documents for protocols, computer languages, standards support, and data portability. The goal with Open Specifications is to help developers open new opportunities to interoperate with Windows, SQL, Office, and SharePoint.
127 questions

15 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Hung-Chun Yu 976 Reputation points Microsoft Employee
    2021-03-02T10:18:12.987+00:00
    0 comments
  2. Fabio Almeida 1 Reputation point
    2021-03-02T12:50:15.757+00:00

    Thank you again.
    Actually we need the algorithm, not a specific technology, for row height.

    For example, we came up with a formula that came extremely close, with a difference of 1 or 2 pixels, whatever font and size is chosen:

    double factor = emHeight / baseline;
    double rowHeight = lineSpacing * factor + 0.75;

    We apply that factor to lineSpacing and add 0.75 points (= 1 pixel) for the border.

    As we came so close, I only would like to confirm if there is another better and more accurate logic.

  3. Castorix31 82,751 Reputation points
    2021-03-02T14:26:32.513+00:00

    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)

    0 comments
  4. wdh_it 1 Reputation point
    2022-09-17T10:01:19.157+00:00

    Hi, this is very informative.
    There is a lot of technical subject matter.
    I was wondering about the algorithm for the row height in Excel, with subscripts, superscripts or a combination of subscripts/superscripts.

    I'm using a font editor, and trying to change settings such as Typo Ascender, Typo Descender, Win Ascent, Win Descent, etc.

    Can someone point me in the right direction.

    0 comments
  5. Hung-Chun Yu 976 Reputation points Microsoft Employee
    2022-09-28T17:43:12.98+00:00

    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://video2.skills-academy.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://video2.skills-academy.com/en-us/dotnet/desktop/winforms/advanced/how-to-obtain-font-metrics?view=netframeworkdesktop-4.8. 

    DirectWrite (DWrite) https://video2.skills-academy.com/en-us/windows/win32/directwrite/direct-write-portal  
  
DWriteCore overview https://video2.skills-academy.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

    0 comments