AutoFitRow returns a different row size than Excel using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

AutoFitRow returns a different row size than Excel in some cases.



See Test code, and run against attached Workbook.



Note Cell T1 the Number format is “;;;”, which has the effect in Excel of not showing the Text in the cell.



The cell also has Wrap Text set in the cell’s format alignment settings.



A best fit in Excel returns a row height of 15.75 points.



Using the code below, AutoFitRow in Aspose cells (7.7.2) returns a row height of 38.25.



Aspose seems to be getting the height of the wraped text (not the formatted text) and applying that to the logic of AutoFitRow, however Excel respects the ;;; formatting first and appears to uses the formatted value as the input to their best fit algorithm.



Aspose Cells should ensure it behaves like Excel in this case.



Workbook sourceWorkbook = new Workbook(@“C:\Before AutoFitRow.xlsx”);

AutoFitterOptions oAutoFitterOptions = new AutoFitterOptions {AutoFitMergedCells = true, IgnoreHidden = false, OnlyAuto = false};

double Height = sourceWorkbook.Worksheets[0].Cells.CreateRange(“1:1”).RowHeight;

sourceWorkbook.Worksheets[0].AutoFitRow(0, 0, 16383, oAutoFitterOptions);

Height = sourceWorkbook.Worksheets[0].Cells.CreateRange(“1:1”).RowHeight;

sourceWorkbook.Save(@“C:\After AutoFitRow Results.Xlsx”, SaveFormat.Xlsx);

2

Hi,


Please change your code a bit, it works fine:
e.g
Sample code:

Workbook sourceWorkbook = new Workbook(@“C:\Before AutoFitRow.xlsx”);
AutoFitterOptions oAutoFitterOptions = new AutoFitterOptions {AutoFitMergedCells = false, IgnoreHidden = false, OnlyAuto = false};
double Height = sourceWorkbook.Worksheets[0].Cells.CreateRange(“1:1”).RowHeight;
sourceWorkbook.Worksheets[0].AutoFitRow(0, 0, 16383, oAutoFitterOptions);
Height = sourceWorkbook.Worksheets[0].Cells.CreateRange(“1:1”).RowHeight;
sourceWorkbook.Save(@“C:\After AutoFitRow Results.Xlsx”, SaveFormat.Xlsx);

Thank you.
3

I tried that previously Amjad…same error.

Regards,

Mike

4
Hi,

Please try our latest version/fix: Aspose.Cells for .NET (Latest Version) I have tested with it using the updated code and it works fine.

Thank you.
5

Hi Amjad,

I have tried 7.7.2.5 and while the saved sheet opened in Excel row 1 has a height of 15.75,
the Aspose Property RowHeight still returns 38.25 points.

I also checked Method sourceWorkbook.Worksheets[0].Cells.GetRowHeight(0) and it also returns 38.25

They should return 15.75.

Regards,

Mike

6

Hi,


Now that is strange. Could you create a separate and simple console application (runnable) (using updated code as per my suggestion ) with v7.7.2.5, zip it and post it here, we will check your issue soon.

Thank you.
7

Attached example as requested.



Please note the methods under Main(), they are commented out in this zip, but if you uncomment them they do not compile under 7.7.2.3 and 7.7.2.5, but they do compile under 7.7.2. You might like to check that out also.



This issue is another matter we discussed earlier see 532546 in reply to 532531



Regards,



Mike

8

Hi Mike,


Thanks for the sample project.

I run your project and it works fine, the output file is fine, please find it attached here.

I suspect it might be an issue with DPI settings on your system, e.g; it might be “Medium - 125%” or larger, whereas it should be set to “Smaller - 100% (default)”, under Control Panel => Appearance and Personalization => Display.

Thank you.


9

Hi Amjad,

Yes I am aware the saved workbook looks ok.

My settings are already “Smaller - 100% (default)”

The problem is that the Aspose Cells API returns the wrong value for the row height after the AutoFitRow (even though visibly the spreadsheet looks correct). Open the spreadsheet in Excel and you will see that the row height is 15.75 points which is correct, however Aspose is returning 38.25 points.

Add the following line of code (make it the last line) to the Main method in the test code I sent you.

System.Console.WriteLine(“The Row Height after AutoFitRow is: " + dRowHeight + " points. It should be 15.75 points. See the saved Excel Workbook, and the row Height of row 1.”);

What do you get output in the console window…???

Cheers,

Mike

10
Hi,

Thanks for providing further details.

I observed the issue as you mentioned by adding the lines of code. Aspose Cells returns the wrong value for the row height after the AutoFitRow method is called, it returns 38.25 points instead of 15.75.
e.g
Sample code:

Workbook sourceWorkbook = new Workbook(@"e:\test2\Before AutoFitRow.xlsx");
AutoFitterOptions oAutoFitterOptions = new AutoFitterOptions { AutoFitMergedCells = true, IgnoreHidden = true, OnlyAuto = false };
double Height = sourceWorkbook.Worksheets[0].Cells.CreateRange("1:1").RowHeight;
sourceWorkbook.Worksheets[0].AutoFitRow(0, 0, 16383, oAutoFitterOptions);
Height = sourceWorkbook.Worksheets[0].Cells.CreateRange("1:1").RowHeight;

System.Console.WriteLine("The Row Height after AutoFitRow is: " + Height + " points");//38.25
System.Console.WriteLine("The Row Height after AutoFitRow is: " + sourceWorkbook.Worksheets[0].Cells.GetRowHeight(0) + " points"); //38.25

sourceWorkbook.Save(@"e:\test2\After AutoFitRow Results1.Xlsx", SaveFormat.Xlsx);

I have logged a ticket with an id "CELLSNET-42472" for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
11

Hi,

Thanks for using Aspose.Cells.

Please download and try the fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

12

Hi Shakeel,

Looks fixed in 7.7.2.6.

Has this fix also been included in 8.0.0.0…???

Thanks,

Mike

13

Hi,


Sure, the fix is included in the latest version: Aspose.Cells for .NET (Latest Version), so please try it:

I have also tested with your template file and sample code and it works fine.

Thank you.