Text is truncated in Excel cells after unusual formatting

Aspose.Cells Product Family
1

Dear Aspose team,

In Excel, if a row with the standard/default height contains a cell with the double underscore, the row height is increased slightly. The text is also elevated a bit. E.g. height 12.75 → 13.5.
Aspose handles it in the same way.

The problem appears in the following scenario.
— Row 1: no formatting.
— Row 2: double underscore
— Row 3: no formatting
— Row 2 is changed to be hidden
— Row 1: automatically adopts double underscore
In Excel Row 1 is 13.5 after this step.
In Aspose Row 1 is 12.75.

A short demo is attached.
It that demo, on result worksheet, rows 4 and 9 are supposed to have height 13.5.
Open in Excel, row 4 displays 13.5, but just because no height is set (automatic),
row 9 displays 12.75, but just because no height is set (automatic).
Aspose reports 12.75 for rows 4 and 9.

It may look very subtle, however we have a number of clients who end up with truncated/cropped text in such rows. Especially for capital T, F.

Aspose Demo — Truncated text.zip (70.7 KB)

2

@alex-rkn

I have checked this issue and I think, your description has some errors, because I don’t see double underscore in Row 2. I see double underscore in Row 5.

And when I follow your mentioned steps and see the height of row 1 in Microsoft Excel, I get 12.75 not 13.5.

Here is the console output of your program and the value 12.75 looks good while the value 13.5 is erroneous. Could you please check again and elaborate it further?

Console Output:

Aspose: v. 17.8.3.0

Running...

Row 4 height before autofit: 20.1
Row 4 height after autofit: 12.75 (expected 12.75)
Row 9 height before autofit: 20.1
Row 9 height after autofit: 12.75 (expected 12.75)
Row 4 height before autofit: 12.75
Row 4 height after autofit: 12.75 (expected 13.5 !!!)
Row 9 height before autofit: 12.75
Row 9 height after autofit: 12.75 (expected 13.5 !!!)
Row 9 height after manual sizing: 12.75

Ok


Done. / 2,858 msec /

Any key to continue...
3

Hi Shakeel,
Probably, my description was a bit confusing. When I described the scenario and mentioned rows 1, 2, 3, it was an abstract description, not the description of my demo. In my specific demo the problematic rows are 4 and 9.

In demo. If you open the result file in Excel,
— row 4 has height 13.5 due to its automatic size. Aspose reports 12.75,
— row 9 is 12.75, its size is set automatically in Aspose, then fixed. As the size is fixed, Excel also displays 12.75. However, if you autosize it in Excel (double click on the border), it size becomes 13.5.

Excel and Aspose behaviour is consistent until there is the tricky formatting with hidden rows I described.

Let me know, please, if it’s still not clear.

4

@alex-rkn

Thanks for your clarification and using Aspose API.

We were able to observe the issue and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45631 - Aspose.Cells AutoFitRow is slightly different than Excel auto fit row causing the formatting issue
5

@alex-rkn

We have looked into this issue and found that the increased row height is caused by that the row 5 is hidden and the double top border of the cell F6 becomes the bottom border of the cell F4.

If you only auto fit the height of the row 4 with Worksheet.AutoFitRow(3) method, We do not check other rows.

So we only can fix this issue with the method Worksheet.AutoFitRows(), we will provide a fix later.

6

@alex-rkn

Please download and try the following fix with Worksheet.AutoFitRows() and let us know your feedback.

7

I’ve checked and to be honest, I don’t see any difference in 17.8.6…

8

@alex-rkn

We will re-check this issue and update you asap.

9

@alex-rkn

You are right. I have added Worksheet.AutoFitRows() like below

Workbook workbook = new Workbook(fileName);

Worksheet worksheet = workbook.Worksheets.First();

worksheet.AutoFitRows();

and got this

Console Output

Aspose: v. 17.8.6

Running...

Row 4 height before autofit: 12.75
Row 4 height after autofit: 12.75 (expected 12.75)
Row 9 height before autofit: 12.75
Row 9 height after autofit: 12.75 (expected 12.75)
Row 4 height before autofit: 12.75
Row 4 height after autofit: 12.75 (expected 13.5 !!!)
Row 9 height before autofit: 12.75
Row 9 height after autofit: 12.75 (expected 13.5 !!!)
Row 9 height after manual sizing: 12.75

Ok


Done. / 1,008 msec /

Any key to continue...

However, I need your help, please share the correct console output of your project. Is this the correct console output

Modified and Expected Console Output

Aspose: v. 17.8.6

Running...

Row 4 height before autofit: 12.75
Row 4 height after autofit: 12.75 (expected 12.75)
Row 9 height before autofit: 12.75
Row 9 height after autofit: 12.75 (expected 12.75)
Row 4 height before autofit: 12.75
Row 4 height after autofit: 13.5  (expected 13.5 !!!)
Row 9 height before autofit: 12.75
Row 9 height after autofit: 13.5 (expected 13.5 !!!)
Row 9 height after manual sizing: 12.75

Ok


Done. / 1,008 msec /

Any key to continue...
10

Thank you, “Modified and Expected Console Output” is almost correct.
Just last row “Row 9 height after manual sizing: 12.75:” → “Row 9 height after manual sizing: 13.5:”
And Excel behaviour would be matched.

11

@alex-rkn

Thanks for correction and clarification.

We have logged this information in our database against this ticket for further investigation and for a fix. Once, there is some news for you, we will share it with you asap.

12

@alex-rkn

Please try the Aspose.Cells for .NET 17.8.6 with this code and it will fix your issue. Let us know your feedback.

C#

Workbook workbook = new Workbook(fileName); 

Worksheet worksheet = workbook.Worksheets[0]; 

Console.WriteLine("Row 4 height before autofit: {0}", worksheet.Cells.Rows[3].Height); 

Console.WriteLine("Row 9 height before autofit: {0}", worksheet.Cells.Rows[8].Height); 
worksheet.AutoFitRows(); 


Console.WriteLine("Row 4 height after autofit: {0} (expected 12.75)", worksheet.Cells.Rows[3].Height); 


Console.WriteLine("Row 9 height after autofit: {0} (expected 12.75)", worksheet.Cells.Rows[8].Height); 


worksheet.Cells.Rows[4].IsHidden = true; 
worksheet.Cells.Rows[9].IsHidden = true; 
// Autofit the row 
Console.WriteLine("Row 4 height before autofit: {0}", worksheet.Cells.Rows[3].Height); 
Console.WriteLine("Row 9 height before autofit: {0}", worksheet.Cells.Rows[8].Height); 
worksheet.AutoFitRows(); 


Console.WriteLine("Row 4 height after autofit: {0} (expected 13.5 !!!)", worksheet.Cells.Rows[3].Height); 

Console.WriteLine("Row 9 height after autofit: {0} (expected 13.5 !!!)", worksheet.Cells.Rows[8].Height);
13

Thank you. I see worksheet.AutoFitRows() works for the whole worksheet.
However, we don’t need all rows to be auto sized. I’ll still need to autosize all, then restore some rows.
It’s not a very high priority for us as work around is working. If you fix it eventually, that would be nice.

14

@alex-rkn

There is also an AutoFitRow() method which autofits a single row. Try that and see if it makes any difference in the resolution of your issue.

e.g.

//It will autofit 3rd alone
worksheet.AutoFitRow(2);
15

Yes, there is AutoFitRow(), but even if I auto size all rows from 0 to the last one, the problematic rows won’t have the exactly correct height. It looks like for now only AutoFitRows() detects the tricky formatting from this thread.

16

@alex-rkn

We have logged your comment in our database for product team investigation to find out if your issue could be resolved with AutoFit Row alone instead of AutoFit Rows. Once, there is some news for you, we will share it with you asap.

17

@alex-rkn

Please try the following code.

C#

	worksheet.Cells.Rows[4].IsHidden = true; 
	worksheet.AutoFitRows(3, GetLastHidden(worksheet.Cells, 3)); 
	Console.WriteLine(worksheet.Cells.Rows[3].Height); 
	worksheet.Cells.Rows[9].IsHidden = true; 

	worksheet.AutoFitRows(8, GetLastHidden(worksheet.Cells, 8)); 
	Console.WriteLine(worksheet.Cells.Rows[8].Height); 
	// Autofit the row 
	workbook.Save(dir + "CellsNet45631.xlsx"); 
} 

private static int GetLastHidden(Cells cells,int row) 
{ 
	for (int i = row + 1;; i++) 
	{ 
		if (!cells.IsRowHidden(i)) 
		{ 
			return i - 1; 
		} 
	} 
	return row; 
}
18

Thank you. I use similar work around.

19

@alex-rkn

It is good to know that this workaround is OK for you. Let us know if you encounter any other issue, we will be glad to help you further.