About the Demo
The Expense Reimbursement demo illustrates
the use of two separate SoftArtisans ExcelWriter
templates to accomplish both the viewing of
stored data and the updating of data with
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.
This demo uses a form page with a dynamically
populated data grid which allows the user
to select whether they would like to view
a spreadsheet with existing data or obtain
a template that will allow them to update
the database with new expense report data.
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 Form Page
The Expense Reimbursement form page uses
an ASP.NET data grid to iterate through and
list the expense reports that already exist
in the database.
|
ASP.NET Code:
private void PopulateDataGrid()
{
DataAccess data = null;
int employeeId = (int)Session["empID"];
string sql = String.Format("SELECT
ExpenseReportID, ExpenseRptName, DateSubmitted
FROM
ExpenseReports WHERE EmployeeId={0}
ORDER
BY DateSubmitted", employeeId.ToString());
try
{
data = new
DataAccess();
GridExpenseReports.DataSource
= data.DoSelectQuery(sql, "ExpenseReports");
GridExpenseReports.DataBind();
}
finally
{
if(data!=null)
data.Dispose();
data = null;
}
}
protected void NewReportBtn_Click(object
sender, EventArgs e)
{
GenerateBlankReport();
}
private void GenerateBlankReport()
{
int employeeID = (int)Session["empID"];
string name = Session["empFirstName"].ToString()
+ " " +
Session["empLastName"].ToString();
string templatepath =
Page.MapPath(ConfigurationSettings.AppSettings["ExpenseReportTemplateBlank"].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("EmployeeInfo",
600, oDS);
xlt.set_DataSource("EmployeeName",
1, name);
xlt.set_DataSource("EmployeeID",
1, employeeID);
xlt.Process("NewExpenseReport.xls",
SAProcessMethod.saProcessOpenInPlace,
0);
Page.Response.Close();
}
finally
{
if(data!=null)
data.Dispose();
data = null;
if(xlt!=null)
xlt.Close();
xlt = null;
}
}
protected void GridExpenseReports_ItemClicked(object
sender, DataGridCommandEventArgs e)
{
int reportId = Convert.ToInt32(((TableCell)e.Item.Cells[1]).Text);
DateTime reportDate =
Convert.ToDateTime(((TableCell)e.Item.Cells[2]).Text);
string reportDesc = ((TableCell)e.Item.Cells[3]).Text;
PopulateExpenseReport(reportId,
reportDate, reportDesc);
}
private void PopulateExpenseReport(int
reportId, DateTime reportDate, string
reportDesc)
{
int employeeID = (int)Session["empID"];
string firstname = Session["empFirstName"].ToString();
string lastname = Session["empLastName"].ToString();
string templatepath =
Page.MapPath(ConfigurationSettings.AppSettings["ExpenseReportTemplate"].ToString());
DataAccess data = null;
SAExcelTemplateDotNet
xlt = null;
try
{
data = new
DataAccess();
DataSet expenseDetail
= data.GetExpenseReport(employeeID,
reportId);
DataSet employeeInfo
= 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("ReportDate",
1, reportDate);
xlt.set_DataSource("ReportDescription",
1, reportDesc);
xlt.set_DataSource("EmployeeInfo",
6000, employeeInfo);
xlt.set_DataSource("ExpenseDetail",
6000, expenseDetail);
xlt.Process("ExpenseReport.xls",
SAProcessMethod.saProcessOpenInPlace,
0);
Page.Response.Close();
}
finally
{
if(data!=null)
data.Dispose();
data = null;
if(xlt!=null)
xlt.Close();
xlt = null;
}
}
|
Step 2: Create Two Templates
Use Microsoft Excel to create templates that
will be used by SoftArtisans
ExcelWriter.
This demo uses two templates - one will simply
be populated with existing database information;
and the other will act as an expense report
data entry point for end-users, with the familiar
interface of an Excel workbook.
The Expense Reimbursement templates contain
simple functions, 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:
Simple Excel Functions
ExcelWriter Data Markers
| Note: |
For the purpose of this
demonstration, live cells (containing
data that will be sent back to the database)
in the Expense Reimbursement template
have been shaded gray. |
Step 3: ExcelWriter Populates the First
Excel Template With Existing Data
Upon request of the spreadsheet, ExcelWriter
is called from an ASP.NET page, which dynamically
populates one of the templates created in
Step 1.
|
ASP.NET Code:
private void PopulateExpenseReport(int
reportId, DateTime reportDate, string
reportDesc)
{
int employeeID = (int)Session["empID"];
string firstname = Session["empFirstName"].ToString();
string lastname = Session["empLastName"].ToString();
string templatepath =
Page.MapPath(ConfigurationSettings.AppSettings["ExpenseReportTemplate"].ToString());
DataAccess data = null;
SAExcelTemplateDotNet
xlt = null;
try
{
data = new
DataAccess();
DataSet expenseDetail
= data.GetExpenseReport(employeeID,
reportId);
DataSet employeeInfo
= 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("ReportDate",
1, reportDate);
xlt.set_DataSource("ReportDescription",
1, reportDesc);
xlt.set_DataSource("EmployeeInfo",
6000, employeeInfo);
xlt.set_DataSource("ExpenseDetail",
6000, expenseDetail);
xlt.Process("ExpenseReport.xls",
SAProcessMethod.saProcessOpenInPlace,
0);
Page.Response.Close();
}
finally
{
if(data!=null)
data.Dispose();
data = null;
if(xlt!=null)
xlt.Close();
xlt = null;
}
}
|
Step 4: Implement HotCell Technology -
Update a Data Source Through Client-Side Excel
In the second 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 purpose As String
Dim rptdate As String
Dim exprng As Range
Dim url As String
Dim httpstatus As Integer
Dim res As VbMsgBoxResult
res = MsgBox("You are
about to submit a new expense report
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
'--- Get the non-tabular
data
purpose = Range("ExpenseReportPurpose").Value
rptdate = Range("ExpenseReportDate").Value
Dim i As Integer
Dim rows(9) As Variant
Dim vals(6) As Variant
For i = 1 To 8
Set exprng
= Range("ExpenseRow" & i)
vals(0) =
exprng.Cells(1, 1).Value
vals(1) =
exprng.Cells(1, 2).Value
vals(2) =
exprng.Cells(1, 4).Value
vals(3) =
exprng.Cells(1, 5).Value
vals(4) =
exprng.Cells(1, 6).Value
vals(5) =
exprng.Cells(1, 7).Value
vals(6) =
exprng.Cells(1, 8).Value
rows(i - 1)
= "Row" & i & "=" & Join(vals,
",")
Next i
rows(8) = "ExpenseRptName="
& purpose
rows(9) = "DateSubmitted="
& rptdate
url = "http://thalia/OfficeWriterEmployeePortal/ExpenseReport.aspx"
On Error Resume Next
httpstatus
= HotCellRequest.SendRequest(url,
"NewExpenseReport", rows)
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 submitted your
expense report.", _
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
Set oHTTP = Nothing
End Function
|
Step 5: 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-Expense");
string rptdesc = Request.Form["ExpenseRptName"];
string rptdate = Request.Form["DateSubmitted"];
DataAccess data = null;
try
{
data = new
DataAccess();
int reportid
= data.CreateNewExpenseReport(empID,
rptdesc, Convert.ToDateTime(rptdate));
for(int i
= 0; i <= 7; i++)
{
string[]
vals = Request.Form[i].Split(',');
if(vals[0]
== String.Empty)
continue;
DateTime
expdate = Convert.ToDateTime(vals[0]);
string
description = vals[1];
double
hotelExpense = vals[2]==String.Empty
? 0.00d : Convert.ToDouble(vals[2]);
double
transportExpense = vals[3]==String.Empty
? 0.00d : Convert.ToDouble(vals[3]);
double
mealsExpense = vals[4]==String.Empty
? 0.00d : Convert.ToDouble(vals[4]);
double
entExpense = vals[5]==String.Empty
? 0.00d : Convert.ToDouble(vals[5]);
double
miscExpense = vals[6]==String.Empty
? 0.00d : Convert.ToDouble(vals[6]);
data.AddExpenseReportDetail(reportid,
expdate,
description,
hotelExpense,
transportExpense,
mealsExpense,
entExpense,
miscExpense);
}
}
finally
{
if(data!=null)
data.Dispose();
data = null;
}
}
|
End Result:
Click on the graphics to view a larger version.
The Expense Reimbursement application provides
the user with two different options:
- View Existing Expense Reports
The resulting Excel spreadsheet is dynamically
populated with data from the SQL Server
database upon request.
End-User Receives
Dynamically Populated Excel Spreadsheet
- Create New Expense Reports
The end-user navigates the familiar interface
of Microsoft Excel to update the server-side
database with current information.
End-User Receives
Template With Which To Update Data Source
|
