接上篇：Vb6 Activex控件创建与使用 - 解决64位系统上读取excel2007时发生的未在本地计算机上注册microsoft.ACE.oledb.12.0的问题
因为Web Service的身份验证问题，所以还需要增强Activex控件，一个思路就是，给ActiveX控件增加一个公开的method, 暴露出来，在javascript通过调用这个方法来驱动Activex控件完成工作，待控件解析excel完毕后，将生成的xml数据存入一个属性中，然后在javascript中就可以读取这个属性，并调用web service方法了，因为我们可以将webpage放置在网站内部，也就是说只有通过授权的用户才能看到此webpage，这样，就可以绕过web service的验证了。
改造后的控件截图如下, 上面白色区域为AcitveX控件，其余灰色区域及开始处理按钮都处于webpage里。
步骤如下：
1.给ActiveX增加一个公开的method例如StartToReadFromExcel，由该method调用下面的代码 (把上一篇中start按钮删除，把里面的代码转移到ReadExcel方法中去.)
'start to parse excel file
Private Sub ReadExcel()
Dim strConn As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Dim extFileName As String
Dim arr_FileName() As String
Dim lenArrFileName As Integer
Dim uploadSheetName As String
Dim stream As New stream
Dim strXML
Dim oHTTP As XMLHTTP
Dim strURL As String
Dim strPost As String
Dim strResult As String
Dim xmlDoc As MSXML2.DOMDocument60
If txtFileName.Text = "" Then
MsgBox "请您选择一个Excel文件!", vbOKOnly, "消息"
Else
'get sheet name that need to deal
uploadSheetName = txtSheet.Text
If uploadSheetName = "" Then
MsgBox "请您输入商机信息所在的Sheet名称!", vbOKOnly, "消息"
Else
On Error Resume Next
'get excel extion name xls or xlsx
arr_FileName() = Split(txtFileName.Text, ".")
extFileName = arr_FileName(UBound(arr_FileName))
'create oledb connstring for xls or xslt
If LCase(extFileName) = "xlsx" Then
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileName.Text & ";Extended Properties='Excel 12.0;HDR=YES'"
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtFileName.Text & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
End If
'open recordset
Set conn = New ADODB.Connection
conn.Open strConn
strSql = "select * from [" & uploadSheetName & "$]"
conn.Execute strSql
Set rs = New ADODB.Recordset
rs.Open strSql, conn, adOpenStatic, adLockOptimistic
If rs.RecordCount <= 0 Then
MsgBox "该Sheet中没有数据，请检查您输入的Sheet名称是否正确!", vbOKOnly, "消息"
Else
'save recordset to stream and xml string
rs.Save stream, PersistFormatEnum.adPersistXML
stream.Flush
stream.Position = 0
strXML = stream.ReadText(stream.Size)
m_BatchBOXml = strXML '把解析后的数据存入一个公开的属性中去.
m_RecordCount = rs.RecordCount
m_SheetName = uploadSheetName
m_ExcelName = txtFileName.Text
Set rs = Nothing
End If
End If
End If
End Sub
2.重新打包cab
3.在webpage中使用ScriptManager, 注册Web service引用.
<asp:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<asp:ServiceReference Path="../WebService/WebServiceBatchUploadBO.asmx" />
</Services>
</asp:ScriptManager>
4.使用javascript驱动activex和web service.
<script type="text/javascript">
function ajaxCall()
{
document.getElementById("BatchUploadExcel").StartToReadFromExcel(); //调用Activex公开的method解析excel中的数据.
var data = document.getElementById("BatchUploadExcel").BatchBOXml; //Activex解析完毕后, 读取公开的属性, 获取解析后的xml数据.
if (data != "0") {
oSPAN.innerHTML = "成功读取" + document.getElementById("BatchUploadExcel").ExcelName + "(" + document.getElementById("BatchUploadExcel").SheetName + ")" + "中" + document.getElementById("BatchUploadExcel").RecordCount + "条数据! 正在调用web service处理，请稍候...";
WebServiceBatchUploadBO.BatchUploadBusinessOpportunityFromXml(data, onSayHelloSucceeded); //调用web service的方法处理数据
}
}
function onSayHelloSucceeded(result) {
alert(result); //异步web service执行完毕返回结果信息.
}
</script>
5.全部的webpage代码如下:
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<script type="text/javascript">
function ajaxCall()
{
document.getElementById("BatchUploadExcel").StartToReadFromExcel(); //调用Activex公开的method解析excel中的数据.
var data = document.getElementById("BatchUploadExcel").BatchBOXml; //Activex解析完毕后, 读取公开的属性, 获取解析后的xml数据.
if (data != "0") {
oSPAN.innerHTML = "成功读取" + document.getElementById("BatchUploadExcel").ExcelName + "(" + document.getElementById("BatchUploadExcel").SheetName + ")" + "中" + document.getElementById("BatchUploadExcel").RecordCount + "条数据! 正在调用web service处理，请稍候...";
WebServiceBatchUploadBO.BatchUploadBusinessOpportunityFromXml(data, onSayHelloSucceeded); //调用web service的方法处理数据
}
}
function onSayHelloSucceeded(result) {
alert(result); //异步web service执行完毕返回结果信息.
}
</script>
</head>
<body >
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
<Services>
<asp:ServiceReference Path="../WebService/WebServiceBatchUploadBO.asmx" />
</Services>
</asp:ScriptManager>
<table width="98%" border="0" align="center" cellpadding="0" cellspacing="0" id="Table2">
<tr>
<td width="100%" align="left" colspan="2" >
<OBJECT ID="BatchUploadExcel" CLASSID="CLSID:E68F88F2-6375-40F3-843B-7229572882AC"
CODEBASE="BatchUploadExcelCtl.CAB#version=1,0,0,0" width="430" height="75">
</OBJECT>
</td>
</tr>
<tr>
<td width="45" align="center">
</td>
<td width="100%" align="left" >
<font color="red"><b><SPAN id="oSPAN"></SPAN></b>
<BUTTON id="btnAjaxCall" runat="server" class="button_common" onclick="ajaxCall();">开始处理</BUTTON>
</td>
</tr>
</table>
</form>
</body>
</html>
6.总结
至此, activex控件就算大功告成了!
当然, 下一步还可以研究一下如何给Activex签名.