Pivot table: date is not shown in sorted order

Aspose.Cells Product Family
1

Dear Support,

Please see the attached “test.xlsx” sheet. I have a temp sheet through which I am creating pivots. I am able to create pivot except that the date values are not shown in the sorted order. Could you please suggest as what should I do to get the pivot s sorted by date i.e. with respect to this sheet “4/5/2013” is shown in end and not in the second column. Any suggestion would be much appreciated. Here is the code to create pivots:

private static void CreatePivotTablesSheet(Workbook workbook)

{

Style style = workbook.CreateStyle();

Worksheet sheet = workbook.Worksheets["temp"];

Cells cells = sheet.Cells;

Range cellRange = cells.MaxDisplayRange;

string sourcedata = cellRange.RefersTo.ToString();

//Obtaining the reference of the newly added worksheet

Worksheet pivotSheet = workbook.Worksheets[0];

pivotSheet.Name = "pivots";

PivotTableCollection pivotTables = pivotSheet.PivotTables;

//****************************************************************** jobPivotTable

int index = pivotTables.Add(sourcedata, 2, 0, "Job1Pivot");

//Accessing the instance of the newly added PivotTable

PivotTable jobPivotTable = pivotTables[index];

jobPivotTable.SaveData = true;

//Draging the "CUSTOMER" field to the row area.

jobPivotTable.AddFieldToArea(PivotFieldType.Row, 2);

//Draging the "DAY" field to the column area.

jobPivotTable.AddFieldToArea(PivotFieldType.Column, 0);

//Draging the "Mins" field to the data area.

jobPivotTable.AddFieldToArea(PivotFieldType.Data, 3);

//Accessing the data fields.

jobPivotTable.DataFields[0].DisplayName = "Job";

jobPivotTable.ColumnHeaderCaption = "DAY";

jobPivotTable.RowHeaderCaption = "Employee";

jobPivotTable.IsAutoFormat = true;

jobPivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

//jobPivotTable.DataFields[0].NumberFormat = "#,##0.0";

jobPivotTable.DataFields[0].NumberFormat = "_(#,##0.0_);_((#,##0.0);_(\" - \"??_);_(@_)";

jobPivotTable.RowFields[0].IsAutoSort = true;

jobPivotTable.ColumnFields[0].IsAutoSort = true;

jobPivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;

jobPivotTable.IsGridDropZones = true;

//******************************************************************

pivotSheet.AutoFitColumns();

workbook.AcceptAllRevisions();

}

I have also tried setting the style for first column

Style st = workbook.CreateStyle();

st.Number = 14;

string lastCell = "A" + (sheet.Cells.MaxDataRow + 1);

Range rng = sheet.Cells.CreateRange("A1:" + lastCell);

StyleFlag flag = new StyleFlag();

flag.All = true;

rng.ApplyStyle(st, flag);

It is not creating the pivots in the order the rows are shown in temp sheet. Please help.

Thank you,

Radha

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

<![endif]–>

2

Hi,


Thanks for the sample Excel file and code.
I have worked over your query/issue.

When I manually do the sorting by right clicking on the Day field on pivot table, it can’t help much either. The entry “4/5/2013” remains stayed at its current position (at the end). Could you do the sorting for your needs mainly in a separate sheet for the pivot table manually in MS Excel, re-save the file and post it here. Also mention how you do this in MS Excel.

Moreover, it looks like Aspose.Cells has problem with moving the pivot item from source indexed position to target position. I did try the following sample code with your file but in vain.

Sample code:

Workbook wb = new Workbook(“e:\test2\test.xlsx”);
PivotTableCollection pivotTables = wb.Worksheets[0].PivotTables;
//Accessing the instance of the added PivotTable
PivotTable pivotTable = pivotTables[0];
PivotItemCollection items = pivotTable.ColumnFields[0].PivotItems;
MessageBox.Show(items[6].Name);“4/5/2013”
items[6].Move(1); //Does not work
//items.ChangeitemsOrder(6,1); //Does not work either.
MessageBox.Show(items.Count.ToString()); 7 fine.

wb.Save(“e:\test2\output1.xlsx”);

We may log a ticket after getting the file with your desired results.

Thank you.
3

Hello Amjad,

Sorry for the delay in replying. I tried this manually in Excel and I agree with you that “4/5/2013” date does not change its position. I guess it is how excel behaves with date column and aspose.cells will not have much to do on this. I am still trying to figure out as how should I show/store the dates (formatting) in the excel sheet so that the pivots are created in sorted date order. I will let you know if there is something else on which you guys can help me out.
I really appreciate your reply. Thank you once again!

Radha

4

Hello Amjad,

I found a workaround, here are the steps i have followed manually in excel (please use the test.xslx sent earlier).
1. Insert a new column to right of DAY
2. Select the range A1:A33
3. In menu bar click "Data"
4. On the Data menu, click Text to Columns.
5. In Step 1 of the Convert Text To Columns Wizard, make sure that the Delimited option is selected, and then click Next.
6. In Step 2 click Next. (TAB should be already checked here)
7. In Step 3 of the Convert Text To Columns Wizard, check Date MDY
8. Here on this step type B1 in the Destination box, and then click Finish.
9. Delete the first column and then create a pivot. This shows the date order correctly.

Please can you suggest are these steps possible with aspose? Please let me know how can i do this with aspose.cells.

Many thanks,
Radha


5

Hi,


Thanks for providing us the manual steps to accomplish your tasks.

I have logged a ticket with an id “CELLSNET-41854” for your issue. We will look into it if we can support your needs or have some other workaround for it to sort the Date in correct order. Our concerned developer will evaluate your issue thoroughly.

Once we have any update on it, we will let you know here.

Thank you.



6

Hi,

Please download and try this fix: Aspose.Cells for .NET v7.5.1.2.

Please see the following code.


C#


book.Worksheets[0].PivotTables[0].ColumnFields[0].PivotItems[6].Move(-5);//move up

book.Worksheets[0].PivotTables[0].RefreshDataOnOpeningFile = true;


7

Hi Shakeel,

I wish this to be that simple but my sheet is not a static sheet so your suggestion does not work in my scenario. The dates can vary and I will not be sure as which dates need to be moved to show the pivot in sorted order. My test sheet has the dates in sorted order but the pivots are not showing the dates in sorted order so the question is how to show the pivot in the sorted date order. Anyhow thanks for your reply.

Amjad, could you please confirm if the ticket is still open and you guys will look into this and will try to find a solution. Please let me know if there is any other workaround for it to sort the Date in correct order, also if the manual step done in excel is possible with aspose. Many thanks for your help, appreciate it.

Best regards,
Radha

8

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We are afraid, you will have to find the workaround for your issue using the above sample code. However, we have logged your comments in our database. We will look into it and provide you a sample code if possible. Once, there is some update for you, we will let you know asap.

9

Hi,

Thanks for using Aspose.Cells for .NET.

We are afraid,we could not find a solution except moving the pivot item’s position now.

If you want to sort the pivot items, please try this sample code:

C#
pivotField.IsAutoSort = true;
pivotField.IsAscendSort = true;

10

As said the sheet will have dynamic data so moving the pivot item’s position would not be a solution for us.

Amjad, could you please advise if the manual step done in excel would be possible with aspose?

Thanks.

11

Hi,

Thanks for your posting and using Aspose.Cells.

We have investigated your issue and we are afraid, there is no other solution that we can suggest for your issue. Thanks for your understanding.

12

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.