Performing mail merge with regions is as easy as without. Just pass a data source object containing data rows to the MailMerge.ExecuteWithRegions method. You can even use a DataSet object to execute mail merge for several regions filling each of them with the data from a separate table. Here is the list of the acceptable objects:
- DataSet. Every table in the DataSet must have a name (the DataTable.TableName property must be set).
- DataTable. The table must have its TableName property set.
- DataView. This method is useful if you retrieve data into a DataTable but then need to apply a filter or sort before the mail merge. The source table of the DataView must have its TableName property set and the document must have a mail merge region defined with name that matches DataView.Table.TableName.
- IDataReader. You can pass SqlDataReader or OleDbDataReader object into this method as a parameter because they both implement the IDataReader interface. This method accepts the second parameter (string) that specifies name of the mail merge region in the document to populate.
Example MailMergeRegions
Executes a mail merge with repeatable regions.
[C#]
public void ExecuteWithRegionsDataTable()
{
Document doc = new Document(MyDir + "MailMerge.ExecuteWithRegions.doc");
int orderId = 10444;
// Perform several mail merge operations populating only part of the document each time.
// Use DataTable as a data source.
DataTable orderTable = GetTestOrder(orderId);
// The table name property should be set to match the name of the region defined in the document.
orderTable.TableName = "Order";
doc.MailMerge.ExecuteWithRegions(orderTable);
// Instead of using DataTable you can create a DataView for custom sort or filter and then mail merge.
DataView orderDetailsView = new DataView(GetTestOrderDetails(orderId));
orderDetailsView.Sort = "ExtendedPrice DESC";
doc.MailMerge.ExecuteWithRegions(orderDetailsView);
doc.Save(MyDir + "MailMerge.ExecuteWithRegionsDataTable Out.doc");
}
private static DataTable GetTestOrder(int orderId)
{
DataTable table = ExecuteDataTable(string.Format(
"SELECT * FROM AsposeWordOrders WHERE OrderId = {0}", orderId));
table.TableName = "Orders";
return table;
}
private static DataTable GetTestOrderDetails(int orderId)
{
DataTable table = ExecuteDataTable(string.Format(
"SELECT * FROM AsposeWordOrderDetails WHERE OrderId = {0} ORDER BY ProductID", orderId));
table.TableName = "OrderDetails";
return table;
}
/// <summary>
/// Utility function that creates a connection, command,
/// executes the command and return the result in a DataTable.
/// </summary>
private static DataTable ExecuteDataTable(string commandText)
{
// Open the database connection.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
DatabaseDir + "Northwind.mdb";
OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
// Create and execute a command.
OleDbCommand cmd = new OleDbCommand(commandText, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable table = new DataTable();
da.Fill(table);
// Close the database.
conn.Close();
return table;
}
[Visual Basic]
Public Sub ExecuteWithRegionsDataTable()
Dim doc As Document = New Document(MyDir & "MailMerge.ExecuteWithRegions.doc")
Dim orderId As Integer = 10444
' Perform several mail merge operations populating only part of the document each time.
' Use DataTable as a data source.
Dim orderTable As DataTable = GetTestOrder(orderId)
' The table name property should be set to match the name of the region defined in the document.
orderTable.TableName = "Order"
doc.MailMerge.ExecuteWithRegions(orderTable)
' Instead of using DataTable you can create a DataView for custom sort or filter and then mail merge.
Dim orderDetailsView As DataView = New DataView(GetTestOrderDetails(orderId))
orderDetailsView.Sort = "ExtendedPrice DESC"
doc.MailMerge.ExecuteWithRegions(orderDetailsView)
doc.Save(MyDir & "MailMerge.ExecuteWithRegionsDataTable Out.doc")
End Sub
Private Shared Function GetTestOrder(ByVal orderId As Integer) As DataTable
Dim table As DataTable = ExecuteDataTable(String.Format("SELECT * FROM AsposeWordOrders WHERE OrderId = {0}", orderId))
table.TableName = "Orders"
Return table
End Function
Private Shared Function GetTestOrderDetails(ByVal orderId As Integer) As DataTable
Dim table As DataTable = ExecuteDataTable(String.Format("SELECT * FROM AsposeWordOrderDetails WHERE OrderId = {0} ORDER BY ProductID", orderId))
table.TableName = "OrderDetails"
Return table
End Function
''' <summary>
''' Utility function that creates a connection, command,
''' executes the command and return the result in a DataTable.
''' </summary>
Private Shared Function ExecuteDataTable(ByVal commandText As String) As DataTable
' Open the database connection.
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseDir & "Northwind.mdb"
Dim conn As OleDbConnection = New OleDbConnection(connString)
conn.Open()
' Create and execute a command.
Dim cmd As OleDbCommand = New OleDbCommand(commandText, conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim table As DataTable = New DataTable()
da.Fill(table)
' Close the database.
conn.Close()
Return table
End Function
[Java]
public void ExecuteWithRegionsDataTable() throws Exception
{
Document doc = new Document(getMyDir() + "MailMerge.ExecuteWithRegions.doc");
int orderId = 10444;
// Perform several mail merge operations populating only part of the document each time.
ResultSet orderTable = GetTestOrder(orderId);
doc.getMailMerge().executeWithRegions("Orders", orderTable);
ResultSet orderDetailsTable = GetTestOrderDetails(orderId, "ExtendedPrice DESC");
doc.getMailMerge().executeWithRegions("OrderDetails", orderDetailsTable);
doc.save(getMyDir() + "MailMerge.ExecuteWithRegionsStringResultSet Out.doc");
}
private static ResultSet GetTestOrder(int orderId) throws Exception
{
return ExecuteDataTable(String.format(
"SELECT * FROM AsposeWordOrders WHERE OrderId = %d", orderId));
}
private static ResultSet GetTestOrderDetails(int orderId, String sort) throws Exception
{
String query = String.format(
"SELECT * FROM AsposeWordOrderDetails WHERE OrderId = %d ORDER BY ProductID", orderId);
if (sort != null && !"".equals(sort))
{
query = String.format(
"SELECT * FROM AsposeWordOrderDetails WHERE OrderId = %d ORDER BY %s", orderId, sort);
}
return ExecuteDataTable(query);
}
/// <summary>
/// Utility function that creates a connection, command,
/// executes the command and return the result in a ResultSet.
/// </summary>
private static ResultSet ExecuteDataTable(String commandText) throws Exception
{
// Open DSN-less DB connection.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String connString = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};" +
"DBQ=" + getDatabaseDir() + "Northwind.mdb;UID=Admin";
Connection conn = DriverManager.getConnection(connString);
// Get the data.
Statement mStatement = conn.createStatement();
return mStatement.executeQuery(commandText);
}