Aspose.Words

How-to: Execute Mail Merge with Regions

Performing mail merge with regions is as easy as one without regions. Just pass a data source object containing data rows to the MailMerge.ExecuteWithRegions method. You can even use a DataSet object to execute a 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 DataTable.TableName property set.

·          IMailMergeDataSource. You can pass any object to this method that implements the IMailMergeDataSource interface. This allows you to merge data into mail merge regions from custom data sources such as business objects, hashtables or lists.

Merging Data from a ResultSet

In Aspose.Words a ResultSet object produced from a database query is passed to the mail merge engine by first wrapping it in a new instance of the DataTable class. The DataTable.#Ctor(ResultSet, String) constructor accepts a ResultSet object and also the name of the table as a string.

Each ResultSet must be wrapped in a new instance of a DataTable object in order to be merged. The DataTable class provides the bare minimum functionality required for nested mail merge when this type of data source is used.

Using the DataTable class, mail merge can be executed directly by passing it to the MailMerge.ExecuteWithRegions(DataTable) method or the DataTable can be added to a DataSet along with other DataTable objects which how nested mail merge is achieved. More details about the DataSet class are covered in the next sections of the documentation.

Since the DataTable is a wrapper for the ResultSet it also provides members which allow you to access the contained data.

·          The DataTable.ResultSet property is used to retrieve the contained ResultSet of the DataTable.

·          The DataTable.DataSet property is used to retrieve the parent DataSet which contains this DataTable. This will return null for a DataTable which does not belong to a DataSet.

·          The DataTable.TableName property is used to retrieve the table name.

Example MailMergeRegions

Executes a mail merge with repeatable regions.

[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.

 

    // Use DataTable as a data source.

    // The table name property should be set to match the name of the region defined in the document.

    com.aspose.words.DataTable orderTable = getTestOrder(orderId);

    doc.getMailMerge().executeWithRegions(orderTable);

 

    com.aspose.words.DataTable orderDetailsTable = getTestOrderDetails(orderId, "ExtendedPrice DESC");

    doc.getMailMerge().executeWithRegions(orderDetailsTable);

 

    doc.save(getMyDir() + "MailMerge.ExecuteWithRegionsDataTable Out.doc");

}

 

private static com.aspose.words.DataTable getTestOrder(int orderId) throws Exception

{

    java.sql.ResultSet resultSet = executeDataTable(java.text.MessageFormat.format(

            "SELECT * FROM AsposeWordOrders WHERE OrderId = {0}", Integer.toString(orderId)));

 

    return new com.aspose.words.DataTable(resultSet, "Orders");

}

 

private static com.aspose.words.DataTable getTestOrderDetails(int orderId, String orderBy) throws Exception

{

    StringBuilder builder = new StringBuilder();

 

    builder.append(java.text.MessageFormat.format(

            "SELECT * FROM AsposeWordOrderDetails WHERE OrderId = {0}", Integer.toString(orderId)));

 

    if ((orderBy != null) && (orderBy.length() > 0))

    {

        builder.append(" ORDER BY ");

        builder.append(orderBy);

    }

 

    java.sql.ResultSet resultSet = executeDataTable(builder.toString());

    return new com.aspose.words.DataTable(resultSet, "OrderDetails");

}

 

/**

* Utility function that creates a connection, command,

* executes the command and return the result in a DataTable.

*/

private static java.sql.ResultSet executeDataTable(String commandText) throws Exception

{

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");// Loads the driver

 

    // Open the database connection.

    String connString = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};" +

            "DBQ=" + getDatabaseDir() + "Northwind.mdb" + ";UID=Admin";

 

    // From Wikipedia: The Sun driver has a known issue with character encoding and Microsoft Access databases.

    // Microsoft Access may use an encoding that is not correctly translated by the driver, leading to the replacement

    // in strings of, for example, accented characters by question marks.

    //

    // In this case I have to set CP1252 for the european characters to come through in the data values.

    java.util.Properties props = new java.util.Properties();

    props.put("charSet", "Cp1252");

 

    // DSN-less DB connection.

    java.sql.Connection conn = java.sql.DriverManager.getConnection(connString, props);

 

    // Create and execute a command.

    java.sql.Statement statement = conn.createStatement();

    return statement.executeQuery(commandText);

}