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
Hi Alex,
Hi,
Previous problem was fixed, but I found the same problem with ‘EnableDrilldown’.Pleasee see attached file.
Thank you,
Alex Shloma
Hi,
Yes, you are correct.
Thank you,
Alex Shloma
Hi,
Thank you!
Best regards,
Alex Shloma
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()I use Aspose.Cells v7.0.3.2.
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
Thank you,
Alex Shloma
Hi,
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
Hi,
Thank you!
Best regards,
Alex Shloma
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.
Thank you. EnableDrildown issue is fixed. Looking forward to get all fixed.
Best regards,
Alex Shloma
Thank you,
All works as expected.
Best regards,
Alex Shloma
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.