今天要分享的这个问题其实也属于excel的高级用法。我们想通过libxlsxwriter来实现一个能对用户的输入进行数据验证的表格功能。
内心os：小白最近是不是有点走火入魔了……我们真的会遇上这样的场景吗：需要通过代码生成一个表格，这个表格中还有若干需要潜在用户输入的单元格，并且还要对这些单元格做数据有效性验证！且不管这个需求是不是很少遇到，就当小白在这里钻了一个技术的牛角尖。
1 例程学习
小白最近特别直率，二话不说，先上例程（扒自libxlsxwriter官方例程，小白进行了中文改造）：
#include "xlsxwriter.h"
#include <iostream>
#include <wchar.h>
#include <windows.h>
using std::string;
string GBKToUTF8(const char* strGBK)
{
int len = MultiByteToWideChar(CP_ACP, 0, strGBK, -1, NULL, 0);
wchar_t* wstr = new wchar_t[len + 1];
memset(wstr, 0, len + 1);
MultiByteToWideChar(CP_ACP, 0, strGBK, -1, wstr, len);
len = WideCharToMultiByte(CP_UTF8, 0, wstr, -1, NULL, 0, NULL, NULL);
char* str = new char[len + 1];
memset(str, 0, len + 1);
WideCharToMultiByte(CP_UTF8, 0, wstr, -1, str, len, NULL, NULL);
string strTemp = str;
if (wstr) delete[] wstr;
if (str) delete[] str;
return strTemp;
}
string UTF8ToGBK(const char* strUTF8)
{
int len = MultiByteToWideChar(CP_UTF8, 0, strUTF8, -1, NULL, 0);
wchar_t* wszGBK = new wchar_t[len + 1];
memset(wszGBK, 0, len * 2 + 2);
MultiByteToWideChar(CP_UTF8, 0, strUTF8, -1, wszGBK, len);
len = WideCharToMultiByte(CP_ACP, 0, wszGBK, -1, NULL, 0, NULL, NULL);
char* szGBK = new char[len + 1];
memset(szGBK, 0, len + 1);
WideCharToMultiByte(CP_ACP, 0, wszGBK, -1, szGBK, len, NULL, NULL);
string strTemp(szGBK);
if (wszGBK) delete[] wszGBK;
if (szGBK) delete[] szGBK;
return strTemp;
}
/*
* 向excel表中写入数据，这些数据用来为后续做合法性判断作辅助作用.
*/
void write_worksheet_data(lxw_worksheet* worksheet, lxw_format* format) {
worksheet_write_string(worksheet, CELL("A1"),
GBKToUTF8("使用libxlsxwriter进行数据验证").data(),
format);
worksheet_write_string(worksheet, CELL("B1"), GBKToUTF8("向此列中输入值").data(), format);
worksheet_write_string(worksheet, CELL("D1"), GBKToUTF8("示例数据").data(), format);
worksheet_write_string(worksheet, CELL("D3"), GBKToUTF8("整数").data(), NULL);
worksheet_write_number(worksheet, CELL("E3"), 1, NULL);
worksheet_write_number(worksheet, CELL("F3"), 10, NULL);
worksheet_write_string(worksheet, CELL("D4"), GBKToUTF8("列表数据").data(), NULL);
worksheet_write_string(worksheet, CELL("E4"), GBKToUTF8("开").data(), NULL);
worksheet_write_string(worksheet, CELL("F4"), GBKToUTF8("高").data(), NULL);
worksheet_write_string(worksheet, CELL("G4"), GBKToUTF8("关").data(), NULL);
worksheet_write_string(worksheet, CELL("D5"), GBKToUTF8("公式").data(), 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);
}
/*
* 创建一个带有数据合法性验证的数据表
*/
int main() {
lxw_workbook* workbook = workbook_new("data_validate1.xlsx");
lxw_worksheet* worksheet = workbook_add_worksheet(workbook, NULL);
// 定义一个验证变量
lxw_data_validation* data_validation = (lxw_data_validation*)calloc(1, sizeof(lxw_data_validation));
/* 创建带颜色强调的表头格式 */
lxw_format* format = workbook_add_format(workbook);
format_set_border(format, LXW_BORDER_THIN);
format_set_fg_color(format, 0xC6EFCE);
format_set_bold(format);
format_set_text_wrap(format);
format_set_align(format, LXW_ALIGN_VERTICAL_CENTER);
format_set_indent(format, 1);
format_set_font_name(format, "SimSun");
/* 创建带颜色强调的表头格式 */
lxw_format* format1 = workbook_add_format(workbook);
format_set_border(format1, LXW_BORDER_THIN);
format_set_align(format1, LXW_ALIGN_VERTICAL_CENTER);
format_set_font_name(format1, "SimHei");
/* 向表中写入一些辅助数据 */
write_worksheet_data(worksheet, format);
/* 设置数据表中的列宽等页面数据 */
worksheet_set_column(worksheet, 0, 0, 70, NULL);
worksheet_set_column(worksheet, 1, 1, 22, NULL);
worksheet_set_column(worksheet, 3, 3, 15, NULL);
worksheet_set_row(worksheet, 0, 36, NULL);
/*
* 例1：向表中输入一个范围值
*/
worksheet_write_string(worksheet,
CELL("A3"),
GBKToUTF8("输入一个1到10之间的整数").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 1;
data_validation->maximum_number = 10;
worksheet_data_validation_cell(worksheet, CELL("B3"), data_validation);
/*
* 例2：向表中输入一个范围之外的值
*/
worksheet_write_string(worksheet,
CELL("A5"),
GBKToUTF8("输入一个不在1到10之间的整数(1和10由单元格E3和F3指定)").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_NOT_BETWEEN;
data_validation->minimum_formula = "=E3";
data_validation->maximum_formula = "=F3";
worksheet_data_validation_cell(worksheet, CELL("B5"), data_validation);
/*
* 例3：向表中输入一个大于某值的数
*/
worksheet_write_string(worksheet,
CELL("A7"),
GBKToUTF8("输入一个比0大的整数").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_GREATER_THAN;
data_validation->value_number = 0;
worksheet_data_validation_cell(worksheet, CELL("B7"), data_validation);
/*
* 例4：向表中输入一个小于某值的数
*/
worksheet_write_string(worksheet,
CELL("A9"),
GBKToUTF8("输入一个比10小的整数").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_LESS_THAN;
data_validation->value_number = 10;
worksheet_data_validation_cell(worksheet, CELL("B9"), data_validation);
/*
* 例5：限制输入为一个范围内的小数
*/
worksheet_write_string(worksheet,
CELL("A11"),
GBKToUTF8("请输入一个在0.1到0.5之间的小数").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_DECIMAL;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 0.1;
data_validation->maximum_number = 0.5;
worksheet_data_validation_cell(worksheet, CELL("B11"), data_validation);
/*
* 例6：限制输入为下拉列表中的值
*/
worksheet_write_string(worksheet,
CELL("A13"),
GBKToUTF8("从下拉列表中选择一个值").data(),
format1);
char temp1[16] = "";
sprintf_s(temp1, GBKToUTF8("开").data());
char temp2[16] = "";
sprintf_s(temp2, GBKToUTF8("高").data());
char temp3[16] = "";
sprintf_s(temp3, GBKToUTF8("关").data());
char* list[] = {
temp1,
temp2,
temp3,
NULL };
data_validation->validate = LXW_VALIDATION_TYPE_LIST;
data_validation->value_list = list;
worksheet_data_validation_cell(worksheet, CELL("B13"), data_validation);
/*
* 例7：限制输入为下拉列表的值（下拉列表由单元格指定）
*/
worksheet_write_string(worksheet,
CELL("A15"),
GBKToUTF8("在下拉列表中选择一个值(下拉列表中的值来自单元格指定)").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_LIST_FORMULA;
data_validation->value_formula = "=$E$4:$G$4";
worksheet_data_validation_cell(worksheet, CELL("B15"), data_validation);
/*
* 例8：限制输入为指定范围内的日期
*/
worksheet_write_string(worksheet,
CELL("A17"),
GBKToUTF8("输入一个 1/1/2008 和 12/12/2008 之间的日期").data(),
format1);
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->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_datetime = datetime1;
data_validation->maximum_datetime = datetime2;
worksheet_data_validation_cell(worksheet, CELL("B17"), data_validation);
/*
* 例9：输入指定范围内的时间
*/
worksheet_write_string(worksheet,
CELL("A19"),
GBKToUTF8("输入 6:00 和 12:00之间的时间").data(),
format1);
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->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_datetime = datetime3;
data_validation->maximum_datetime = datetime4;
worksheet_data_validation_cell(worksheet, CELL("B19"), data_validation);
/*
* 例10：限制输入的字符比指定的长度要长
*/
worksheet_write_string(worksheet,
CELL("A21"),
GBKToUTF8("输入一个比3个字符长的字符串").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_LENGTH;
data_validation->criteria = LXW_VALIDATION_CRITERIA_GREATER_THAN;
data_validation->value_number = 3;
worksheet_data_validation_cell(worksheet, CELL("B21"), data_validation);
/*
* 例11：基于公式来限制输入.
*/
worksheet_write_string(worksheet,
CELL("A23"),
GBKToUTF8("如果以下为真则允许输入一个值 \"=AND(F5=50,G5=60)\"").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_CUSTOM_FORMULA;
data_validation->value_formula = "=AND(F5=50,G5=60)";
worksheet_data_validation_cell(worksheet, CELL("B23"), data_validation);
/*
* 例12：为数据验证显示提示信息
*/
worksheet_write_string(worksheet,
CELL("A25"),
GBKToUTF8("选中此单元格时显示提示").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 1;
data_validation->maximum_number = 100;
data_validation->input_title = const_cast<char*>(GBKToUTF8("输入一个整数值:").c_str());
data_validation->input_message = const_cast<char*>(GBKToUTF8("在1到100之间").c_str());
worksheet_data_validation_cell(worksheet, CELL("B25"), data_validation);
/*
* 例13：输入错误时以错误对话框弹出提示
*/
worksheet_write_string(worksheet,
CELL("A27"),
GBKToUTF8("当输入的值不在1到100之间时显示一个错误对话框").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 1;
data_validation->maximum_number = 100;
data_validation->input_title = const_cast<char*>(GBKToUTF8("输入一个整数值:").c_str());
data_validation->input_message = const_cast<char*>(GBKToUTF8("在1到100之间").c_str());
data_validation->error_title = const_cast<char*>(GBKToUTF8("输入的值不合法").c_str());
data_validation->error_message = const_cast<char*>(GBKToUTF8("输入的值必须在1到100之间").c_str());
worksheet_data_validation_cell(worksheet, CELL("B27"), data_validation);
/*
* 例14：输入错误时以提示对话框进行提示
*/
worksheet_write_string(worksheet,
CELL("A29"),
GBKToUTF8("当输入的值不在1到100之间时显示一个提示对话框").data(),
format1);
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 1;
data_validation->maximum_number = 100;
data_validation->error_type = LXW_VALIDATION_ERROR_TYPE_INFORMATION;
data_validation->input_title = const_cast<char*>(GBKToUTF8("输入一个整数值:").c_str());
data_validation->input_message = const_cast<char*>(GBKToUTF8("在1到100之间").c_str());
data_validation->error_title = const_cast<char*>(GBKToUTF8("输入的值不合法").c_str());
data_validation->error_message = const_cast<char*>(GBKToUTF8("输入的值必须在1到100之间").c_str());
//
worksheet_data_validation_cell(worksheet, CELL("B29"), data_validation);
/* 清理. */
free(data_validation);
return workbook_close(workbook);
}
由于内容比较多，所以我们不直接上效果图了，在第2小节里慢慢掰开了讲。
2 关键提要
这次的例程比较，额，长。
因为这里面其实一共有14个例子。
但是这14个例子又有很强的重复性，因此它仍然是“一个”例程，而不是“14个”例程。
读者朋友们请先忽略例程头部的两个转码函数，这只是为了适用中文，小白冥思苦想的下策。关于这个问题小白也已经有专门文章有过解释，不明白的同学可以移步libxlsxwriter中文报错问题。小白在下面讲到这里的转码时也会再进行解释。
2.1 验证值的输入范围（例1-例5）
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 1;
data_validation->maximum_number = 10;
worksheet_data_validation_cell(worksheet, CELL("B3"), data_validation);
最简单的应用就是通过限定值的输入范围来控制输入：
worksheet_data_validation_cell 作为最终对数据验证作用的单元格起作用的函数；
另一个函数为worksheet_data_validation_range，可以令数据验证对多个单元格起作用。
对例1来说，data_validation->validate表示的是验证的类型，这里选择LXW_VALIDATION_TYPE_INTEGER表示是整数型值验证。
data_validation->criteria表示的是验证的规则，这里选择LXW_VALIDATION_CRITERIA_BETWEEN表示“介于两个数之间”。
正因如此，以下指定上、下限：
data_validation->minimum_number即下限为1；
data_validation->maximum_number即上限为10。
于是例1 实际上就是在限制B3这个单元格的输入需要在1-10之间的整数，其他的输入皆为非法。
可见，当输入11时，就会弹出错误框提示不合法。
例2 其实和例1很相似，但有两个区别：
- 例1是“介于两个数之间”，例2是“不介于两个数之间”；
- 例2的上下限数值通过单元格中的数据来进行限制。
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_NOT_BETWEEN;
data_validation->minimum_formula = "=E3";
data_validation->maximum_formula = "=F3";
通过设置的参数可以看到，指定上下限的变量变成了minimum_formula和maximum_formula.分别指定Excel表格中的两个单元格，以其内容作为限定的上下限。
此时输入5，则为非法，一样弹出错误框提示。
例3 更简单，判断是不是需要比某个值更大。
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_GREATER_THAN;
data_validation->value_number = 0;
可以看到，criteria变成了LXW_VALIDATION_CRITERIA_GREATER_THAN；
value_number设置为0。即需要输入数比0大。
效果图就不贴了，因为太简单。
例4跟例3相类似。
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_LESS_THAN;
data_validation->value_number = 10;
没啥要多说的，criteria变成了LXW_VALIDATION_CRITERIA_LESS_THAN.
例5输入的是小数
data_validation->validate = LXW_VALIDATION_TYPE_DECIMAL;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 0.1;
data_validation->maximum_number = 0.5;
注意validate的类型变了，变成了LXW_VALIDATION_TYPE_DECIMAL.
这也没啥要多说的，非常简单，不想贴图了。（小白最近加班有点过头，允许偷个懒）
2.2 提供下拉列表来限定输入范围（例6-例7）
oops，这里插入一则小白的炫耀帖（请允许本小白无法平抑有点激动的心情）：在例7中小白找到了github代码中的一处bug，已经在github上跟libxlsxwriter的作者反馈（by xiaobai’s poor English）并得到了修改，这是小白的first issue in github. 传送门：围观可以，不要说话
本篇的重头戏来了。例6和例7，小白替大家连踩两坑，希望此处有点掌声（六个项目压在身上，早8晚9，晚上还在踩坑的小白是什么精神555555~）。
例6的坑不是由于libxlsxwriter本身引起的，纯纯的中文编码问题。
/*
* 例6：限制输入为下拉列表中的值
*/
worksheet_write_string(worksheet,
CELL("A13"),
GBKToUTF8("从下拉列表中选择一个值").data(),
format1);
char temp1[16] = "";
sprintf_s(temp1, GBKToUTF8("开").data());
char temp2[16] = "";
sprintf_s(temp2, GBKToUTF8("高").data());
char temp3[16] = "";
sprintf_s(temp3, GBKToUTF8("关").data());
char* list[] = {
temp1,
temp2,
temp3,
NULL };
data_validation->validate = LXW_VALIDATION_TYPE_LIST;
data_validation->value_list = list;
worksheet_data_validation_cell(worksheet, CELL("B13"), data_validation);
这里小白用了一个非常蹩脚的方式在做编码的转换，无奈，既要适应windows这位大爷的GBK程序源码编码，又要适应最终目标——office excel这位大爷的utf8编码格式。只能出此下策。
而且这里组合list的时候需要注意，末尾需要以一个NULL结尾。特别要注意的是，小白用了sprintf_s函数来生成字符串，而不是在list的定义那里直接赋值（因为会失败），想要尝试的同学也可以自己改一下看，看这个坑摔得疼不疼。
所以很可能小白采取的这一种折中的方式是唯一可行的方案。。。丑你也得接受一下。
下拉列表的效果图如下：
例7的坑小白已经用身躯帮大家填平了，现在大家直接去官网上看例程，bug已经修复（当然本篇博客中的代码也是没有bug的安全代码）。
原始代码中主要的错误为：
data_validation->validate = LXW_VALIDATION_TYPE_LIST;
这样赋值其实是不对的，但如果你是在例6的后面运行例7，很难发现这个错误，因为例6的值会影响到例7，而他们的下拉列表内容又一样，会生成一个看似正确的下拉列表。（正因为如此这个坑才这样难发现，但是如果你是按照错误的方式写的，你会发现无论怎么修改对应单元格中的内容，下拉列表的值都不会发生变化）。
data_validation->validate = LXW_VALIDATION_TYPE_LIST_FORMULA;
data_validation->value_formula = "=$E$4:$G$4";
例7和例6的最大差别在于validate类型不同，设定后对应到value_formula赋值。
可以看下效果图，这里小白把对应单元格中的值做了修改，看到下拉列表与单元格内容是匹配的。
2.3 限定时间和日期输入（例8-例9）
例8和例9是进行了时间和日期的限定输入。
先看例8，注意lxw_datetime的格式输入法。其他很简单。同上，不贴效果图。
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->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_datetime = datetime1;
data_validation->maximum_datetime = datetime2;
再看例9，与例8仅有一点技巧，就是如何表示时间：表示日期的年月日全写0就好。
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->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_datetime = datetime3;
data_validation->maximum_datetime = datetime4;
同上，此处不贴效果图。
2.4 限定输入的字符长度（例10）
例10的玩法有点适合英文。因为字符长度的计算，对于中文编码来说会有点混乱（小白已经在此事上吃了多少亏了，唉）。
data_validation->validate = LXW_VALIDATION_TYPE_LENGTH;
data_validation->criteria = LXW_VALIDATION_CRITERIA_GREATER_THAN;
data_validation->value_number = 3;
也没啥好注意的，validate的类型是LXW_VALIDATION_TYPE_LENGTH罢了。
一样，不用贴效果图。
2.5 基于公式来限定输入（例11）
例11有点像一个开关控制。如果公式表达式为true，则允许输入，反之则不允许输入。
data_validation->validate = LXW_VALIDATION_TYPE_CUSTOM_FORMULA;
data_validation->value_formula = "=AND(F5=50,G5=60)";
唯一需要注意的是这个类型的写法LXW_VALIDATION_TYPE_CUSTOM_FORMULA.
此例中是需要F5单元格中为50，G5单元格中为60同时成立才允许输入。
2.6 为数据输入提供提示（例12-例14）
例12到例14是带有输入提示信息的验证。
例12中添加了input_title和input_message两个变量的控制，分别表示提示框的标题，以及提示框的内容。需要注意的是这两者都有长度限制，不能过长。具体是多长参看文档，小白也不记得，这似乎不是很重要（谁会写一个太长的提示呢）
data_validation->validate = LXW_VALIDATION_TYPE_INTEGER;
data_validation->criteria = LXW_VALIDATION_CRITERIA_BETWEEN;
data_validation->minimum_number = 1;
data_validation->maximum_number = 100;
data_validation->input_title = const_cast<char*>(GBKToUTF8("输入一个整数值:").c_str());
data_validation->input_message = const_cast<char*>(GBKToUTF8("在1到100之间").c_str());
效果展示一下：
例13比例12多出了error_title和error_message。
data_validation->error_title = const_cast<char*>(GBKToUTF8("输入的值不合法").c_str());
data_validation->error_message = const_cast<char*>(GBKToUTF8("输入的值必须在1到100之间").c_str());
效果图贴一下，注意弹出的是错误提示框。
例14比例13只多出一样，但小白发现如果是中文编码时，一定要保证先定义error_type，否则会报错，这应该又是编码的锅。
data_validation->error_type = LXW_VALIDATION_ERROR_TYPE_INFORMATION;
data_validation->input_title = const_cast<char*>(GBKToUTF8("输入一个整数值:").c_str());
data_validation->input_message = const_cast<char*>(GBKToUTF8("在1到100之间").c_str());
data_validation->error_title = const_cast<char*>(GBKToUTF8("输入的值不合法").c_str());
data_validation->error_message = const_cast<char*>(GBKToUTF8("输入的值必须在1到100之间").c_str());
贴下效果图，注意错误发生时提示框是一个信息提示而不是一个错误提示，这是与例13的区别。
4608
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
