.
...
Code
...
Sample:
...
Importing
...
from
...
a
...
Database
...
[
...
...
]
...
[
...
...
]
| Excerpt |
|---|
The sample above generates a spreadsheet from the template using ADO.NET objects as the data source. |
The template contains the following data markers:
| Code Block | ||
|---|---|---|
| ||
%%=Employee.Name %%=Employee.Title %%=Orders.OrderID %%=Orders.Customer %%=Orders.OrderDate %%=Orders.OrderTotal {newcode} |
These
...
data
...
markers
...
will
...
be
...
populated
...
in
...
the
...
ASP.NET
...
code
...
by
...
two
...
database
...
data
...
sources.
...
The
...
data
...
source
...
for
...
the
...
%%=Employee.*
...
data markers
...
is
...
an
...
ADO.NET
...
DataTable,
...
and
...
the
...
data
...
source
...
for
...
the
...
%%=Orders.*
...
data
...
markers
...
is
...
an
...
ADO.NET
...
DataSet.
...
To
...
set
...
a
...
template
...
data
...
source
...
to
...
a
...
DataSet
...
or
...
DataTable
...
using
...
OleDB,
...
import
...
the
...
System.Data
...
and
...
System.Data.OleDB
...
namespaces
...
to
...
the
...
ASP.NET
...
page.
...
For
...
example:
...
| Code Block |
|---|
using System.Data;
using System.Data.OleDb;
{csharp}
{vbnet}
|
| Code Block | ||
|---|---|---|
| ||
Imports System.Data
Imports System.Data.OleDb
{vbnet}
h2. Setting a Data Source to a DataSet with OleDB
To create a DataSet to use as a data source:
# Create an OleDbConnection object to open a database connection.
# Create a SQL query to get data from the database.
# Create an OleDbDataAdapter that will execute the SQL command at the data source and fill the DataSet.
# Create a DataSet and use the OleDbDataAdapter to fill it with the data retrieved from the database.
For example, in Databasebind.aspx, the following code creates and fills OrdersDs, the DataSet which will be used as the data source for the set of %%=Orders.* data markers.
{csharp:2}
|
Setting a Data Source to a DataSet with OleDB
To create a DataSet to use as a data source:
Create an OleDbConnection object to open a database connection.
Create a SQL query to get data from the database.
Create an OleDbDataAdapter that will execute the SQL command at the data source and fill the DataSet.
Create a DataSet and use the OleDbDataAdapter to fill it with the data retrieved from the database.
For example, in Databasebind.aspx, the following code creates and fills OrdersDs, the DataSet which will be used as the data source for the set of %%=Orders.* data markers.
| Code Block |
|---|
OleDbConnection Conn = new OleDbConnection();
DataSet OrdersDs = null;
try
{
Conn.ConnectionString = Application["connstring"].ToString();
//--- SQL Query for orders
string OrdersSQL = "SELECT Orders.OrderID, " +
"Customers.CompanyName As Customer, " +
"Orders.OrderDate, " +
"([Order Details].UnitPrice * " +
"[Order Details].Quantity) As [OrderTotal] " +
"FROM Orders, [Order Details], Customers " +
"WHERE Orders.OrderID=[Order Details].OrderID AND " +
"Orders.CustomerID=Customers.CustomerID AND Orders.EmployeeID=?";
OleDbCommand CmdOrders = new OleDbCommand(OrdersSQL, Conn);
CmdOrders.Parameters.Add("@EmployeeID", EmployeeId);
OleDbDataAdapter AdptSales = new OleDbDataAdapter(CmdOrders);
OrdersDs = new DataSet();
AdptSales.Fill(OrdersDs, "Orders");
}
{csharp}{vbnet}
Dim |
| Code Block | ||
|---|---|---|
| ||
Dim Conn As New OleDbConnection() Dim OrdersDs As DataSet = Nothing Try Conn.ConnectionString = Application("connstring").ToString() '--- SQL Query for orders Dim OrdersSQL As String = "SELECT Orders.OrderID, " & _ "Customers.CompanyName As Customer, " & _ "Orders.OrderDate, " & _ "([Order Details].UnitPrice * " & _ "[Order Details].Quantity) As [OrderTotal] " & _ "FROM Orders, [Order Details], Customers " & _ "WHERE Orders.OrderID=[Order Details].OrderID AND " & _ "Orders.CustomerID=Customers.CustomerID AND Orders.EmployeeID=?" Dim CmdOrders As New OleDbCommand(OrdersSQL, Conn) CmdOrders.Parameters.Add("@EmployeeID", EmployeeId) Dim AdptSales As New OleDbDataAdapter(CmdOrders) OrdersDs = New DataSet() AdptSales.Fill(OrdersDs, "Orders") End Try {vbnet} |
To
...
bind
...
a
...
DataSet
...
to
...
a
...
template
...
data
...
marker,
...
call
...
[BindData|ExcelTemplate.BindData]
...
using
...
the
...
following
...
signature:
...
| Code Block |
|---|
ExcelTemplate.BindData(DataSet aData, String aDataMarkerName, DataBindingProperties aDataBindingProperties){code} |
This
...
signature
...
takes
...
a
...
DataSet,
...
the
...
name
...
of
...
the
...
data
...
marker
...
to
...
which
...
the
...
data
...
source
...
should
...
bind,
...
and
...
a
...
collection
...
of
...
binding
...
property
...
values.
...
In
...
Databasebind.aspx,
...
this
...
BindData
...
call
...
binds
...
the
...
DataSet
...
OrdersDs
...
to
...
the
...
data
...
markers
...
*%%=Orders.OrderID*,
...
*%%=Orders.Customer*,
...
*%%=Orders.OrderDate*,
...
and
...
*%%=Orders.OrderTotal*
...
in
...
the
...
template:
...
| Code Block |
|---|
xlt.BindData(OrdersDs, "Orders", xlt.CreateDataBindingProperties()); {csharp} {vbnet} xlt |
| Code Block | ||
|---|---|---|
| ||
xlt.BindData(OrdersDs, _ "Orders", _ xlt.CreateDataBindingProperties()) {vbnet} h2. Setting a Data Source to a DataTable with OleDB To create a DataTable to use as a data source: # Create an OleDbConnection object to open a database connection. # Create a SQL query to get data from the database. # Create an OleDbDataAdapter that will execute the SQL command at the data source and fill the DataTable. # Create a DataTable and use the OleDbDataAdapter to fill it with the data retrieved from the database. For example, in Databasebind.aspx, the following code creates and fills EmployeeDt, the DataTable which will be used as the data source for the set of %%=Employee.* data markers. {csharp:12} |
Setting a Data Source to a DataTable with OleDB
To create a DataTable to use as a data source:
Create an OleDbConnection object to open a database connection.
Create a SQL query to get data from the database.
Create an OleDbDataAdapter that will execute the SQL command at the data source and fill the DataTable.
Create a DataTable and use the OleDbDataAdapter to fill it with the data retrieved from the database.
For example, in Databasebind.aspx, the following code creates and fills EmployeeDt, the DataTable which will be used as the data source for the set of %%=Employee.* data markers.
| Code Block |
|---|
OleDbConnection Conn = new OleDbConnection();
DataTable EmployeeDt = null;
try
{
Conn.ConnectionString = Application["connstring"].ToString();
//--- SQL Query for employee information
string EmployeeSQL = "SELECT FirstName + ' ' + LastName As Name, Title " +
"FROM Employees WHERE EmployeeID=?";
OleDbCommand CmdEmployee = new OleDbCommand(EmployeeSQL, Conn);
CmdEmployee.Parameters.Add("@EmployeeID", EmployeeId);
OleDbDataAdapter AdptEmployee = new OleDbDataAdapter(CmdEmployee);
EmployeeDt = new DataTable();
AdptEmployee.Fill(EmployeeDt);
}
{csharp}{vbnet}
Dim |
| Code Block | ||
|---|---|---|
| ||
Dim Conn As New OleDbConnection() Dim EmployeeDt As DataTable = Nothing Try Conn.ConnectionString = Application("connstring").ToString() '--- SQL Query for employee information Dim EmployeeSQL As String = "SELECT FirstName & ' ' & LastName As Name, Title " & _ "FROM Employees WHERE EmployeeID=?" Dim CmdEmployee As New OleDbCommand(EmployeeSQL, Conn) CmdEmployee.Parameters.Add("@EmployeeID", EmployeeId) Dim AdptEmployee As New OleDbDataAdapter(CmdEmployee) EmployeeDt = New DataTable() AdptEmployee.Fill(EmployeeDt) End Try {vbnet} To bind a DataTable to a template data marker, call |
To bind a DataTable to a template data marker, call [BindData|ExcelTemplate.BindData]
...
using
...
the
...
following
...
signature:
...
| Code Block |
|---|
BindData(DataTable aData, String aDataMarkerName, DataBindingProperties aDataBindingProperties) {newcode} This signature takes a DataTable, the name of the data marker to which the data source should bind, and a collection of binding property values. In Databasebind.aspx, this BindData call binds the DataTable EmployeeDt to the data markers , aDataBindingProperties) |
This signature takes a DataTable, the name of the data marker to which the data source should bind, and a collection of binding property values. In Databasebind.aspx, this BindData call binds the DataTable EmployeeDt to the data markers *%%=Employee.Name*
...
and
...
*%%=Employee.Title*
...
in
...
the
...
template:
...
| Code Block |
|---|
xlt.BindData(EmployeeDt, "Employee", xlt.CreateDataBindingProperties()); {csharp} {vbnet} xlt |
| Code Block | ||
|---|---|---|
| ||
xlt.BindData(EmployeeDt, _ "Employee", _ xlt.CreateDataBindingProperties()) {vbnet} \ {scrollbar} |