EXCEL破冰 - 锁定单元格样式和输入格式

需求背景

最近项目中一个功能，需要用户以Excel文件的方式提供录入数据。为了避免用户误操作给后续的文件解析工作带来额外的巨大工作量，我们需要对Excel模板文件进行定制。以下图为例

具体要求如下

• 锁定Excel样式，不能修改单元格样式和位置；
• 限制特定单元格输入内容的格式，比如电话号码，日期等；
• 不允许修改深灰色部分的表头，但是可以输入和修改浅灰色的单元格内容；

限制单元格输入格式

通过Excel的数据验证功能就可以实现在输入内容的同时进行校验，只有满足格式条件的数据才能保存。使用方法比较简单，首先选中你要校验的单元格（比如下图中的A列），然后点击【数据】菜单下的【数据验证】，根据具体的要求设置验证条件即可。

通过内置的验证条件，以及可以使用公式的自定义验证，我们可以对很多复杂的数据格式进行校验。因为我们项目中的实际需求不算太复杂，所以这篇文章中仅仅展示了部分Excel的功能。

日期

在数据验证的下拉框【允许】中，选择日期，然后指定一个范围。本例中指定了一个非常宽的范围，目的仅仅是为了保证用户输入的是一个合法的日期，不会出现2月30日这种手滑失误。

作为一个用户友好的程序，在用户输入出错的时候，应该给出一个明确的信息，指导用户修改错误。所以我们在设置了验证条件之后，还需要配置出错警告信息，当然如果一定要偷懒的话也可以省略这一步。如下图

整数和小数

数字类的验证比较类似，这里仅仅举一个例子，如下

手机号码

对于像身份证，电话号码这一类信息，没有现成的验证条件可以使用。不过Excel提供了自定义这种验证方式，可以通过写一个公式，来曲线救国完成复杂数据格式的验证。

还是以手机号码为例，本质上是要求输入一个11位长度的数字，那么我们就把验证拆分为同时成立的两个条件：

1. 长度11位，公式 LEN(D1)=11
2. 必须是数字，公式 ISNUMBER(D1)

如下图，我们在【允许】下拉框中选择【自定义】，然后在下方的【公式】里面输入公式

锁定样式

为了避免用户无意或者有意的修改Excel模板，我们需要对样式进行锁定。锁定之后的文件，用户只能在我们规定的单元格内输入内容，并且不能随意修改单元格样式。Excel提供的保护工作表功能可以帮助我们实现这个愿望，仅仅需要点几下鼠标，我们即可达成目标。

首先我们要选定不需要锁定的单元格，这里要解释一下，为啥是选不需要锁定？在本例中，浅灰色的单元格是录入区域，也是我们不需要锁定的部分。我们需要保护的是非录入区域，比如表头。Excel默认是对所有单元格开启了锁定选项的，所以我们要把非录入区域的锁定取消，否则用户就没法录入数据了。

如下图，我们选中所有浅灰色单元格，然后鼠标右键点击【设置单元格格式】。

在设置单元格格式窗口中，取消【锁定】。记住，默认是勾选的，一定要取消，一定要取消，一定要取消！

然后的任务就简单了，在【审核】菜单中找到并点击【保护工作表】，提供用于解锁的密码，大功告成。

PS：如果有更多特殊要求，可以修改允许此工作表的所有用户进行中的选项来完成更细粒度的控制。

转载于:https://juejin.im/post/5afc3083f265da0b9c10d28f