CODEWITHSUNDEEP
pythonpython-3.xexcelopenpyxl
Sector97
Sector97

Reputation: 116

Is there a way to make merged cells filterable in excel using Openpyxl?

I was looking for a way to make merged cells filterable using openpyxl.

E.g Filtering on first name "John" in the below workbook:

Workbook

Should return the first three rows: Desired Filter Behavior Rather than returning only the top row (the default excel behavior): Default Excel Behavior

The problem is that by default merged ranges only contain a value in the top left cell of the range: Default merged cell values

Using openpyxl is it possible to overwrite the None values within merged cells effectively making them filterable? Desired Merged cell values

For reference, you can already create filterable merged cells using macros or manual formatting as per the answer here: https://stackoverflow.com/a/49817670/14251756

I've already tried overwriting the none values using the below script but get the error AttributeError: 'MergedCell' object attribute 'value' is read-only

import openpyxl
wb = openpyxl.load_workbook("Workbook")
ws = wb['Worksheet']

#Get list of ranges of merged cells
merged_cell_ranges = ws.merged_cells.ranges
merged_cell_ranges = list(map(str,merged_cell_ranges))

#Iterate through ranges and write top merged cell value to empty cells
for merged_range in merged_cell_ranges:
    for row in ws[merged_range]:
        for cell in row:
            cell.value = ws[merged_range.split(':')[0]].value

Upvotes: 0

Views: 980

Answers (1)

David Gray
David Gray

Reputation: 46

Far from ideal, but I achieved the desired result by modifying the openpyxl source.

The function to modify is located here: https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/worksheet/worksheet.py#L586

you can simply add an argument to choose whether or not the cells become merged cells and therefor "cleaned".

The modified function now looks like this:

    def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None, clean=True):
        """ Set merge on a cell range.  Range is a cell range (e.g. A1:E1) """
        if range_string is None:
            cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row,
                      max_col=end_column, max_row=end_row)
            range_string = cr.coord
        mcr = MergedCellRange(self, range_string)
        self.merged_cells.add(mcr)
        if clean:
            self._clean_merge_range(mcr)
        else:
            mcr.format()

Simply add clean=False to keep the values.

ws.merge_cells('A2:A9', clean=False)

Upvotes: 3

Related Questions