DocIO: Using Nested Mail Merge with Word Formula capabilities

[Denis Torresan]

Hello to all,

We have implemented a solution for generating DOCX report with nested mail merge functionality, and this is working right.

Our customer now, ask us to add on docx template also the capabilities to make some calculations, I mean to apply some formula's to data passed on document.MailMerge.ExecuteNestedGroup().

Is there a way to implement complex calculations on these data?

IE: We render a table with a list of person, like:

|NAME|SURNAME|AGE|GENDER|
|John  | Doe         | 35 | M         |
|Ann   | Tailor       | 28 | F           |

And our customer want to calculate the total elements of the table, than the SUM of Male and SUM of FEMALE.

Is there a way to do that inside DOCIO?

I already seen this post:   https://www.syncfusion.com/forums/101449/formula-with-mergefield
but the links are broken.

Many thanks!
Denis

[Sathish K]

Hi Denis,

Thank you for contacting Syncfusion support.

Mail merge functionality of DocIO has been implemented to populate the data which come from the other data source on the corresponding merge fields. As per the MS Word behaviour it is currently not feasible to internally perform calculation on the mail merge data such as SUM, DIVISION, etc., using DocIO.

In the post (https://www.syncfusion.com/forums/101449/formula-with-mergefield), we have explained about UpdateDocumentFields method which updates the field preserved in the document. Here the needed data are populated using mail merge functionality and then updated the sum by calling the UpdateDocumentFields method.

Please refer to the following UG documentation to know more about UpdateDocumentFields method of DocIO:
http://help.syncfusion.com/aspnet/docio/working-with-fields

Please refer to the following online sample which demonstrates this functionality:
http://asp.syncfusion.com/demos/web/docio/updatefields.aspx

Regards,
Sathish

[Denis Torresan]

Dear Sathish,
thank you for your respose.

What strategy, and what product you suggest to achieve our requirements? Maybe report component?

thanks!
Denis

[Sathish K]

Hi Denis,

You shall make use of Mail merge event functionality to process data during mail merge. Please refer to the following online sample of mail merge event functionality from the following link.

Online sample link:
http://asp.syncfusion.com/demos/web/docio/mailmergeevent.aspx
Kindly use the below code to retrieve the count or sum of records based on gender using mail merge event handler:
int maleCount = 0;

int femaleCount = 0;
document.MailMerge.MergeField += new MergeFieldEventHandler(GetCount);

document.MailMerge.ExecuteNestedGroup(dataSet, commands);

static void GetCount(object sender, MergeFieldEventArgs args)

{

if (args.FieldName == "Gender")

{

if (args.FieldValue == "Male")

maleCount++;

else

femaleCount++;

}
}

Regards,
Sathish

[Sathish K]

Hi Denis,

Please ignore our previous update.

You shall make use of Mail merge event functionality to process data during mail merge. Please refer to the following online sample of mail merge event functionality from the following link.

Online sample link:
http://asp.syncfusion.com/demos/web/docio/mailmergeevent.aspx

Kindly use the below code to retrieve the count or sum of records based on gender using mail merge event handler:
int maleCount = 0;

int femaleCount = 0;
document.MailMerge.MergeField += new MergeFieldEventHandler(GetCount);

document.MailMerge.ExecuteNestedGroup(dataSet, commands);

static void GetCount(object sender, MergeFieldEventArgs args)

{

if (args.FieldName == "Gender")

{

if (args.FieldValue == "Male")

maleCount++;

else

femaleCount++;

}
}

If you have any complex formulae values to be calculated then you shall make use of our Essential XlsIO control by importing values on the cells to compute the values. Please refer to the below online sample link to know about formula support in XlsIO.

Online sample link:
http://asp.syncfusion.com/demos/web/xlsio/formulas.aspx 

Regards,
Sathish

[Denis Torresan]

Hi Denis,

Please ignore our previous update.

You shall make use of Mail merge event functionality to process data during mail merge. Please refer to the following online sample of mail merge event functionality from the following link.

Online sample link:
http://asp.syncfusion.com/demos/web/docio/mailmergeevent.aspx

Kindly use the below code to retrieve the count or sum of records based on gender using mail merge event handler:
int maleCount = 0;

int femaleCount = 0;
document.MailMerge.MergeField += new MergeFieldEventHandler(GetCount);

document.MailMerge.ExecuteNestedGroup(dataSet, commands);

static void GetCount(object sender, MergeFieldEventArgs args)

{

if (args.FieldName == "Gender")

{

if (args.FieldValue == "Male")

maleCount++;

else

femaleCount++;

}
}

If you have any complex formulae values to be calculated then you shall make use of our Essential XlsIO control by importing values on the cells to compute the values. Please refer to the below online sample link to know about formula support in XlsIO.

Online sample link:
http://asp.syncfusion.com/demos/web/xlsio/formulas.aspx 

Regards,
Sathish

Hello Sathish,

thanks for the response.

One question: there's a way to access to the full DataSet row in the event?

Let me explain what I want to try to do:

I have a list of users, taken from database with a query like: "Select name, surname from user".

What I need to do is to create on Word a table with the line number, something like:

NUMBER | NAME | SURNAME |
1          | John   | Smith     |
2          | Mark   | Adams    |
3          | ....

where the NUMBER row is a dynamic row calculated during BeginGroup "foreach".

There's a way to do something like:



static void GetCount(object sender, MergeFieldEventArgs args)

{

    args.CurrentRow.AddColumn("number").GetValue(args.RowIndex + 1);

}

Alternatively... Simply modify a value of the current row, like:


static void GetCount(object sender, MergeFieldEventArgs args)

{

    args.CurrentRow.GetColumn("number").SetValue(args.RowIndex + 1);

}


Thank you!
Denis

[Sarathkumar V]

Hi Denis,

Thank you for your update.

On further analyzing the given details, we found that you are trying to update the sequence number value for each merged record. To archive this requirement, you don’t need to use the mail merge events to update the sequence number instead you can use the merge sequence field for this purpose. For your reference we have prepared the sample to update the sequence number for each merge record during mail merge process. Please find the sample from the following link:

http://www.syncfusion.com/downloads/support/forum/120727/ze/GenerateWord-1644113451.zip

If we misunderstood your requirement, kindly elaborate your requirement with clear description (like screenshot of your output or expected output document), Thereby we will analyse further on your requirement and update you appropriate solution at the earliest

Please let us know if you have any other queries.

Regards,
Sarath

[Denis Torresan]

Thank you Sarath!

We're trying this solution and hope it work!

One question: where I can find documentation about out of the box implicit "merge field" like merge sequence field ?

Thank you!
Denis

[Suganya Rathinam]

Hi Denis,

Thank you for your update.

We will update about the Merge Sequence Field in our documentation and let you know once it has been published online.

Regards,
Suganya

[Denis Torresan]

Hello Suganya,

any update about  Merge Sequence Field in our documentation?

May I find something online?

thank you!
Denis

[Sarathkumar V]

Hi Denis,

Thank you for your update.

Currently we are working on the UG document for merge sequence field and it is published in the 2015 Volume 4 release which is excepted to be available in the end of this month.

Please let us know if you have any queries.

Regards,
Sarath