Free Support Forum - aspose.com

Aspose.Cells.Slicer.Refresh() fails

Hello,

My code below fails with this input Slicer.zip (29.4 KB)

Would you advise me that the code like this is OK for “replacing all the texts found in the file.” ?

private static void testSlicer()
{
    Workbook book = new Workbook("Slicer.xlsx");

    foreach (Worksheet worksheet in book.Worksheets)
    {
        worksheet.Name = "{SHT} " +worksheet.Name;

        foreach (Cell cell in worksheet.Cells)
        {
            if (cell.Type == CellValueType.IsString)
            {
                cell.PutValue("{CEL} "+ cell.DisplayStringValue);
            }
        }

        foreach (Shape shape in worksheet.Shapes)
        {
            if (shape.IsGroup)
            {
                foreach (Shape shp in ((GroupShape)shape).GetGroupedShapes())
                {
                    if (shp.Text != null)
                    {
                        shp.Text = "{GRP} " + shp.Text;
                    }
                }
            }
            
            if (shape.Text != null)
            {
                shape.Text = "{SHP} " + shape.Text;
            }
            
            if (shape.AlternativeText != null)
            {
                shape.AlternativeText = "{ALT} " + shape.AlternativeText;
            }
            
            if (shape.Name != null)
            {
                shape.Name = "{NAM} " + shape.Name;
            }
        }
    }

    // I Added the following because only with the above operation the text strings such as "ラーフル" remain the same as the original ones in slicers.
    foreach (Worksheet worksheet in book.Worksheets)
    {
	//foreach (PivotTable pivottable in worksheet.PivotTables)
        //{
        //    pivottable.RefreshData();                    // This caused a similar error, too.
        //    pivottable.CalculateData();
        //}

        foreach (Slicer slicer in worksheet.Slicers)
        {
            slicer.Refresh();                                     // Error
        }
    }
    book.Save("Slicer_out.xlsx");
}

The detail of the error is as follows.

System.ArgumentOutOfRangeException
  HResult=0x80131502
  Message=Index was out of range. Must be non-negative and less than the size of the collection.
  Source=System.Private.CoreLib
  スタック トレース:
   場所 System.ThrowHelper.ThrowArgumentOutOfRange_IndexException()
   場所 Aspose.Cells.Pivot.PivotTable. ()
   場所 Aspose.Cells.Pivot.PivotTable.RefreshData()
   場所 Aspose.Cells.Slicers.Slicer.Refresh()
   場所 FileTrans.ParseExcel.testSlicer() 

I read this and was glad that something fixed at 18.7, but in fact the above occurred. Am I doing anything wrong here?

I always appreciate your help and support.

@KDSSHO,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46270 - Exception ArgumentOutOfRangeException raised while calling Slicer.Refresh()

@KDSSHO,

This is to inform you that we have fixed your issue CELLSNET-46270 now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

Thank you for your information. I can’t wait to update my environment.

@KDSSHO,

You are welcome.

@KDSSHO,

Please give a try to our latest fix Aspose.Cells for .NET 18.8.1 which should resolve your issue.

Since you have changed the data source of PivotTable, so you should refresh and calculate it.
Because the PiovtField’s name is also changed, the relative slicer can’t find the data, so it will be deleted.
You can do it like this in MS-Excel, You will get the same result. We use the following code to test it, please try it.
e.g
Sample code:

           Workbook book = new Workbook(filePath + "Slicer.xlsx");
            foreach (Worksheet worksheet in book.Worksheets)
            {
                worksheet.Name = "{SHT} " + worksheet.Name;

                foreach (Cell cell in worksheet.Cells)
                {
                    if (cell.Type == CellValueType.IsString)
                    {
                        cell.PutValue("{CEL} " + cell.DisplayStringValue);
                    }
                }

                foreach (Shape shape in worksheet.Shapes)
                {
                    if (shape.IsGroup)
                    {
                        foreach (Shape shp in ((GroupShape)shape).GetGroupedShapes())
                        {
                            if (shp.Text != null)
                            {
                                shp.Text = "{GRP} " + shp.Text;
                            }
                        }
                    }

                    if (shape.Text != null)
                    {
                        shape.Text = "{SHP} " + shape.Text;
                    }

                    if (shape.AlternativeText != null)
                    {
                        shape.AlternativeText = "{ALT} " + shape.AlternativeText;
                    }

                    if (shape.Name != null)
                    {
                        shape.Name = "{NAM} " + shape.Name;
                    }
                }
            }

            // I Added the following because only with the above operation the text strings such as "ラーフル" remain the same as the original ones in slicers. 
            foreach (Worksheet worksheet in book.Worksheets)
            {               
                foreach (PivotTable pivottable in worksheet.PivotTables)
                {
                    pivottable.RefreshData(); // This caused a similar error, too. 
                    pivottable.CalculateData();
                } 

               foreach (Slicer slicer in worksheet.Slicers)
                {
                    slicer.Refresh(); 
                }
            }
            book.Save(filePath + "out.xlsx");

Let us know your feedback.

How can I apply the update DLLs you attached?

@KDSSHO,

You may please refer to the following link and if requirement is not fulfilled, provide us details. We will assist you further once details are shared.

Referencing Aspose.Cells from a .NET Project

May I wait for Aspose.Cells 18.9 Release, which is supposed to include this 18.8.1 fixation?
That must be the most easy and safe way to download it via NuGet Package Manager.

I did as follows on Visual Studio 2017.
Right-clicked on Solution Explorer > Reference to open Reference Manager, then browsed the sent .dll file located under Aspose.Cells for .NET 18.8.1\Aspose.Cells18.8.1 For .Net4.0 folder when I unzip the archive.
But “Reference” tree in Solution Explorer doesn’t seem to react at this point.
I am not yet going to modify .csproj manually which I may be able to change the linkage of those references directly.

Lastly I apologize for my very belated response on this topic.

@KDSSHO,

Well, you should first remove the existing Aspose.Cells.Dll reference from the solution explorer in VS.NET and then add reference to the newer fixed version, it should work fine.

Moreover, we are scheduled to publish our next official version of the product, i.e., Aspose.Cells for .NET v18.9 within the next few days (3-5 days or so). The release will include all the previous fixes including v18.8.1. So, you may wait for the next release and can download it via Nuget Package Manager.

The issues you have found earlier (filed as CELLSNET-46270) have been fixed in Aspose.Cells for .NET v18.9. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

Thank you for guiding me, Amjad.

I got it downloaded via NuGet but the following message started to appear on the output console in Visual Studio while building the app.

 warning NU1701: Package 'Aspose.Cells 18.9.0' was restored using '.NETFramework,Version=v4.6.1' instead of the project target framework '.NETCoreApp,Version=v2.1'. This package may not be fully compatible with your project.

Then It became to fail reading ANY Excel document throwing the exception below, right after new Workbook(filename);.

 No data is available for encoding 1252. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.[source]
 System.Private.CoreLib[stacktrace]
   at System.Text.Encoding.GetEncoding(Int32 codepage)
   at 窶・€≫€€.(Int32 )
   at 窶・€≫€€.()
   at 窶・€≫€€.(Stream , String )
   at 窶・€≫€€.(窶≫€≫€€ , String )
   at 窶≫€≫€€.()
   at 窶≫€≫€€.()
   at 窶≫€≫€€.(String , FontStyle , Boolean )
   at 窶や€≫€€.窶や€≫€€窶・€・(String , FontStyle , Boolean )
   at 窶・€≫€€.(String , Single , String , FontStyle )
   at 窶銀€・(String , Double , FontStyle )
   at Aspose.Cells.WorksheetCollection.窶€()
   at Aspose.Cells.WorksheetCollection..ctor(Workbook )
   at Aspose.Cells.Workbook.(String , LoadOptions )
   at Aspose.Cells.Workbook..ctor(String file)

What can I do for this issue?

@KDSSHO,

You can download package (62.19 MB) from NuGet and then rename it from .nupkg to .zip, extract it and manually add reference of Aspose.Cells in your project, from the folder named netstandard2.0. If this does not resolve your problem then share the project with us, we will provide our feedback after analysis.

I downloaded .nupkg, renamed it to .zip and extracted the contents.
I could find a folder named “netstandard20_AuthenticodeSigned” in it, just as I find the same one in my local installation directory (C:\Users\${KDSSHO}\.nuget\packages\Aspose.Cells\18.9.0\lib\netstandard20_AuthenticodeSigned)
whose the name doesn’t equal to “netstandard2.0”.
Is the path above correct one to manually add reference in my project?

Hmm I’m still unable to fix it Please kindly help me out! Here is my project.
ToAspose.com.zip (1.1 MB)

Where do I do wrong with this? In contrast I can use 18.8.0 with no problem.

@KDSSHO

I have locally added the reference of Aspose.Cells API and the build succeeds with 18.9 version as well.
ToAspose_Resolved.zip (8.2 MB)

Let us know your feedback.

I regret that I can not download ToAspose_Resolved.zip even if I log out and log in once again. ( just like I reported very the same situation on another ticket somewhere the other day. )

Sorry, this file is private. Only visible to topic owner and staff members.

Could you provide me the data in other way?

@KDSSHO,

Please download it from Dropbox link.

https://www.dropbox.com/s/r3erilx4jbbwika/ToAspose_Resolved.zip?dl=0

Thank you I can get it and build the project and confirmed this error and this error ceased.

However some steps to apply the latest DLL like above are complicated.
I would like to be up-to-date by only executing

PM> Install-Package Aspose.XXX 

What was the root cause of this issue I experienced this time?
How can I avoid it?

@KDSSHO,

We were able to observe the issue that adding package through Nuget Package manager hangs and warning is raised as mentioned by you, but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNETCORE-9 - Package for v18.9.0 cannot be added to .NETCore project from NuGet Manager as it hangs