Exsead - Excel 数据验证和命名范围示例
3.00/5 (1投票)
一个真正用于业务中清理 Excel 命名范围的 JScript 脚本。
引言
在这篇文章中，我将结合使用我在 'Exsead' 下分别讨论过的两个概念：与 Excel 通信，以及使用 Internet Explorer 作为 GUI。
这里讨论的脚本来自一个真实的项目。为了保护公司机密，一些细节已被更改；但是，该项目的行动和结构目前正在使用，每天都在使用，用于世界上最大的 IT 公司之一。 Exsead 不仅仅是一个“好主意”，它正在被使用，并且效果很好。
Named_Ranges_Setup.js 正是这样做的：它在 Excel 工作簿中设置命名范围。该工作簿形成了业务流程的信息模板，该流程将提出的调用分配给管理域。不幸的是，映射在源数据中是未知的。但是，在源数据中，我们确实知道每个人被分配到的电子邮件地址（组长）。我们还知道每个组长领导哪个组。这两种关系在两个不同的电子表格中列出。然后，电子表格中的数据被链接到命名范围。然后，这些命名范围被用于工作流程的更下方，以进行映射。
此代码修复的真实业务问题是更新关系。当电子邮件地址、经理或应用程序发生变化时，模板需要更新，并且需要重新创建命名范围。当这个过程留给人类时，对他们来说保持重置命名范围是非常乏味的。对于人类来说，确保他们在两个关系电子表格中正确设置电子邮件地址甚至更难。Named_Ranges_Setup.js 清除现有的命名范围，然后重新创建它们。完成此操作后，它使用哈希集检查两个电子表格之间的电子邮件关系，并向用户显示任何不一致之处。
它是如何工作的？
var excel=WScript.CreateObject('Excel.Application');
excel.DisplayAlerts=false;
首先，脚本创建一个在 RAM 中运行的 Excel 应用程序。该应用程序可以通过 OLE 被脚本访问。
// The GUI window is a wrapper class around IE
// in this script we will just keep adding output to
// the bottom of the content of the window. To make this easy
// we define a string that holds the content. We always make the end
// of the string be <!-- --> . Then when we add content we replace the
// <!-- --> (html comment) with the new content and a new <!-- -->
var gui=new GUIWindow();
var txt='<h2>Nerds-Central Dept Template Cleaner:</h2>' +
'<pre>Loading Template Into Excel...\r\n<!-- --></pre>';
gui.SetHTML(txt);
// Sleep to give the GUI chance to update
WScript.sleep(16);
gui.SetVisible(true);
接下来，脚本创建一个在 RAM 中运行并通过 OLE 连接的新 Internet Explorer 应用程序。在这种情况下，使用了 JScript 类 GUI。该类包含在源 zip 文件的脚本的末尾。
// Open the workbook to be setup
var wb=excel.WorkBooks.Open(WScript.arguments.item(0));
// Clean out all existing named ranges
while(wb.Names.Count>0)
{
wb.Names.Item(1).Delete();
}
脚本需要一个参数：这是 Excel 工作簿文件。这意味着用户可以将脚本放在桌面上，并将 Excel 文件“拖放”到脚本上。这种方法非常受那些必须使用此 Exsead 代码的忙碌的项目经理的喜爱。
Excel 打开文件后，所有现有的名称都会被删除。然后，使用一个简单的值检查代码，该代码运行在电子表格中的单元格值上，以建立命名范围的新维度。一旦知道了这些，就会创建新的命名范围。
wb.Names.Add
(
'Deptarray',
"'Dept lookup'!$A$2:$D$"+rowDeptL
);
wb.Names.Add
(
'lookupvector',
"'App Lookup'!$A$2:$A$"+rowAppL
);
wb.Names.Add
(
'resultvector',
"'App Lookup'!$B$2:$B$"+rowAppL
);
wb.Names.Add
(
'tablearray',
"'App Lookup'!$A$2:$B$"+rowAppL
);
请注意命名范围是如何存储在工作簿级别，而不是工作表级别的。
// find which emails are in Dept Lookup but not in
// App Lookup
for(var i=0;i<DeptKeys.length;++i)
{
if(!appEmails.exists(DeptKeys[i]))
{
DeptUnlinked.add(DeptKeys[i],true);
}
}
哈希表（脚本中包含的另一个 JScript 类）用于累积电子邮件存在于一个电子表格中而不在另一个电子表格中的实例。然后，这些通过 GUI 报告给最终用户
// Create humand readable output
txt2='';
txt2+='Duplication Email Addresses In Dept Lookup:\r\n';
txt2+='<i>It is ok to have duplicates, ' +
'but they might be worth checking.</i>\r\n';
for(var i=0;i<DeptDupicates.length;++i)
{
txt2+=' '+DeptDupicates[i]+'\r\n';
}
txt2+='\r\nEmail Addresses In Dept Lookup But Not App Lookup:\r\n';
for(var i=0;i<DeptUnlinked.length;++i)
{
txt2+=' '+DeptUnlinked[i]+'\r\n';
}
最后，Excel 工作簿使用新的命名范围保存。此时，IE GUI 添加了一个表单，允许用户交互
// This loop keeps checking the value of the hidden input
// and responses appropreately. The actual innner listening
// loop is provided inside the WaitOnId method of the GUI object.
// All you have to do is define an ID for the hidden (in the html see above)
// and pass this ID to the method. The method returns with the value
// of the hidden when it changes from the value to which it was set
// when the call was made.
while(true)
{
var action=gui.WaitOnId('eventKey');
if(action=='close')
{
gui.Quit();
WScript.Quit(0);
}
if(action=='save')
{
var wind=gui.GetWindow();
var toCopy=gui.GetDocument();
toCopy=toCopy.body;
toCopy=toCopy.innerText;
var cbd=wind.clipboardData;
cbd.setData("text", toCopy);
}
}
它有效吗？
要检查它是否有效，您可以将包含的 Excel 工作簿拖放到脚本上，并查看新的命名范围。要检查脚本运行前后的命名范围，请按照下面的屏幕截图操作
总结
它可能看起来没有做多少事情。但是，就像业务中的许多脚本一样，它节省了很多时间，因此也节省了很多钱。像这样的 Exsead 脚本的另一个优点是，它们提高了业务工作流程中的数据完整性，这对于工作流程的效率至关重要。在脚本中简单地添加 GUI 将其从“神奇的小部件”变成了最终用户可以使用和理解的真实应用程序。