巴彦淖尔市网站建设_网站建设公司_移动端适配_seo优化

第七章：Excel数据验证与保护

7.1 数据验证基础

7.1.1 数据验证概述

数据验证是Excel中用于限制用户输入的功能，可以确保数据的准确性和一致性。NPOI支持创建各种类型的数据验证规则。

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("数据验证");// 获取数据验证帮助类
IDataValidationHelper validationHelper = sheet.GetDataValidationHelper();

7.1.2 验证类型

NPOI支持以下验证类型：

验证类型	说明	枚举值
任何值	不限制	ValidationType.ANY
整数	整数验证	ValidationType.INTEGER
小数	小数验证	ValidationType.DECIMAL
列表	下拉列表	ValidationType.LIST
日期	日期验证	ValidationType.DATE
时间	时间验证	ValidationType.TIME
文本长度	文本长度验证	ValidationType.TEXT_LENGTH
自定义	公式验证	ValidationType.FORMULA

7.2 下拉列表

7.2.1 显式列表（直接指定选项）

// 创建显式列表约束
string[] options = { "是", "否", "未知" };
IDataValidationConstraint constraint = validationHelper.CreateExplicitListConstraint(options);// 指定应用范围（A2:A100）
CellRangeAddressList addressList = new CellRangeAddressList(1, 99, 0, 0);// 创建数据验证
IDataValidation validation = validationHelper.CreateValidation(constraint, addressList);// 设置下拉箭头显示
validation.ShowDropDown = false;  // false表示显示下拉箭头// 设置错误提示
validation.ShowErrorBox = true;
validation.CreateErrorBox("输入错误", "请从下拉列表中选择一个值");// 设置输入提示
validation.ShowPromptBox = true;
validation.CreatePromptBox("提示", "请选择一个选项");// 应用验证
sheet.AddValidationData(validation);

7.2.2 引用列表（从单元格区域获取选项）

// 先在某个区域填入选项
ISheet optionSheet = workbook.CreateSheet("选项");
string[] departments = { "技术部", "市场部", "人事部", "财务部", "运营部" };
for (int i = 0; i < departments.Length; i++)
{optionSheet.CreateRow(i).CreateCell(0).SetCellValue(departments[i]);
}// 创建引用列表约束
IDataValidationConstraint refConstraint = validationHelper.CreateFormulaListConstraint("选项!$A$1:$A$5");CellRangeAddressList deptAddressList = new CellRangeAddressList(1, 99, 1, 1);
IDataValidation deptValidation = validationHelper.CreateValidation(refConstraint, deptAddressList);
deptValidation.ShowErrorBox = true;
deptValidation.CreateErrorBox("错误", "请选择有效的部门");sheet.AddValidationData(deptValidation);

7.2.3 命名范围列表

// 创建命名范围
IName namedRange = workbook.CreateName();
namedRange.NameName = "ProductList";
namedRange.RefersToFormula = "选项!$B$1:$B$10";// 使用命名范围创建下拉列表
IDataValidationConstraint namedConstraint = validationHelper.CreateFormulaListConstraint("ProductList");CellRangeAddressList prodAddressList = new CellRangeAddressList(1, 99, 2, 2);
IDataValidation prodValidation = validationHelper.CreateValidation(namedConstraint, prodAddressList);
sheet.AddValidationData(prodValidation);

7.3 数值验证

7.3.1 整数验证

// 创建整数约束（1-100之间）
IDataValidationConstraint intConstraint = validationHelper.CreateIntegerConstraint(OperatorType.BETWEEN, "1", "100");CellRangeAddressList intAddressList = new CellRangeAddressList(1, 99, 3, 3);
IDataValidation intValidation = validationHelper.CreateValidation(intConstraint, intAddressList);
intValidation.ShowErrorBox = true;
intValidation.CreateErrorBox("数值错误", "请输入1到100之间的整数");
intValidation.ShowPromptBox = true;
intValidation.CreatePromptBox("数量", "请输入1-100之间的整数");sheet.AddValidationData(intValidation);

7.3.2 小数验证

// 创建小数约束（大于0）
IDataValidationConstraint decConstraint = validationHelper.CreateDecimalConstraint(OperatorType.GREATER_THAN, "0", null);CellRangeAddressList decAddressList = new CellRangeAddressList(1, 99, 4, 4);
IDataValidation decValidation = validationHelper.CreateValidation(decConstraint, decAddressList);
decValidation.ShowErrorBox = true;
decValidation.CreateErrorBox("错误", "金额必须大于0");sheet.AddValidationData(decValidation);

7.3.3 比较运算符

// 支持的运算符类型
OperatorType.BETWEEN;           // 介于
OperatorType.NOT_BETWEEN;       // 不介于
OperatorType.EQUAL;             // 等于
OperatorType.NOT_EQUAL;         // 不等于
OperatorType.GREATER_THAN;      // 大于
OperatorType.LESS_THAN;         // 小于
OperatorType.GREATER_OR_EQUAL;  // 大于或等于
OperatorType.LESS_OR_EQUAL;     // 小于或等于// 示例：大于或等于某个单元格的值
IDataValidationConstraint dynamicConstraint = validationHelper.CreateIntegerConstraint(OperatorType.GREATER_OR_EQUAL, "$E$1", null);

7.4 日期时间验证

7.4.1 日期验证

// 日期在指定范围内
IDataValidationConstraint dateConstraint = validationHelper.CreateDateConstraint(OperatorType.BETWEEN, "2024-01-01", "2024-12-31", "yyyy-MM-dd");CellRangeAddressList dateAddressList = new CellRangeAddressList(1, 99, 5, 5);
IDataValidation dateValidation = validationHelper.CreateValidation(dateConstraint, dateAddressList);
dateValidation.ShowErrorBox = true;
dateValidation.CreateErrorBox("日期错误", "请输入2024年的日期");sheet.AddValidationData(dateValidation);

7.4.2 时间验证

// 时间在工作时间内（9:00-18:00）
IDataValidationConstraint timeConstraint = validationHelper.CreateTimeConstraint(OperatorType.BETWEEN, "09:00", "18:00");CellRangeAddressList timeAddressList = new CellRangeAddressList(1, 99, 6, 6);
IDataValidation timeValidation = validationHelper.CreateValidation(timeConstraint, timeAddressList);
timeValidation.ShowErrorBox = true;
timeValidation.CreateErrorBox("时间错误", "请输入9:00到18:00之间的时间");sheet.AddValidationData(timeValidation);

7.5 文本长度验证

// 文本长度限制（5-20个字符）
IDataValidationConstraint textLengthConstraint = validationHelper.CreateTextLengthConstraint(OperatorType.BETWEEN, "5", "20");CellRangeAddressList textAddressList = new CellRangeAddressList(1, 99, 7, 7);
IDataValidation textValidation = validationHelper.CreateValidation(textLengthConstraint, textAddressList);
textValidation.ShowErrorBox = true;
textValidation.CreateErrorBox("长度错误", "文本长度必须在5-20个字符之间");sheet.AddValidationData(textValidation);

7.6 自定义公式验证

7.6.1 基本公式验证

// 使用公式验证（检查是否为有效的邮箱格式简化版本）
IDataValidationConstraint formulaConstraint = validationHelper.CreateCustomConstraint("AND(ISNUMBER(FIND(\"@\",A2)),ISNUMBER(FIND(\".\",A2)))");CellRangeAddressList emailAddressList = new CellRangeAddressList(1, 99, 8, 8);
IDataValidation emailValidation = validationHelper.CreateValidation(formulaConstraint, emailAddressList);
emailValidation.ShowErrorBox = true;
emailValidation.CreateErrorBox("格式错误", "请输入有效的邮箱地址");sheet.AddValidationData(emailValidation);

7.6.2 复杂公式验证示例

// 验证身份证号（简化：18位数字或17位数字+X）
string idCardFormula = "OR(AND(LEN(J2)=18,ISNUMBER(VALUE(J2))),AND(LEN(J2)=18,ISNUMBER(VALUE(LEFT(J2,17))),RIGHT(J2,1)=\"X\"))";
IDataValidationConstraint idConstraint = validationHelper.CreateCustomConstraint(idCardFormula);CellRangeAddressList idAddressList = new CellRangeAddressList(1, 99, 9, 9);
IDataValidation idValidation = validationHelper.CreateValidation(idConstraint, idAddressList);
idValidation.ShowErrorBox = true;
idValidation.CreateErrorBox("格式错误", "请输入18位身份证号");sheet.AddValidationData(idValidation);// 验证手机号（11位数字，以1开头）
string phoneFormula = "AND(LEN(K2)=11,LEFT(K2,1)=\"1\",ISNUMBER(VALUE(K2)))";
IDataValidationConstraint phoneConstraint = validationHelper.CreateCustomConstraint(phoneFormula);CellRangeAddressList phoneAddressList = new CellRangeAddressList(1, 99, 10, 10);
IDataValidation phoneValidation = validationHelper.CreateValidation(phoneConstraint, phoneAddressList);
phoneValidation.ShowErrorBox = true;
phoneValidation.CreateErrorBox("格式错误", "请输入11位手机号码");sheet.AddValidationData(phoneValidation);

7.7 验证设置选项

7.7.1 错误提示设置

// 错误提示样式
validation.ErrorStyle = ErrorStyle.STOP;       // 停止：禁止输入无效数据
validation.ErrorStyle = ErrorStyle.WARNING;    // 警告：允许输入但显示警告
validation.ErrorStyle = ErrorStyle.INFO;       // 信息：仅显示信息提示// 创建错误提示框
validation.ShowErrorBox = true;
validation.CreateErrorBox("错误标题", "错误详细信息");// 或设置错误提示属性
validation.ErrorBoxTitle = "错误标题";
validation.ErrorBoxText = "错误详细信息";

7.7.2 输入提示设置

// 输入提示
validation.ShowPromptBox = true;
validation.CreatePromptBox("输入提示标题", "请按照要求输入数据");// 或设置输入提示属性
validation.PromptBoxTitle = "提示标题";
validation.PromptBoxText = "提示内容";

7.7.3 其他设置

// 是否允许空值
validation.EmptyCellAllowed = true;// 下拉列表是否显示下拉箭头（注意：false表示显示）
validation.ShowDropDown = false;// 是否抑制下拉列表
validation.SuppressDropDownArrow = false;

7.8 工作表保护

7.8.1 保护工作表

// 保护工作表（带密码）
sheet.ProtectSheet("password123");// 对于XSSFSheet，可以设置更详细的保护选项
if (sheet is XSSFSheet xssfSheet)
{xssfSheet.LockAutoFilter(true);      // 锁定自动筛选xssfSheet.LockDeleteColumns(true);   // 锁定删除列xssfSheet.LockDeleteRows(true);      // 锁定删除行xssfSheet.LockFormatCells(true);     // 锁定格式化单元格xssfSheet.LockFormatColumns(true);   // 锁定格式化列xssfSheet.LockFormatRows(true);      // 锁定格式化行xssfSheet.LockInsertColumns(true);   // 锁定插入列xssfSheet.LockInsertRows(true);      // 锁定插入行xssfSheet.LockInsertHyperlinks(true);// 锁定插入超链接xssfSheet.LockPivotTables(true);     // 锁定数据透视表xssfSheet.LockSort(true);            // 锁定排序xssfSheet.LockObjects(true);         // 锁定对象xssfSheet.LockScenarios(true);       // 锁定方案xssfSheet.LockSelectLockedCells(false);  // 允许选择锁定的单元格xssfSheet.LockSelectUnlockedCells(false);// 允许选择未锁定的单元格
}

7.8.2 取消工作表保护

// 取消保护（需要正确的密码）
// NPOI中直接调用ProtectSheet即可取消，某些版本可能需要密码
if (sheet is XSSFSheet xssfSheet)
{// 对于XSSF，可以通过设置空密码来取消保护xssfSheet.ProtectSheet(null);
}

7.9 单元格保护

7.9.1 锁定单元格

// 默认情况下，所有单元格都是锁定的
// 要让锁定生效，需要保护工作表// 创建锁定样式
ICellStyle lockedStyle = workbook.CreateCellStyle();
lockedStyle.IsLocked = true;// 创建未锁定样式（允许编辑）
ICellStyle unlockedStyle = workbook.CreateCellStyle();
unlockedStyle.IsLocked = false;// 应用样式
IRow row = sheet.CreateRow(0);// 锁定单元格
ICell lockedCell = row.CreateCell(0);
lockedCell.SetCellValue("锁定的单元格");
lockedCell.CellStyle = lockedStyle;// 未锁定单元格
ICell unlockedCell = row.CreateCell(1);
unlockedCell.SetCellValue("可编辑的单元格");
unlockedCell.CellStyle = unlockedStyle;// 保护工作表使锁定生效
sheet.ProtectSheet("password");

7.9.2 隐藏公式

// 隐藏公式（保护工作表后，公式栏不显示公式）
ICellStyle hiddenFormulaStyle = workbook.CreateCellStyle();
hiddenFormulaStyle.IsHidden = true;
hiddenFormulaStyle.IsLocked = true;ICell formulaCell = row.CreateCell(2);
formulaCell.SetCellFormula("SUM(A1:A10)");
formulaCell.CellStyle = hiddenFormulaStyle;

7.10 工作簿保护

7.10.1 保护工作簿结构

// 保护工作簿结构（防止添加、删除、重命名工作表）
if (workbook is XSSFWorkbook xssfWorkbook)
{xssfWorkbook.LockStructure();xssfWorkbook.LockWindows();  // 锁定窗口大小和位置
}

7.10.2 设置工作簿密码

// 对于XSSF工作簿，可以设置打开密码
// 注意：这需要使用POIFSFileSystem，NPOI中的实现可能有限

7.11 数据验证辅助类

/// <summary>
/// 数据验证辅助类
/// </summary>
public static class DataValidationHelper
{/// <summary>/// 添加下拉列表/// </summary>public static void AddDropdownList(ISheet sheet, int firstRow, int lastRow, int column, string[] options, string title = null, string message = null){IDataValidationHelper helper = sheet.GetDataValidationHelper();IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(options);CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);IDataValidation validation = helper.CreateValidation(constraint, addressList);validation.ShowErrorBox = true;validation.CreateErrorBox("选择错误", "请从下拉列表中选择有效值");if (!string.IsNullOrEmpty(title) || !string.IsNullOrEmpty(message)){validation.ShowPromptBox = true;validation.CreatePromptBox(title ?? "提示", message ?? "请选择一个选项");}sheet.AddValidationData(validation);}/// <summary>/// 添加数值范围验证/// </summary>public static void AddNumberValidation(ISheet sheet, int firstRow, int lastRow,int column, double? minValue, double? maxValue, bool isInteger = false,string errorTitle = null, string errorMessage = null){IDataValidationHelper helper = sheet.GetDataValidationHelper();IDataValidationConstraint constraint;if (minValue.HasValue && maxValue.HasValue){constraint = isInteger? helper.CreateIntegerConstraint(OperatorType.BETWEEN, minValue.Value.ToString(), maxValue.Value.ToString()): helper.CreateDecimalConstraint(OperatorType.BETWEEN,minValue.Value.ToString(), maxValue.Value.ToString());}else if (minValue.HasValue){constraint = isInteger? helper.CreateIntegerConstraint(OperatorType.GREATER_OR_EQUAL,minValue.Value.ToString(), null): helper.CreateDecimalConstraint(OperatorType.GREATER_OR_EQUAL,minValue.Value.ToString(), null);}else if (maxValue.HasValue){constraint = isInteger? helper.CreateIntegerConstraint(OperatorType.LESS_OR_EQUAL,maxValue.Value.ToString(), null): helper.CreateDecimalConstraint(OperatorType.LESS_OR_EQUAL,maxValue.Value.ToString(), null);}else{return;  // 没有限制}CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);IDataValidation validation = helper.CreateValidation(constraint, addressList);validation.ShowErrorBox = true;validation.CreateErrorBox(errorTitle ?? "数值错误",errorMessage ?? $"请输入{(minValue.HasValue ? $"不小于{minValue}" : "")}{(minValue.HasValue && maxValue.HasValue ? "且" : "")}{(maxValue.HasValue ? $"不大于{maxValue}" : "")}的{(isInteger ? "整数" : "数值")}");sheet.AddValidationData(validation);}/// <summary>/// 添加日期范围验证/// </summary>public static void AddDateValidation(ISheet sheet, int firstRow, int lastRow,int column, DateTime? minDate, DateTime? maxDate,string errorTitle = null, string errorMessage = null){IDataValidationHelper helper = sheet.GetDataValidationHelper();string minStr = minDate?.ToString("yyyy-MM-dd");string maxStr = maxDate?.ToString("yyyy-MM-dd");IDataValidationConstraint constraint;if (minStr != null && maxStr != null){constraint = helper.CreateDateConstraint(OperatorType.BETWEEN, minStr, maxStr, "yyyy-MM-dd");}else if (minStr != null){constraint = helper.CreateDateConstraint(OperatorType.GREATER_OR_EQUAL, minStr, null, "yyyy-MM-dd");}else if (maxStr != null){constraint = helper.CreateDateConstraint(OperatorType.LESS_OR_EQUAL, maxStr, null, "yyyy-MM-dd");}else{return;}CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);IDataValidation validation = helper.CreateValidation(constraint, addressList);validation.ShowErrorBox = true;validation.CreateErrorBox(errorTitle ?? "日期错误",errorMessage ?? "请输入有效的日期");sheet.AddValidationData(validation);}/// <summary>/// 添加文本长度验证/// </summary>public static void AddTextLengthValidation(ISheet sheet, int firstRow, int lastRow,int column, int? minLength, int? maxLength,string errorTitle = null, string errorMessage = null){IDataValidationHelper helper = sheet.GetDataValidationHelper();IDataValidationConstraint constraint;if (minLength.HasValue && maxLength.HasValue){constraint = helper.CreateTextLengthConstraint(OperatorType.BETWEEN,minLength.Value.ToString(), maxLength.Value.ToString());}else if (minLength.HasValue){constraint = helper.CreateTextLengthConstraint(OperatorType.GREATER_OR_EQUAL,minLength.Value.ToString(), null);}else if (maxLength.HasValue){constraint = helper.CreateTextLengthConstraint(OperatorType.LESS_OR_EQUAL,maxLength.Value.ToString(), null);}else{return;}CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);IDataValidation validation = helper.CreateValidation(constraint, addressList);validation.ShowErrorBox = true;validation.CreateErrorBox(errorTitle ?? "长度错误",errorMessage ?? $"文本长度必须{(minLength.HasValue ? $"至少{minLength}个字符" : "")}{(minLength.HasValue && maxLength.HasValue ? "，" : "")}{(maxLength.HasValue ? $"最多{maxLength}个字符" : "")}");sheet.AddValidationData(validation);}/// <summary>/// 设置单元格为可编辑（取消锁定）/// </summary>public static void SetCellsEditable(ISheet sheet, CellRangeAddress range, IWorkbook workbook){ICellStyle unlockedStyle = workbook.CreateCellStyle();unlockedStyle.IsLocked = false;for (int rowIdx = range.FirstRow; rowIdx <= range.LastRow; rowIdx++){IRow row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);for (int colIdx = range.FirstColumn; colIdx <= range.LastColumn; colIdx++){ICell cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);// 保留原有样式但取消锁定if (cell.CellStyle != null){ICellStyle newStyle = workbook.CreateCellStyle();newStyle.CloneStyleFrom(cell.CellStyle);newStyle.IsLocked = false;cell.CellStyle = newStyle;}else{cell.CellStyle = unlockedStyle;}}}}
}

7.12 综合示例

7.12.1 创建数据录入模板

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.IO;public class DataEntryTemplateExample
{public static void CreateEmployeeEntryTemplate(){IWorkbook workbook = new XSSFWorkbook();ISheet sheet = workbook.CreateSheet("员工信息录入");// 创建表头IRow headerRow = sheet.CreateRow(0);string[] headers = { "姓名", "性别", "出生日期", "部门", "职位", "入职日期", "薪资", "手机号", "邮箱", "备注" };ICellStyle headerStyle = CreateHeaderStyle(workbook);for (int i = 0; i < headers.Length; i++){ICell cell = headerRow.CreateCell(i);cell.SetCellValue(headers[i]);cell.CellStyle = headerStyle;sheet.SetColumnWidth(i, 15 * 256);}// 创建数据输入区域样式（未锁定）ICellStyle inputStyle = workbook.CreateCellStyle();inputStyle.IsLocked = false;inputStyle.FillForegroundColor = IndexedColors.LightYellow.Index;inputStyle.FillPattern = FillPattern.SolidForeground;// 预设数据行for (int i = 1; i <= 100; i++){IRow row = sheet.CreateRow(i);for (int j = 0; j < headers.Length; j++){ICell cell = row.CreateCell(j);cell.CellStyle = inputStyle;}}// 获取数据验证帮助器IDataValidationHelper helper = sheet.GetDataValidationHelper();// 1. 姓名：文本长度2-20DataValidationHelper.AddTextLengthValidation(sheet, 1, 100, 0, 2, 20, "姓名格式错误", "姓名长度应为2-20个字符");// 2. 性别：下拉列表DataValidationHelper.AddDropdownList(sheet, 1, 100, 1, new[] { "男", "女" }, "选择性别", "请选择员工性别");// 3. 出生日期：日期范围DataValidationHelper.AddDateValidation(sheet, 1, 100, 2,new DateTime(1960, 1, 1), DateTime.Now.AddYears(-18),"日期错误", "请输入有效的出生日期（员工需年满18岁）");// 4. 部门：下拉列表DataValidationHelper.AddDropdownList(sheet, 1, 100, 3,new[] { "技术部", "市场部", "人事部", "财务部", "运营部" },"选择部门", "请选择所属部门");// 5. 职位：下拉列表DataValidationHelper.AddDropdownList(sheet, 1, 100, 4,new[] { "经理", "主管", "高级工程师", "工程师", "助理" },"选择职位", "请选择职位级别");// 6. 入职日期：日期范围DataValidationHelper.AddDateValidation(sheet, 1, 100, 5,new DateTime(2000, 1, 1), DateTime.Now,"日期错误", "入职日期不能晚于今天");// 7. 薪资：数值范围DataValidationHelper.AddNumberValidation(sheet, 1, 100, 6,3000, 1000000, false, "薪资错误", "薪资范围应为3000-1000000");// 8. 手机号：自定义公式验证IDataValidationConstraint phoneConstraint = helper.CreateCustomConstraint("AND(LEN(H2)=11,LEFT(H2,1)=\"1\")");CellRangeAddressList phoneRange = new CellRangeAddressList(1, 100, 7, 7);IDataValidation phoneValidation = helper.CreateValidation(phoneConstraint, phoneRange);phoneValidation.ShowErrorBox = true;phoneValidation.CreateErrorBox("格式错误", "请输入11位手机号码");sheet.AddValidationData(phoneValidation);// 9. 邮箱：自定义公式验证IDataValidationConstraint emailConstraint = helper.CreateCustomConstraint("AND(ISNUMBER(FIND(\"@\",I2)),ISNUMBER(FIND(\".\",I2)))");CellRangeAddressList emailRange = new CellRangeAddressList(1, 100, 8, 8);IDataValidation emailValidation = helper.CreateValidation(emailConstraint, emailRange);emailValidation.ShowErrorBox = true;emailValidation.CreateErrorBox("格式错误", "请输入有效的邮箱地址");sheet.AddValidationData(emailValidation);// 10. 备注：文本长度限制DataValidationHelper.AddTextLengthValidation(sheet, 1, 100, 9, null, 500,"备注过长", "备注最多500个字符");// 冻结首行sheet.CreateFreezePane(0, 1);// 保护工作表sheet.ProtectSheet("admin123");// 保存using (FileStream fs = new FileStream("员工信息录入模板.xlsx", FileMode.Create)){workbook.Write(fs);}Console.WriteLine("数据录入模板创建成功！");}private static ICellStyle CreateHeaderStyle(IWorkbook workbook){ICellStyle style = workbook.CreateCellStyle();IFont font = workbook.CreateFont();font.IsBold = true;font.FontHeightInPoints = 11;style.SetFont(font);style.Alignment = HorizontalAlignment.Center;style.VerticalAlignment = VerticalAlignment.Center;style.FillForegroundColor = IndexedColors.Grey25Percent.Index;style.FillPattern = FillPattern.SolidForeground;style.BorderBottom = BorderStyle.Thin;style.BorderTop = BorderStyle.Thin;style.BorderLeft = BorderStyle.Thin;style.BorderRight = BorderStyle.Thin;style.IsLocked = true;return style;}
}

7.13 本章小结

本章详细介绍了NPOI中的数据验证与保护功能。通过本章学习，你应该掌握：

• 数据验证的基本概念和类型
• 下拉列表的创建（显式列表、引用列表、命名范围）
• 数值验证（整数、小数、范围限制）
• 日期时间验证
• 文本长度验证
• 自定义公式验证
• 错误提示和输入提示的设置
• 工作表保护和取消保护
• 单元格锁定和公式隐藏
• 工作簿结构保护

数据验证和保护功能可以有效提高数据输入的准确性，防止误操作，是创建专业数据录入模板的重要手段。

---

下一章预告：第八章将介绍Excel图表与图形功能，包括各种类型图表的创建、图片插入和形状绘制。