Conditional formating in Pivot Table

Aspose.Cells Product Family
1

Hi


I’ve been happily running ASPOSE.CELLS for a number of years (v2.2). One of or reports creates a pivot table and puts conditional formatting on the cells within the pivot table. This has all but running fine for a number of years. The report is not run often so it has only recently been drawn to my attention that the cells the conditional formating are being applied to the wrong cells. The only thing I can tell that has changed is we are viewing the spreadsheets in Excel 2007 which we only changed to about 6 months ago.

I have checked the code and the right CellArea values are being set but when the report is produced the conditional formatting ‘apply to’ cell value when explored in Excel is incorrect and while the same incorrect cells are in the ‘apply to’ cell value there isn’t any obvious connection between what it should be and what it is.

Can anyone shed any light on this? I am running an older version (v2.2) of cells.net. Has anyone encountered this problem and solved it? Is it something about Excel 2007? Any help will be greatly appreciated.

Matt

2

Hi,


Well, since you are using some older version of the product for which we cannot help you much. It is quite possible that in your older versions the support for either pivot tables for Excel 2007 might not be included or enhanced or advanced conditional formatting of Excel 2007/2010 might not be completely supported. The only thing we can do is to recommend you to kindly upgrade to the latest versions of the product in which we have made enhancements and properly supported PivotTables/Pivot Charts and Advanced conditional formattings for Excel 2007/2010. The latest version is v7.5.0 that you may try. If you still have any issue (you might need to update your code a bit, so please check the docs for your complete reference: https://docs.aspose.com/display/cellsnet/Developer+Guide), we can check it and fix it in the new versions (we cannot include any enhancements or fixes in the older versions).

Thank you.

3

Thanks for the very quick reply. I’m impressed.


The version i am using is 4.7.1 not the 2.2 I read in the licence. So it is a few years old but not as old as I thought.

I opened the file in excel 2003 and the conditional formation is displayed correctly. It is only when I open it in 2007 so there must be some backward compatibility problems.

I attempted to upgrade the latest version last night but is seems there is a lot of changes I need to make. The namespaces structure seems to have changed in the later versions of CELLS?

The issue I am facing seems very clear and so I would have thought that it may have been registered on on bug fix register. I’d really like to find out if it has been identified and resolved before I spend time upgrading as there is a chance that the upgrade will not fix it and the effort will be wasted.

Are you able to tell me if the issue (from v 4.7.1 on ) was logged in your bug register?

Many thanks
Matt

4

Hi,


Yes, in later versions, the namespace structure is changed, we have added more namespaces and respective APIs are now moved to the relevant namespaces to make it more organized, please see the document for your reference:
https://docs.aspose.com/display/cellsnet/Home
and check Aspose.Cells for .NET API Reference for your complete reference.

Well, in the new versions we made tremendous enhancements regarding PivotTables and conditional formattings (Excel 97-2007/2010), so you may try our latest version (you need to update your existing code a bit accordingly as I mentioned before). If you still find any issue, we can provide a fix and provide it to you. Moreover, I think in the older version that you are using (e.g v4.7.1), we had limited support for MS Excel 2007, so you are getting this issue (I am not entirely certain though). Anyways, you have to use/try the latest version of the product (v7.5.0), the reason is we only provide fixes based on latest version of the product and cannot provide fixes based on older versions. I think you may comment out the licensing code and check if every thing is working fine (it should be working fine).

Thank you.

5

I updated to v7.5 and the same error occurs. The conditional formatting within the pivot table is rendered to the wrong cells within the pivot table.


I searched further in the aspose.cells forums and found a statement that conditional formating in pivot tables is not supported.

<a href="https://forum.aspose.com/t/94457

Just so i make sure I am not missing any thing can you conform for me that the issue I raised above is due to conditional formatting not being supported in pivot tables.

The work around I have in mind is to , in-code, convert the pivot table to standard cells. Perhaps by some kind of copy and paste operation. Would this work? If so, could you provide a code snippet to demonstrate how to do this copy paste ( including preserving formatting) in C#.NET. Many thanks in advance.
6

Hi,


Your issue might relate to the issue mentioned in another thread. Anyways, we still appreciate if you could provide a sample console application, zip it and post it here to reproduce the issue on our end. We will check it first.

Thank you.