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,995 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.
139 questions
{count} votes

15 answers

Sort by: Most helpful
  1. Fabio Almeida 1 Reputation point
    2021-02-22T17:53:28.023+00:00

    As we need to draw the table in a CAD application, given a font height in points, we need to calculate the row height in points properly, in order mimic exactly as it is seen in Excel.

    That's why we need the algorithm that transform a given font height in points, into a row height in points.

    In summary, given a font name and height in points, what is the algorithm that calculates row height in points?

    Example: Calibri 11 height into 15 row height.

    At first, we are tempted to deduce that there are 3 points as margins + 1 point. But this factor is not linear as we change font height, and worse, it changes depending on the font, for the same height.

    We appreciate you patience in helping us. We are looking forward for this algorithm. My deadline is so close!


  2. Fabio Almeida 1 Reputation point
    2021-02-23T00:00:35.777+00:00

    Thank you again, but that is not the answer for my previous question.

    We simply need the algorithm, or the logic, or the explanation.

    Let us focus on the "Why". It may help us.

    Why Calibri 11 points height is 15 points row height?


  3. Fabio Almeida 1 Reputation point
    2021-02-23T18:37:39.74+00:00

    We are getting close!!! Thank you!

    If you double-click the row header (not column header) the row height is automatically adjusted to a standard height, according to the font height plus some undocumented factor or formula.

    We need that formula.


  4. Fabio Almeida 1 Reputation point
    2021-03-01T13:12:47.067+00:00

    Thank you again for your patience and willing to help!

    As mentioned, we need to draw the table in a CAD platform. As we need to replicate exactly what we see in Excel, we came to an imprecise formula: (font line-spacing * 1.05) + 1 pixel.

    It is evident that there is some margin or padding above and below the text. That value varies according to font size and font name, and it is not linear.

    So we are looking for a precise algorithm. Is there a way to setup a meeting with you or an Excel software engineer, so I could present my need and get this answer?

    Thank you again. Looking forward to your answer.


  5. Fabio Almeida 1 Reputation point
    2021-03-01T18:07:52.877+00:00

    That is not my question. We are not using Excel. We are developing an Excel compatible application, so we need to understand the algorithm to calculate row height, given a font name and its size.

    See the picture. You will notice that there is a certain amount of space above the text.
    Could someone tell us, based on font metrics, what parameter is involved and what is the equation?

    73094-font-metrics.png

    73107-excelrow.png

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.