Inquiry Regarding Updating Slicer Selected Values in Excel Functions

Aspose.Cells Product Family
,
1

Dear Aspose Support,

I hope this email finds you well. My name is tizu. and I have query regaarding updating selected values from a slicer in a Excel function, specifically VLOOKUP.

Attached is an Excel file where April is currently selected. Additionally, I have included a Python script named “aspose_slicer.py” which, upon execution, changes the slicer to December and creates a new Excel file.

However, upon examining the resulting file, I noticed the “Selected Month” values is not “12” as expected but rather “1”. Could you please advise on how to ensure that it reflects “12”?

Furthermore, I observed that swapping lines 18 and 19 in “aspose_slicer.py” leads to different results in the table.

I appreciate your assistance in resolving these queries.
aspose_slicer.zip (10.5 KB)

2

@tizu
By using the sample file for testing on the latest version v24.4, we were able to obtain the correct results. Please refer to the attachment. result.zip (47.0 KB)

The sample code as follows:

from aspose.cells import Workbook, CellsHelper

print(CellsHelper.get_version())
wb = Workbook("ExcelSlicer.xlsx")

ws = wb.worksheets.get("sheet1")
sl = ws.slicers[0]


sindx = 0
i = 0
for ssli in sl.slicer_cache.slicer_cache_items:
    if (ssli.selected):
        sindx = i
        break
    i = i  + 1

print(sindx)
print(sl.slicer_cache.slicer_cache_items[11].value)
sl.slicer_cache.slicer_cache_items[11].selected = True
sl.slicer_cache.slicer_cache_items[sindx].selected = False

sl.refresh()
wb.calculate_formula()
wb.refresh_dynamic_array_formulas(True)

wb.save("update_ExcelSlicer.xlsx")

The ouput result:

24.4
3
12

Hope helps a bit.

3

@John.He

Thank you for your prompt response and for providing the attached photo. Upon reviewing it, I noticed that the “Selected Month” remains as “1”. # The value below the yellow background.
(The desired value is “12”.)

Could you please reconsider?

Thank you in advance for your assistance.

4

@tizu
By using sample files and code for testing on the latest version v24.4, we were able to reproduce the problem. Discovering incorrect values obtained from VLOOKUP function after calling formula calculation.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSPYTHONNET-197

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.