Smart markers and pivot table

Aspose.Cells Product Family
1

Hello there:

I have a template with pivot table that use to work perfectly with 18.4 version of ASPOSE.Cells
I’m using smart markers in the headers and grouped data, so setting the datasource used to work.
Now, after the solution was migrated to the 20.9 version, it stopped working and I cannot get the smart markers working anymore.
This is for cells that are part of a pivot table. If I put a smart marker outside of it, another row same sheet, it works perfectly, so I’m pretty sure is an issue related to the pivot table. I have also another tab with raw data that works fine.

If you see attached picture, the circled marker works. Pivot table smart markers don’t. :frowning:
Could you help me to get this working again, please?

Thanks in advance

pivot_table_issue.JPG (83.1 KB)

2

@jgarceso
Could you share a sample proejct and a template file here? We will check it soon.

3

Hello:

SO My case is the following.

I have 1 sheet with raw data that uses smart markers. My pivot table is in another sheet and it has a reference to it, so the smarts markers are the same.

For pivot table, when processing is changing the smart markers names and appending a 2, to make them different than my reference data that also has the same smart markers.

For example, “&=Data.sup_name”, I have this one in the pivot table and in the raw data. after process the file template with data, I open the template and this will be changed to “&=Data.sup_name2” and of course, I won’t have this column in my dataset and it will be shown as blank.

I’ve tried many times and is always changing the template as I said and that could be the reason why I don’t get data in pivot table.

This was working as I said in version 18.4. Current version 20.9

If you try to change any name in the sample template(pivot table) after has been processed to remove the appended “2”, excel will crash, so you need to press ESC key to undo the change, otherwise you won’t be able to close the file.

under the folder bin/Debug/net6.0 are the templates and the files generated when running the program.
You will notice also that I put 2 smart markers outside the pivot table and work perfectly.

See the picture, that is the status of my smart markers in the sheet after processes the data into the template. Names are changed as I described before.

Notice those outside the pivot table, werent’ touched.

Other picture is that you can see both smart markers in both sheets how they are originally
smart markers in pivot table and another sheet as reference.png (117.3 KB)

Clearly is an issue related to pivot table and reference data using the same smart markers name. This was working before :frowning:

“template_sample_original.xls” is the template how it should be. I would recommend creating copies and changing the code to test. You will see that after the process, smart markers change in the template.

changed smart markers.JPG (125.2 KB)

Attaching my sample project
ConsoleApp1.zip (7.4 MB)

Thanks for your help.

4

@jgarceso,

Thanks for the screenshots and samples.

I checked your Excel file (“template_sample.xls”) and tested your scenario/case using latest version of Aspose.Cells for .NET v22.8. I am still not sure if the issue is with the Aspose.Cells for .NET APIs or with your source data for the pivot table. Moreover, you have not provided the template Excel file “template_sample_original.xls”. Could you please also provide the “template_sample_original.xls” file, we will check it further. Furthermore, in your sample file “template_sample.xls”, there are some other fields, e.g., “Offre”, “Fournisseur”, etc. for which I am not sure if these are renamed fields, please elaborate it?

5

hello:

Thanks for your reply. I’m adding a new version of the sample app.
“template_sample.xls" now it has the original in prd code.
Notice, “%%” is being used as asposeMarkerPrefix since there is another logic to replace those by “&=” as required to make smart markers work. Check the new code, please. After processing, I’m getting blank values in pivot table but not in raw data as explained before. This is telling me that smart markers in pivot table are not working as before, version 18.4. (The code has not changed in our side, just the ASPOSE.cells version to 20.9).

In my previous project I’ve tried to add the syntax directly to the pivot table and raw data, but that was causing smart markers changes after processing, so I’ve decided to update the code to something more similar that we have in production environment.

What we are doing is replace “%%” by “&=” in each sheet, then set the datasource and process.

If you try version 18.4 it works as expected.

Check “Analyse” sheet. Only smart markers within pivot table won’t work but the one that I put outside the pivot table for testing, it does, so it couldn’t be more clear where is the issue :frowning:

Check the picture as well, please
sample_screen.JPG (146.6 KB)
ConsoleApp1.zip (7.4 MB)

Thanks for your help.

Regards,

6

@jgarceso,

I did test your scenario/case using your template file and sample.
Since the source data range is set for the the pivot table so it is same for chart. But you have updated/replaced “%%” chars by “&=” to make these markers which is not reflected properly in the pivot table and hence in pivot chart. So, could you add a few lines in your code if this works for your needs. You may re-update/change the data source for pivot table and chart accordingly:
e.g.
Sample code:

.......
foreach (Worksheet ws in wd.Workbook.Worksheets)
{
                Aspose.Cells.Range r = GetMaxRange(ws);

                for (int col = 0; col < r.ColumnCount; col++)
                {
                    for (int row = 0; row < r.RowCount; row++)
                    {
                        string cellValue = r[row, col].Value as string;
                        if (string.IsNullOrEmpty(cellValue))
                            continue;

                        r[row, col].PutValue(cellValue.Replace(asposeMarkerPrefix, "&="));
        }
    }
}

//Get the pivot table.
PivotTable pivotTable = wd.Workbook.Worksheets["Analyse"].PivotTables[0];
//update the source data which becomes markers now.
string[] lsRanges = new string[] { "Analyse!pivot_table_source" };
pivotTable.ChangeDataSource(lsRanges);
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.CalculateRange();            
             
wd.Workbook.Worksheets["Analyse"].Charts[0].PivotSource = "Analyse!Tableau croisé dynamique1";

wd.SetDataSource(dt);
..........

Let us know with details where you find the issue in the output Excel file, we may log appropriate ticket for it (if appropriate).

7

hello:
Thanks again for your help. The piece of code didn’t work. The behavior is the same as before and I already explained that. Analyse tab smart markers work as long as they are not in the pivot table.

I would insist this seems to be an issue introduced in upper versions since it was working fine (same template, code) in version 18.4

I’m attaching the same code that works using the 18.4 version this time. Compare to the other project that I sent. The only difference is the version of ASPOSE.Cells

In the project, you could find a folder called “dlls” and 2 folders inside of it. ! for each aspose version.
(18.4 working, 20.9 not working). You could chnage the reference in the project and check that is a version issue.

The code sent by you is no needed to make this work FYI.

Please see the pictures of the same file using different versions. 20.9 version shows “blank” on pivot table whereas 18.4 shows the data correctly.

Thanks again for your help.

Regards,
SampleUsingAspose18.4.JPG (141.6 KB)
SampleUsingAspose20.9.JPG (160.3 KB)

ConsoleApp1.zip (9.2 MB)

8

@jgarceso,

Thanks for providing screenshots and more samples.

Since I was already able to reproduce the issue you mentioned by using your template file as you do not want to add lines of code to re-update/change the data source for the pivot table. I found some Smart Markers inserted into pivot table are not processed properly in newer versions. I have logged a ticket with an id “CELLSNET-51987” for your issue. We will investigate and look into the details of the issue.

Once we figure it out or we have updates on it, we will let you know.

9

Thanks, @Amjad_Sahi. Where I could see the ticket progress? Or will you keep me updated here?

Regards,

10

@jgarceso,

Yes, we will keep you posted with any updates (once available) on your issue.

11

Hello, there! Do you have any news? 11 days have been past from the issue request. We have clients that are complaining about this issue since a core functionality in our end is broken now.
Please, any update would be helpful to see what is the ETA to get a fix.

Regards,

12

@jgarceso,

Your issue is not resolved yet. We are evaluating your issue and will get back to you with updates or ETA on it.

We are sorry for any inconvenience caused!

13

@jgarceso,

The ETA for your issue is end of this week. Once we figure it out, we will update you.

14

ok thanks for the update. I will be watching since as I said, we have clients in production waiting for this fix
Regards,

15

@jgarceso,

Sure, we will keep you posted with new updates (once available) on it.

16

@jgarceso,

We are pleased to inform you that your issue has been resolved now. The fix will be included in the next version of the upcoming release (Aspose.Cells for .NET v22.10) which is scheduled in the first/second week of October 2022. You will also be notified when the next version is released.

At the moment, could you please move the data processing section before the data using section if it makes any difference. Please see the following code for your reference:


            //please set the data of a designer spreadsheet before using it.
            wd.SetDataSource(dt);
            wd.Process(false);

            //Get the pivot table.
            PivotTable pivotTable = wd.Workbook.Worksheets["Analyse"].PivotTables[0];
            //update the source data which becomes markers now.
            string[] lsRanges = new string[] { "Analyse!pivot_table_source" };
            pivotTable.ChangeDataSource(lsRanges);
            pivotTable.RefreshData();
            pivotTable.CalculateData();
17

hello, @Amjad_Sahi

Thanks for your reply. I did what you suggested but it doesn’t make any difference. the result is the same.

It seems that we need to upgrade to the latest version when available.

Regards,

18

@jgarceso,

Please try our current latest version Aspose.Cells for .NET v22.9 with suggested code segment and let us know your feedback.

19

The issues you have found earlier (filed as CELLSNET-51987) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi