Subtotal per page with mailmerge

[NicolasC]

Hi,

I would like to create a report using the mail merge feature of GemBox.Document.
I didn’t find the feature that I need listed, and it may be possible to do but I don’t know how.

I need to fill a table from a mail merge, at the end of each page, have a subtotal computed, and at the end of the table on the last page, a total computed.

The total at the end of the report, it’s easy to do, I have seen it in the Merge Ranges example.
But how do I do a subtotal per page?

Thanks for the help

[mario.gembox]

Hi Nicolas,

You could easily have a subtotal for every table like shown in Nested Merge example.
But having a subtotal for a page is difficult because Word files are of flow-document type, they don’t have a page concept, the elements are not defined on which page they’re located, rendered.

Nevertheless, can you send us an example Word file that contains the structure, the layout, that you would like to generate?
I’ll investigate your exact requirements and try to come up with something that will suit you.

Regards,
Mario

[NicolasC]

Hello Mario,

I understand the difficulty, I don’t have the word template yet but I’ll come up with one soon.

Would it be easier if the subtotal was in the footer section of the document ?
We could first render the table, let it spread over the pages it needs, and then count the rows for each page, match it with the rows in the data table and write the subtotal in the footer section of each page ?

[mario.gembox]

Hi Nicolas,

I was thinking something similar, but I’ll wait for your sample document to see how your desired layout looks like.

Nevertheless, the footer won’t help us for this because that same content is repeated for each page.
Instead, I was thinking of paginating the content, splitting the tables per each page based on the calculated rows height, and adding floating textboxes that are anchored to bottom of the page between those tables.

Regards,
Mario

[mario.gembox]

Hi Nicolas,

Here is what I was thinking, let’s say you have something like this dummy document as a result of the mail merge:

var document = new DocumentModel();

var section = new Section(document);
document.Sections.Add(section);

var table = new Table(document);
section.Blocks.Add(table);

var random = new Random();
for (int i = 0; i < 60; i++)
{
    var row = new TableRow(document,
        new TableCell(document,
            new Paragraph(document,
                string.Join("\n", Enumerable.Repeat("Sample", random.Next(1, 4))))),
        new TableCell(document,
            new Paragraph(document,
                (random.NextDouble() * 1000).ToString())));

    row.RowFormat.AllowBreakAcrossPages = false;
    table.Rows.Add(row);
}

document.Save("Output.docx");

The table has rows with the first cell that has a random number of lines (for a dynamic row height) and the second cell with some numerical value.

Here is the screenshot:

Now what we could do is add PAGE Field to each TableRow, update their value with GemBox.Document’s rendering engine, split the Table accordingly, and separate each Table with the Paragraph that has a floating TextBox for a subtotal.

So, something like this:

var document = DocumentModel.Load("Output.docx");
var section = document.Sections[0];

var mainTable = (Table)section.GetChildElements(false, ElementType.Table).First();
var tableIndex = section.Blocks.IndexOf(mainTable);

// Add PAGE fields at the beginning of each row.
var mainRows = mainTable.Rows;
var pageField = new Field(document, FieldType.Page);
foreach (var row in mainRows)
    row.Content.Start.InsertRange(pageField.Content);

// Update PAGE fields.
document.GetPaginator(new PaginatorOptions() { UpdateFields = true });

// Split the table into multiple tables based on the PAGE fields in the rows.
int currentRowIndex = 0;
string previousRowPage = "1";
double currentSubtotal = 0;
double total = 0;
TableRowCollection splitRows = null;

while (currentRowIndex < mainRows.Count)
{
    var currentRow = mainRows[currentRowIndex];
    var currentField = currentRow.GetChildElements(true, ElementType.Field).First();
    var currentRowPage = currentField.Content.ToString();

    // Create paragraph with subtotal and new table after it.
    if (currentRowPage != previousRowPage)
    {
        var subtotalParagraph = new Paragraph(document,
            new TextBox(document,
                new FloatingLayout(
                    new HorizontalPosition(70, LengthUnit.Point, HorizontalPositionAnchor.Page),
                    new VerticalPosition(800, LengthUnit.Point, VerticalPositionAnchor.Page),
                    new Size(200, 25))
                {
                    WrappingStyle = TextWrappingStyle.InFrontOfText
                },
                new Paragraph(document, "Subtotal: " + currentSubtotal.ToString())))
        {
            CharacterFormatForParagraphMark = { Size = 1 },
            ParagraphFormat = { SpaceAfter = 0, SpaceBefore = 0, LineSpacing = 1 }
        };

        var splitTable = mainTable.Clone(false);
        splitRows = splitTable.Rows;

        section.Blocks.Insert(++tableIndex, subtotalParagraph);
        section.Blocks.Insert(++tableIndex, splitTable);

        previousRowPage = currentRowPage;
        currentSubtotal = 0;
    }

    // Remove PAGE field.
    currentField.Content.Delete();

    // Add to subtotal.
    var value = double.Parse(currentRow.Cells[1].Content.ToString());
    currentSubtotal += value;
    total += value;

    // Move row to new table.
    if (splitRows != null)
    {
        splitRows.Add(currentRow.Clone(true));
        mainRows.RemoveAt(currentRowIndex);
    }
    else
    {
        ++currentRowIndex;
    }
}

section.Blocks.Insert(++tableIndex, new Paragraph(document, "Total: " + total.ToString()));

document.Save("OutputWithSubtotals.docx");

Here is the screenshot:

I hope this helps.

Regards,
Mario

[NicolasC]

Thanks for the help and the working code.