libxlsxwriter
data_validate.c
<< autofilter.c conditional_format1.c >>

Example of adding data validations to a worksheet.

/*
* Examples of how to add data validation and dropdown lists using the
* libxlsxwriter library.
*
* Data validation is a feature of Excel which allows you to restrict the data
* that a user enters in a cell and to display help and warning messages. It
* also allows you to restrict input to values in a drop down list.
*
* Copyright 2014-2021, John McNamara, jmcnamara@cpan.org
*/
#include "xlsxwriter.h"
/*
* Write some data to the worksheet.
*/
void write_worksheet_data(lxw_worksheet *worksheet, lxw_format *format) {
worksheet_write_string(worksheet, CELL("A1"),
"Some examples of data validation in libxlsxwriter",
format);
worksheet_write_string(worksheet, CELL("B1"), "Enter values in this column", format);
worksheet_write_string(worksheet, CELL("D1"), "Sample Data", format);
worksheet_write_string(worksheet, CELL("D3"), "Integers", NULL);
worksheet_write_number(worksheet, CELL("E3"), 1, NULL);
worksheet_write_number(worksheet, CELL("F3"), 10, NULL);
worksheet_write_string(worksheet, CELL("D4"), "List Data", NULL);
worksheet_write_string(worksheet, CELL("E4"), "open", NULL);
worksheet_write_string(worksheet, CELL("F4"), "high", NULL);
worksheet_write_string(worksheet, CELL("G4"), "close", NULL);
worksheet_write_string(worksheet, CELL("D5"), "Formula", NULL);
worksheet_write_formula(worksheet, CELL("E5"), "=AND(F5=50,G5=60)", NULL);
worksheet_write_number(worksheet, CELL("F5"), 50, NULL);
worksheet_write_number(worksheet, CELL("G5"), 60, NULL);
}
/*
* Create a worksheet with data validations.
*/
int main() {
lxw_workbook *workbook = workbook_new("data_validate1.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
lxw_data_validation *data_validation = calloc(1, sizeof(lxw_data_validation));
/* Add a format to use to highlight the header cells. */
lxw_format *format = workbook_add_format(workbook);
format_set_fg_color(format, 0xC6EFCE);
format_set_bold(format);
format_set_indent(format, 1);
/* Write some data for the validations. */
write_worksheet_data(worksheet, format);
/* Set up layout of the worksheet. */
worksheet_set_column(worksheet, 0, 0, 55, NULL);
worksheet_set_column(worksheet, 1, 1, 15, NULL);
worksheet_set_column(worksheet, 3, 3, 15, NULL);
worksheet_set_row(worksheet, 0, 36, NULL);
/*
* Example 1. Limiting input to an integer in a fixed range.
*/
CELL("A3"),
"Enter an integer between 1 and 10",
NULL);
data_validation->minimum_number = 1;
data_validation->maximum_number = 10;
worksheet_data_validation_cell(worksheet, CELL("B3"), data_validation);
/*
* Example 2. Limiting input to an integer outside a fixed range.
*/
CELL("A5"),
"Enter an integer not between 1 and 10 (using cell references)",
NULL);
data_validation->minimum_formula = "=E3";
data_validation->maximum_formula = "=F3";
worksheet_data_validation_cell(worksheet, CELL("B5"), data_validation);
/*
* Example 3. Limiting input to an integer greater than a fixed value.
*/
CELL("A7"),
"Enter an integer greater than 0",
NULL);
data_validation->value_number = 0;
worksheet_data_validation_cell(worksheet, CELL("B7"), data_validation);
/*
* Example 4. Limiting input to an integer less than a fixed value.
*/
CELL("A9"),
"Enter an integer less than 10",
NULL);
data_validation->value_number = 10;
worksheet_data_validation_cell(worksheet, CELL("B9"), data_validation);
/*
* Example 5. Limiting input to a decimal in a fixed range.
*/
CELL("A11"),
"Enter a decimal between 0.1 and 0.5",
NULL);
data_validation->minimum_number = 0.1;
data_validation->maximum_number = 0.5;
worksheet_data_validation_cell(worksheet, CELL("B11"), data_validation);
/*
* Example 6. Limiting input to a value in a dropdown list.
*/
CELL("A13"),
"Select a value from a drop down list",
NULL);
char *list[] = {"open", "high", "close", NULL};
data_validation->validate = LXW_VALIDATION_TYPE_LIST;
data_validation->value_list = list;
worksheet_data_validation_cell(worksheet, CELL("B13"), data_validation);
/*
* Example 7. Limiting input to a value in a dropdown list.
*/
CELL("A15"),
"Select a value from a drop down list (using a cell range)",
NULL);
data_validation->value_formula = "=$E$4:$G$4";
worksheet_data_validation_cell(worksheet, CELL("B15"), data_validation);
/*
* Example 8. Limiting input to a date in a fixed range.
*/
CELL("A17"),
"Enter a date between 1/1/2008 and 12/12/2008",
NULL);
lxw_datetime datetime1 = {2008, 1, 1, 0, 0, 0};
lxw_datetime datetime2 = {2008, 12, 12, 0, 0, 0};
data_validation->validate = LXW_VALIDATION_TYPE_DATE;
data_validation->minimum_datetime = datetime1;
data_validation->maximum_datetime = datetime2;
worksheet_data_validation_cell(worksheet, CELL("B17"), data_validation);
/*
* Example 9. Limiting input to a time in a fixed range.
*/
CELL("A19"),
"Enter a time between 6:00 and 12:00",
NULL);
lxw_datetime datetime3 = {0, 0, 0, 6, 0, 0};
lxw_datetime datetime4 = {0, 0, 0, 12, 0, 0};
data_validation->validate = LXW_VALIDATION_TYPE_DATE;
data_validation->minimum_datetime = datetime3;
data_validation->maximum_datetime = datetime4;
worksheet_data_validation_cell(worksheet, CELL("B19"), data_validation);
/*
* Example 10. Limiting input to a string greater than a fixed length.
*/
CELL("A21"),
"Enter a string longer than 3 characters",
NULL);
data_validation->value_number = 3;
worksheet_data_validation_cell(worksheet, CELL("B21"), data_validation);
/*
* Example 11. Limiting input based on a formula.
*/
CELL("A23"),
"Enter a value if the following is true \"=AND(F5=50,G5=60)\"",
NULL);
data_validation->value_formula = "=AND(F5=50,G5=60)";
worksheet_data_validation_cell(worksheet, CELL("B23"), data_validation);
/*
* Example 12. Displaying and modifying data validation messages.
*/
CELL("A25"),
"Displays a message when you select the cell",
NULL);
data_validation->minimum_number = 1;
data_validation->maximum_number = 100;
data_validation->input_title = "Enter an integer:";
data_validation->input_message = "between 1 and 100";
worksheet_data_validation_cell(worksheet, CELL("B25"), data_validation);
/*
* Example 13. Displaying and modifying data validation messages.
*/
CELL("A27"),
"Display a custom error message when integer isn't between 1 and 100",
NULL);
data_validation->minimum_number = 1;
data_validation->maximum_number = 100;
data_validation->input_title = "Enter an integer:";
data_validation->input_message = "between 1 and 100";
data_validation->error_title = "Input value is not valid!";
data_validation->error_message = "It should be an integer between 1 and 100";
worksheet_data_validation_cell(worksheet, CELL("B27"), data_validation);
/*
* Example 14. Displaying and modifying data validation messages.
*/
CELL("A29"),
"Display a custom info message when integer isn't between 1 and 100",
NULL);
data_validation->minimum_number = 1;
data_validation->maximum_number = 100;
data_validation->input_title = "Enter an integer:";
data_validation->input_message = "between 1 and 100";
data_validation->error_title = "Input value is not valid!";
data_validation->error_message = "It should be an integer between 1 and 100";
worksheet_data_validation_cell(worksheet, CELL("B29"), data_validation);
/* Cleanup. */
free(data_validation);
return workbook_close(workbook);
}
lxw_data_validation::value_formula
char * value_formula
Definition: worksheet.h:982
workbook_close
lxw_error workbook_close(lxw_workbook *workbook)
Close the Workbook object and write the XLSX file.
lxw_data_validation::criteria
uint8_t criteria
Definition: worksheet.h:931
format_set_text_wrap
void format_set_text_wrap(lxw_format *format)
Wrap text in a cell.
lxw_data_validation::input_message
char * input_message
Definition: worksheet.h:1062
lxw_data_validation::input_title
char * input_title
Definition: worksheet.h:1053
LXW_ALIGN_VERTICAL_CENTER
@ LXW_ALIGN_VERTICAL_CENTER
Definition: format.h:153
worksheet_write_formula
lxw_error worksheet_write_formula(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format)
Write a formula to a worksheet cell.
lxw_data_validation
Worksheet data validation options.
Definition: worksheet.h:920
format_set_align
void format_set_align(lxw_format *format, uint8_t alignment)
Set the alignment for data in the cell.
lxw_data_validation::error_type
uint8_t error_type
Definition: worksheet.h:961
lxw_data_validation::minimum_datetime
lxw_datetime minimum_datetime
Definition: worksheet.h:1025
workbook_new
lxw_workbook * workbook_new(const char *filename)
Create a new workbook object.
lxw_data_validation::error_title
char * error_title
Definition: worksheet.h:1070
format_set_border
void format_set_border(lxw_format *format, uint8_t style)
Set the cell border style.
format_set_bold
void format_set_bold(lxw_format *format)
Turn on bold for the format font.
LXW_VALIDATION_ERROR_TYPE_INFORMATION
@ LXW_VALIDATION_ERROR_TYPE_INFORMATION
Definition: worksheet.h:210
LXW_VALIDATION_TYPE_LENGTH
@ LXW_VALIDATION_TYPE_LENGTH
Definition: worksheet.h:158
lxw_data_validation::minimum_number
double minimum_number
Definition: worksheet.h:1013
lxw_worksheet
Struct to represent an Excel worksheet.
Definition: worksheet.h:2107
lxw_data_validation::maximum_number
double maximum_number
Definition: worksheet.h:1031
lxw_format
Struct to represent the formatting properties of an Excel format.
Definition: format.h:358
lxw_data_validation::value_list
char ** value_list
Definition: worksheet.h:1001
LXW_VALIDATION_TYPE_DECIMAL
@ LXW_VALIDATION_TYPE_DECIMAL
Definition: worksheet.h:123
LXW_VALIDATION_CRITERIA_GREATER_THAN
@ LXW_VALIDATION_CRITERIA_GREATER_THAN
Definition: worksheet.h:189
lxw_data_validation::maximum_formula
char * maximum_formula
Definition: worksheet.h:1037
LXW_VALIDATION_TYPE_CUSTOM_FORMULA
@ LXW_VALIDATION_TYPE_CUSTOM_FORMULA
Definition: worksheet.h:166
lxw_datetime
Struct to represent a date and time in Excel.
Definition: common.h:155
LXW_VALIDATION_TYPE_LIST
@ LXW_VALIDATION_TYPE_LIST
Definition: worksheet.h:130
format_set_fg_color
void format_set_fg_color(lxw_format *format, lxw_color_t color)
Set the pattern foreground color for a cell.
LXW_VALIDATION_TYPE_INTEGER
@ LXW_VALIDATION_TYPE_INTEGER
Definition: worksheet.h:116
lxw_data_validation::validate
uint8_t validate
Definition: worksheet.h:925
LXW_VALIDATION_TYPE_LIST_FORMULA
@ LXW_VALIDATION_TYPE_LIST_FORMULA
Definition: worksheet.h:134
lxw_data_validation::minimum_formula
char * minimum_formula
Definition: worksheet.h:1019
lxw_workbook
Struct to represent an Excel workbook.
Definition: workbook.h:292
lxw_data_validation::value_number
double value_number
Definition: worksheet.h:975
lxw_data_validation::error_message
char * error_message
Definition: worksheet.h:1081
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_data_validation_cell
lxw_error worksheet_data_validation_cell(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, lxw_data_validation *validation)
Add a data validation to a cell.
LXW_VALIDATION_TYPE_DATE
@ LXW_VALIDATION_TYPE_DATE
Definition: worksheet.h:137
LXW_VALIDATION_CRITERIA_NOT_BETWEEN
@ LXW_VALIDATION_CRITERIA_NOT_BETWEEN
Definition: worksheet.h:180
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.
LXW_BORDER_THIN
@ LXW_BORDER_THIN
Definition: format.h:295
CELL
#define CELL(cell)
Convert an Excel A1 cell string into a (row, col) pair.
Definition: utility.h:45
LXW_VALIDATION_CRITERIA_LESS_THAN
@ LXW_VALIDATION_CRITERIA_LESS_THAN
Definition: worksheet.h:192
workbook_add_worksheet
lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)
Add a new worksheet to a workbook.
lxw_data_validation::maximum_datetime
lxw_datetime maximum_datetime
Definition: worksheet.h:1043
format_set_indent
void format_set_indent(lxw_format *format, uint8_t level)
Set the cell text indentation level.
LXW_VALIDATION_CRITERIA_BETWEEN
@ LXW_VALIDATION_CRITERIA_BETWEEN
Definition: worksheet.h:177
workbook_add_format
lxw_format * workbook_add_format(lxw_workbook *workbook)
Create a new Format object to formats cells in worksheets.