How Microsoft Excel works with row heights

Sometimes I get bored and, armed with a debugger, I start digging in different programs. This time, my choice fell on Excel and there was a desire to understand how it operates with the heights of the rows, what it stores, how it considers the height of the range of cells, etc. I parsed Excel 2010 (excel.exe, 32bit, version 14.0.4756.1000, SHA1 a805cf60a5542f21001b0ea5d142d1cd0ee00b28).

If you refer to the VBA documentation for Microsoft Office, you can see that the height of the series can somehow be obtained through two properties:

And if to glance also here: Excel specifications and limits. You may find that the maximum row height is 409 points. Unfortunately, this is not the only case where Microsoft’s official documents are a little cunning. In fact, in Excel code, the maximum row height is defined as 2047 pixels, which will be 1535.25 in points. And the maximum font size is 409.55 points. It’s impossible to get a row of such a huge height by simple assignment in VBA / Interop, but you can take a row, set its first cell with the font of Cambria Math, and set the font size to 409.55 points. Then Excel will calculate the height of the row based on the cell format by its tricky algorithm, get a number exceeding 2047 pixels (take a word) and set the maximum possible height of the row itself. If you ask the height of this series through the UI, then Excel sovret that the height of 409.5 points, but if you request the height of the series through VBA, you get honest 1535.25 points, which equals 2047 pixels. True, after saving the document, the height will still be reset to 409.5 points. This manipulation can be seen here on this video:http://recordit.co/ivnFEsELLI

I knowingly mentioned pixels in the previous paragraph. Excel actually stores and calculates the cell sizes in integers (it generally does everything as much as possible in integers). Most often these are pixels multiplied by a certain factor. Interestingly, Excel stores the appearance scale as an ordinary fraction, for example, the 75% scale will be stored as two numbers 3 and 4. And when you need to display a row, Excel will take the row height as an integer number of pixels, multiply by 3 and divide by 4. But to perform this operation, it will be at the very end of this effect, that everything is considered in fractional numbers. To make sure of this, write the following code in VBA:

``````w.Rows(1).RowHeight = 75.375
Debug.Print w.Rows(1).Height``````

VBA will give 75, because 75.375 pixels will be 100.5 pixels, and Excel cannot afford it and will drop the fractional part up to 100 pixels. When VBA requests the height of the row in points, Excel will honestly translate 100 pixels into points and return 75.

In principle, we’ve got to write a class in C # that will describe the information about the height of the series:

``````classRowHeightInfo {
publicushort Value { get; set; } //высота ряда в целых пикселях, умноженная на 4.publicushort Flags { get; set; } //дополнительные флаги
}``````

You still have to take my word for it, but in Excel, the height of the row is stored that way. Ie, if it is set that the row height is 75 points, it will be 100 in pixels, then 400 will be stored in Value. I don’t figure out what all the bits mean in Flags (it’s difficult and time to figure out the flag values), but I know for sure that 0x4000 is set for rows whose height is set manually, and 0x2000 is set for hidden rows. In general, for visible rows with a manually set height, Flags most often equals 0x4005, and for rows whose height is calculated based on the formatting, Flags equals either 0xA or 0x800E.

Now, in principle, you can look at the method from excel.exe, which returns the height of the series by its index (thanks to HexRays for the beautiful code):

``````int __userpurge GetRowHeight@<eax>(signedint rowIndex@<edx>, SheetLayoutInfo *sheetLayoutInfo@<esi>, bool flag)
{
RowHeightInfo *rowHeightInfo; // eaxint result; // ecxif ( sheetLayoutInfo->dword1A0 )
return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
if ( rowIndex < sheetLayoutInfo->MinRowIndexNonDefault )
return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
if ( rowIndex >= sheetLayoutInfo->MaxRowIndexNonDefault )
return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
rowHeightInfo = GetRowHeightCore(sheetLayoutInfo, rowIndex);
if ( !rowHeightInfo )
return sheetLayoutInfo->defaultFullRowHeightMul4 | (~(sheetLayoutInfo->defaultRowDelta2 >> 14 << 15) & 0x8000);
result = 0;
if ( flag || !(rowHeightInfo->Flags & 0x2000) )
result = rowHeightInfo->Value;
if ( !(rowHeightInfo->Flags & 0x4000) )
result |= 0x8000u;
return result;
}``````

What is dword1A0 I did not find out, because I could not find the place where this flag is set :(
What is defaultRowDelta2 for me is still a mystery. When excel calculates row height based on the format, it represents it as the sum of two numbers. defaultRowDelta2 is the second number of this sum for the standard height The value of the flag parameter is also mysterious, because everywhere I saw a call to this method the flag was passed false.
This method also contains the class SheetLayoutInfo. I called it that way because it contains a lot of information about the appearance of the sheet SheetLayoutInfo has such fields as :

• DefaultFullRowHeightMul4 - standard row height;
• MinRowIndexNonDefault - the index of the first row, whose height differs from the standard;
• MaxRowIndexNonDefault - the index of the row following the last one, whose height differs from the standard one;
• DefaultRowDelta2 is that part of the sum of the standard row height.
• GroupIndexDelta - more on that later

In principle, the logic of this method is quite clear:

1. If the row index is less than the first with a non-standard height, then we return the standard one;
2. If the row index is greater than the last with a non-standard height, then we return the standard one;
3. Otherwise, we get a rowHeightInfo object for the row from the GetRowHeightCore method;
4. If rowHeightInfo == null returns the standard row height;
5. There is magic with flags, but in general we return what is in rowHeightInfo.Value and set the 16th bit in the response if the row height was not set manually.

If you rewrite this code in C #, you get something like the following:

``````constulong HiddenRowMask = 0x2000;
publicstaticushortGetRowHeight(int rowIndex, SheetLayoutInfo sheetLayoutInfo) {
ushort defaultHeight = (ushort) (sheetLayoutInfo.DefaultFullRowHeightMul4 | (~(sheetLayoutInfo.DefaultRowDelta2 >> 14 << 15) & DefaultHeightMask));
if (rowIndex < sheetLayoutInfo.MinRowIndexNonDefault)
return defaultHeight;
if (rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
return defaultHeight;
RowHeightInfo rowHeightInfo = GetRowHeightCore(sheetLayoutInfo, rowIndex);
if (rowHeightInfo == null)
return defaultHeight;
ushort result = 0;
if ((rowHeightInfo.Flags & HiddenRowMask) == 0)
result = rowHeightInfo.Value;
if ((rowHeightInfo.Flags & CustomHeightMask) == 0)
return result;
}``````

Now you can see what's going on inside GetRowHeightCore:

``````RowHeightInfo *__fastcall GetRowHeightCore(SheetLayoutInfo *sheetLayoutInfo, signedint rowIndex){
RowHeightInfo *result; // eax
RowsGroupInfo *rowsGroupInfo; // ecxint rowInfoIndex; // edx
result = 0;
if ( rowIndex < sheetLayoutInfo->MinRowIndexNonDefault || rowIndex >= sheetLayoutInfo->MaxRowIndexNonDefault )
return result;
rowsGroupInfo = sheetLayoutInfo->RowsGroups[sheetLayoutInfo-GroupIndexDelta + (rowIndex >> 4)];
result = 0;
if ( !rowsGroupInfo )
return result;
rowInfoIndex = rowsGroupInfo->Indices[rowIndex & 0xF];
if ( rowInfoIndex )
result = (rowsGroupInfo + 8 * (rowInfoIndex + rowsGroupInfo->wordBA + rowsGroupInfo->wordBC - rowsGroupInfo->wordB8));
return result;
}``````

1. Again, at the beginning, Excel checks if the index of the series is among the rows with altered height and if not, returns null.
2. Finds the desired group of series, if there is no such group, then returns null.
3. Gets the index of a row in a group.
4. Then, by the index of the series, it finds the required object of the RowHeightInfo class. wordBA, wordBC, wordB8 - some kind of constant. They change only with the story. In principle, they do not affect the understanding of the algorithm.

Here it is necessary to deviate from the topic and tell more about RowsGroupInfo. Excel stores RowHeightInfo in groups of 16 pieces, where the i-th group, represented by the RowsGroupInfo class, will store information about rows from i × 16 to i × 16 + 15 inclusive.

But information about the height of rows in RowsGroupInfo is stored in a somewhat unusual way. Most likely because of the need to maintain history in Excel.

There are three important fields in RowsGroupInfo: Indices, HeightInfos, and RowsCount, the second one is not visible in the code above (it should be in this line: (rowsGroupInfo + 8 × (...)), since rowInfoIndex can take very different values I have seen even more than 1000 and I have no idea how to set up such a structure in IDA. The RowsCount field is not found in the code above, but that’s how many really non-standard rows are stored in the group.
In addition, SheetLayoutInfo has GroupIndexDelta - the difference between real the group index and the index of the first group with a modified row height.

The Indices field stores RowHeightInfo offsets for each index of a row within a group. They are stored there in order, but in HeightInfos RowHeightInfo is already stored in the order of change.

Suppose we have a new blank sheet and we somehow changed the height of row number 23. This row lies in the second group of 16 rows, then:

1. Excel will determine the group index for this series. In the current case, the index will be equal to 1 and will change GroupIndexDelta = -1.
2. Create a RowsGroupInfo class object for the group of rows and put it in sheetLayoutInfo-> RowsGroups with index 0 (sheetLayoutInfo-> GroupIndexDelta + 1);
3. In RowsGroupInfo Excel will allocate memory for 16 4-byte Indices, for RowsCount, wordBA, wordBC and wordB8, etc ..;
4. Then Excel calculates the index of the series in the group through the operation of bitwise AND (this is much faster than taking the remainder of the division): rowIndex & 0xF. The required index in the group will be: 23 & 0xF = 7;
5. After that, Excel gets the offset for the index 7: offset = Indices [7]. If offset = 0, then Excel allocates 8 bytes at the end of the RowsGroupInto, increases the RowsCount by one and writes the new offset to the Indices [7]. In any case, at the end of Excel will write down the information about the new height of the series and the flags by offset in RowsGroupInfo.

The C # class RowsGroupInfo class itself would look like this:

``````classRowsGroupInfo {
publicint[] Indices { get; }
public List<RowHeightInfo> HeightInfos { get; }
publicRowsGroupInfo() {
Indices = newint[SheetLayoutInfo.MaxRowsCountInGroup];
HeightInfos = new List<RowHeightInfo>();
for (int i = 0; i < SheetLayoutInfo.MaxRowsCountInGroup; i++) {
Indices[i] = -1;
}
}
}``````

The GetRowHeightCore method would look like this:

``````static RowHeightInfo GetRowHeightCore(SheetLayoutInfo sheetLayoutInfo, int rowIndex) {
if (rowIndex < sheetLayoutInfo.MinRowIndexNonDefault || rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
returnnull;
RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[sheetLayoutInfo.GroupIndexDelta + (rowIndex >> 4)];
if (rowsGroupInfo == null)
returnnull;
int rowInfoIndex = rowsGroupInfo.Indices[rowIndex & 0xF];
return rowInfoIndex != -1 ? rowsGroupInfo.HeightInfos[rowInfoIndex] : null;
}``````

And that’s what SetRowHeight would look like (I didn’t list its excel.exe code):

``````publicstaticvoidSetRowHeight(int rowIndex, ushort newRowHeight, ushort flags, SheetLayoutInfo sheetLayoutInfo) {
sheetLayoutInfo.MaxRowIndexNonDefault = Math.Max(sheetLayoutInfo.MaxRowIndexNonDefault, rowIndex + 1);
sheetLayoutInfo.MinRowIndexNonDefault = Math.Min(sheetLayoutInfo.MinRowIndexNonDefault, rowIndex);
int realGroupIndex = rowIndex >> 4;
if (sheetLayoutInfo.RowsGroups.Count == 0) {
sheetLayoutInfo.GroupIndexDelta = -realGroupIndex;
}
elseif (sheetLayoutInfo.GroupIndexDelta + realGroupIndex < 0) {
int bucketSize = -(sheetLayoutInfo.GroupIndexDelta + realGroupIndex);
sheetLayoutInfo.RowsGroups.InsertRange(0, new RowsGroupInfo[bucketSize]);
sheetLayoutInfo.GroupIndexDelta = -realGroupIndex;
}
elseif (sheetLayoutInfo.GroupIndexDelta + realGroupIndex >= sheetLayoutInfo.RowsGroups.Count) {
int bucketSize = sheetLayoutInfo.GroupIndexDelta + realGroupIndex - sheetLayoutInfo.RowsGroups.Count + 1;
}
RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[sheetLayoutInfo.GroupIndexDelta + realGroupIndex];
if (rowsGroupInfo == null) {
rowsGroupInfo = new RowsGroupInfo();
sheetLayoutInfo.RowsGroups[sheetLayoutInfo.GroupIndexDelta + realGroupIndex] = rowsGroupInfo;
}
int rowInfoIndex = rowsGroupInfo.Indices[rowIndex & 0xF];
RowHeightInfo rowHeightInfo;
if (rowInfoIndex == -1) {
rowHeightInfo = new RowHeightInfo();
rowsGroupInfo.Indices[rowIndex & 0xF] = rowsGroupInfo.HeightInfos.Count - 1;
}
else {
rowHeightInfo = rowsGroupInfo.HeightInfos[rowInfoIndex];
}
rowHeightInfo.Value = newRowHeight;
rowHeightInfo.Flags = flags;
}``````

Some practice

After examining the above example of changing the height of row 23 in Excel memory, there will be something like this (I set the row 23 to the height of 75 points):

sheetLayoutInfo
• DefaultFullRowHeightMul4 = 80
• DefaultRowDelta2 = 5
• MaxRowIndexNonDefault = 24
• MinRowIndexNonDefault = 23
• GroupIndexDelta = -1
• RowsGroups Count = 1
• [0] RowsGroupInfo
• HeightInfos Count = 1
• [0] RowHeightInfo
• Flags = 0x4005
• Value = 100
• Indices
• [0] = -1
• [1] = -1
• [2] = -1
• [3] = -1
• [4] = -1
• [5] = -1
• [6] = -1
• [7] = 0
• [8] = -1
• [9] = -1
• [10] = -1
• [11] = -1
• [12] = -1
• [13] = -1
• [14] = -1
• [15] = -1

Here and in the next example, I will lay out a schematic representation of how the data in Excel memory looks, made in Visual Studio from self-written classes, because the direct memory dump is not very informative.
Now let's try to hide row 23. To do this, set the 0x2000 bit of the Flags. We will change the memory to live. The result can be seen on this video: http://recordit.co/79vYIbwbzB .
If you hide any rows, Excel does the same.
Now we will set the height of the row not explicitly, but through the cell format. Let cell A20 have a font of 40 points in height, then the height of the cell in points will be 45.75 and in Excel memory there will be something like this:
sheetLayoutInfo
• DefaultFullRowHeightMul4 = 80
• DefaultRowDelta2 = 5
• MaxRowIndexNonDefault = 24
• MinRowIndexNonDefault = 20
• GroupIndexDelta = -1
• RowsGroups Count = 1
• [0] RowsGroupInfo
• HeightInfos Count = 2
• [0] RowHeightInfo
• Flags = 0x4005
• Value = 100
• [1] RowHeightInfo
• Flags = 0x800E
• Value = 244
• Indices
• [0] = -1
• [1] = -1
• [2] = -1
• [3] = -1
• [4] = 1
• [5] = -1
• [6] = -1
• [7] = 0
• [8] = -1
• [9] = -1
• [10] = -1
• [11] = -1
• [12] = -1
• [13] = -1
• [14] = -1
• [15] = -1

You may notice that Excel always stores the height of the row, if it is not standard. Even if the height is not specified explicitly, but is calculated based on the contents of the cells or the format, Excel will still calculate it once and put the result in the appropriate group.

Understanding row insertion / deletion

It would be interesting to make out what happens when inserting / deleting rows. The corresponding code in excel.exe is easy to find, but there was no desire to disassemble it, you can take a look at some of it:

sub_305EC930

Флаг a5 определяет какая именно сейчас происходит операция.

``````int __userpurge sub_305EC930@<eax>(int a1@<eax>, int a2@<edx>, int a3@<ecx>, int a4, int a5, int a6)
{
int v6; // esiint v7; // ebxint v8; // ediint v9; // edxint v10; // ecxsize_t v11; // eax
_WORD *v12; // ebpsize_t v13; // eaxsize_t v14; // eaxint v15; // eaxunsigned __int16 *v16; // ecx
_WORD *v17; // eax
_WORD *v18; // ecxint v19; // edx
__int16 v20; // bxint v21; // eax
_WORD *v22; // ecxint v24; // edxint v25; // eaxint v26; // esiint v27; // ebxsize_t v28; // eaxint v29; // ebpsize_t v30; // eaxint v31; // esisize_t v32; // eaxint v33; // eaxunsigned __int16 *v34; // ecxint v35; // eax
_WORD *v36; // edx
_WORD *v37; // ecxint v38; // eax
__int16 v39; // bxint v40; // eax
_WORD *v41; // ecxint v42; // [esp+10h] [ebp-48h]int v43; // [esp+10h] [ebp-48h]int v44; // [esp+14h] [ebp-44h]char v45; // [esp+14h] [ebp-44h]int Dst[16]; // [esp+18h] [ebp-40h]int v47; // [esp+5Ch] [ebp+4h]int v48; // [esp+60h] [ebp+8h]
v6 = a1;
v7 = a1 & 0xF;
v8 = a2;
if ( !a5 )
{
v24 = a4 - a1;
v25 = a1 - a3;
v43 = a4 - v6;
if ( v7 >= v25 )
v7 = v25;
v47 = a4 - v7;
v26 = v6 - v7;
v27 = v7 + 1;
v48 = v27;
if ( !v8 )
return v27;
v28 = 4 * v24;
if ( (4 * v24) > 0x40 )
v28 = 64;
v45 = v27 + v26;
v29 = (v27 + v26) & 0xF;
memmove(Dst, (v8 + 4 * v29), v28);
v30 = 4 * v27;
if ( (4 * v27) > 0x40 )
v30 = 64;
v31 = v26 & 0xF;
memmove((v8 + 4 * (v47 & 0xF)), (v8 + 4 * v31), v30);
v32 = 4 * v43;
if ( (4 * v43) > 0x40 )
v32 = 64;
memmove((v8 + 4 * v31), Dst, v32);
if ( !a6 )
return v48;
v33 = v29;
if ( v29 < v29 + v43 )
{
v34 = (v8 + 4 * v29 + 214);
do
{
Dst[v33++] = *v34 >> 15;
v34 += 2;
}
while ( v33 < v29 + v43 );
}
v35 = (v45 - 1) & 0xF;
if ( v35 >= v31 )
{
v36 = (v8 + 4 * ((v27 + v47 - 1) & 0xF) + 214);
v37 = (v8 + 4 * ((v45 - 1) & 0xF) + 214);
v38 = v35 - v31 + 1;
do
{
v39 = *v37 ^ (*v37 ^ *v36) & 0x7FFF;
v37 -= 2;
*v36 = v39;
v36 -= 2;
--v38;
}
while ( v38 );
v27 = v48;
}
v40 = v31;
if ( v31 >= v31 + v43 )
return v27;
v41 = (v8 + 4 * v31 + 214);
do
{
*v41 = *v41 & 0x7FFF | (LOWORD(Dst[v40++]) << 15);
v41 += 2;
}
while ( v40 < v31 + v43 );
return v27;
}
v9 = a1 - a4;
v10 = a3 - a1;
v42 = a1 - a4;
v48 = 16 - v7;
if ( 16 - v7 >= v10 )
v48 = v10;
if ( !v8 )
return v48;
v11 = 4 * v9;
if ( (4 * v9) > 0x40 )
v11 = 64;
v12 = (v8 + 4 * (a4 & 0xF));
v44 = a4 & 0xF;
memmove(Dst, v12, v11);
v13 = 4 * v48;
if ( (4 * v48) > 0x40 )
v13 = 64;
memmove(v12, (v8 + 4 * v7), v13);
v14 = 4 * v42;
if ( (4 * v42) > 0x40 )
v14 = 64;
memmove((v8 + 4 * ((a4 + v48) & 0xF)), Dst, v14);
if ( !a6 )
return v48;
v15 = a4 & 0xF;
if ( v44 < v44 + v42 )
{
v16 = (v8 + 4 * v44 + 214);
do
{
Dst[v15++] = *v16 >> 15;
v16 += 2;
}
while ( v15 < v44 + v42 );
}
if ( v7 < v48 + v7 )
{
v17 = (v8 + 4 * v7 + 214);
v18 = v12 + 107;
v19 = v48;
do
{
v20 = *v17 ^ (*v17 ^ *v18) & 0x7FFF;
v17 += 2;
*v18 = v20;
v18 += 2;
--v19;
}
while ( v19 );
}
v21 = a4 & 0xF;
if ( v44 >= v44 + v42 )
return v48;
v22 = (v8 + 4 * (v44 + v48) + 214);
do
{
*v22 = *v22 & 0x7FFF | (LOWORD(Dst[v21++]) << 15);
v22 += 2;
}
while ( v21 < v44 + v42 );
return v48;
}``````

In addition, in appearance, you can roughly understand what is happening there, and finish the rest by indirect signs.
Let's try to identify these indirect signs. First, we set the height for rows 16 through 64 inclusive in a random order. Then before a row under the index 39 we insert a new row. The new row will copy the height of row 38.
Let's look at the information in the row groups before and after adding the row, I highlighted the bold differences:

Offsets in the first group:Offsets in the first group:
0E, 04, 07, 00, 05, 0A, 09, 0F, 03, 06, 08, 0D, 01, 0B, 0C, 020E, 04, 07, 00, 05, 0A, 09, 0F, 03, 06, 08, 0D, 01, 0B, 0C, 02
The values ​​of the heights of the rows in the first group:The values ​​of the heights of the rows in the first group:
05, 2B, 35, 45, 4B, 50, 5B, 6B, 7B, 8B, A5, AB, B0, B5, E0, 10005, 2B, 35, 45, 4B, 50, 5B, 6B, 7B, 8B, A5, AB, B0, B5, E0, 100
Offsets in the second group:Offsets in the second group:
06, 02, 0E, 09, 01, 07, 0F, 0C , 00, 0A, 04, 0B, 03, 08, 0D, 0506, 02, 0E, 09, 01, 07, 0F, 05, 0C , 00, 0A, 04, 0B, 03, 08, 0D
The values ​​of the heights of the rows in the second group:The values ​​of the heights of the rows in the second group:
10, 15, 20, 25, 30, 75 , 85, 90, 9B, A0, C5, CB, D0, D5, E5, F010, 15, 20, 25, 30, F0 , 85, 90, 9B, A0, C5, CB, D0, D5, E5, F0
Offsets in the third group:Offsets in the third group:
0C, 08, 0E, 07, 0A, 01, 06, 0F, 09, 0D, 00, 05, 0B, 02, 04, 0303 , 0C, 08, 0E, 07, 0A, 01, 06, 0F, 09, 0D, 00, 05, 0B, 02, 04
The values ​​of the heights of the rows in the third group:The values ​​of the heights of the rows in the third group:
0B, 1B, 3B, 40 , 55, 60, 65, 70, 80, 95, BB, C0, DB, EB, F5, FB0B, 1B, 3B, 75 , 55, 60, 65, 70, 80, 95, BB, C0, DB, EB, F5, FB
Offsets in the fourth group:Offsets in the fourth group:
_00
The values ​​of the heights of the rows in the fourth group:The values ​​of the heights of the rows in the fourth group:
_40

It turns out that it was expected: Excel inserts a new row in the second group with index 7 (39 & 0xF), whose offset is 0x05, copies the row height of index 6, while the last row, which was offset 05, is pushed into the next group, and from there the last row is pushed to the fourth, etc.

Now let's see what happens if you delete the 29th row.

Before removing a rowAfter removing a row
Offsets in the first group:Offsets in the first group:
0E, 04, 07, 00, 05, 0A, 09, 0F, 03, 06, 08, 0D, 01, 0B, 0C, 020E, 04, 07, 00, 05, 0A, 09, 0F, 03, 06, 08, 0D, 01, 0C, 02, 0B
The values ​​of the heights of the rows in the first group:The values ​​of the heights of the rows in the first group:
05, 2B, 35, 45, 4B, 50, 5B, 6B, 7B, 8B, A5, AB , B0, B5, E0, 10005, 2B, 35, 45, 4B, 50, 5B, 6B, 7B, 8B, A5, 85 , B0, B5, E0, 100
Offsets in the second group:Offsets in the second group:
06 , 02, 0E, 09, 01, 07, 0F, 05, 0C, 00, 0A, 04, 0B, 03, 08, 0D02, 0E, 09, 01, 07, 0F, 05, 0C, 00, 0A, 04, 0B, 03, 08, 0D, 06
The values ​​of the heights of the rows in the second group:The values ​​of the heights of the rows in the second group:
10, 15, 20, 25, 30, F0, 85 , 90, 9B, A0, C5, CB, D0, D5, E5, F010, 15, 20, 25, 30, F0, 75 , 90, 9B, A0, C5, CB, D0, D5, E5, F0
Offsets in the third group:Offsets in the third group:
03 , 0C, 08, 0E, 07, 0A, 01, 06, 0F, 09, 0D, 00, 05, 0B, 02, 040C, 08, 0E, 07, 0A, 01, 06, 0F, 09, 0D, 00, 05, 0B, 02, 04, 03
The values ​​of the heights of the rows in the third group:The values ​​of the heights of the rows in the third group:
0B, 1B, 3B, 75 , 55, 60, 65, 70, 80, 95, BB, C0, DB, EB, F5, FB0B, 1B, 3B, 40 , 55, 60, 65, 70, 80, 95, BB, C0, DB, EB, F5, FB
Offsets in the fourth group:Offsets in the fourth group:
0000
The values ​​of the heights of the rows in the fourth group:The values ​​of the heights of the rows in the fourth group:
4050

In principle, when deleting a row, operations are inverse to the insert. At the same time, the fourth group continues to exist and the value of the row height is filled there with the standard height with the corresponding flag - 0x8005.

This data is enough to reproduce this algorithm in C #:

InsertRow
``````publicstaticvoidInsertRow(SheetLayoutInfo sheetLayoutInfo, int rowIndex) {
if (rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
return;
RowHeightInfo etalonRowHeightInfo = GetRowHeightCore(sheetLayoutInfo, rowIndex);
RowHeightInfo newRowHeightInfo = etalonRowHeightInfo != null ? etalonRowHeightInfo.Clone() : CreateDefaultRowHeight(sheetLayoutInfo);
int realGroupIndex = (rowIndex + 1) >> 4;
int newRowInGroupIndex = (rowIndex + 1) & 0xF;
int groupIndex;
for (groupIndex = realGroupIndex + sheetLayoutInfo.GroupIndexDelta; groupIndex < sheetLayoutInfo.RowsGroups.Count; groupIndex++, newRowInGroupIndex = 0) {
if (groupIndex < 0)
continue;
if (groupIndex == SheetLayoutInfo.MaxGroupsCount)
break;
RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[groupIndex];
if (rowsGroupInfo == null) {
if ((newRowHeightInfo.Flags & CustomHeightMask) == 0)
continue;
rowsGroupInfo = new RowsGroupInfo();
sheetLayoutInfo.RowsGroups[groupIndex] = rowsGroupInfo;
}
int rowInfoIndex = rowsGroupInfo.Indices[newRowInGroupIndex];
RowHeightInfo lastRowHeightInGroup;
if (rowInfoIndex == -1 || rowsGroupInfo.HeightInfos.Count < SheetLayoutInfo.MaxRowsCountInGroup) {
lastRowHeightInGroup = GetRowHeightCore(sheetLayoutInfo, ((groupIndex - sheetLayoutInfo.GroupIndexDelta) << 4) + SheetLayoutInfo.MaxRowsCountInGroup - 1);
Array.Copy(rowsGroupInfo.Indices, newRowInGroupIndex, rowsGroupInfo.Indices, newRowInGroupIndex + 1, SheetLayoutInfo.MaxRowsCountInGroup - 1 - newRowInGroupIndex);
rowsGroupInfo.Indices[newRowInGroupIndex] = rowsGroupInfo.HeightInfos.Count - 1;
}
else {
int lastIndex = rowsGroupInfo.Indices[SheetLayoutInfo.MaxRowsCountInGroup - 1];
lastRowHeightInGroup = rowsGroupInfo.HeightInfos[lastIndex];
Array.Copy(rowsGroupInfo.Indices, newRowInGroupIndex, rowsGroupInfo.Indices, newRowInGroupIndex + 1, SheetLayoutInfo.MaxRowsCountInGroup - 1 - newRowInGroupIndex);
rowsGroupInfo.HeightInfos[lastIndex] = newRowHeightInfo;
rowsGroupInfo.Indices[newRowInGroupIndex] = lastIndex;
}
newRowHeightInfo = lastRowHeightInGroup ?? CreateDefaultRowHeight(sheetLayoutInfo);
}
if ((newRowHeightInfo.Flags & CustomHeightMask) != 0 && groupIndex != SheetLayoutInfo.MaxGroupsCount) {
SetRowHeight(((groupIndex - sheetLayoutInfo.GroupIndexDelta) << 4) + newRowInGroupIndex, newRowHeightInfo.Value, newRowHeightInfo.Flags, sheetLayoutInfo);
}
else {
sheetLayoutInfo.MaxRowIndexNonDefault = Math.Min(sheetLayoutInfo.MaxRowIndexNonDefault + 1, SheetLayoutInfo.MaxRowsCount);
}
}``````

RemoveRow
``````publicstaticvoidRemoveRow(SheetLayoutInfo sheetLayoutInfo, int rowIndex) {
if (rowIndex >= sheetLayoutInfo.MaxRowIndexNonDefault)
return;
int realGroupIndex = rowIndex >> 4;
int newRowInGroupIndex = rowIndex & 0xF;
int groupIndex;
for (groupIndex = realGroupIndex + sheetLayoutInfo.GroupIndexDelta; groupIndex < sheetLayoutInfo.RowsGroups.Count; groupIndex++, newRowInGroupIndex = 0) {
if (groupIndex < -1)
continue;
if (groupIndex == -1) {
sheetLayoutInfo.RowsGroups.Insert(0, null);
sheetLayoutInfo.GroupIndexDelta++;
groupIndex = 0;
}
if (groupIndex == SheetLayoutInfo.MaxGroupsCount)
break;
var newRowHeightInfo = groupIndex == SheetLayoutInfo.MaxGroupsCount - 1 ? null : GetRowHeightCore(sheetLayoutInfo, (groupIndex - sheetLayoutInfo.GroupIndexDelta + 1) << 4);
RowsGroupInfo rowsGroupInfo = sheetLayoutInfo.RowsGroups[groupIndex];
if (rowsGroupInfo == null) {
if (newRowHeightInfo == null || (newRowHeightInfo.Flags & CustomHeightMask) == 0)
continue;
rowsGroupInfo = new RowsGroupInfo();
sheetLayoutInfo.RowsGroups[groupIndex] = rowsGroupInfo;
}
if (newRowHeightInfo == null) {
newRowHeightInfo = CreateDefaultRowHeight(sheetLayoutInfo);
}
int rowInfoIndex = rowsGroupInfo.Indices[newRowInGroupIndex];
if (rowInfoIndex == -1) {
for (int i = newRowInGroupIndex; i < SheetLayoutInfo.MaxRowsCountInGroup - 1; i++) {
rowsGroupInfo.Indices[i] = rowsGroupInfo.Indices[i + 1];
}
rowsGroupInfo.Indices[SheetLayoutInfo.MaxRowsCountInGroup - 1] = rowsGroupInfo.HeightInfos.Count - 1;
}
else {
for(int i = newRowInGroupIndex; i < rowsGroupInfo.HeightInfos.Count - 1; i++) {
rowsGroupInfo.Indices[i] = rowsGroupInfo.Indices[i + 1];
}
rowsGroupInfo.Indices[rowsGroupInfo.HeightInfos.Count - 1] = rowInfoIndex;
rowsGroupInfo.HeightInfos[rowInfoIndex] = newRowHeightInfo;
}
}
if(rowIndex <= sheetLayoutInfo.MinRowIndexNonDefault) {
sheetLayoutInfo.MinRowIndexNonDefault = Math.Max(sheetLayoutInfo.MinRowIndexNonDefault - 1, 0);
}
}``````

All the above code can be found on GitHub

findings

Excel code is not the first time surprising interesting techniques. This time I found out how he keeps information about the heights of the rows. If the community is interested, in the next article I will show how Excel considers the height of the range of cells (spoiler: there is something similar to SQRT decomposition, but for some reason without caching sums), there you can also see how it applies scaling in integers .