libxlsxwriter
conditional_format2.c
<< conditional_format1.c images.c >>

A more comprehensive example of how to add conditional formatting to an libxlsxwriter file. Conditional formatting allows you to apply a format to a cell or a range of cells based on certain criteria.

/*
* An example of how to add conditional formatting to an libxlsxwriter file.
*
* Conditional formatting allows you to apply a format to a cell or a
* range of cells based on certain criteria.
*
* Copyright 2014-2020, John McNamara, jmcnamara@cpan.org
*
*/
#include "xlsxwriter.h"
/* Write some data to the worksheet. */
void write_worksheet_data(lxw_worksheet *worksheet) {
uint8_t data[10][10] = {
{34, 72, 38, 30, 75, 48, 75, 66, 84, 86},
{6, 24, 1, 84, 54, 62, 60, 3, 26, 59},
{28, 79, 97, 13, 85, 93, 93, 22, 5, 14},
{27, 71, 40, 17, 18, 79, 90, 93, 29, 47},
{88, 25, 33, 23, 67, 1, 59, 79, 47, 36},
{24, 100, 20, 88, 29, 33, 38, 54, 54, 88},
{6, 57, 88, 28, 10, 26, 37, 7, 41, 48},
{52, 78, 1, 96, 26, 45, 47, 33, 96, 36},
{60, 54, 81, 66, 81, 90, 80, 93, 12, 55},
{70, 5, 46, 14, 71, 19, 66, 36, 41, 21},
};
int row, col;
for (row = 0; row < 10; row++)
for (col = 0; col < 10; col++)
worksheet_write_number(worksheet, row +2, col +1, data[row][col], NULL);
}
/* Reset the conditional format options back to their initial state. */
void reset_conditional_format(lxw_conditional_format *conditional_format) {
memset(conditional_format, 0, sizeof(lxw_conditional_format));
}
int main() {
lxw_workbook *workbook = workbook_new("conditional_format.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);
lxw_worksheet *worksheet8 = workbook_add_worksheet(workbook, NULL);
lxw_worksheet *worksheet9 = workbook_add_worksheet(workbook, NULL);
/* Add a format. Light red fill with dark red text. */
lxw_format *format1 = workbook_add_format(workbook);
format_set_bg_color(format1, 0xFFC7CE);
format_set_font_color(format1, 0x9C0006);
/* Add a format. Green fill with dark green text. */
lxw_format *format2 = workbook_add_format(workbook);
format_set_bg_color(format2, 0xC6EFCE);
format_set_font_color(format2, 0x006100);
/* Create a single conditional format object to reuse in the examples. */
lxw_conditional_format *conditional_format = calloc(1, sizeof(lxw_conditional_format));
/*
* Example 1. Conditional formatting based on simple cell based criteria.
*/
write_worksheet_data(worksheet1);
CELL("A1"),
"Cells with values >= 50 are in light red. "
"Values < 50 are in light green.",
NULL);
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 50;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet1, RANGE("B3:K12"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 50;
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet1, RANGE("B3:K12"), conditional_format);
/*
* Example 2. Conditional formatting based on max and min values.
*/
write_worksheet_data(worksheet2);
CELL("A1"),
"Values between 30 and 70 are in light red. "
"Values outside that range are in light green.",
NULL);
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->min_value = 30;
conditional_format->max_value = 70;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet2, RANGE("B3:K12"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->min_value = 30;
conditional_format->max_value = 70;
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet2, RANGE("B3:K12"), conditional_format);
/*
* Example 3. Conditional formatting with duplicate and unique values.
*/
write_worksheet_data(worksheet3);
CELL("A1"),
"Duplicate values are in light red. "
"Unique values are in light green.",
NULL);
conditional_format->type = LXW_CONDITIONAL_TYPE_DUPLICATE;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet3, RANGE("B3:K12"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_UNIQUE;
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet3, RANGE("B3:K12"), conditional_format);
/*
* Example 4. Conditional formatting with above and below average values.
*/
write_worksheet_data(worksheet4);
CELL("A1"),
"Above average values are in light red. "
"Below average values are in light green.",
NULL);
conditional_format->type = LXW_CONDITIONAL_TYPE_AVERAGE;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet4, RANGE("B3:K12"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_AVERAGE;
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet4, RANGE("B3:K12"), conditional_format);
/*
* Example 5. Conditional formatting with top and bottom values.
*/
write_worksheet_data(worksheet5);
CELL("A1"),
"Top 10 values are in light red. "
"Bottom 10 values are in light green.",
NULL);
conditional_format->type = LXW_CONDITIONAL_TYPE_TOP;
conditional_format->value = 10;
conditional_format->format = format1;
worksheet_conditional_format_range(worksheet5, RANGE("B3:K12"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_BOTTOM;
conditional_format->value = 10;
conditional_format->format = format2;
worksheet_conditional_format_range(worksheet5, RANGE("B3:K12"), conditional_format);
/*
* Example 6. Conditional formatting with multiple ranges.
*/
write_worksheet_data(worksheet6);
CELL("A1"),
"Cells with values >= 50 are in light red."
"Values < 50 are in light green. Non-contiguous ranges.",
NULL);
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 50;
conditional_format->format = format1;
conditional_format->multi_range = "B3:K6 B9:K12";
worksheet_conditional_format_range(worksheet6, RANGE("B3:K12"), conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_CELL;
conditional_format->value = 50;
conditional_format->format = format2;
conditional_format->multi_range = "B3:K6 B9:K12";
worksheet_conditional_format_range(worksheet6, RANGE("B3:K12"), conditional_format);
/* Reset the options before the next example. */
reset_conditional_format(conditional_format);
/*
* Example 7. Conditional formatting with 2 color scales.
*/
/* Write the worksheet data. */
for (int i = 1; i <= 12; i++) {
worksheet_write_number(worksheet7, i + 1, 1, i, NULL);
worksheet_write_number(worksheet7, i + 1, 3, i, NULL);
worksheet_write_number(worksheet7, i + 1, 6, i, NULL);
worksheet_write_number(worksheet7, i + 1, 8, i, NULL);
}
CELL("A1"),
"Examples of color scales with default and user colors.",
NULL);
worksheet_write_string(worksheet7, CELL("B2"), "2 Color Scale", NULL);
worksheet_write_string(worksheet7, CELL("D2"), "2 Color Scale + user colors", NULL);
worksheet_write_string(worksheet7, CELL("G2"), "3 Color Scale", NULL);
worksheet_write_string(worksheet7, CELL("I2"), "3 Color Scale + user colors", NULL);
/* 2 color scale with standard colors. */
conditional_format->type = LXW_CONDITIONAL_2_COLOR_SCALE;
worksheet_conditional_format_range(worksheet7, RANGE("B3:B14"), conditional_format);
/* 2 color scale with user defined colors. */
conditional_format->type = LXW_CONDITIONAL_2_COLOR_SCALE;
conditional_format->min_color = 0xFF0000;
conditional_format->max_color = 0x00FF00;
worksheet_conditional_format_range(worksheet7, RANGE("D3:D14"), conditional_format);
/* Reset the colors before the next example. */
reset_conditional_format(conditional_format);
/* 3 color scale with standard colors. */
conditional_format->type = LXW_CONDITIONAL_3_COLOR_SCALE;
worksheet_conditional_format_range(worksheet7, RANGE("G3:G14"), conditional_format);
/* 3 color scale with user defined colors. */
conditional_format->type = LXW_CONDITIONAL_3_COLOR_SCALE;
conditional_format->min_color = 0xC5D9F1;
conditional_format->mid_color = 0x8DB4E3;
conditional_format->max_color = 0x538ED5;
worksheet_conditional_format_range(worksheet7, RANGE("I3:I14"), conditional_format);
reset_conditional_format(conditional_format);
/*
* Example 8. Conditional formatting with data bars.
*/
/* Write the worksheet data. */
for (int i = 1; i <= 12; i++) {
worksheet_write_number(worksheet8, i + 1, 1, i, NULL);
worksheet_write_number(worksheet8, i + 1, 3, i, NULL);
worksheet_write_number(worksheet8, i + 1, 5, i, NULL);
worksheet_write_number(worksheet8, i + 1, 7, i, NULL);
worksheet_write_number(worksheet8, i + 1, 9, i, NULL);
}
int data[] = {-1, -2, -3, -2, -1, 0, 1, 2, 3, 2, 1, 0};
for (int i = 1; i <= 12; i++) {
worksheet_write_number(worksheet8, i + 1, 11, data[i -1], NULL);
worksheet_write_number(worksheet8, i + 1, 13, data[i -1], NULL);
}
CELL("A1"),
"Examples of data bars.",
NULL);
worksheet_write_string(worksheet8, CELL("B2"), "Default data bars", NULL);
worksheet_write_string(worksheet8, CELL("D2"), "Bars only", NULL);
worksheet_write_string(worksheet8, CELL("F2"), "With user color", NULL);
worksheet_write_string(worksheet8, CELL("H2"), "Solid bars", NULL);
worksheet_write_string(worksheet8, CELL("J2"), "Right to left", NULL);
worksheet_write_string(worksheet8, CELL("L2"), "Excel 2010 style", NULL);
worksheet_write_string(worksheet8, CELL("N2"), "Negative same as positive", NULL);
conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
worksheet_conditional_format_range(worksheet8, RANGE("B3:B14"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_only = LXW_TRUE;
worksheet_conditional_format_range(worksheet8, RANGE("D3:D14"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_color = 0x63C384;
worksheet_conditional_format_range(worksheet8, RANGE("F3:F14"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_solid = LXW_TRUE;
worksheet_conditional_format_range(worksheet8, RANGE("H3:H14"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
worksheet_conditional_format_range(worksheet8, RANGE("J3:J14"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->data_bar_2010 = LXW_TRUE;
worksheet_conditional_format_range(worksheet8, RANGE("L3:L14"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_DATA_BAR;
conditional_format->bar_negative_color_same = LXW_TRUE;
worksheet_conditional_format_range(worksheet8, RANGE("N3:N14"), conditional_format);
reset_conditional_format(conditional_format);
/*
* Example 9. Conditional formatting with icon sets.
*/
/* Write the worksheet data. */
for (int i = 1; i <= 3; i++) {
worksheet_write_number(worksheet9, 2, i, i, NULL);
worksheet_write_number(worksheet9, 3, i, i, NULL);
worksheet_write_number(worksheet9, 4, i, i, NULL);
worksheet_write_number(worksheet9, 5, i, i, NULL);
}
for (int i = 1; i <= 4; i++) {
worksheet_write_number(worksheet9, 6, i, i, NULL);
}
for (int i = 1; i <= 5; i++) {
worksheet_write_number(worksheet9, 7, i, i, NULL);
worksheet_write_number(worksheet9, 8, i, i, NULL);
}
CELL("A1"),
"Examples of conditional formats with icon sets.",
NULL);
conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
worksheet_conditional_format_range(worksheet9, RANGE("B3:D3"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
conditional_format->reverse_icons = LXW_TRUE;
worksheet_conditional_format_range(worksheet9, RANGE("B4:D4"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
conditional_format->icons_only = LXW_TRUE;
worksheet_conditional_format_range(worksheet9, RANGE("B5:D5"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
worksheet_conditional_format_range(worksheet9, RANGE("B6:D6"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
worksheet_conditional_format_range(worksheet9, RANGE("B7:E7"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
worksheet_conditional_format_range(worksheet9, RANGE("B8:F8"), conditional_format);
reset_conditional_format(conditional_format);
conditional_format->type = LXW_CONDITIONAL_TYPE_ICON_SETS;
worksheet_conditional_format_range(worksheet9, RANGE("B9:F9"), conditional_format);
reset_conditional_format(conditional_format);
free(conditional_format);
return workbook_close(workbook);
}
lxw_conditional_format::bar_negative_border_color_same
uint8_t bar_negative_border_color_same
Definition: worksheet.h:1131
lxw_conditional_format::min_value
double min_value
Definition: worksheet.h:1038
workbook_close
lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
lxw_conditional_format
Worksheet conditional formatting options.
Definition: worksheet.h:998
lxw_conditional_format::format
lxw_format * format
Definition: worksheet.h:1034
LXW_CONDITIONAL_ICONS_5_RATINGS
@ LXW_CONDITIONAL_ICONS_5_RATINGS
Definition: worksheet.h:527
workbook_new
lxw_workbook * workbook_new(const char *filename)
Create a new workbook object.
LXW_CONDITIONAL_TYPE_DUPLICATE
@ LXW_CONDITIONAL_TYPE_DUPLICATE
Definition: worksheet.h:246
LXW_CONDITIONAL_CRITERIA_GREATER_THAN_OR_EQUAL_TO
@ LXW_CONDITIONAL_CRITERIA_GREATER_THAN_OR_EQUAL_TO
Definition: worksheet.h:314
LXW_CONDITIONAL_BAR_DIRECTION_RIGHT_TO_LEFT
@ LXW_CONDITIONAL_BAR_DIRECTION_RIGHT_TO_LEFT
Definition: worksheet.h:446
LXW_CONDITIONAL_CRITERIA_AVERAGE_BELOW
@ LXW_CONDITIONAL_CRITERIA_AVERAGE_BELOW
Definition: worksheet.h:371
LXW_CONDITIONAL_TYPE_AVERAGE
@ LXW_CONDITIONAL_TYPE_AVERAGE
Definition: worksheet.h:243
lxw_conditional_format::bar_direction
uint8_t bar_direction
Definition: worksheet.h:1142
lxw_conditional_format::mid_color
lxw_color_t mid_color
Definition: worksheet.h:1068
lxw_conditional_format::data_bar_2010
uint8_t data_bar_2010
Definition: worksheet.h:1102
LXW_CONDITIONAL_TYPE_TOP
@ LXW_CONDITIONAL_TYPE_TOP
Definition: worksheet.h:253
lxw_conditional_format::type
uint8_t type
Definition: worksheet.h:1003
lxw_worksheet
Struct to represent an Excel worksheet.
Definition: worksheet.h:1603
LXW_CONDITIONAL_CRITERIA_NOT_BETWEEN
@ LXW_CONDITIONAL_CRITERIA_NOT_BETWEEN
Definition: worksheet.h:323
LXW_CONDITIONAL_TYPE_UNIQUE
@ LXW_CONDITIONAL_TYPE_UNIQUE
Definition: worksheet.h:249
lxw_format
Struct to represent the formatting properties of an Excel format.
Definition: format.h:358
LXW_CONDITIONAL_TYPE_ICON_SETS
@ LXW_CONDITIONAL_TYPE_ICON_SETS
Definition: worksheet.h:289
RANGE
#define RANGE(range)
Convert an Excel A1:B2 range into a (first_row, first_col, last_row, last_col) sequence.
Definition: utility.h:82
LXW_TRUE
@ LXW_TRUE
Definition: common.h:53
lxw_conditional_format::bar_only
uint8_t bar_only
Definition: worksheet.h:1094
format_set_bg_color
void format_set_bg_color(lxw_format *format, lxw_color_t color)
Set the pattern background color for a cell.
lxw_conditional_format::bar_negative_color_same
uint8_t bar_negative_color_same
Definition: worksheet.h:1125
LXW_CONDITIONAL_ICONS_3_TRAFFIC_LIGHTS_UNRIMMED
@ LXW_CONDITIONAL_ICONS_3_TRAFFIC_LIGHTS_UNRIMMED
Definition: worksheet.h:486
LXW_CONDITIONAL_CRITERIA_BETWEEN
@ LXW_CONDITIONAL_CRITERIA_BETWEEN
Definition: worksheet.h:320
lxw_conditional_format::icons_only
uint8_t icons_only
Definition: worksheet.h:1167
lxw_conditional_format::min_color
lxw_color_t min_color
Definition: worksheet.h:1051
LXW_CONDITIONAL_TYPE_CELL
@ LXW_CONDITIONAL_TYPE_CELL
Definition: worksheet.h:231
lxw_conditional_format::bar_solid
uint8_t bar_solid
Definition: worksheet.h:1106
LXW_CONDITIONAL_DATA_BAR
@ LXW_CONDITIONAL_DATA_BAR
Definition: worksheet.h:285
LXW_CONDITIONAL_ICONS_5_ARROWS_COLORED
@ LXW_CONDITIONAL_ICONS_5_ARROWS_COLORED
Definition: worksheet.h:520
lxw_workbook
Struct to represent an Excel workbook.
Definition: workbook.h:273
lxw_conditional_format::multi_range
char * multi_range
Definition: worksheet.h:1183
lxw_conditional_format::bar_color
lxw_color_t bar_color
Definition: worksheet.h:1090
lxw_conditional_format::criteria
uint8_t criteria
Definition: worksheet.h:1011
LXW_CONDITIONAL_ICONS_3_ARROWS_COLORED
@ LXW_CONDITIONAL_ICONS_3_ARROWS_COLORED
Definition: worksheet.h:477
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.
lxw_conditional_format::reverse_icons
uint8_t reverse_icons
Definition: worksheet.h:1163
lxw_conditional_format::icon_style
uint8_t icon_style
Definition: worksheet.h:1159
lxw_conditional_format::value
double value
Definition: worksheet.h:1015
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.
format_set_font_color
void format_set_font_color(lxw_format *format, lxw_color_t color)
Set the color of the font used in the cell.
LXW_CONDITIONAL_CRITERIA_AVERAGE_ABOVE
@ LXW_CONDITIONAL_CRITERIA_AVERAGE_ABOVE
Definition: worksheet.h:368
LXW_CONDITIONAL_CRITERIA_LESS_THAN
@ LXW_CONDITIONAL_CRITERIA_LESS_THAN
Definition: worksheet.h:311
lxw_conditional_format::max_value
double max_value
Definition: worksheet.h:1073
lxw_conditional_format::max_color
lxw_color_t max_color
Definition: worksheet.h:1086
worksheet_conditional_format_range
lxw_error worksheet_conditional_format_range(lxw_worksheet *worksheet, lxw_row_t first_row, lxw_col_t first_col, lxw_row_t last_row, lxw_col_t last_col, lxw_conditional_format *conditional_format)
Add a conditional format to a worksheet range.
CELL
#define CELL(cell)
Convert an Excel A1 cell string into a (row, col) pair.
Definition: utility.h:45
LXW_CONDITIONAL_TYPE_BOTTOM
@ LXW_CONDITIONAL_TYPE_BOTTOM
Definition: worksheet.h:257
workbook_add_worksheet
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
LXW_CONDITIONAL_3_COLOR_SCALE
@ LXW_CONDITIONAL_3_COLOR_SCALE
Definition: worksheet.h:281
LXW_CONDITIONAL_2_COLOR_SCALE
@ LXW_CONDITIONAL_2_COLOR_SCALE
Definition: worksheet.h:277
LXW_CONDITIONAL_ICONS_4_ARROWS_COLORED
@ LXW_CONDITIONAL_ICONS_4_ARROWS_COLORED
Definition: worksheet.h:503
workbook_add_format
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.