About the Demo
The Update Employee Personal Information
demo illustrates SoftArtisans ExcelWriter's
HotCell Technology. HotCell Technology allows
the end-user to update the server data source,
in this case a SQL Server database, through
the familiar interface of Microsoft Excel.
In this demonstration, VBA is used to send
data from the client-side Excel spreadsheet
to an ASP.NET page that delivers it to the
SQL Server data source.
|
Server-side code is marked in gray
Courier font.
| Note: |
This
explanation contains only portions
of the code necessary to run this
application.
To view the entire ASP.NET page,
use the corresponding "Source"
link on the demo index page. |
|
How It Works
Step 1: Create a Template
Use Microsoft Excel to create a template
that will be used by SoftArtisans
ExcelWriter.
The Update Employee Personal Information
template contains graphics, hyperlinks, custom
colors, formatting, a command button and data
markers that tell ExcelWriter where to input
data from the SQL Server database when the
document is requested:
ExcelWriter Data Markers
| Note: |
For the purpose of this
demonstration, live cells (containing
data that will be sent back to the database)
in the Update Employee Personal Information
template have been shaded gray. |
Step 2: ExcelWriter Populates the Excel
Template
Upon request of the spreadsheet, ExcelWriter
is called from an ASP.NET page, which dynamically
populates the template created in Step 1.
|
ASP.NET Code:
private void PopulateEmployeeInfo()
{
int employeeID = (int)Session["empID"];
string firstname = Session["empFirstName"].ToString();
string lastname = Session["empLastName"].ToString();
string templatepath =
Page.MapPath(ConfigurationSettings.AppSettings["PersonalInfoTemplate"].ToString());
DataAccess data = null;
SAExcelTemplateDotNet
xlt = null;
try
{
data = new
DataAccess();
DataSet oDS
= data.GetEmployeeInfo(employeeID);
xlt = new
SAExcelTemplateDotNet();
xlt.Open(templatepath);
xlt.set_DataSource("EmployeeName",
1, firstname + " " + lastname);
xlt.set_DataSource("EmployeeID",
1, employeeID.ToString());
xlt.set_DataSource("EmployeeInfo",
6000, oDS);
xlt.Process("UpdatePersonalInfo.xls",
SAProcessMethod.saProcessOpenInPlace,
0);
}
catch(Exception ex)
{
System.Diagnostics.Debug.Write("INPAGEEX:
" + ex.ToString());
Page.Response.Write(ex.ToString());
}
finally
{
if(data!=null)
data.Dispose();
data = null;
if(xlt!=null)
xlt.Close();
xlt = null;
}
}
|
Step 3: Implement HotCell Technology -
Update a Data Source Through Client-Side Excel
In the Excel template, behind the UpdateDatabase
command button, the Click() function collects
the values that have been entered into the
live cells:
|
VBA Code:
Private Sub UpdateDatabase_Click()
Dim res As VbMsgBoxResult
res = MsgBox("You are about to send
a database update to the server. Are
you sure you " & _
"want to update
the database with the values you have
entered?", _
vbYesNo, "Proceed
with Database Update?")
If res <> vbYes Then
Exit Sub
End If
Dim oD As Object
Dim values(7) As Variant
Dim url As String
Dim httpstatus As Integer
values(0) = "Address=" & Range("Address").value
values(1) = "City=" & Range("City").value
values(2) = "State=" & Range("State").value
values(3) = "PostalCode=" & Range("PostalCode").value
values(4) = "HomePhone=" & Range("HomePhone").value
values(5) = "MobilePhone=" & Range("MobilePhone").value
values(6) = "EmergContactName=" &
Range("EmergencyContactName").value
values(7) = "EmergContactNum=" &
Range("EmergencyContactNum").value
url = "http://localhost/OfficeWriterEmployeePortal/UpdatePersonalInfo.aspx"
On Error Resume Next
httpstatus = HotCellRequest.SendRequest(url,
"UpdatePersonalInfo", values)
If Err.Number <>
0 Then
MsgBox "Error
sending HotCell request. Could not
contact server database update page."
& _
vbCrLf
& "Details: " & Err.Description,
_
vbCritical,
"HotCell Request Failed"
Exit Sub
End If
On Error GoTo 0
If httpstatus = 200 Then
UpdateDatabase.Enabled
= False
UpdateDatabase.Caption
= "Update Successful"
MsgBox "You have successfully
updated your personal information.",
vbOKOnly, "HotCell Update Succeeded"
Else
MsgBox "The HotCell database
update did not succeed. The server-side
database update " & _
"page returned
an error. The server returned status
code: " & httpstatus, _
vbCritical,
"HotCell Update Error"
End If
End Sub
|
The SendRequest function, called by the Click()
function above, uses HTTP to send the HotCell
values to an ASP.NET page:
|
VBA Code:
Public Function SendRequest(url
As String, requestname As String,
pairs As Variant) As Integer
Dim strReq As String
Dim oHTTP As Object
'--- The XMLHTTP object needs form
values to be sent in this form:
'--- "name1=value1&name2=value2&name3=value3",
etc
'--- we're forming a request of that
format here
strReq = Join(pairs, "&")
'--- Use the XMLHTTP object to do
the HTTP request
On Error Resume Next
Set oHTTP = CreateObject("Msxml2.XMLHTTP.3.0")
If Err.Number <> 0 Then
Err.Raise
Err.Number, "HotCellRequest", _
"Could
not create XMLHTTP object which is
required by HotCells."
Exit Function
End If
On Error GoTo 0
On Error Resume Next
oHTTP.Open "POST", url,
False
If Err.Number <> 0 Then
Err.Raise
Err.Number, "HotCellRequest", _
"HotCell
failed to connect to " & url &
" " & _
Err.Description
Exit Function
End If
On Error GoTo 0
'--- We need to set this whenever
submitting form data
oHTTP.SetRequestHeader "Content-Type",
"application/x-www-form-urlencoded"
oHTTP.SetRequestHeader "X-SaHotCellRequest",
requestname
'--- Send the request
On Error Resume Next
oHTTP.Send CStr(strReq)
If Err.Number <> 0 Then
Err.Raise
Err.Number, "HotCellRequest", _
"HotCell
failed when sending data to " &
url & " " & _
Err.Description
Exit Function
End If
On Error GoTo 0
SendRequest = oHTTP.Status
'--- Clean up
Set oHTTP = Nothing
End Function
|
Step 4: ASP.NET Page Updates the SQL Server
Data Source
|
ASP.NET Code:
private void DoHotCellUpdate()
{
int empID = (int)Session["empID"];
System.Diagnostics.Debug.Write("DoHotCellUpdate");
//--- Copy posted form
values to arrays
string[] names = {"@EmployeeId",
"@Address", "@City", "@PostalCode",
"@State", "@HomePhone",
"@MobilePhone",
"@EmergContactName", "@EmergContactNum"};
object[] values = {empID,
Request.Form["Address"], Request.Form["City"],
Request.Form["PostalCode"],
Request.Form["State"],
Request.Form["HomePhone"],
Request.Form["MobilePhone"],
Request.Form["EmergContactName"],
Request.Form["EmergContactNum"]};
DataAccess data = null;
try
{
data = new
DataAccess();
int ra = data.DoStoredProcedureNonQuery("UpdatePersonalInfo",
names, values);
System.Diagnostics.Debug.Write("RA:
" + ra.ToString());
}
finally
{
if(data!=null)
data.Dispose();
data = null;
}
}
|
End Result:
The resulting Excel spreadsheet is dynamically
populated with data from the SQL Server database
upon request.
It also allows the end-user to use the familiar
interface of Microsoft Excel to update the
server-side database with current information.
Click on the graphic below to view a larger
version.
|
