Excel formatting dimensions

[Sergiy Radyakin]

Dear All,

I am trying to make the Excel file produced with Stata to look exactly as the prototype supplied by the client.
I am having troubles with specifying row heights.
Consider the following [example] code:

clear all
version 17.0
mata
   b = xl()
   b.create_book("C:\temp\test.xlsx","Test","xlsx")
   b.set_sheet("Test")
   b.set_row_height(1,1,36)      /* !?!? */
   b.set_column_width(1,1,128)
   b.close_book()
 end

The resulting row height is very different from the supplied value 36, it is 24:

This is being executed in Stata/MP 17.0 for Windows (64-bit x86-64).

I don't notice such a big discrepancy for the column width, though, Excel reports the width of the column A above as 128.09.

I wonder if there is any bug in the functioning of the set_row_height() method in mata? or if perhaps some other units are used to specify the row height?

I've noticed also that the ratio of 36 to 24 is suspiciously close to the zoom setting for my display, which should not matter in theory, but just in case:

Is this a known problem that has been fixed? Is it reproducible on another machine/platform? With a different version of MS Office?

Any hints on how to achieve precise setting of the dimensions (widths and heights) in Excel are much appreciated.

Thank you and best regards, Sergiy

[Ali Atia]

I can reproduce your problem with Excel 365 on Windows 10 Pro 64-bit using Stata SE 16.1. I tried a range of different numbers, and found that the resulting row height is always multiplied by two-thirds and the column width has 0.09 added to it. Changing the scaling setting from 150% to 100% and restarting Stata does not fix the problem. For a temporary workaround, this code produces your desired results:

clear all
mata
   b = xl()
   b.create_book("test.xlsx","Test","xlsx")
   b.set_sheet("Test")
   b.set_row_height(1,1,<desired_row_height>/(2/3))      /* !?!? */
   b.set_column_width(1,1,<desired_column_width>-.09)
   b.close_book()
 end

But this seems like a bug which should be reported to StataCorp.

[William Lisowski]

I got 48 instead of 24. Stata SE 17.0 for Mac on macOS Monterey 12.2 with Excel 16.57. But like you, the column width was very close to the column width requested.

[Sergiy Radyakin]

Dear Ali Atia ,

1. thank you very much for confirming the problem.
2. Thank you for suggesting the workaround. So far I have ignored the minor differences in column widths, and ended up inflating the row heights by 1.5 to match the desired height after numerous trials. But I had no clue of where this coefficient is coming from, why it is equal to this particular value, and why it applies only to heights, and not to widths.
3. Your setup is very similar to mine. Perhaps someone having a newer version of Stata installed could replicate the issue? Or try it on a different platform (other than Windows)?

Since my code contains hardcoded constants, I want to be sure that the output would be the same regardless of the platform/version (of Stata and/or Office).

If this behavior is confirmed as a bug, what would be the recommendation for writing the code in a way that it continues to produce the same output when the bug is eventually fixed? (afaik the -version- statements do not reinstate buggy behavior).

Thank you, Sergiy