Exsead – 使用 XML 配置的 Excel 数据库查询工具赋能用户
3.00/5 (1投票)
如何使用 XML 配置制作一个简单的 Excel 数据库报表引擎。
引言
在我的帮助真实企业进行真实 IT 工作的世界里，很少有时间编写完整的应用程序扩展来实现报告。管理者们面临着巨大的压力，需要让项目重回正轨，并阻止现金流失。他们今天就需要一张包含数据的电子表格 - 这篇文章展示了如何将它提供给他们。您会受到欢迎的！
这里的脚本只是一个开始。它嵌入了一个 XML 配置部分，该部分定义了一组 SQL 查询，用于在 Excel 中创建报表。我已经开始为实际项目开发更复杂的版本，这些项目的数据将来自不同的来源。使用这项技术，在几个小时内就可以生成传统应用程序编程需要数天甚至数周才能生成的报表。更重要的是，这种灵活性和对信息的直接访问正是管理者们想要的。
好了，我们不再夸大其词，让我们来看看这个脚本实际做了什么
在脚本的顶部嵌入了 XML 配置。这由 IncludeGrabber 函数读取，然后使用 Microsoft 的 DOM 解析器进行解析。在我的测试脚本中，XML 看起来像这样
// Inside the comment block below must be an XML document which
// defines the properties for the reports created from this
// script
/*
//---START-config---
<config>
<!-- There is one report section per SQL query and the results
from that query will go into a spreadsheet. There can be
as many reports as you want.
-->
<report>
<!-- Put the ado connect string in here. It must be the dbole
string appropreate for the db in question.
-->
<connect><![CDATA[
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes;
]]></connect>
<!-- Put in here some sql which will produce a single result set.
-->
<sql><![CDATA[
select * from T_User
]]></sql>
<!-- This section holds the config for the spread sheet which will
be created for and populated by the query defined for this
report.
-->
<workSheet>
<name>Users</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes;
]]></connect>
<sql><![CDATA[
select * from T_BusnUnit
]]></sql>
<workSheet>
<name>UserGroups</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes;
]]></connect>
<sql><![CDATA[
select * from T_Mchn
]]></sql>
<workSheet>
<name>Machines</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
</config>
//---END-config---
*/
每个 report 部分都会在创建的 Excel 文档中生成一个单独的工作表。在 report 中，有几个部分定义了数据库连接、要运行的 SQL、是否以及在哪里拆分报表电子表格，以及它的名称。
解析 XML 相当简单。我稍微注意了一下允许存在注释，但总的来说，它使用了一种普通的“遍历 DOM”方法。处理注释是通过查找相应的节点名称来完成的
// Simply loading the node assuming there are no comments
var reportNode=entryNode.firstChild;
// Becomes - allowing for comments
var reportNode=entryNode.firstChild;
while(reportNode.nodeName!='report')
reportNode=reportNode.nextSibling;
下面的代码块实际上是从解析后的 DOM 加载配置。然后，您可以看到 IncludeGrabber 从脚本中获取 XML。其中还有几个正则表达式替换函数，用于在用 DOM 解析器解析 XML 之前，从 XML 的封闭根节点中删除任何空白字符。
// Get the config XML and parse it into a DOM.
var xml=IncludeGrabber('config');
var dom = WScript.CreateObject("Microsoft.XMLDOM");
xml=xml.replace(/^\s*/,'');
xml=xml.replace(/\s*$/,'');
dom.loadXML(xml);
var entryNode=dom.firstChild;
var reportNode=entryNode.firstChild;
// Hunt through and comments
while(reportNode.nodeName!='report')
reportNode=reportNode.nextSibling;
// Loop over all the report
while(reportNode!=null)
{
// 'Walk' the dom to get the data for the report
var connect=reportNode.firstChild
while(connect.nodeName!='connect')
connect=connect.nextSibling;
var sql=connect.nextSibling;
while(sql.nodeName!='sql')
sql=sql.nextSibling;
var workSheet=sql.nextSibling;
while(workSheet.nodeName!='workSheet')
workSheet=workSheet.nextSibling;
var name=workSheet.firstChild;
while(name.nodeName!='name')
name=name.nextSibling;
var useAutoFilter=name.nextSibling;
while(useAutoFilter.nodeName!='useAutoFilter')
useAutoFilter=useAutoFilter.nextSibling;
var verticleSplitPoint=useAutoFilter.nextSibling;
while(verticleSplitPoint.nodeName!='verticleSplitPoint')
verticleSplitPoint=verticleSplitPoint.nextSibling;
var horrizontalSplitPoint=verticleSplitPoint.nextSibling;
while(verticleSplitPoint.nodeName!='verticleSplitPoint')
verticleSplitPoint=verticleSplitPoint.nextSibling;
// Extract the text data from the text child nodes
connect=connect.firstChild.nodeValue;
sql=sql.firstChild.nodeValue;
name=name.firstChild.nodeValue;
useAutoFilter=useAutoFilter.firstChild.nodeValue;
verticleSplitPoint=verticleSplitPoint.firstChild.nodeValue;
horrizontalSplitPoint=horrizontalSplitPoint.firstChild.nodeValue;
现在我们有了配置，我们需要运行 SQL 并将结果放入电子表格中。有一些方法可以将电子表格附加到数据源。然而，我们想要的是报表，而不是附加的电子表格。因此，我采用的方法是写入结果集并将结果数据复制到电子表格中。这在 RecSet2WorkSheet 中完成。获得良好性能的技巧是将每一行的数据存储到 Scripting.Dictionary 对象中，然后从 Dictionary 的 items 属性设置电子表格的 Row。之所以有效，是因为 items 属性返回一个 OLE SafeArray; 当 Excel range 的值设置为 SafeArray 时，整个值会一次性设置，这比一次设置一个值更有效率。
一旦工作表的所有值都设置完毕，就需要添加一些格式设置和一些自动筛选。是否执行此操作实际上是在 XML 中设置的。这是格式化代码
ws.Activate();
var rng=ws.range(ws.cells(1,1),ws.cells(1,nColumns));
// Apply Filters
if(UseAutoFilter)
{
rng.AutoFilter();
}
// Apply Colour etc
with(rng.Font)
{
Name = "Arial";
FontStyle = "Bold";
Size = 12;
}
with(rng.Interior)
{
ColorIndex = 42;
Pattern = 1;
PatternColorIndex = -4105;
}
// Autofit to make look nice
for(var i=1;i<=nColumns;++i)
{
ws.columns(i).AutoFit();
}
// Apply splits
if(VerticleSplitPoint!=0)
{
excel.ActiveWindow.SplitColumn=VerticleSplitPoint;
}
if(HorrizontalSplitPoint!=0)
{
excel.ActiveWindow.SplitRow=HorrizontalSplitPoint;
}
请注意，您必须在设置屏幕分割之前自动调整列大小，否则分割将与单元格边界不符。此外，Excel 中的某些格式设置方法/属性适用于工作表，而另一些则适用于窗口。要使其良好运行的方法是使用工作表的 Activate 方法。这将包含工作表的窗口设为活动窗口；然后您可以使用 Application 对象的 ActiveWindow 来访问那些无法直接通过工作表访问的方法/属性。
完整的脚本！
请勿忘记，此示例是为我的测试机器设置的，因此如果您想使用它，您将必须更改连接字符串和查询。
// Inside the comment block below must be an XML document which
// defines the properties for the reports created from this
// script
/*
//---START-config---
<config>
<!-- There is one report section per SQL query and the results
from that query will go into a spreadsheet. There can be
as many reports as you want.
-->
<report>
<!-- Put the ado connect string in here. It must be the dbole
string appropreate for the db in question.
-->
<connect><![CDATA[
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes;
]]></connect>
<!-- Put in here some sql which will produce a single result set.
-->
<sql><![CDATA[
select * from T_User
]]></sql>
<!-- This section holds the config for the spread sheet which will
be created for and populated by the query defined for this
report.
-->
<workSheet>
<name>Users</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes;
]]></connect>
<sql><![CDATA[
select * from T_BusnUnit
]]></sql>
<workSheet>
<name>UserGroups</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
<report>
<connect><![CDATA[
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes;
]]></connect>
<sql><![CDATA[
select * from T_Mchn
]]></sql>
<workSheet>
<name>Machines</name>
<useAutoFilter>true</useAutoFilter>
<verticleSplitPoint>1</verticleSplitPoint>
<horrizontalSplitPoint>1</horrizontalSplitPoint>
</workSheet>
</report>
</config>
//---END-config---
*/
// Here are some handy connect strings
/*
SQL Server 2000 - SQL Server Authentication
===========================================
Provider=sqloledb;Server=XP1;Database=DV;Uid=myUsername;Pwd=myPassword;
SQL Server 2000 - Windows Authentication
========================================
Provider=sqloledb;Server=XP1;Database=DV;Trusted_Connection=yes;
SQL Server 2005 - SQL Server Authentication
===========================================
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
- Or for Express -
Provider=SQLNCLI;Server=myComputerName\SQLEXPRESS;
Database=myDataBase;Uid=myUsername;Pwd=myPassword;
SQL Server 2005 - Windows Authentication
========================================
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
- Or for Express -
Provider=SQLNCLI;Server=myComputerName\SQLEXPRESS;
Database=myDataBase;Trusted_Connection=yes;
MySQL - Using MyODBC
====================
DRIVER={MySQL ODBC 3.51 Driver};SERVER=myServer;
DATABASE=myDataBase;UID=myUserName;PWD=myPassword;OPTION=3;
MS Jet (The old db behind MS Access)
====================================
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myFile.mdb;Mode=Share Exclusive;
*/
/*===============================*/
/** DO NOT EDIT BELOW THIS LINE **/
/*===============================*/
RunReports();
function RunReports()
{
// Now we have the record set, we create an instance of Excel so that;
// we can write the data to it;
var excel=WScript.CreateObject("Excel.Application");
var wb=excel.WorkBooks.Add();
// Here - as this is a demo we will make Excel visible, this makes the script;
// nicer for the user because they get to see what is happening but this has;
// the disadvantage that if the user interacts with Excel during the running;
// of the script, it can cause the script to fail.;
excel.visible=true;
// Get the config XML and parse it into a DOM
var xml=IncludeGrabber('config');
var dom = WScript.CreateObject("Microsoft.XMLDOM");
xml=xml.replace(/^\s*/,'');
xml=xml.replace(/\s*$/,'');
dom.loadXML(xml);
var entryNode=dom.firstChild;
var reportNode=entryNode.firstChild;
// Hunt through and comments
while(reportNode.nodeName!='report') reportNode=reportNode.nextSibling;
// Loop over all the report
while(reportNode!=null)
{
// 'Walk' the dom to get the data for the report
var connect=reportNode.firstChild
while(connect.nodeName!='connect') connect=connect.nextSibling;
var sql=connect.nextSibling;
while(sql.nodeName!='sql') sql=sql.nextSibling;
var workSheet=sql.nextSibling;
while(workSheet.nodeName!='workSheet') workSheet=workSheet.nextSibling;
var name=workSheet.firstChild;
while(name.nodeName!='name') name=name.nextSibling;
var useAutoFilter=name.nextSibling;
while(useAutoFilter.nodeName!='useAutoFilter')
useAutoFilter=useAutoFilter.nextSibling;
var verticleSplitPoint=useAutoFilter.nextSibling;
while(verticleSplitPoint.nodeName!='verticleSplitPoint')
verticleSplitPoint=verticleSplitPoint.nextSibling;
var horrizontalSplitPoint=verticleSplitPoint.nextSibling;
while(verticleSplitPoint.nodeName!='verticleSplitPoint')
verticleSplitPoint=verticleSplitPoint.nextSibling;
// Extract the text data from the text child nodes
connect=connect.firstChild.nodeValue;
sql=sql.firstChild.nodeValue;
name=name.firstChild.nodeValue;
useAutoFilter=useAutoFilter.firstChild.nodeValue;
verticleSplitPoint=verticleSplitPoint.firstChild.nodeValue;
horrizontalSplitPoint=horrizontalSplitPoint.firstChild.nodeValue;
RunReport
(
excel,
wb,
connect,
sql,
name,
verticleSplitPoint,
horrizontalSplitPoint,
useAutoFilter
)
// Move to the next report or to the end of the reports
reportNode=reportNode.nextSibling;
while(reportNode!=null && reportNode.nodeName!='report')
reportNode=reportNode.nextSibling;
}
}
function RunReport
(
excel,
wb,
connectString,
sqlString,
name,
VerticleSplitPoint,
HorrizontalSplitPoint,
UseAutoFilter
)
{
// Some handy definitions;
var adOpenStatic;
var adLockOptimistic;
var adCmdText;
adOpenStatic = 3;
adLockOptimistic = 3;
adCmdText = 1;
// These two variables will hold the necessary
// objects to read the csv file;
// using Microsoft//s formidable ADODB library;
var objConnection;
var objRecordSet;
objConnection = WScript.CreateObject("ADODB.Connection");
objRecordSet = WScript.CreateObject("ADODB.Recordset");
objConnection.Open(connectString);
objRecordSet.Open
(
sqlString,
objConnection,
adOpenStatic,
adLockOptimistic,
adCmdText
);
// Let us create a new workbook in excel and find the first;
// spread sheet in that workbook;
var ws;
// By default, Excel always populates new,
// blank workbooks with sheets Sheet1,;
// Sheet2 and Sheet3;
ws=wb.worksheets.Add();
ws.name=name;
var nColumns=RecSet2WorkSheet(objRecordSet,ws);
objRecordSet.Close();
ws.Activate();
var rng=ws.range(ws.cells(1,1),ws.cells(1,nColumns));
// Apply Filters
if(UseAutoFilter)
{
rng.AutoFilter();
}
// Apply Colour etc
with(rng.Font)
{
Name = "Arial";
FontStyle = "Bold";
Size = 12;
}
with(rng.Interior)
{
ColorIndex = 42;
Pattern = 1;
PatternColorIndex = -4105;
}
// Autofit to make look nice
for(var i=1;i<=nColumns;++i)
{
ws.columns(i).AutoFit();
}
// Apply splits
if(VerticleSplitPoint!=0)
{
excel.ActiveWindow.SplitColumn=VerticleSplitPoint;
}
if(HorrizontalSplitPoint!=0)
{
excel.ActiveWindow.SplitRow=HorrizontalSplitPoint;
}
}
function RecSet2WorkSheet(objRecordSet,ws)
{
// this variable will keep track of the row we are on;
var row=1;
// and this the column;
var column=1;
// and this a particular field;
var field;
var dict=WScript.CreateObject("Scripting.Dictionary");
var l;
while(!objRecordSet.eof)
{
dict.RemoveAll();
l=objRecordSet.Fields.count;
// First time around, put in the column headers;
if(row===1)
{
for(var i=0;i<l;++i)
{
field = objRecordSet.Fields.item(i);
ws.cells(row,column).value=field.name;
++column;
}
row=2;
}
column=1;
for(var i=0;i<l;++i)
{
field = objRecordSet.Fields.item(i);
dict.Add("_" + i, field);
++column;
}
ws.range(ws.cells(row,1),ws.cells(row,l)).value=dict.Items();
objRecordSet.MoveNext();
++row;
}
return l;
}
/** Function to extract from the script file
* its self block of text between
* //---START-definition---
* and
* //---END-definition---
* lines where definition is the passed string
* argument.
*/
function IncludeGrabber(definition)
{
var lines=new Array();
var fso=WScript.CreateObject('Scripting.FileSystemObject');
var ts2=fso.OpenTextFile(WScript.ScriptFullName,1,false);
while(true)
{
var l=ts2.ReadLine();
if(l=='//---START-'+definition+'---')
{
break;
}
}
while(true)
{
var l=ts2.ReadLine();
if(l=='//---END-'+definition+'---')
{
break;
}
lines.push(l+"\r\n");
}
ts2.close();
var ar2=new Array();
for(;lines.length>1;)
{
var l=lines.length;
for(var c=0;c<l;c+=2)
{
if(c+1==l)
{
ar2.push(lines[c]);
}
else
{
ar2.push(''+lines[c]+lines[c+1]);
}
}
lines=ar2;
ar2=new Array();
}
return lines[0];
}
有关类似主题的更多信息，请访问 Nerds-Central！