Free Support Forum - aspose.com

Mail merge with region not working

Hi all,

First I want to thank you of the Aspose documentation, it has helped me a lot.

I am new in developping using aspose.word API.

I have read the documentation precisely in section How to Execute Mail Merge with Regions.

But the mail merge didn’t work and I didn’t figure out the problem.

I want to execute the mail merge with region and generate the result document.
I have prepared the template “startTable.docx” document and I have defined the region “Orders”.

Simply I want to retreive the sender information from the database and execute the mail merge with region.

So the expected result is that the region with name “orders” will output the sender information that
was retrieved from the database.
But the document “startTableResult.docx” dosen’t output the result in place of merged field and I didn’t figure out the problem.

I am using a spring boot application, and I add the aspose.word dependency in the pom file.
This is the main class
@SpringBootApplication
public class DinnerInvitaionDemoApplication implements CommandLineRunner {

   static final String USER = "root";
   static final String PASS = "";

@Autowired
private InvitedRepository invitedRepository;

@Autowired
private SenderRepository senderRepository;

@Autowired
private OrderDetailRepository orderDetailRepository;

@Autowired
private SenderService senderService;

public static void main(String[] args) {
	SpringApplication.run(DinnerInvitaionDemoApplication.class, args);
}

@Override
public void run(String... args) throws Exception {
	

	String dataDir = "src/main/resources/templates/";
	Document document = new Document(dataDir + "TableStart.docx");
	DataTable senderTable = getSenderInformation();
	document.getMailMerge().executeWithRegions(senderTable);

	DataTable orderDetailTable = getOrderDetails();
	document.getMailMerge().executeWithRegions(orderDetailTable);
	document.save(dataDir + "TableStartResult.docx");

	System.out.println("Merge complete successfully !! ");

}

private DataTable getSenderInformation() throws Exception {
	ResultSet resultSet = executeDataTable("SELECT * FROM SENDER WHERE ID = 1");
	resultSet.next();
	// just to make sure that I have retrieved the row from the database
	System.out.println(resultSet.getString(1));
	System.out.println(resultSet.getString(2));
	System.out.println(resultSet.getString(3));
	return new DataTable(resultSet, "Orders");
}

private DataTable getOrderDetails() throws Exception {
	ResultSet resultSet = executeDataTable("SELECT * FROM ORDER_DETAIL WHERE product_id = 2");
	resultSet.next();
	// just to make sure that I have retrieved the row from the database
	System.out.println(resultSet.getString(1));
	System.out.println(resultSet.getString(2));
	return new DataTable(resultSet, "OrderDetail");
}

private ResultSet executeDataTable(String format) throws Exception {

	String connString = "jdbc:mysql://localhost:3306/dinnerdb";

	Properties props = new java.util.Properties();
	
	props.put("charSet", "Cp1252");

	Connection conn = DriverManager.getConnection(connString, USER, PASS);

// DriverManager.getConnectio
Statement statement = conn.createStatement();

	return statement.executeQuery(format);

}

}

Thanks

This the Sender model:
@Entity
public class Sender {

@Id
@GeneratedValue
private Long id;
@Column(name = "senderName")
private String senderName;
@Column(name = "firstAdress")
private String firstAdress;
@Column(name = "secondAdress")
private String secondAdress;

public Sender() {
}

public Sender(String senderName, String firstAdress, String secondAdress) {
	super();
	this.senderName = senderName;
	this.firstAdress = firstAdress;
	this.secondAdress = secondAdress;
}

public Long getId() {
	return id;
}

public void setId(Long id) {
	this.id = id;
}

public String getSenderName() {
	return senderName;
}

public void setSenderName(String senderName) {
	this.senderName = senderName;
}

public String getFirstAdress() {
	return firstAdress;
}

public void setFirstAdress(String firstAdress) {
	this.firstAdress = firstAdress;
}

public String getSecondAdress() {
	return secondAdress;
}

public void setSecondAdress(String secondAdress) {
	this.secondAdress = secondAdress;
}

@Override
public String toString() {
	return "Sender [id=" + id + ", senderName=" + senderName + ", firstAdress=" + firstAdress + ", secondAdress="
			+ secondAdress + "]";
}

}

this the orderDetail model:
@Entity
public class OrderDetail {

@Id
@GeneratedValue
private Long productId;
private String productName;
private Long quantity;
private Long unitPrice;
private Long discount;
private Long extendedPrice;

public OrderDetail() {
}

public OrderDetail(String productName, Long quantity, Long unitPrice, Long discount, Long extendedPrice) {
	super();
	this.productName = productName;
	this.quantity = quantity;
	this.unitPrice = unitPrice;
	this.discount = discount;
	this.extendedPrice = extendedPrice;
}

public Long getProductId() {
	return productId;
}

public void setProductId(Long productId) {
	this.productId = productId;
}

public String getProductName() {
	return productName;
}

public void setProductName(String productName) {
	this.productName = productName;
}

public Long getQuantity() {
	return quantity;
}

public void setQuantity(Long quantity) {
	this.quantity = quantity;
}

public Long getUnitPrice() {
	return unitPrice;
}

public void setUnitPrice(Long unitPrice) {
	this.unitPrice = unitPrice;
}

public Long getDiscount() {
	return discount;
}

public void setDiscount(Long discount) {
	this.discount = discount;
}

public Long getExtendedPrice() {
	return extendedPrice;
}

public void setExtendedPrice(Long extendedPrice) {
	this.extendedPrice = extendedPrice;
}

@Override
public String toString() {
	return "OrderDetail [productId=" + productId + ", productName=" + productName + ", quantity=" + quantity
			+ ", unitPrice=" + unitPrice + ", discount=" + discount + ", extendedPrice=" + extendedPrice + "]";
}

}

I am using spring data (JPA/Hibernate) to manage the persistence layer:

the Sender table in Mysql database name “Sender”
senderTable.PNG (6.0 KB)
The order_detail table :
order_detail.PNG (10.0 KB)
The template “StartTable.docx” and the result “StartTableResult.docx” which hasn’t output the data.
templateAndResultDoc.zip (18.1 KB)

I need help and thanks.

@abenamorgg,

Thanks for your inquiry. Please insert TableEnd:Orders field at the end of document. We have attached the modified document with this post for your kind reference. TableStart.zip (9.4 KB)

We suggest you please read following article.
Nested Mail Merge Regions

Please check the code example of mail merge with region from Aspose.Words for Java examples repository at GitHub.

@tahir.manzoor Thanks for the reply.

I did what you have told me but it didn’t work, the “Orders” region didn’t output data.

But I want that the template have two region independent not nested region.

The “orders” region will output the data from “Sender” table from database and the “OrderDetail” region will output the data from “order_detail” from database. two regions that are independent not nested.

But the problem that the region dosen’t output the data and I didn’ figure out the problem.

Thanks.
Adem

@abenamorgg,

Thanks for your inquiry. Please make sure that you are getting the data from database correctly and names of table’s column are correct. We have tested the scenario using latest version of Aspose.Words for Java 18.4 with following code example and have not found the shared issue.

Document document = new Document(MyDir + "TableStart.docx");

DataTable senderTable = getSenderInformation();
document.getMailMerge().executeWithRegions(senderTable);

DataTable orderDetailTable = getOrderDetails();
document.getMailMerge().executeWithRegions(orderDetailTable);

document.save(MyDir + "18.4.docx");

private static DataTable getSenderInformation() throws Exception {

    DataTable dataTable = new DataTable("Orders");
    dataTable.getColumns().add("id");
    dataTable.getColumns().add("senderName");
    dataTable.getColumns().add("firstAdress");
    dataTable.getColumns().add("secondAdress");
    DataRow row = dataTable.newRow();
    dataTable.getRows().add(row);

    row.set(0, "1");
    row.set(1, "Jhon");
    row.set(2, "This is first Address");
    row.set(2, "This is second Address");
    return dataTable;
}

private static DataTable getOrderDetails() throws Exception {
    DataTable dataTable = new DataTable("OrderDetail");
    dataTable.getColumns().add("productId");
    dataTable.getColumns().add("productName");

    DataRow row = dataTable.newRow();
    dataTable.getRows().add(row);

    row.set(0, "1");
    row.set(1, "Prodcut 1");

    return dataTable;
}

@tahir.manzoor thanks for the reply,

I debugged the code and I figure out that the data table “senderTable” have 0 DataRow. (I used
senderTable.getRows().getCount() = 0)
So what I have underestood:
private DataTable getSenderInformation() throws Exception {
ResultSet resultSet = executeDataTable(“SELECT * FROM SENDER WHERE ID = 1”);
resultSet.next();
// just to make sure that I have retrieved the row from the database
System.out.println(resultSet.getString(1));
System.out.println(resultSet.getString(2));
System.out.println(resultSet.getString(3));
return new DataTable(resultSet, “sender”);
}

this method shows the data of record in the database, I have successfully retrieved the data from database.

And then
String dataDir = “src/main/resources/templates/”;
Document document = new Document(dataDir + “template.docx”);
DataTable senderTable = getSenderInformation();
DataColumn firstCol = senderTable.getColumns().get(1);
System.out.println(firstCol.getColumnName());

	DataRowCollection firstRow = senderTable.getRows(); 
	System.out.println("count:  "+firstRow.getCount());
	
	document.getMailMerge().executeWithRegions(senderTable);

The dataTable contains the “sender” table column but it has no rows

senderTable.getRows().getCount()=0 

It means, I have to create the DataRow for each resultset and added it in DataTable.
It is not done automatically when I do
return new DataTable(resultSet, “sender”); ?

Thanks.
Adem

@tahir.manzoor thanks for your support.
The problem is the column name from the database is different from the template.
That’s why when I execute the mail merge operation, it dosen’t output data.

Thanks a lot.
regards
Adem

@abenamorgg,

Thanks for your feedback. It is nice to hear from you that your problem has been solved. Please feel free to ask if you have any question about Aspose.Words, we will be happy to help you.

1 Like