Sorting a pivot table in VB.Net

Aspose.Cells Product Family
1

Hi Im currently evaluating the capabilities of Aspose.cells and Im having trouble getting a pivot table to autosort.

I have a table of data that is 21 columns wide by a variable number of rows that lists defects on a production line. I am trying to get a pivot table that show a pareto of the defects. I can do it manually in excel but I cant get Cells to do it.

my code is :

Dim pivotTables As PivotTables = failuresheet.PivotTables

Dim index As Integer = pivotTables.Add("=A1:U" + CStr(failurerow), "A" + CStr(failurerow + 3), "PivotTable2")

Dim pivotTable As PivotTable = pivotTables(index)

pivotTable.AddFieldToArea(PivotFieldType.Row, 8)

pivotTable.AddFieldToArea(PivotFieldType.Data, 8)

Dim pivotFields As PivotFields = pivotTable.RowFields

Dim pivotField As PivotField = pivotFields(0)

pivotField.IsAutoSort = True

pivotField.IsAscendSort = True

pivotField.AutoSortField = -1

This gives the following pivot table in the spreadsheet :

Count of Defect
Defect Total
Assembly - Wrong Orientation 6
Solder - Solder Bridge 25
Component - Extra Component 1
Component - Damaged 16
Test - Test Methodology 1
Assembly - Missing Component 9
Component - Faulty 12
Assembly - Misplaced \ Misaligned Component 2
Test - Test Equipment Fault 3
(blank)
Solder - Unsoldered 1
Assembly - Incorrect Component 1
Solder - Dry Joint 4
Wiring - Wiring Connected Wrongly 1
Wiring - Wiring Damaged 1
ID Ten T Error 8
Bonding - Insufficient 1
Contamination - Unknown 1
Grand Total 93

This displays all the information I need but I need it to autosort the Total ('Count Of Defect') Column in the pivot table to look like this :

Count of Defect
Defect Total
Solder - Solder Bridge 25
Component - Damaged 16
Component - Faulty 12
Assembly - Missing Component 9
ID Ten T Error 8
Assembly - Wrong Orientation 6
Solder - Dry Joint 4
Test - Test Equipment Fault 3
Assembly - Misplaced \ Misaligned Component 2
Contamination - Unknown 1
Bonding - Insufficient 1
Solder - Unsoldered 1
Assembly - Incorrect Component 1
Component - Extra Component 1
Wiring - Wiring Connected Wrongly 1
Wiring - Wiring Damaged 1
Test - Test Methodology 1
(blank)
Grand Total 93

I have tried 0 and 1 for the autosort field as well but nothing seems to happen.

Im probably doing something stupid but can you help ??

Thanks

Kev

2

Hi,

Thank you for considering Aspose.

After an initial test we have found out the issue you have mentioned. We will get back to you soon.

Thank You & Best Regards,

3

Hi,

Thank you for considering Aspose.

Well, after further exploring your issue, we think that you can modify you code as per the following
code to get the desired result,

Dim pivotTables As PivotTables = failuresheet.PivotTables
Dim index As Integer = pivotTables.Add("=A1:U" + CStr(failurerow), "A" + CStr(failurerow + 3),
"PivotTable2")
Dim pivotTable As PivotTable = pivotTables(index)
pivotTable.AddFieldToArea(PivotFieldType.Row, 8)
pivotTable.AddFieldToArea(PivotFieldType.Data, 8)
Dim pivotFields As PivotFields = pivotTable.RowFields
Dim pivotField As PivotField = pivotFields(0)
pivotField.IsAutoSort = true;
pivotField.IsAscendSort = false;//Descending
pivotField.AutoSortField = 0;//The first data field.

Thank You & Best Regards,

4

Hi,

Thanks for the information but unfortunatley it made no difference and gave the following results

Count of Defect
Defect Total
Assembly - Wrong Orientation 6
Solder - Solder Bridge 26
Component - Extra Component 1
Component - Damaged 16
Test - Test Methodology 1
Component - Faulty 14
Assembly - Missing Component 13
Assembly - Misplaced \ Misaligned Component 2
Test - Test Equipment Fault 3
(blank)
Solder - Unsoldered 1
Assembly - Incorrect Component 1
Wiring - Wiring Connected Wrongly 1
Wiring - Wiring Damaged 1
Solder - Dry Joint 2
ID Ten T Error 8
Bonding - Insufficient 1
Contamination - Unknown 1
Grand Total 98
5

Hi,

Thank you for considering Aspose.

Please try the latest version of Aspose.Cells for .NET V4.6.0.1 (Attached with the Post). If you still face the problem, please send us you template file. So we can figure out your issue.

Thank You & Best Regards,

6

Hi,

I tried the new version (4.6.0.1) and it made no difference. I do not use a template file, I generate 100% of the spreadsheet within my VB.Net code.

I have attached a spreadsheet generated by the code for you to look at.

Thanks

Kevin

7

Hi,

Thank you for sharing the output file. We will check it and get back to you soon.

Thank You & Best Regards,

8

Hi,

Thank you for Considering Aspose.

Please try the attached latest version of Aspose.Cells. We have fixed the issue regarding PivotField.AutoSortField does not sort the data properly.

Thank You & Best Regards

9

Hi,

My apologies for the delay in replying but we have been closed for christmas.

I have tried the new version that you attached and it now doesn't work at all.

My code generates the spreadsheet and saves it in Excel2003 format I then open the file in Excel2003 using the following code:

workbook.Save(fileName, FileFormatType.Excel2003)

Process.Start(fileName)

When excel opens I get an error message "Unable To Read File"

If I hit 'OK' then excel opens the file and displays a 'Repairs to test.xls' message box with the following message

Errors were detected in 'test.xls' but Microsoft Office Excel was able to open the file by making the repairs listed below. Save the file to make these repairs permanent.

PivotTable report 'PivotTable2' on '[test.xls]Failures' was discarded due to integrity problems.

The spreadsheet now opens but as the error message states the pivot table has been removed.

I have attached the generated spreadsheet.

Thanks

Kevin

10

Hi,

Thank you for considering Aspose.

We have found the issue after an initial test. We will figure it out and get back to you soon.

Thank You & Best Regards,

11

Hi,

Thank you for considering Aspose.

Can you please try the attached latest version of Aspose.Cells. I have checked the test.xls file you attached in one of your previous posts and it works fine with this latest version. Please do let us know if you still face any problem in this latest release.

If you still face the problem, please create a sample application and share it here. We will look into it.

Thank You & Best Regards,

12

Hi , thank you for your quick response but unfortunatley it still has exactley the same problem (please see the attached screen shot).

I will try and write a sample application for you today.

You should also see the error if you open the text.xls file I attached with the last reply.

Thanks

Kevin

13

Hi,

I have completed a sample application by cutting and pasting most of the code from my real application . The main difference is that I populate the spreadsheet with random data rather than data from a dataset.

The sample application works great !! The pivot table is generated and is sorted correctly ! I have tried both acending and decending sorting and they both work !

But the real application still doesn't work and the code is practically identical !

I tried deleting the reference to apose.cells and adding the reference back in again but no difference !

Thanks

Kev

14

found this in the microsft knowledge base

`http://support.microsoft.com/default.aspx?scid=kb;en-us;819853`

I am running Office 2003 with SP3 installed so shouldn't see this issue.

Kevin

15

Hi,

Thank you for providing us the details. We will get back to you soon.

Thank You & Best Regards,

16

I think I have found the problem.

Your pivot table routine looks like it can't handle any NULLs in the data !

I changed my source code to insert one space (" ") into the cell if the data was a null and it seems to be working now.

Thank you for your assistance and quick responses.

Kevin

17

Hi,

Thank you for considering Aspose.

After checking your provided file, we found out that this issue is caused by the DateTime values. We will look into it and fix this soon.

Thank you for your info and files.

18

Thank you,

I changed my source code to remove all the nulls and this looked like it was working but a trial I carried out today gave the same error. I can tie the error down to two particular lines in the spreadsheet but they don't look any different from any other line ! So I will await your update.

Thanks

Kevin

19

Hi,

Please try the attached version (4.6.0.6), we have fixed the bug of creating the pivot tables whose data sources contain DateTime values.

Thank you.

20

Hi,

This update has corrected the issue. I no longer get the error message and the pivot table is created correctly. I have also removed the code I wrote to add spaces when it found NULLs in the data and it still works fine.

Thanks for your help.

Kevin