Description
Lets say that we want to perform some calculations on a data set
stored in a database backend server. On the client side we can have the Excel
program or the browser (or mobile browser). This example proposes a simple
solution to this with no server-side dependence on Excel.
The basic operations in almost web applications consist in
retrieving and updating data; so we use a class with two public functions:
Public Function GetValues() As ReturnValues
Public Function UpdateData( ByVal Avg_sales_deal_size As Double,
_ByVal Avg_total_number_of_deals_year As Double, ByVal Avg_profit_margin_customer As Double,
_ByVal Increase_in_margin_customer As Double, ByVal Reduction_in_price_discounted_deals As Double _) As Integer
This class is used to connect the database with the Excel
(through a webservice) or the browser/mobile browser (through the webform/mobile
webform).
The Excel clients must have installed SOAP Toolkit (in this case
I tested with SOAP Toolkit 2.0 SP2) and to use the Excel (version 9.0) file from
source zip (its recommended to save it locally and not to use it directly from
server because if you try to save you can get an error during security
permissions on server).
The spreadsheet file uses VBA macros to perform the connection
with the webservice so you enable macros when you try to open it. In order to
use the SOAP I added a reference to SOAP Type Lib:
Dim objSOAPClient As MSSOAPLib.SoapClient
Dim wsReturn As Object
Set objSOAPClient
= New MSSOAPLib.SoapClient
objSOAPClient.mssoapinit
bstrWSDLFile:=http://localhost/ROIModel/Service1.asmx?WSDL
Set wsReturn
= objSOAPClient.GetValues()
For clients without Excel we used usual webform and mobileform to
perform the same calculations as in the excel file.
Conclusion
we don't have any server-side dependence on Excel, only on
client-side; if the client has the Excel he can use it; if not, he can use the
preferred browser even from the mobile phone.