Hi Aspose Support,
I am facing an issue with autofit columns. Below is the code for autofit I am trying but it does not work the same way as it does with the manual autofit in the excel sheet.
Please find below the code snippet and the desired behavior required in a screenshot (autofit.png) attached with the post. Have also attached a full source code example for reference.
class Program
{
static void Main(string[] args)
{
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("DataRange");
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");
}
}
public static class Extensions
{
public static void Apply<T>(this IEnumerable<T> source, Action<T> action)
{
foreach (var item in source)
{
action(item);
}
}
}
AsposeAutoFit-SourceCode-InclusingExcelSheet.zip (16.7 KB)
autofit.png (42.2 KB)
Could you please help me achieve the same behaviour as we get in the excel sheet.
Thanks !
Shobhit Bansal
@hi.shobhit76,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-48473 - AutoFit Column not working properly
@hi.shobhit76,
This is to inform you that your issue has been resolved. We will provide you the fixed version within the next few days after performing QA and incorporating other enhancements and fixes.
Thanks. I will trying this fix in a short while and let you know if it works as expected.
@hi.shobhit76,
Sure, please take your time to test the new fix. Hopefully it will fix your issue.
Hi team,
Thanks for providing a solution to the autofit issue but it is still not working the same as it works in the excel sheet although the differences are very minor but still it may make a difference to us where we have more number of columns.
Please find attached the image for the differences I have observed. There is a diff of 2 pixels on each column width as compared to the manual autofit on the same excel sheet.
autofit-update.png (14.2 KB)
Could you please get rid of these differences. Also as part of this change consider and test “AutoFit Rows” which should work the same way as in excel manual autofit. In the sample I have provided I could not find any issues with the rows but request you to please test at your end with more use cases.
Thanks
Shobhit
@hi.shobhit76,
I guess 1/2 pixel difference would be inevitable for certain cases when performing auto-fit rows/cols operation via Aspose.Cells APIs, so you have to live with it. We have also recorded your findings and screenshot to your existing ticket into our database. We will evaluate it.
Once we have any new information/update available, we will let you know here.
Hi Amjad,
The fix is not available in the nuget package which i had been using. When is it likely to going to get live into the PROD ?
Regards
Shobhit
@hi.shobhit82,
This is hot fix and will not be available through NuGet package manager. It will be part of our next regular release expected at the end of next week. You may please wait till the regular release to get this fix through NuGet package manager.
Hi,
I tried to run through the fixed library with different sheets but looks like the fix is only working with the sheet which i had provided earlier.
Could you please try the sheet attached with the code below.
AutoFitIssue.zip (11.6 KB)
namespace AsposeAutoFit
{
class Program
{
static void Main(string[] args)
{
FileStream fs;
fs = File.Open("AutoFitIssue.xlsx", FileMode.Open);
Workbook wb = new Workbook(fs);
var sheet = wb.Worksheets["Rendering"];
var range = sheet.Workbook.Worksheets.GetRangeByName("ImageRange");
Enumerable.Range(range.FirstColumn, range.ColumnCount)
.Apply(colIdx =>
{
if(!range.Worksheet.Cells.Columns[colIdx].IsHidden)
range.Worksheet.AutoFitColumn(colIdx);
});
fs.Close();
//save the worksheet in the working directory usually in ...../bin/debug/
wb.Save("AutoFitIssue.xlsx");
}
}
public static class Extensions
{
public static void Apply<T>(this IEnumerable<T> source, Action<T> action)
{
foreach (var item in source)
{
action(item);
}
}
}
}
Could you please make sure the fix is working in all the cases i.e. for e.g. merged cells, conditionally formatted cells, numerically formatted etc etc.
Regards
Shobhit
@hi.shobhit82,
We have observed the issue and logged it with the ticket for further investigation. You will be notified here once any update is ready for sharing.
@hi.shobhit82,
For performance , Aspose.Cells does not consider conditionally formatting when auto fitting columns’ width.
We will add a property in AutoFitterOptions to check whether considering conditionally formatting.
Is that a root cause of the issue where the AutoFit did not work when there was conditional formatting present, as the excel i faced issue with had that.
@hi.shobhit82,
Yes, you are right. Let us evaluate and add the relevant attribute to check and consider formatting while auto-fitting rows/cols operation.
Thanks. Please also provide a sample code snippet to implement the same as it would be helpful. Regards.
@hi.shobhit82,
Yes, once we will provide the new fix/version (with added attribute), we will give you test code.
Any updates on this please ?
@hi.shobhit82,
The new attribute is still not implemented to the APIs. Once we do it, we will prepare a fix and share it with you.
Any Updates on this please ? Any rough idea when is it likely to be fixed ?