Adjust width of the Filter Dropdown button with AutoFitColumns using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

Hello,


I am using worksheet.AutoFitColumns() but it does not take into account the width of the Filter Dropdown button (arrow), resulting in the column not being wide enough, please see attached screenshot.

If one manually autofits the column in Excel (ie. by double clicking the edge of the column) then Excel sizes it correctly.

I found this previous forum post where the user had the same problem but I don’t see any resolution of this specific issue in there.

I am using version 8.0.0.0

Please advise how we fix this?

Thank you

2

Hi,


Please download and try our latest fix/version : Aspose.Cells for .NET (Latest Version) if it makes any difference.

If you still find the issue, kindly attach your template file here, we will check your issue soon.

Thank you.

3

Thanks for the quick response.


I tried with that dll but it is still not working (FYI the filename of the download was Aspose.Cells for .NET(Latest Version) which I assume means it is for .NET 2.0 but we are using 4.0, so I downloaded 8.0.1.0 from here which includes a version for .NET 3.5/4.0 and tried with it but had the same result).

What template file do you need please (I am unsure of what file you are referring to)?

Thank you.



4

Hi,


Well, you may try to use Aspose.Cells for .NET v8.0.1.1 which is compiled on .NET framework 2.0 and will work fine on .NET framework >= 2.0 (including yours .NET framework 4.0).

Regarding template file, I meant your Excel file. You may save the Excel file and provide it here, we will open it via Aspose.Cells APIs and perform AutoFitColumns operation to evaluate your issue on our end.

Thank you.
5

Thank you please find my excel template attached which is what Aspose.Cells has exported.





6

Hi Richard,

Thanks for your posting and using Aspose.Cells.

You provided the output file because it seems to be generated with Aspose.Cells. Could you please provide the template (source) file instead.

Anyway, I have autofit columns using the following code with your given file by opening it and autofitting it and it looks fine. I have attached the output xlsx file for your reference.

C#


string filePath = @“F:\Lease+Expiry+Profile+20140425+1529±+for+Aspose+Support.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


worksheet.AutoFitColumns();


workbook.Save(“out.xlsx”);

7

Hi Richard,


Thank you for sharing the sample spreadsheet. Although we required your input file for our testing but we have managed to sort this out by narrowing the column width to minimum and executing the below piece of code to auto fit the column widths. Please note, you have to use the AutoFitterOptions class and set it’s OnlyAuto property to true. Please have a look at the below code snippet for better elaboration, and give it a try on your end with your original input spreadsheet.

C#

var book = new Workbook(myDir + “sample.xlsx”);
var sheet = book.Worksheets[0];

sheet.AutoFitColumns(new AutoFitterOptions() { OnlyAuto = true});

book.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);

In case the problem persists, please provide the input spreadsheet for our review as the problem could be due to the template it self.
8

Thanks for the reply. I tried with the AutoFitterOptions but it is still not working.


I was creating a new workbook using data from SQL with a ListObject, not a a template file, but I have managed to replicate the issue using a template file (AsposeInputTemplate.xlsx attached):

I have narrowed it down and the bug only occurs if I insert new row(s) above the ListObject.

The following code produces the correct results ie.correct width of columns:

VB.NET:


Dim wb As Workbook = New Workbook(“c:\Users\Richards\Documents\AsposeInputTemplate.xlsx”)

Dim sheet As Worksheet = wb.Worksheets(0)

Dim listObjects As Aspose.Cells.Tables.ListObjectCollection = sheet.ListObjects

Dim listObject As Aspose.Cells.Tables.ListObject = sheet.ListObjects(listObjects.Add(1, 1, 5, 3, True))

listObject.TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium2

sheet.AutoFitColumns(New AutoFitterOptions() With {.OnlyAuto = True})

wb.Save(“c:\Users\Richards\Documents\MyBook.xlsx”, New OoxmlSaveOptions)

Please see output file "MyBook-no row inserted.xlsx’ attached - column width is correct.

However, the same code, but with a new row inserted above the ListObject produces the bug:

Dim wb As Workbook = New Workbook(“c:\Users\Richards\Documents\AsposeInputTemplate.xlsx”)

Dim sheet As Worksheet = wb.Worksheets(0)

Dim listObjects As Aspose.Cells.Tables.ListObjectCollection = sheet.ListObjects

Dim listObject As Aspose.Cells.Tables.ListObject = sheet.ListObjects(listObjects.Add(1, 1, 5, 3, True))

listObject.TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium2

sheet.Cells.InsertRows(0, 1)

sheet.AutoFitColumns(New AutoFitterOptions() With {.OnlyAuto = True})

wb.Save(“c:\Users\Richards\Documents\MyBook.xlsx”, New OoxmlSaveOptions)

Please see output file "MyBook-row inserted.xlsx’ attached - the column width is now incorrect.

Please kindly advise?

Thank you.

9

Hi Richard,


Thank you for providing your detailed analysis of the problem.

We are able to replicate the issue on our end while using the latest version of Aspose.Cells for .NET. The problem seems to occur when new rows are inserted on run time before calling the AutoFitColumns function. We have logged a ticket (CELLSNET-42591) in our bug tracking system to properly investigate the problem cause, and to provide a fix at earliest.

Please spare us little time for thorough analysis. In the meanwhile, we will keep you posted with updates in this regard.
10

Hi Richard,

Thanks for using Aspose.Cells.

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

11

The issues you have found earlier (filed as CELLSNET-42591) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
12

I tested this with 8.0.2.0 and it is now working correctly.


Thank you for the help.

13

Hi,


Good to know that your issue is resolved by the new fix now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.