libxlsxwriter
autofilter.c
<< merge_rich_string.c data_validate.c >>

Example of adding autofilters to a worksheets and adding filter conditions.

/*
* Example of adding an autofilter to a worksheet in Excel using
* libxlsxwriter.
*
* Copyright 2014-2021, John McNamara, jmcnamara@cpan.org
*
*/
#include "xlsxwriter.h"
void write_worksheet_header(lxw_worksheet *worksheet, lxw_format *header);
int main() {
lxw_workbook *workbook = workbook_new("autofilter.xlsx");
lxw_worksheet *worksheet1 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet2 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet3 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet4 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet5 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet6 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet7 = workbook_add_worksheet(workbook, NULL);
struct row {
char region[16];
char item[16];
int volume;
char month[16];
};
struct row data[] = {
{"East", "Apple", 9000, "July" },
{"East", "Apple", 5000, "July" },
{"South", "Orange", 9000, "September" },
{"North", "Apple", 2000, "November" },
{"West", "Apple", 9000, "November" },
{"South", "Pear", 7000, "October" },
{"North", "Pear", 9000, "August" },
{"West", "Orange", 1000, "December" },
{"West", "Grape", 1000, "November" },
{"South", "Pear", 10000, "April" },
{"West", "Grape", 6000, "January" },
{"South", "Orange", 3000, "May" },
{"North", "Apple", 3000, "December" },
{"South", "Apple", 7000, "February" },
{"West", "Grape", 1000, "December" },
{"East", "Grape", 8000, "February" },
{"South", "Grape", 10000, "June" },
{"West", "Pear", 7000, "December" },
{"South", "Apple", 2000, "October" },
{"East", "Grape", 7000, "December" },
{"North", "Grape", 6000, "April" },
{"East", "Pear", 8000, "February" },
{"North", "Apple", 7000, "August" },
{"North", "Orange", 7000, "July" },
{"North", "Apple", 6000, "June" },
{"South", "Grape", 8000, "September" },
{"West", "Apple", 3000, "October" },
{"South", "Orange", 10000, "November" },
{"West", "Grape", 4000, "July" },
{"North", "Orange", 5000, "August" },
{"East", "Orange", 1000, "November" },
{"East", "Orange", 4000, "October" },
{"North", "Grape", 5000, "August" },
{"East", "Apple", 1000, "December" },
{"South", "Apple", 10000, "March" },
{"East", "Grape", 7000, "October" },
{"West", "Grape", 1000, "September" },
{"East", "Grape", 10000, "October" },
{"South", "Orange", 8000, "March" },
{"North", "Apple", 4000, "July" },
{"South", "Orange", 5000, "July" },
{"West", "Apple", 4000, "June" },
{"East", "Apple", 5000, "April" },
{"North", "Pear", 3000, "August" },
{"East", "Grape", 9000, "November" },
{"North", "Orange", 8000, "October" },
{"East", "Apple", 10000, "June" },
{"South", "Pear", 1000, "December" },
{"North", "Grape", 10000, "July" },
{"East", "Grape", 6000, "February" }
};
uint16_t i;
lxw_format *header = workbook_add_format(workbook);
format_set_bold(header);
/*
* Example 1. Autofilter without conditions.
*/
/* Set up the worksheet data. */
write_worksheet_header(worksheet1, header);
/* Write the row data. */
for (i = 0; i < sizeof(data)/sizeof(struct row); i++) {
worksheet_write_string(worksheet1, i + 1, 0, data[i].region, NULL);
worksheet_write_string(worksheet1, i + 1, 1, data[i].item, NULL);
worksheet_write_number(worksheet1, i + 1, 2, data[i].volume, NULL);
worksheet_write_string(worksheet1, i + 1, 3, data[i].month, NULL);
}
/* Add the autofilter. */
worksheet_autofilter(worksheet1, 0, 0, 50, 3);
/*
* Example 2. Autofilter with a filter condition in the first column.
*/
/* Set up the worksheet data. */
write_worksheet_header(worksheet2, header);
/* Write the row data. */
for (i = 0; i < sizeof(data)/sizeof(struct row); i++) {
worksheet_write_string(worksheet2, i + 1, 0, data[i].region, NULL);
worksheet_write_string(worksheet2, i + 1, 1, data[i].item, NULL);
worksheet_write_number(worksheet2, i + 1, 2, data[i].volume, NULL);
worksheet_write_string(worksheet2, i + 1, 3, data[i].month, NULL);
/* It isn't sufficient to just apply the filter condition below. We
* must also hide the rows that don't match the criteria since Excel
* doesn't do that automatically. */
if (strcmp(data[i].region, "East") == 0) {
/* Row matches the filter, no further action required. */
}
else {
/* Hide rows that don't match the filter. */
worksheet_set_row_opt(worksheet2, i + 1, LXW_DEF_ROW_HEIGHT, NULL, &hidden);
}
/* Note, the if() statement above is written to match the logic of the
* criteria in worksheet_filter_column() below. However you could get
* the same results with the following simpler, but reversed, code:
*
* if (strcmp(data[i].region, "East") != 0) {
* worksheet_set_row_opt(worksheet2, i + 1, LXW_DEF_ROW_HEIGHT, NULL, &hidden);
* }
*
* The same applies to the Examples 3-6 as well.
*/
}
/* Add the autofilter. */
worksheet_autofilter(worksheet2, 0, 0, 50, 3);
/* Add the filter criteria. */
.value_string = "East"};
worksheet_filter_column(worksheet2, 0, &filter_rule2);
/*
* Example 3. Autofilter with a dual filter condition in one of the columns.
*/
/* Set up the worksheet data. */
write_worksheet_header(worksheet3, header);
/* Write the row data. */
for (i = 0; i < sizeof(data)/sizeof(struct row); i++) {
worksheet_write_string(worksheet3, i + 1, 0, data[i].region, NULL);
worksheet_write_string(worksheet3, i + 1, 1, data[i].item, NULL);
worksheet_write_number(worksheet3, i + 1, 2, data[i].volume, NULL);
worksheet_write_string(worksheet3, i + 1, 3, data[i].month, NULL);
if (strcmp(data[i].region, "East") == 0 || strcmp(data[i].region, "South") == 0) {
/* Row matches the filter, no further action required. */
}
else {
/* We need to hide rows that don't match the filter. */
worksheet_set_row_opt(worksheet3, i + 1, LXW_DEF_ROW_HEIGHT, NULL, &hidden);
}
}
/* Add the autofilter. */
worksheet_autofilter(worksheet3, 0, 0, 50, 3);
/* Add the filter criteria. */
.value_string = "East"};
.value_string = "South"};
worksheet_filter_column2(worksheet3, 0, &filter_rule3a, &filter_rule3b, LXW_FILTER_OR);
/*
* Example 4. Autofilter with filter conditions in two columns.
*/
/* Set up the worksheet data. */
write_worksheet_header(worksheet4, header);
/* Write the row data. */
for (i = 0; i < sizeof(data)/sizeof(struct row); i++) {
worksheet_write_string(worksheet4, i + 1, 0, data[i].region, NULL);
worksheet_write_string(worksheet4, i + 1, 1, data[i].item, NULL);
worksheet_write_number(worksheet4, i + 1, 2, data[i].volume, NULL);
worksheet_write_string(worksheet4, i + 1, 3, data[i].month, NULL);
if (strcmp(data[i].region, "East") == 0 &&
data[i].volume > 3000 && data[i].volume < 8000)
{
/* Row matches the filter, no further action required. */
}
else {
/* We need to hide rows that don't match the filter. */
worksheet_set_row_opt(worksheet4, i + 1, LXW_DEF_ROW_HEIGHT, NULL, &hidden);
}
}
/* Add the autofilter. */
worksheet_autofilter(worksheet4, 0, 0, 50, 3);
/* Add the filter criteria. */
.value_string = "East"};
.value = 3000};
.value = 8000};
worksheet_filter_column(worksheet4, 0, &filter_rule4a);
worksheet_filter_column2(worksheet4, 2, &filter_rule4b, &filter_rule4c, LXW_FILTER_AND);
/*
* Example 5. Autofilter with a dual filter condition in one of the columns.
*/
/* Set up the worksheet data. */
write_worksheet_header(worksheet5, header);
/* Write the row data. */
for (i = 0; i < sizeof(data)/sizeof(struct row); i++) {
worksheet_write_string(worksheet5, i + 1, 0, data[i].region, NULL);
worksheet_write_string(worksheet5, i + 1, 1, data[i].item, NULL);
worksheet_write_number(worksheet5, i + 1, 2, data[i].volume, NULL);
worksheet_write_string(worksheet5, i + 1, 3, data[i].month, NULL);
if (strcmp(data[i].region, "East") == 0 ||
strcmp(data[i].region, "North") == 0 ||
strcmp(data[i].region, "South") == 0)
{
/* Row matches the filter, no further action required. */
}
else {
/* We need to hide rows that don't match the filter. */
worksheet_set_row_opt(worksheet5, i + 1, LXW_DEF_ROW_HEIGHT, NULL, &hidden);
}
}
/* Add the autofilter. */
worksheet_autofilter(worksheet5, 0, 0, 50, 3);
/* Add the filter criteria. */
char* list[] = {"East", "North", "South", NULL};
worksheet_filter_list(worksheet5, 0, list);
/*
* Example 6. Autofilter with filter for blanks.
*/
/* Set up the worksheet data. */
write_worksheet_header(worksheet6, header);
/* Simulate one blank cell in the data, to test the filter. */
data[5].region[0] = '\0';
/* Write the row data. */
for (i = 0; i < sizeof(data)/sizeof(struct row); i++) {
worksheet_write_string(worksheet6, i + 1, 0, data[i].region, NULL);
worksheet_write_string(worksheet6, i + 1, 1, data[i].item, NULL);
worksheet_write_number(worksheet6, i + 1, 2, data[i].volume, NULL);
worksheet_write_string(worksheet6, i + 1, 3, data[i].month, NULL);
if (strcmp(data[i].region, "") == 0) {
/* Row matches the filter, no further action required. */
}
else {
/* We need to hide rows that don't match the filter. */
worksheet_set_row_opt(worksheet6, i + 1, LXW_DEF_ROW_HEIGHT, NULL, &hidden);
}
}
/* Add the autofilter. */
worksheet_autofilter(worksheet6, 0, 0, 50, 3);
/* Add the filter criteria. */
worksheet_filter_column(worksheet6, 0, &filter_rule6);
/*
* Example 7. Autofilter with filter for non-blanks.
*/
/* Set up the worksheet data. */
write_worksheet_header(worksheet7, header);
/* Write the row data. */
for (i = 0; i < sizeof(data)/sizeof(struct row); i++) {
worksheet_write_string(worksheet7, i + 1, 0, data[i].region, NULL);
worksheet_write_string(worksheet7, i + 1, 1, data[i].item, NULL);
worksheet_write_number(worksheet7, i + 1, 2, data[i].volume, NULL);
worksheet_write_string(worksheet7, i + 1, 3, data[i].month, NULL);
if (strcmp(data[i].region, "") != 0) {
/* Row matches the filter, no further action required. */
}
else {
/* We need to hide rows that don't match the filter. */
worksheet_set_row_opt(worksheet7, i + 1, LXW_DEF_ROW_HEIGHT, NULL, &hidden);
}
}
/* Add the autofilter. */
worksheet_autofilter(worksheet7, 0, 0, 50, 3);
/* Add the filter criteria. */
worksheet_filter_column(worksheet7, 0, &filter_rule7);
return workbook_close(workbook);
}
void write_worksheet_header(lxw_worksheet *worksheet, lxw_format *header) {
/* Make the columns wider for clarity. */
worksheet_set_column(worksheet, 0, 3, 12, NULL);
/* Write the column headers. */
worksheet_set_row(worksheet, 0, 20, header);
worksheet_write_string(worksheet, 0, 0, "Region", NULL);
worksheet_write_string(worksheet, 0, 1, "Item", NULL);
worksheet_write_string(worksheet, 0, 2, "Volume", NULL);
worksheet_write_string(worksheet, 0, 3, "Month", NULL);
}
worksheet_autofilter
lxw_error worksheet_autofilter(lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col)
Set the autofilter area in the worksheet.
lxw_row_col_options
Options for rows and columns.
Definition: worksheet.h:839
workbook_close
lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
lxw_filter_rule
Options for autofilter rules.
Definition: worksheet.h:1663
LXW_DEF_ROW_HEIGHT
#define LXW_DEF_ROW_HEIGHT
Definition: worksheet.h:76
workbook_new
lxw_workbook * workbook_new(const char *filename)
Create a new workbook object.
lxw_filter_rule::criteria
uint8_t criteria
Definition: worksheet.h:1666
worksheet_set_row_opt
lxw_error worksheet_set_row_opt(lxw_worksheet *worksheet, lxw_row_t row, double height, lxw_format *format, lxw_row_col_options *options)
Set the properties for a row of cells.
worksheet_filter_column
lxw_error worksheet_filter_column(lxw_worksheet *worksheet, lxw_col_t col, lxw_filter_rule *rule)
Write a filter rule to an autofilter column.
format_set_bold
void format_set_bold(lxw_format *format)
Turn on bold for the format font.
LXW_FILTER_CRITERIA_GREATER_THAN
@ LXW_FILTER_CRITERIA_GREATER_THAN
Definition: worksheet.h:607
lxw_worksheet
Struct to represent an Excel worksheet.
Definition: worksheet.h:2107
LXW_FILTER_OR
@ LXW_FILTER_OR
Definition: worksheet.h:638
LXW_FILTER_CRITERIA_BLANKS
@ LXW_FILTER_CRITERIA_BLANKS
Definition: worksheet.h:619
lxw_format
Struct to represent the formatting properties of an Excel format.
Definition: format.h:358
LXW_FILTER_CRITERIA_EQUAL_TO
@ LXW_FILTER_CRITERIA_EQUAL_TO
Definition: worksheet.h:601
LXW_TRUE
@ LXW_TRUE
Definition: common.h:53
lxw_row_col_options::hidden
uint8_t hidden
Definition: worksheet.h:841
lxw_workbook
Struct to represent an Excel workbook.
Definition: workbook.h:280
worksheet_filter_list
lxw_error worksheet_filter_list(lxw_worksheet *worksheet, lxw_col_t col, char **list)
Write multiple string filters to an autofilter column.
worksheet_write_string
lxw_error worksheet_write_string(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *string, lxw_format *format)
Write a string to a worksheet cell.
worksheet_set_row
lxw_error worksheet_set_row(lxw_worksheet *worksheet, lxw_row_t row, double height, lxw_format *format)
Set the properties for a row of cells.
worksheet_write_number
lxw_error worksheet_write_number(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, double number, lxw_format *format)
Write a number to a worksheet cell.
worksheet_filter_column2
lxw_error worksheet_filter_column2(lxw_worksheet *worksheet, lxw_col_t col, lxw_filter_rule *rule1, lxw_filter_rule *rule2, uint8_t and_or)
Write two filter rules to an autofilter column.
LXW_FILTER_CRITERIA_NON_BLANKS
@ LXW_FILTER_CRITERIA_NON_BLANKS
Definition: worksheet.h:622
worksheet_set_column
lxw_error worksheet_set_column(lxw_worksheet *worksheet, lxw_col_t first_col, lxw_col_t last_col, double width, lxw_format *format)
Set the properties for one or more columns of cells.
workbook_add_worksheet
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
LXW_FILTER_AND
@ LXW_FILTER_AND
Definition: worksheet.h:635
workbook_add_format
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.
LXW_FILTER_CRITERIA_LESS_THAN
@ LXW_FILTER_CRITERIA_LESS_THAN
Definition: worksheet.h:610