Hello,
Hi Richard,
Thanks for your posting and using Aspose.Cells.
After initial investigation, we found that pivotTable.IsGridDropZone does not have effect on the auto fitting of the columns. We have tried it both ways by setting its value true or false.
Could you please provide us your full runnable sample code/project? It will help us investigate this issue more closely and we will update you asap.
Hello,
Hi Richard,
Thanks for your sample code and using Aspose.Cells.
There seems to be some problem with your code, because it does not generate the pivot table at all. When I open the output xlsx file, it shows message. Did you post a complete code? I tested it with the latest version: Aspose.Cells
for .NET v8.0.1.3. Which version did you use to generate your files correctly?
I have attached the output xlsx file and screenshot showing the error message for a reference.
Hi,
Hi Richard,
Thanks for your posting and using Aspose.Cells.
We were able to observe this issue. AutoFitColumns generate correct results when PivotTable.IsGridDropZone is set False, but when it is set to True, AutoFitColumns generate wrong results. It does not autofit the PivotTable columns correctly (they are too small).
Earlier the issue was occurring because Aspose.Cells is not working fine in License mode.
We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as CELLSNET-42610.
I have attached the output files and screenshot highlighting the issue for a reference.
C#
Dim book As Workbook = New Workbook(“AsposePivotAutoFitInput.xlsx”)
Dim pivotSheet As Worksheet = book.Worksheets.Add(“Pivot Table”)
Dim pivotTables As Pivot.PivotTableCollection = pivotSheet.PivotTables
Dim dataSheet As Worksheet = book.Worksheets(“Data”)
Dim pivotIndex As Integer = pivotTables.Add(String.Format("=Data!A1:{0}", CellsHelper.CellIndexToName(dataSheet.Cells.MaxDataRow, dataSheet.Cells.MaxDataColumn)), “A1”, “PivotTable1”)
Dim pivotTable As Pivot.PivotTable = pivotTables(pivotIndex)
pivotSheet.MoveTo(0)
pivotTable.AddFieldToArea(Pivot.PivotFieldType.Row, “GroupIdentifier”)
pivotTable.AddFieldToArea(Pivot.PivotFieldType.Row, “Group2Identifier”)
pivotTable.AddFieldToArea(Pivot.PivotFieldType.Row, “MainUnitNo”)
pivotTable.AddFieldToArea(Pivot.PivotFieldType.Row, “LeaseStart”)
pivotTable.AddFieldToArea(Pivot.PivotFieldType.Row, “LeaseEnd”)
pivotTable.PivotTableStyleType = Pivot.PivotTableStyleType.PivotTableStyleMedium15
Dim style As Style = book.CreateStyle()
style.Font.Size = 8
style.Font.Name = "Calibri"
pivotTable.FormatAll(style)
pivotTable.RowGrand = True
pivotTable.ColumnGrand = True
pivotTable.NullString = 0
pivotTable.EnableWizard = False
’If IsGridDropZones is False, the AutoFitColumns generate correct results
’If IsGridDropZones is True, then AutoFitColumns generate wrong results
pivotTable.IsGridDropZones = True
pivotSheet.AutoFitColumns()
pivotSheet.Cells.StandardHeight = 11.25
pivotSheet.FreezePanes(“H7”, 0, 7)
Dim flag As New StyleFlag
flag.FontSize = True
flag.FontName = True
pivotSheet.Cells.ApplyStyle(style, flag)
book.Save(“AsposePivotAutoFitOutput-IsGridDropZones-True-Wrong.xlsx”, New OoxmlSaveOptions)
Hi Shakeel,
Hi,
Could you add the following two lines in your code segment before calling AutoFitColumns operations. I have tested it and it works fine.
Thank you, I tried it and it worked.
Code:
Please advise?
Thanks.
Hi,
pivotTable.RefreshDataFlag = True
pivotTable.RefreshData()
pivotTable.CalculateData()
pivotTable.RefreshDataFlag = False
Thank you Amjad. I have used your code tip.
Hi Richard,
Thanks for using Aspose.Cells.
Please download and try this fix: Aspose.Cells
for .NET v8.0.1.4 and let us know your feedback.
The issues you have found earlier (filed as CELLSNET-42612) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
I tested it with 8.0.2.0 and it is now working correctly.
Hi,