Auto-filter combined with merged cells broken

[znakeeye]

Using libxlsxwriter 1.1.4. Consider the sample below. When merging cells, the auto-filter produces incorrect filter setup. Toggling the filters in Excel corrects the problem.

#include "xlsxwriter.h"

int main() {

    lxw_workbook* workbook = workbook_new("autofilter.xlsx");
    lxw_worksheet* worksheet = workbook_add_worksheet(workbook, NULL);

    worksheet_write_string(worksheet, 0, 0, "A", NULL);
    worksheet_merge_range(worksheet, 0, 1, 0, 2, "B", NULL); // <-- breaks auto-filter
    worksheet_autofilter(worksheet, 0, 0, 0, 1);

    return workbook_close(workbook);
}

Actual:

Expected:

[jmcnamara]

Thanks for the detailed sample code.

This looks like a bit of an unusual use case. You can almost get what you want by extending the autofilter to column C (which strictly speaking is where is should be):

#include "xlsxwriter.h"

int main() {

    lxw_workbook *workbook = workbook_new("merge_filter03.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);

    worksheet_write_string(worksheet, 0, 0, "A", NULL);
    worksheet_merge_range(worksheet, 0, 1, 0, 2, "B", NULL);
    worksheet_autofilter(worksheet, 0, 0, 0, 2); // Changed here.

    return workbook_close(workbook);
}

This give the following output:

However, it has an additional filter arrow in column B that you don't want (based on your Expected image). Turning off the filter arrow for a column is not currently supported by any of the xlsxwriter variants. So you'll probably need to find some other way to represent the functionality that you want.

[znakeeye]

In Excel 365 you simply cannot create the result you get from xlsxwriter, and vice versa. How come? Doesn't libxlsxwriter strive for 100% Excel compatibility?

If we compare the files (before and after clicking the button in Excel), maybe we can find the trick.

[jmcnamara]

How come?

There is a autoFilter xml attribute called showButton that is used by Excel to hide one of the filter arrows. It is usually only accessible via VBA. I'm surprised that it can be enabled like this.

What is the use case a merged autofilter?

[jmcnamara]

Unfortunately, this is too much of an edge case to support. Closing until there are more requests for it and a definite use case.