About the Demo
The Retirement Planner demo utilizes Excel's
dynamic charting capability. In this demo,
the Excel spreadsheet is populated on the
fly with data from a SQL Server database.
Upon file open, the chart automatically updates
with the values that have been inserted by
ExcelWriter.
|
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 Retirement Planner template contains
graphics, hyperlinks, custom colors, formatting,
a chart and data markers that tell ExcelWriter
where to input data from the SQL Server database
when the document is requested:
Nested Excel Function
ExcelWriter Data Markers
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 PopulateRetirementPlanner()
{
int employeeID = (int)Session["empID"];
string name = String.Format("{0}
{1}", Session["empFirstName"], Session["empLastName"]);
string templatepath =
Page.MapPath(ConfigurationSettings.AppSettings["RetirementPlannerTemplate"].ToString());
DataAccess data = null;
SAExcelTemplateDotNet
xlt = null;
try
{
data = new
DataAccess();
xlt = new
SAExcelTemplateDotNet();
xlt.Open(templatepath);
//--- Split
the DataSet into two DataSets
DataTable[]
oTables = data.GetRetirementPlanDetails(employeeID);
DataSet dsPlanDetails
= new DataSet();
DataSet dsContribDetails
= new DataSet();
dsPlanDetails.Tables.Add(oTables[0]);
dsContribDetails.Tables.Add(oTables[1]);
xlt.set_DataSource("EmployeeName",
1, name);
xlt.set_DataSource("PlanDetails",
6000, dsPlanDetails);
xlt.set_DataSource("ContribDetails",
6000, dsContribDetails);
xlt.Process("RetirementPlanner.xls",
SAProcessMethod.saProcessOpenInPlace,
0);
Page.Response.Close();
}
finally
{
if(data!=null)
data.Dispose();
data = null;
if(xlt!=null)
xlt.Close();
xlt = null;
}
}
|
End Result:
The resulting Excel spreadsheet is dynamically
populated with data from the SQL Server database
upon request.
The chart automatically updates upon file
open with the new data, input by ExcelWriter.
The end-user can modify values in the resultant
spreadsheet and the chart will update with
each entry of new values.
Click on the graphic below to view a larger
version.
|
