PivotTable. Reading configuration of Group Field

Hi,

I found ‘set’ methods, but none of ‘get’ methods. Could you please explain how to get configuration of Group Field.
SetAutoGroupField
SetManualGroupField
SetUngroup

Thank you,
Alex Shloma

Hi,


I have logged a ticket for it with an id: CELLSNET-40176. We will look into it if we can provide the Get methods or relevant properties for it.

By the way, could you give us sample Excel file (containing the pivot table) by manually created in MS Excel to show on how do you want to have Get methods to provide you the returned values.

Thank you.

Hi,

I need these properties for analyzing documents from clients, so I do not know how they may use grouping.

In the help of Excel I found that method has four optional arguments, so I need these values and some mechanism to determine what the arguments is used.
Here is help from Microsoft http://msdn.microsoft.com/en-us/library/bb178847%28v=office.12%29.aspx.

I attached images of dialogs.

Thank you,
Alex Shloma

Hi Alex,


Thanks for providing more details

We have logged it and it might be helpful.

Thanks,
Hi,

Please use this sample code for your needs:
e.g
PivotTable table = wb.Worksheets[0].PivotTables[0];
SxRng range = table.RowFields[0].Range;
Object start = range.Start;
Object end = range.End;
Object by = range.By;
Object type = range.GroupByType;

Also, we recommend you to try the latest fix: Aspose.Cells for .NET v7.0.4.1

Hi,


How we can read all group types of grouped field? In Excel it’s a Boolean array.

Thank you,

Alex Shloma

Hi,

We have logged your question in our database.

Once, we will get any answer, we will update you asap.

Hi,


I have more one question. How can I determine that for start/end are used default (auto) values?
Excel may get the values of start/end from source data or uses values that user set. The main difference that default values would be updated every Refresh operation, but user data will always be the same. A default value has a selected checkbox in Excel GUI.

Thank you,
Alex Shloma

Hi,

Sorry for delay, I have added your questions in the database log.

Also, I have reopened your issue because it was close.

Hi,

Please using Range.GroupByTypes to get the Boolean array of all group types of grouped field and we will give properties to determine start/end are used default (auto) values in next minor release.

FYI: The current latest version is Aspose.Cells for .NET v7.0.4.3

Hi,

Please download: Aspose.Cells for .NET v7.0.4.4

Please use the following sample code to determine start/end are used default (auto) values

C#


SxRng range = table.RowFields[0].Range;

Console.WriteLine(range.IsAutoStart);

Console.WriteLine(range.IsAutoEnd);



Hi
Shakeel,


I found some discrepancy within values from *.xlsx and *.xls documents. In *.xlsx document all the fields, that were used in group (date - time), have all types of group (year, quarter, month etc.). In *.xls document, the same fields have only current type of group (one of the listed above).

As I understand, the first variant is logical (user) view, and the second - representation of file structure. All ways are right, but there are difference between the same documents in different file formats, so it’s problem.
Will be fine, if you create separate properties for these two types of views or reduce it to one type, at least.

Thank you,
Alex Shloma

Hi,

Please explain the problem with a sample test code, sample source xls/xlsx files and screenshots.

We will log it after looking into it. Thanks for your help.

Hi,


I’ve created samples.
Excel views:
1. GUI - ExcelGUI.png (the same for all fields in group);
2. File (*.xlsx):
name=“04.01.2012 14:01” - groupBy=“months”
name=“Quarters” - groupBy=“quarters”
name=“Years” - groupBy=“years”
Aspose.Cells v7.0.4.4
1. *.xls (as Excel 1. File)
‘04.01.2012 14:01’ - months
‘Quarters’ - quarters
‘Years’ - years
2. *.xlsx (as Excel 2. GUI)
‘04.01.2012 14:01’ - months, quarters, years
‘Quarters’ - months, quarters, years
‘Years’ - months, quarters, years

Thank you,
Alex Shloma

Hi,

Thanks for your help and illustrating your issues.

We have logged them in our database. We will get back to you asap.

Hi,

Please download and try the latest fix: Aspose.Cells for .NET v7.0.4.5

Thank you!!! All works fine.


Best regards,
Alex Shloma

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


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