AutoFit Column Not working properly

amjad.sahi · August 26, 2021, 11:02am

@hi.shobhit82,

We will check if we could provide you an ETA on it.
We will get back to you soon.

We are sorry for any inconvenience caused!

amjad.sahi · August 26, 2021, 11:54am

@hi.shobhit82,

We are pleased to inform you that the issue will be fixed in the next week hopefully.

Keep in touch.

hi.shobhit82 · August 26, 2021, 1:58pm

Thanks for the update !!

amjad.sahi · August 30, 2021, 9:12am

@hi.shobhit82,

This is to inform you that your issue (logged earlier as “CELLSNET-48473”) has been resolved now. You may expect the fix before the end of this week or so.

hi.shobhit82 · August 30, 2021, 1:51pm

Is it possible to provide the fixed patch so that i can test it in the meantime ?

ahsaniqbalsidiqui · August 30, 2021, 2:16pm

@hi.shobhit82,
Sure, as mentioned by @Amjad_Sahi, it will be provided before the end of this week. I am afraid that without performing the proper QA, it is not possible to share any version.

simon.zhao · August 31, 2021, 1:38am

@hi.shobhit82
Please try the latest fix 21.8.3.
Aspose.Cells21.8.3 For .Net2_AuthenticodeSigned.Zip (5.6 MB)
Aspose.Cells21.8.3 For .Net4.0.Zip (5.6 MB)
Aspose.Cells21.8.3 For .NetStandard20.Zip (5.6 MB)

hi.shobhit82 · August 31, 2021, 4:55am

Hi,

How to use this new library to get the desired result ?

I tried using the below code and issue is still the same

hi.shobhit82:

	FileStream fs;

	fs = File.Open("RegReport.xlsx", FileMode.Open);
	Workbook wb = new Workbook(fs);
	var sheet = wb.Worksheets[0];
	var range = sheet.Workbook.Worksheets.GetRangeByName("ImageRange");
	
	Enumerable.Range(range.FirstColumn, range.ColumnCount)
	.Apply(colIdx =>
	{
		range.Worksheet.AutoFitColumn(colIdx,);
	});            

	fs.Close();

	//save the worksheet in the working directory usually in ...../bin/debug/
	wb.Save("RegReport.xlsx");

AutoFitIssueExcel.zip (11.6 KB)

amjad.sahi · August 31, 2021, 10:08am

@hi.shobhit82,

Could you please zip and attach your desired output Excel file (you may perform auto-fit columns operation in MS Excel manually and save the file). This will help to trace the issue precisely on our end.

hi.shobhit82 · August 31, 2021, 11:31am

Please find attached the desired output by manually autofitting the columns.

I believe you said the issue is fixed, could you please help me with a code snippet using your fixed library as to what setting i have to use to get the fix working.

AutoFitIssueDesired.zip (11.7 KB)

amjad.sahi · August 31, 2021, 3:09pm

@hi.shobhit82,

Thanks for the sample expected file.
I evaluated the issue with the new fix and noticed the issue is not fixed using your code snippet. I have logged your expected file with the ticket. We will evaluate it thoroughly and get back to you with updates and sample code segment to figure out your issue.

ahsaniqbalsidiqui · September 1, 2021, 7:37am

@hi.shobhit82,
Please set AutoFitterOptions.FormatStrategy as CellValueFormatStrategy.DisplayStyle as the following codes:

[Test]
public void CELLSNET48455_2() {
Workbook workbook = new Workbook(Constants.sourcePath + "CELLSNET48473_1.xlsx");
AutoFitterOptions options = new AutoFitterOptions();
options.FormatStrategy = CellValueFormatStrategy.DisplayStyle;
workbook.Worksheets[1].AutoFitColumns(options);
Cells cells = workbook.Worksheets[1].Cells;
Assert.AreEqual(27, cells.GetColumnWidthPixel(14 + 0));
Assert.AreEqual(27, cells.GetColumnWidthPixel(14 + 1));
Assert.AreEqual(27, cells.GetColumnWidthPixel(14 + 2));
workbook.Save(Constants.destPath + "CELLSNET48473_1.xlsx");
}

hi.shobhit82 · September 1, 2021, 2:50pm

Thanks! I got this working now at my end. The only difference is that the width of the columns in Excel is 25 pixels whereas the Aspose leaves it as 27 pixels ? Can this be optimized to 25 please ?

ahsaniqbalsidiqui · September 1, 2021, 2:57pm

@hi.shobhit82,
Thank you for the feedback. We have noted it and will share our comments soon.

hi.shobhit82 · September 1, 2021, 4:22pm

BTW , when’s this fix likely to be available in the prod i.e. aspose nuget package ?

ahsaniqbalsidiqui · September 1, 2021, 4:39pm

@hi.shobhit82,
It is expected around the mid of September, 2021. You will be notified here once this version is released and available through NuGet package manager.

hi.shobhit82 · September 5, 2021, 5:51am

ahsaniqbalsidiqui:

@hi.shobhit82,
Please set AutoFitterOptions.FormatStrategy as CellValueFormatStrategy.DisplayStyle as the following codes:

[Test]
public void CELLSNET48455_2() {
Workbook workbook = new Workbook(Constants.sourcePath + "CELLSNET48473_1.xlsx");
AutoFitterOptions options = new AutoFitterOptions();
options.FormatStrategy = CellValueFormatStrategy.DisplayStyle;
workbook.Worksheets[1].AutoFitColumns(options);
Cells cells = workbook.Worksheets[1].Cells;
Assert.AreEqual(27, cells.GetColumnWidthPixel(14 + 0));
Assert.AreEqual(27, cells.GetColumnWidthPixel(14 + 1));
Assert.AreEqual(27, cells.GetColumnWidthPixel(14 + 2));
workbook.Save(Constants.destPath + "CELLSNET48473_1.xlsx");
}

I am facing an issue with the fix where the data has decimal places and the cells where we format to show single decimal the auto fit now fitting the column to an extent where the decimal value is not accounted for and results in “###” for e.g. where the cell has the value 12.3567 and the display format of the cell is “0.0”, dont have a sheet to share with you at this time but could please try this case and see if anything is a miss in the implementation of the fix please.

ahsaniqbalsidiqui · September 5, 2021, 8:34am

@hi.shobhit82,
We have noted your concern and will share our feedback after detailed analysis.

amjad.sahi · September 6, 2021, 7:38am

@hi.shobhit82,

Can this be optimized to 25?

We use GDI to measure the width of the string, which can only be as close as possible to the width of MS Excel. We will continue working on this issue but it could not be fixed soon.

…results in “###” for e.g. where the cell has the value 12.3567 and the display format of the cell is “0.0”

To test the scenario/case, we changed Cell “L13” value as 12.345, the resultant width of the column L is 34 pixels. We cannot see your mentioned issue “###”. Please provide more details and sample file and/or code to reproduce the issue.

aspose.notifier · September 15, 2021, 7:00am

The issues you have found earlier (filed as CELLSNET-48473) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao