Compatibility problems in the pivot table

Aspose.Cells Product Family
1

Hi,

These options work only for documents in *.xls format:
PageFieldWrapCount
DisplayNullString
PreserveFormatting
ItemPrintTitles
EnableFieldList

I can set them in both *.xls and *.xlsx files. In the *.xlsx file these options are always set to default, but When I convert the file to *.xls - all values are correct.

I created simple Excel file with pivot table.
Options:
PageFieldWrapCount = 2
DisplayNullString = false
PreserveFormatting = false
ItemPrintTitles = false
EnableFieldList = false
*.xlsx:
PageFieldWrapCount = 0
DisplayNullString = true
PreserveFormatting = true
ItemPrintTitles = true
EnableFieldList = true
*.xls:
PageFieldWrapCount = 2
DisplayNullString = false
PreserveFormatting = false
ItemPrintTitles = false
EnableFieldList = false

Workbook wb = new Workbook(fName);
foreach(Worksheet ws in wb.Worksheets)
{
printLine(ws.Name, string.Empty, SPACE0); // my inner printing method
foreach(PivotTable pt in ws.PivotTables)
{
try { pt.RefreshData(); }
catch { }
try { pt.CalculateData(); }
catch { }
try { pt.CalculateRange(); }
catch { }

printLine(“PageFieldWrapCount”, pt.PageFieldWrapCount.ToString(), SPACE2);
printLine(“DisplayNullString”, pt.DisplayNullString.ToString(), SPACE2);
printLine(“PreserveFormatting”, pt.PreserveFormatting.ToString(), SPACE2);
printLine(“ItemPrintTitles”, pt.ItemPrintTitles.ToString(), SPACE2);
printLine(“EnableFieldList”, pt.EnableFieldList.ToString(), SPACE2);
}
}

Best regards,
Alex Shloma

2

Hi Alex,


Thanks for the template file with sample code.

After an initial test, I can find the issue as you have mentioned for XLSX file. I have logged a ticket with an id: CELLSNET-40114. We will look into it soon.

Thank you.


3

Hi,


You need to try our latest version/fix: v7.0.3.2, your issue is fixed.

Thank you

4

Hi,

Previous problem was fixed, but I found the same problem with ‘EnableDrilldown’.Pleasee see attached file.

Thank you,
Alex Shloma

5

Hi,


Do you mean it still returns “True” for your template XLS file’s pivot table?
e.g using the code:
MessageBox.Show("EnableDrilldown " + pt.EnableDrilldown.ToString()); --> True
Thank you.
6

Yes, you are correct.

Thank you,
Alex Shloma

7

Hi,


Thanks for the confirmation regarding EnableDrildown attribute. I have reopened your issue now, we will fix it soon.

Thank you.
8

Thank you!

Best regards,
Alex Shloma

9

Hi,

I found more few properties with the same problem:
PivotTable
Tag
DisplayImmediateItems
EnableFieldDialog
EnableWizard
PivotField
DragToColumn
DragToHide
DragToPage
DragToRow

In *.xlsm documents these properties always are true (logical) or empty (string).

I used these macro to change properties:
I had problems with some commands in Excel 2010, but in Excel 2003 they are working properly. I created documents in Excel 2003. These documents and the documents that were converted to *.xlsm have no problem with given macro in Excel 2010.

Sub RestrictPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
On Error Resume Next
.EnableWizard = False
.EnableFieldDialog = False
.Tag = “”
On Error Resume Next
.DisplayImmediateItems = False
On Error Resume Next
For Each pf In .PivotFields
With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToHide = False
End With
Next pf
End With
End Sub

Sub AllowPivotTable()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
With pt
.EnableWizard = True
.EnableFieldDialog = True
.Tag = “tag_str long str”
On Error Resume Next
.DisplayImmediateItems = True
On Error Resume Next
For Each pf In pt.PivotFields
With pf
.DragToPage = True
.DragToRow = True
.DragToColumn = True
.DragToHide = True
End With
Next pf
End With
End Sub
I use Aspose.Cells v7.0.3.2.

Thank you,
Alex Shloma
10

Hi,


I am afraid, Aspose.Cells does not support to create/manipulate or run Macros dynamically in Excel template files. So, if the attributes are set by macros, I am afraid, when you open the template file by Aspose.Cells API, the macros are not executed for the pivot tables, hence, you will not get correct attribute values. To execute the macros, you need to open the Excel file into MS Excel to run the macros.

Thank you.
11

Hi,

I know about it. I run macro manually in Excel and saved the documents with changed properties. These documents I opened by Aspose.Cells and read property values. Please, check files that were attached to my previous message.

Thank you,
Alex Shloma

12

Hi,


Thanks for providing further details.

We have logged it. We will look into it soon. Once we have any update about it, we will let you know here.

Thank you.
13

Thank you!

Best regards,
Alex Shloma

14

Hi Alex,

Please try the fixed version: v7.0.3.3.


The EnableDrildown issue is fixed in it. We will look into the other properties later on.

Thank you.

15

Thank you. EnableDrildown issue is fixed. Looking forward to get all fixed.

Best regards,
Alex Shloma

16

Hi,

Your issue be fixed now, please download: Aspose.Cells for .NET v7.0.3.4

17

Thank you,

All works as expected.

Best regards,
Alex Shloma

18

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.