Hi,
We are facing multiple issues in production after upgrading Aspose.Cells to v24.12 from 20.2.
We found that the common culprit across most of the issues is ‘AutoFitColumns’.
If you refer the screenshot attached here (core issue explained.png (48.4 KB)),users are setting the ‘Print_Area’ for the chart on the right side. But they also have a chart object defined to the left of the ‘PrintArea’, which is intruding when we extract the image from the PrintArea. We found that this is happening because of the ‘maximum text length’ in the ‘M’ column is shorter than the actual width of the chart object. You can see the issue, refer the attached image here ( issue before setting the longer text.PNG (64.2 KB)).
But, if we set a longer text for any one of the rows in the ‘M’ column, as shown in the first screenshot, it works fine.
Users are complaining , it was not happening in the previous version of AsposeCells(v20.2), but after the upgrade(v24.12), it’s happening.
So, couple of questions regarding the upgrade differences.
- What exactly changed between these two versions to cause this behavior?
- Was this ‘AutoFitColumns’ change in behavior is intentional?
More Importantly,
- To fix this issue in production without having users to change all 5000 excel files, is there a way to make the ‘AutoFitColumns’ to be applied based on the maximum content width of the the ‘chart object’/‘any other similar object if exists’ ?
Based on our testing, We believe as of now, ‘AutoFitColumns’ relies only on the maximum text content width, which is causing all these issues.
Thanks in advance,
Prathap
@PrathapSV
It seems you are experiencing issues with the AutoFitColumns feature after upgrading from Aspose.Cells version 20.2 to 24.12. Here are some insights and suggestions regarding your concerns:
Changes Between Versions
-
Behavioral Changes: While I don’t have specific details on the exact changes between these two versions, it is common for updates to introduce changes in how features like AutoFitColumns operate. This can sometimes lead to differences in behavior, especially if the underlying logic for calculating widths has been modified.
-
Intentional Changes: If the behavior of AutoFitColumns has changed, it may have been an intentional update to improve functionality or address previous limitations. However, without official release notes or documentation detailing these changes, it’s difficult to confirm.
Fixing the Issue
To address the issue without requiring users to modify all 5000 Excel files, you can consider the following approach:
- Custom AutoFit Logic: You can implement a custom logic to adjust the column widths based on the maximum content width of both text and any chart objects. Unfortunately, the built-in
AutoFitColumns method may not account for chart widths, so you would need to manually calculate the required width and set it accordingly.
Here’s a sample code snippet that demonstrates how to auto-fit a column based on the maximum content width, which you can extend to include chart widths:
using System.IO;
using Aspose.Cells;
namespace CustomAutoFit
{
public class AutoFitWithCharts
{
public static void Run()
{
string dataDir = "path_to_your_directory";
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
// Auto-fit the column based on text content
worksheet.AutoFitColumn(4);
// Here you would add logic to check for chart widths and adjust the column width accordingly
// For example:
// double chartWidth = GetChartWidth(worksheet);
// if (chartWidth > worksheet.Cells.GetColumnWidth(4))
// {
// worksheet.Cells.SetColumnWidth(4, chartWidth);
// }
workbook.Save(dataDir + "output.xlsx");
}
// Placeholder for a method to get the width of a chart
private static double GetChartWidth(Worksheet worksheet)
{
// Implement logic to calculate the width of the chart
return 0; // Replace with actual width
}
}
}
Conclusion
While the upgrade may have introduced some unintended issues with AutoFitColumns, implementing a custom solution to account for both text and chart widths should help mitigate the problem. If you continue to face challenges, consider reaching out to Aspose support for more detailed insights into the changes between versions.
If you have any further questions or need additional assistance, feel free to ask!
@PrathapSV
Would you like to provide your sample files and runnable test code? If you can provide sample files and two versions of test result files, it will be very helpful for us to locate the issue. We will check it soon.
Here you go. Attached is the sample project replicated along with the excel file and the issue before/after screenshots.
Change the file path as per your directory location.
chart autofit col issue.zip (284.2 KB)
.
@PrathapSV
By testing on the latest version v25.4 using sample files and code, we can reproduce the issue. Excess data appears in the printing area after adjusting the column width and saving the worksheet to the image.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-58271
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@PrathapSV ，
After running your code with Aspose.Cells v20.2, the left chart is also intruded.
It seems that your code is changed after upgrading to Aspose.Cells v24.12.
Is it possible that sourceWorksheet.AutoFitColumns(); is just added after upgrading to Aspose.Cells v24.12?
@PrathapSV
Please auto fit column “M” in MS Excel, then print preview the worksheet, you will find the part ”Chart 1“ will be printed as Aspose.Cells.
If you call AutoFitColumns, the width of the Column “M” becomes smaller.
But the position and size of the chart “Chart 1” is “absolute” (<xdr:twoCellAnchor editAs="absolute">), so it will not be moved or changed, then the chart will be in the print area “L1:X27”, finally the part of chart will be exported to image too. Aspose.Cells works same as MS Excel.
Is it possible that sourceWorksheet.AutoFitColumns(); is just added after upgrading to Aspose.Cells v24.12?
-No we didn’t add this in v24.12, it was there in the older version too.(20.2)
If you call AutoFitColumns, the width of the Column “M” becomes smaller.
But the position and size of the chart “Chart 1” is “absolute” (<xdr:twoCellAnchor editAs="absolute"> ), so it will not be moved or changed, then the chart will be in the print area “L1:X27”, finally the part of chart will be exported to image too. Aspose.Cells works same as MS Excel.
Is there a way to fix this now, in our scenario as a global solution?
@PrathapSV,
Thanks for your feedback.
We will look into the issue further and get back to you soon.
@PrathapSV
Please check attached two results that are generated by your project with 24.x and 20.x of Aspose.Cells. We think the “issue” also exists for 20.x versions. Would you please confirm whether you can get different results by your provided project with those two different versions at your end?
20.x.jpg (194.8 KB)
24.x.jpg (202.7 KB)
It looks the same for me when I run the sample with these two different versions.
However, all customers in production saying the they had no issues with previous version. It’s a different aspect though, will double check.
But meanwhile, is there a way to fix this now, without having the users to modify all the 5000 excel files. Basically, We need the ‘AutoFitColumns’ to consider the chart objects when doing auto fit, looks like now it relies on the ‘text content’ for auto fit.
Please let us know,
Thanks,
Prathap
@PrathapSV
How about removing the code of auto-fitting from your application to fix this issue? It is hard for us to guess the cause of the difference too without the project that can reproduce the issue.
For auto-fitting with other objects such as the chart, the behavior of our component is same for those versions, also same with ms excel. In ms excel you cannot make it auto-fit row/columns according to the chart’s size. We are afraid we cannot make such kind of change too.