We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Create Slicer on Excel table and Get/Set the slicer shape properties in .NET

Hi,
It’s seems we can add slicer only on PivotTable, could we have the same options for Excel Table?

Thanks

@Chabie,
This feature is currently not available however we have logged a ticket in our database for investigation to implement this new feature (if possible). You will be notified here once any update is ready to share.

This issue is logged in our database as:
CELLSNET-47524 - Support for slicers in Excel Tables

@Chabie,

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

The issues you have found earlier (filed as CELLSNET-47524) have been fixed in Aspose.Cells for .NET v20.8. This message was posted using Bugs notification tool by Amjad_Sahi

When I call sheet.Slicers.Add(_selectedTable, listColumn, rowIndex, colIndex)
_selectTable not null
listColumn (3rd column in _selectTable) not null
rowIndex > 0 && < MaxDataRow
rowColumn > 0 && = MaxDataColumn + 2 (in my case 17)

I got an exception
System.NullReferenceException: ‘Object reference not set to an instance of an object.’

at Aspose.Cells.Slicers.SlicerCache.(ListObject )
at Aspose.Cells.Slicers.SlicerCache.()
at Aspose.Cells.Slicers.SlicerCollection.Add(ListObject table, ListColumn listColumn, Int32 row, Int32 column)

@Chabie,

Please provide sample runnable code with your template file (you may zip the file prior attaching) to reproduce the issue, we will check it soon.

[Test]
public void BugSlicer()
{
var file = Path.Combine(TestContext.CurrentContext.TestDirectory, “Ressources”, $"{nameof(BugSlicer)}.xlsx");
if (File.Exists(file))
{
File.Delete(file);
}

        using(var workbook = new Workbook())
        {
            var sheet = PrepareSheet(workbook);

            // Create Table
            var selectedTable = sheet.ListObjects[sheet.ListObjects.Add(0, 0, sheet.Cells.MaxDataRow, sheet.Cells.MaxDataColumn, true)];
            var listColumn = selectedTable.ListColumns[2];
            sheet.Slicers.Add(selectedTable, listColumn, 5, 7);

            workbook.Save(file);
        }
        Assert.Pass("Validation done by manual file inspection");
    }

    private static Worksheet PrepareSheet(Workbook workbook)
    {
        workbook.FileFormat = FileFormatType.Xlsx;
        workbook.Worksheets.Clear();

        var iIndex = workbook.Worksheets.Add(SheetType.Worksheet);
        var sheet = workbook.Worksheets[iIndex];
        sheet.Name = "Data";
        workbook.Worksheets.ActiveSheetIndex = sheet.Index;
        sheet.ActiveCell = "A1";

        sheet.Cells["A1"].PutValue("Col1");
        sheet.Cells["B1"].PutValue("Col2");
        sheet.Cells["C1"].PutValue("Col3");
        sheet.Cells["D1"].PutValue("Col4");
        sheet.Cells["E1"].PutValue("Col5");
        sheet.Cells["F1"].PutValue("Col6");

        for (var i = 2; i < 102; i++)
        {
            sheet.Cells["A" + i].PutValue(i);
            sheet.Cells["B" + i].PutValue(DateTime.Today.AddDays(-i));
            sheet.Cells["C" + i].PutValue("Test" + (i % 4));
            sheet.Cells["D" + i].PutValue(DateTime.Today.AddDays(i % 8));
            sheet.Cells["E" + i].PutValue("Another Test" + (i % 5));
            sheet.Cells["F" + i].PutValue(i * 3.14);
        }

        var dateStyle = workbook.CreateStyle();
        dateStyle.Custom = "dd-MM-yyyy";
        var flag = new StyleFlag()
        {
            All = true
        };
        sheet.Cells.ApplyColumnStyle(1, dateStyle, flag);
        sheet.Cells.ApplyColumnStyle(3, dateStyle, flag);

        sheet.AutoFitColumns();

        return sheet;
    }

@Chabie,
We will test this code and share our feedback soon.

@Chabie,
We have reproduced this issue and logged it in our database for further investigation. We will notify you here once any update is ready to share.

This issue is logged as:
CELLSNET-47547-Exception raised while adding slicer for table

@Chabie,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47547”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@Chabie,

Please try our latest version/fix: Aspose.Cells for .NET v20.8.1 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells20.8.1 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.8.1 For .Net4.0.Zip (5.4 MB)

I need a .net standard version 2.0

@Chabie,

Please wait for a few days as we will try to provide you the .NET Standard 2.0 fix for your issue.

Keep in touch.

@Chabie,

Please find attached Aspose.Cells for .NET Standard fix with other fixes. Please try it.
Aspose.Cells20.8.2 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.8.2 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.8.2 For .NetStandard20.Zip (5.4 MB)

Hi,
Ok the slicer work now but when we select an items the slicer is automatically resize and unusable.
How can I set the slicer property to “don’t move or size with cells” (in excel right click on slicer --> Size and properties --> Properties.)

thanks

@Chabie,

Good to know that your original issue is fixed now.

We will check and get back to you with more details on it.

@Chabie,

We created the following ticket for your recent issue regarding slicer:
CELLSNET-47567 - Support Get/Set the slicer shape properties

Once we have an update on it, we will let you know here.

@Chabie,

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

@Chabie,
We have supported Get/Set the slicer shape properties. Please download the new fix version(Aspose.Cells 20.8.5).

You can set slicer.Placement = PlacementType.FreeFloating to “don’t move or size with cells” (in excel right click on slicer --> Size and properties --> Properties.)".

The sample code as follows:

int index = sheet.Slicers.Add(selectedTable, listColumn, 5, 7);
Slicer slicer = sheet.Slicers[index];
slicer.Placement = PlacementType.FreeFloating; 

Aspose.Cells20.8.5 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.8.5 For .Net4.0.Zip (5.4 MB)

Hi,
Let me know when it’s available with Nuggets