Filter the merged cells

Tags: #<Tag:0x00007f18a6d7a328> #<Tag:0x00007f18a6d7a1e8>
#1

Hi, guys
I am evaluating your product and have some puzzles about its function of fitler.
I want to filter out all the rows related to the merged cells without checking the “blanks” in filter conditions. like the function described at https://www.extendoffice.com/documents/excel/1955-excel-filter-merged-cells.html

Is it possible to implement it and any example will be very helpful?
Thanks!

#2

Hi @space.chang

Currently, there is no official API to alter the list of values visible in the ‘Filter by value’ section of the dropdown menu. This list is based on what’s in the dataset (also underneath the merged area). An option to make that query disappear would be to fill out all the data under the merged area with corresponding values, but I do not know if that meets your project requirements.

#3

Hi @aleksandra_budnik
Actually, I have filled in the same value for the merged cell before merging them,
Before merging:
image
After Merging:
image
What I expected is all the rows related to merged cell “A0” can be filtered out when filter value is “A0”, like below:
image
Actual result is only the first row related to ‘A0’ is filtered out:
image

Hope for your reply!
Thanks!

#4

Yes, you’re right @space.chang

so you also need to add the unmerge() method. Here’s an example https://jsfiddle.net/yb7L0ceu/

#5

Hi @aleksandra_budnik
It works well for my requirement and thanks for your kindly reply!

#6

I’m happy to hear that. Thank you for the update @space.chang

#8

Hi @aleksandra_budnik previously i only tried the sample case and it works well. Now I used it in my large data, i found it works abnormally. below is my reduced code:

<script>
import 'handsontable/dist/handsontable.full.css';
import * as Vue from 'vue';
import Handsontable from 'handsontable';
function generateData() {
    let _data = [];
    let _mergeData = [];
    for (let i = 0; i < 10; i++) {
      _data.push([`A${i}`, `B${i}`, `C${i}`]);
    }
    _mergeData.push({ row: 3, col: 0, rowspan: 7, colspan: 1 });
    return { data: _data, mergeData: _mergeData };
}

let _ret = generateData();
let table = null;
export default {
  name: "App",
  data: function () {
    return {};
  },
  setup() {
    Vue.onMounted(() => {
      let div = document.getElementById("example");
      console.time("HandsonTable");
      table = new Handsontable(div, {
      data: _ret.data,
      colWidths: 100,
      stretchH: "all",
      colHeaders: true,
      rowHeaders: true,
      licenseKey: "non-commercial-and-evaluation",
      height: "600",
      width: "100%",
      dropdownMenu: ["filter_by_value", "filter_action_bar"],
      filters: true,
    });
    console.timeEnd("HandsonTable");

    table.batch(() => {
      console.time("B");
      table.updateSettings({ mergeCells: _ret.mergeData });
      table.setDataAtCell(4, 0, "A3");
      table.setDataAtCell(5, 0, "A3");
      table.setDataAtCell(6, 0, "A3");
      table.setDataAtCell(7, 0, "A3");
      table.setDataAtCell(8, 0, "A3");
      table.setDataAtCell(9, 0, "A3");
      console.timeEnd("B");
    });
    table.addHook("beforeFilter", (conditions) => {
      if (conditions[0].column === 0) {
        // index of a cell where we have merged area
        table.getPlugin("MergeCells").unmerge(3, 0, 10, 0);
        table.render();
      }
    });
  });
},};</script>

When I filter “A3” in first column, the table will display unmerged cells, I hope the A3 are still merged.
image

Hope for your reply!
thanks!

#9

Could you please share the code via JSFiddle/CodeSandbox or any other JS playground? This way we will be able to work on the same replication scenario.

#10

Sorry, I cannot share codes with approaches you recommended.
I alos tried it on your case https://jsfiddle.net/yb7L0ceu/ using below two steps:

  1. when you filter “B2”, the B2-related cells become unmerged.
  2. when you filter “All” on column including “B2”, the B2-related cells are still unmerged.

I hope the B2-related merged cells should be always merged though they are filtered.

hope for your check!
thanks!

#11

It would be hard ot help without understanding what you already have implemented especially as unmerging cells upon filter is a custom solution.

#12

Hi @space.chang

I need to close this thread as there was no update for 7 days.

If anything changes with the code access please write to us at support@handsontable.com

#13