Reporting system on Aspose.Word / C#

Question

I am currently in need of an idea, I'm developing a software solution and I have to create Words documents from BDD's information.

Everything is fine about informations's use but one point and it's the most important :

On my Word document, I use signets to repeat a table where information is written. One of the column refer to a cost, and since I'm using the signets's system, I don't know where my table end, it can take 1 page as it can take 2500's but I need to calculate at the end of EACH page a total of every row that was written on the current page, and then rewrite this total at the beginning of the next page. So it would be like :

• Page 1

nameOfTheItem1 Cost1
nameOfTheItem2 Cost2 
nameOfTheItem3 Cost3
nameOfTheItem4 Cost4
TOTAL PAGE 1 : TotalPage1

• Page 2

TotalPage1

nameOfTheItem5 Cost5
nameOfTheItem6 Cost6
nameOfTheItem7 Cost7
nameOfTheItem8 Cost8
TOTAL PAGE 2 : TotalPage2(+TotalPage1)

and this for each page existing for this document.

I'm still looking for a solution by myself, and every help would be great.

Answer 1

It can be done with the mail merge feature of Aspose.Words. The solution is particularly related to the nested mail merge.

1. You have to arrange the data in a DataSet, in order to make the report appear according to your requirements. For this scenario, arrange in 2 tables. One is "Page" and other is "Item"
2. Your template document (DOCX) should define the merge fields according the the image below. Note that there is a page break after the table.

The following code will help you to get started. It uses dummy data of course. You can populate with your own data to make it work for you.

MS Word template document that works with this code: Download Template

private void yourMethod()
{
    string srcDoc = dataDir + "ItemsTemplate.docx";
    string dstDoc = dataDir + "ItemsTemplate_Result.docx";

    int totalRecords = 10;
    int recordsPerPage = 4;

    // Prepare some data
    DataSet ds = getData(totalRecords, recordsPerPage);

    // Prepare the document in Aspose
    Aspose.Words.Document doc = new Aspose.Words.Document(srcDoc);
    doc.MailMerge.ExecuteWithRegions(ds);
    doc.MailMerge.CleanupOptions = Aspose.Words.Reporting.MailMergeCleanupOptions.RemoveEmptyParagraphs;
    doc.Save(dstDoc);

    Process.Start(dstDoc);
}

private DataSet getData(int totalRecords, int recordsPerPage)
{
    DataSet ds = new DataSet("Dataset");

    // Add the page table
    System.Data.DataTable pageTable = new System.Data.DataTable("Page");
    pageTable.Columns.Add("PageNumber");
    pageTable.Columns.Add("PageTotal");
    pageTable.Columns.Add("PreviousPageTotal");

    // Add the item table
    System.Data.DataTable itemTable = new System.Data.DataTable("Item");
    itemTable.Columns.Add("ID");
    itemTable.Columns.Add("Name");
    itemTable.Columns.Add("Cost");
    itemTable.Columns.Add("PageNumber");

    // Add pages
    int iRow = 1, iPage = 1;
    while (iRow <= totalRecords )
    {
        DataRow pageRow = pageTable.NewRow();
        pageRow["PageNumber"] = iPage;
        pageRow["PageTotal"] = 0;

        // Add the items in this page
        int iRecordsPerPage = 1;
        while (iRow <= totalRecords && iRecordsPerPage <= recordsPerPage)
        {
            DataRow itemRow = itemTable.NewRow();
            itemRow["ID"] = iRow;
            itemRow["Name"] = "Item " + iRow;
            itemRow["Cost"] = iRow;
            itemRow["PageNumber"] = iPage;

            pageRow["PageTotal"] = int.Parse(pageRow["PageTotal"].ToString()) + int.Parse(itemRow["Cost"].ToString());

            itemTable.Rows.Add(itemRow);
            iRow++;
            iRecordsPerPage++;
        }

        pageTable.Rows.Add(pageRow);

        // Previous page total
        if (iPage == 1)
            pageRow["PreviousPageTotal"] = 0; // Always 0 for first page
        else
            pageRow["PreviousPageTotal"] = pageTable.Rows[iPage - 2]["PageTotal"]; // Get total of previous page

        iPage++;
    }

    ds.Tables.Add(pageTable);
    ds.Tables.Add(itemTable);

    // We must have relationship for Aspose mail merge to work correctly
    ds.Relations.Add(pageTable.Columns["PageNumber"], itemTable.Columns["PageNumber"]);

    return ds;
}

Try changing the values of totalRecords and recordsPerPage variables and you will see the data arranged in pages accordingly. Just be sure to keep the recordsPerPage value low, so that it does not exceed single page.

I am a developer evangelist at Aspose.

Answer 2

I am exporting data to existing word template which is .dotx But the final report does not show the table of contents even though i have added it in code. My code is as follows

    public void ExportToWordUsingTemplate()
    {

        Aspose.Words.Document doc1 = new Aspose.Words.Document(@"E:/excel/HOVEDMAL Prognoserapporter 2.dotx");
        DocumentBuilder docBuilder1 = new DocumentBuilder(doc1);

        SkinAPI.ReportAPISoapClient svc = new SkinAPI.ReportAPISoapClient();
        SkinAPI.GetReportContextResult myReportContext = svc.GetReportContext(1);

        docBuilder1.InsertHtml("<h1 align='left'>" + myReportContext[0].MainReportName + "</h1>");
        docBuilder1.InsertTableOfContents("\\o \"1-3\" \\h \\z \\u");


        //for (int i = 0; i < myReportContext.Count - 2; i++)
        for (int i = 0; i < 5; i++)
        {
            SkinAPI.GetReportElementGraphDataResult myElementGraphData = svc.GetReportElementGraphData(myReportContext[i].ReportId, myReportContext[i].ElementId);
            SkinAPI.GetReportElementDataResult myElementData = svc.GetReportElementData(myReportContext[i].ReportId, myReportContext[i].ElementId, 0, 0, 0);    // Three last parameters set to 0, used when fetching drilldown data for tables that support it

            docBuilder1.ParagraphFormat.StyleIdentifier = StyleIdentifier.Heading1;
            docBuilder1.Writeln(myReportContext[i].ElementHeader);
            docBuilder1.ParagraphFormat.StyleIdentifier = StyleIdentifier.BodyText;

            // Is there a graph for this element, and has it a datasource other than the main data source as fetched above?
            if (myReportContext[i].HasGraph && myReportContext[i].SeparateGraphDataSource)
            {
                // Is there a text part for this element
                if (myReportContext[i].HasText)
                {
                    // The returned string will contain a HTML text.
                    // Note that the text is connected to a TileId, not an ElementId, meening the text might have been fetched before.
                    string myElementHTMLDescription = svc.GetReportText(myReportContext[i].TileId);
                    docBuilder1.InsertHtml(myElementHTMLDescription);                        
                }
            }
            docBuilder1.InsertBreak(BreakType.PageBreak);
        }

        doc1.Save(@"E:/excel/HOVEDMAL Prognoserapporter 2_Report.doc");
    }