AutoFitColumns for PivotTable and IsGridDropZone

Aspose.Cells Product Family
1

Hello,


I am creating a Pivot Table using Aspose.

If I set pivotTable.IsGridDropZone = True then when I call pivotTable.AutoFitColumns() it does not autofit the PivotTable columns correctly (they are too small), please see attached file "IsGridDropZones True - incorrect.xlsx"

However, if I comment out the 'pivotTable.IsGridDropZone = True ’ line (ie. I don’t set this property) then AutoFitColumns() works correctly, please see attached file “Without IsGridDropZones - correct.xlsx”

Please kindly advise.

Thank you.


2

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.

3

Hello,


Please see attached file AsposePivotAutoFitInput.xlsx for input template.

The following code produces the correct result (please see AsposePivotAutoFitOutput-correct.xls):

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

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.xlsx”, New OoxmlSaveOptions)


However the following code, with the IsGridDropZone call, produces the incorrect result (please see AsposePivotAutoFitOutput-incorrect.xls):

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
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.xlsx”, New OoxmlSaveOptions)

Thank you.


4

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.

5

Hi,


I ran the code I posted now and it created the expected Pivot Table (please see attached screenshot) (I ran the code with the IsGridDropZone = True).

The only change I made was the filepaths:

"c:\Users\Richards\Documents\AsposePivotAutoFitInput.xlsx"
and
"c:\Users\Richards\Documents\AsposePivotAutoFitOutput.xlsx"

I am using Aspose.Cells 8.0.1.1

Please kindly try to run it again?

Thank you.


6

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)

7

Hi Shakeel,


Thank you for the quick and helpful response!

I have been very impressed with Aspose’s support on these forums, it is brilliant :slight_smile:
8

Hi,



Could you add the following two lines in your code segment before calling AutoFitColumns operations. I have tested it and it works fine.
e.g
Sample code:

…
pivotTable.IsGridDropZones = True

pivotTable.RefreshData()
pivotTable.CalculateData()

pivotSheet.AutoFitColumns()
…

Thank you.
9

Thank you, I tried it and it worked.


There is however still a problem whereby the columns are not made wide enough to take into account the width of the filter dropdown arrow, please see attached screenshot and excel output file.

Code:
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
pivotTable.IsGridDropZones = True
pivotTable.RefreshData()
pivotTable.CalculateData()

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.xlsx”, New OoxmlSaveOptions)

Please advise?

Thanks.
10

Hi,


1) After an initial test, I observed the issue as you mentioned. I found the Auto-fit Columns feature is not working properly for the Pivot’s filtered drop down cols although it is working fine for simple text cells in the cols. We did evaluate your issue a bit. We found as the C and D column headers contain both text and icon, so when calling pivotSheet.AutoFitColumns(), the columns’ width only calculate the text width but not the filtered icons width at all. This might be a limitation in Aspose.Cells but we will sort it out soon. I have logged a ticket with an id “CELLSNET-42612” for your issue. We will look into it soon.

2) By the way, as a tip you may always try to use the following piece of codes when you need to auto-fit columns which are also a part of a pivot table:
e.g
Sample code:

pivotTable.IsGridDropZones = True

pivotTable.RefreshDataFlag = True
pivotTable.RefreshData()
pivotTable.CalculateData()
pivotTable.RefreshDataFlag = False

pivotSheet.AutoFitColumns()


And, once we have any update on the issue “CELLSNET-42612”, we will let you know here immediately.

Thank you.
11

Thank you Amjad. I have used your code tip.

12

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.

13

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.
14

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


Thank you for the help.

15

Hi,


Good to know that your issue is resolved by the new fix/version 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.