Pivot Table: Why is this code failing?

Aspose.Cells Product Family
1

It fails with:

System.ArgumentException was unhandled
Message=this name or range is not supported.
Source=Aspose.Cells
StackTrace:
at Aspose.Cells.Pivot.PivotTable.RefreshData()
at Aspose.Cells.WorkbookDesigner.Process(Boolean isPreserved)
at Aspose.Cells.WorkbookDesigner.Process()
at TestAspose.Program.Procedure11() in C:\projects\csharp\TestAspose\Program.cs:line 30
at TestAspose.Program.Main(String[] args) in C:\projects\csharp\TestAspose\Program.cs:line 16

Now, if I don’t use the the designer stuff it works. This is a contrived example, the sample spreadsheet doesn’t use smart markers but the real code it does hence the use of the WorkbookDesigner class.

Is there a way to get around the problem other than not using smart markers?

Thanks

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Aspose.Cells;


namespace TestAspose
{
class Program
{
static void Main(string[] args)
{
Procedure11();
Console.WriteLine(“Done!”);
Console.ReadKey();
}

private static void Procedure11()
{
Workbook workbook = new Workbook(@"…\pivot_tables.xlsx");

WorkbookDesigner designer = new WorkbookDesigner();

designer.Workbook = workbook;


designer.Process();


workbook.CalculateFormula(false);
Cells cells = workbook.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);
dt.TableName = “MyTableName”;
dt.WriteXml(Console.Out);
}
}
}

2

This code fails as well:

private static void Procedure12()
{

Workbook workbook = new Workbook(@"…\pivot_tables.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

PivotTable pivotTable = worksheet.PivotTables[0];
pivotTable.RefreshData();
}

So the issue is the RefreshData method…

3

Hi,

Thanks for your posting and using Aspose.Cells for .NET and drilling down this issue further.

You are right. I was able to replicate this issue using the following code with the latest version:
Aspose.Cells
for .NET v7.3.0.3

We have logged this issue in our database. We will look into it and fix the problems. Once the issue is resolved or we have some other update for you, we will let you know aap

This issue has been logged as CELLSNET-40940.

I have attached the screenshot highlighting the exception for a reference.

C#


Workbook workbook = new Workbook(“pivot_tables.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];


PivotTable pivotTable = worksheet.PivotTables[0];

pivotTable.RefreshData();


Screenshot:
4

Hi,

We have fixed this issue.

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

5

Thanks for the quick fix.

Unfortunately there is something funky going on with these pivot tables and the way Aspose.Cells processes them.

Below is again the code. I also attached the spreadsheet. The code recalculates the pivot table and it creates a new spreadsheet pivot_tables2.xlsx.

The issue is this. In pivot_tables.xlsx there is one row on line 27 that is not included in the pivot table. I wanted to see if aspose.cells refreshes properly the pivot table. The code snippet refreshes the pivot table and it saves the workbook to pivot_tables2.xlsx. I also called CalculateData as per your advice from the other thread.

Now, if you refresh the pivot table in excel (open pivot_tables.xlsx) you get the table in aspose_screenshot_pivot_tables.

If you open pivot_tables2.xlsx which was created using the api you get the second screenshot. Do you see the difference? The dates lose the formatting and they are displayed on the first row of the pivot table instead of the second one.

Could you please take a look and see what’s going on? Maybe I am doing something wrong…

Thank you

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Aspose.Cells;
using Aspose.Cells.Pivot;


namespace TestAspose
{
class Program
{
static void Main(string[] args)
{
Procedure12();
Console.WriteLine(“Done!”);
Console.ReadKey();
}

private static void Procedure12()
{

Workbook workbook = new Workbook(@"…\pivot_tables.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

PivotTable pivotTable = worksheet.PivotTables[0];
Console.WriteLine(String.Join(", “, pivotTable.DataSource));
pivotTable.RefreshData();
pivotTable.CalculateData();

workbook.CalculateFormula(false);

Cells cells = workbook.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);
dt.TableName = “MyTableName”;
dt.WriteXml(Console.Out);
workbook.Save(@”…\pivot_tables2.xlsx");
}
}
}

6

Hi,


Thanks for your sample file, screen shots and details.

I am able to find the new issue as you have mentioned. I think you are right, there is some problem while refreshing the pivot table or calculating the pivot data in the template file. Using your template file and code, I can generate a file in which I can see the Date formatting is lost as you pointed out. We will surely look into it and get back to you soon.

I have reopened your issue “CELLSNET-40940” again and our concerned developer will look into it soon.

Thank you.
7

Thank you for reopening the test case. I am just curious, when you do minor fixes, you do run regression tests, right? Also, I assume that you have test cases for all the features that you support. Am I correct?

8

Hi,

Thanks for your question and considering Aspose.

Yes, we have automated testing framework and we test all features. Minor releases are equally useful for production use.

Please ignore the above fix, because your issue is still under progress and it was resolved partially.

I have also added your comments in our database against the issue id: CELLSNET-40940

Once, it is resolved completely, we will provide you a fix again.

9

Hi,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and

try this fix: Aspose.Cells

for .NET v7.3.0.5 and let us know your feedback.

10

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

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