Free Support Forum - aspose.com

Mail merge from database into dynamic table

Hello, is it possible to create dynamically a table and put mail merge results on it.
We fill our merge fields from a database table ?

On a static way, we obtain this table:

image code description country
an image here 12422 USA

But we want a table like this :

image code country
an image here 12422 USA

in this table description is removed because it contains any value .

Any help will be appreciated !

Hi,

It’s impossible to remove a column from an existing table. You can try to build the table using DocumentBuilder prior to running mail merge; it should look if there is any non-empty fields in the description field of the data source and add the corresponding column or not based on this condition.

Thank you,

How can we add colums to a table ?

and if we use doc.MailMerge.ExecuteWithRegions(dataSet);

how can we test that the dataSet contains a column with no data ?

If some columns in your table are optional, you should implement a method that creates it from scratch using DocumentBuilder. There’s a couple of examples in the forums of how to build tables dynamically, for instance see here:

http://www.aspose.com/forums/ShowPost.aspx?PostID=19796

Don’t forget that you just need a table with the only row for further mail merge.

Since DataSet contains multiple tables, you should repeat this process (checking if a column has no data and building the table in the template in a corresponding way) for every single table.

Hi,
the first page of my table contains an image.

I’m using this event :



private void HandleMergeImageField(object sender, MergeImageFieldEventArgs e)

{

if (e.FieldName == “Photo”)

{

if(e.FieldValue!=null)

{

string fileName = (string)e.FieldValue;

e.ImageFileName = Server.MapPath("")+"\Image\Gauthier\"+fileName;

//

DocumentBuilder builder = new DocumentBuilder(e.Document);

System.Drawing.Image img = System.Drawing.Image.FromFile(e.ImageFileName);

builder.MoveToMergeField(e.FieldName);

builder.InsertImage(img, 80, 80);

//

}

}

}




And drawing table by document builder.

But the is not drawed at the correct position.

How can we move the table to the correct position on document ?

We need help !!

Hi,

Basically, you can place a bookmark to specify where you want to insert a table and move there first using DocumentBuilder.MoveToBookmark. Please indicate the desired position in your document template and attach it to let us take a look.

Hi,
I send you our template:
Here is our code :



private void HandlerGauthier(object sender, MergeFieldEventArgs e)

//DocumentBuilder builder = new DocumentBuilder(doc);

{

DocumentBuilder builder = new DocumentBuilder(e.Document);

builder.MoveToBookmark(“TableGauthier”);

builder.StartTable();


if ((e.FieldName == “FirstName”) && (e.FieldValue.ToString() != String.Empty))

{

builder.CellFormat.Shading.BackgroundPatternColor = Color.Blue;

builder.CellFormat.Width = 100;

builder.InsertCell();

builder.EndRow();

builder.MoveToMergeField(e.FieldName);

builder.Font.Name = “Arial”;

builder.Font.Size = 12;

builder.Font.Bold = true;

builder.Write(“FirstName : “);

builder.Font.Bold = false;

builder.Write(e.FieldValue.ToString());

}

if (e.FieldName == “LastName” && (e.FieldValue.ToString() != String.Empty))

{

builder.CellFormat.Shading.BackgroundPatternColor = Color.Blue;

builder.CellFormat.Width = 100;

builder.InsertCell();

builder.EndRow();

builder.MoveToMergeField(e.FieldName);

builder.Font.Name = “Arial”;

builder.Font.Size = 12;

builder.Font.Bold = true;

builder.Write(“Last Name : “);

builder.Font.Bold = false;

builder.Write(e.FieldValue.ToString());

}
}


And we use HandlerGauthier like this :


doc.MailMerge.MergeImageField += new MergeImageFieldEventHandler(HandleMergeImageField);

doc.MailMerge.MergeField += new MergeFieldEventHandler(HandlerGauthier);


And to insert image by mailmerge we define HandleMergeImageField like this :


private void HandleMergeImageField(object sender, MergeImageFieldEventArgs e)

{

if (e.FieldName == “Photo”)

{

if(e.FieldValue!=null)

{

string fileName = (string)e.FieldValue;

e.ImageFileName = Server.MapPath(””)+”\Image\Gauthier\”+fileName;

DocumentBuilder builder = new DocumentBuilder(e.Document);

System.Drawing.Image img = System.Drawing.Image.FromFile(e.ImageFileName);

builder.MoveToMergeField(e.FieldName);

builder.InsertImage(img, 50, 50);

}

}

}


But we obtain the error : Cannot move the cursor while building a table .

How can we Use draw table and add column only if e.FieldName is not null and insert a image at the Column name Photo ?






and

As I said above, it seems that in your case it’s easier to build the table (the table row in the template) completely from scratch inserting there the merge fields prior to running mail merge. Can you check your data source object that you pass to ExecuteWithRegions preliminarily? It could look like in the following pseudocode:

DocumentBuilder builder = new DocumentBuilder(doc);
builder.MoveToBookmark(“TableGauthier”);
builder.StartTable();
builder.InsertCell();
builder.InsertField(@“MERGEFIELD Image:Photo * MERGEFORMAT”, “”);

if (data source has at least one value for FirstName)
{
Insert the new cell and put there FirstName MERGEFIELD
}

if (data source has at least one value for LastName)
{
Insert the new cell and put there LastName MERGEFIELD
}

builder.InsertCell();
builder.InsertField(@“MERGEFIELD Address * MERGEFORMAT”, “”);
builder.EndRow();
builder.EndTable();



Thank you,
if we add image in that way we’ve the error : Cannot load image from field ‘Photo’. The field contains data in unsupported format. Amy.jpg

Note that we have stocked images on a directory as jpg files ( Amy.jpg) and in a table column we’ve Amy.jpg as string on Photo field.

On this way, all records of our table will be on one table.

But we want have to each record on a table.

Thanks !!!

The above example simply shows how to prepare a template programmatically for further mail merge. Please attach the resulting source code that causes the error, I will take a look, try to make some necessary corrections and send it back to you.

Hello
Here is our code.

Our database table and report

Our template

Your task seems to be very custom so use of regular mail merge is probably not the best way. I suppose to just iterate through the rows of your data source and insert new table into the document for each row as required. Attached is a working project that performs it. Please use it as an example and add all necessary table formatting to the code yourself. I have also applied some appropriate changes to your document in order to use DocumentBuilder instead of mail merge.

Let me know whether it is suitable for you.