Free Support Forum - aspose.com

Dynamic Columns and Rows Report using Mail Merge

We want to create a report were table in word document will be poplulated at runtime using merge fileds. All the examples we have seen, columns in word table are fixed with pre defined merge fields.

We would like to populate word table with dynamic columns and rows from the datatable at runtime since number of columns are not known at design time.

Can you give us an sample code on how to do this? Also we would like to have first row in word table as header row which repeats across pages.

Thanks

Hi Nik,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks for your request. You can use Documentbuilder to build table with random number of columns. For example, see the following code:

public void Test165()

{

//Get dummy datasource (data table with random number of rows)

DataTable data = GetDataTable();

//Open or create document and create DocumentBuilder

Document doc = new Document();

//Document builder will be needed to build table in the document

DocumentBuilder builder = new DocumentBuilder(doc);

//if you use template, you can move documentBuilder cursor to any location

//Using MoveToXXX methods, MoveToBookmark for instance

//Customize builder properties

builder.CellFormat.Borders.LineStyle = LineStyle.Single;

builder.CellFormat.Borders.LineWidth = 1;

builder.Font.Name = "Arial";

builder.Font.Size = 10;

//Start building table

builder.StartTable();

//build header row

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

builder.Font.Bold = true;

builder.Font.Italic = true;

builder.RowFormat.HeadingFormat = true;

foreach (DataColumn col in data.Columns)

{

builder.InsertCell();

builder.Write(col.ColumnName);

}

builder.EndRow();

//Starting from here there is two aproach.

//First build entire table using documentBuilder

//another build one row with mergefields and use mail merge to fill table with data

//Here we will use the second aproach

//Build entire table

builder.CellFormat.Shading.BackgroundPatternColor = Color.FromArgb(255, 255, 255, 255);

builder.Font.Bold = false;

builder.Font.Italic = false;

builder.RowFormat.HeadingFormat = false;

foreach (DataColumn col in data.Columns)

{

builder.InsertCell();

//If it is first column we should insert tableStart mergefield

if (col.Equals(data.Columns[0]))

builder.InsertField(String.Format("MERGEFIELD \"TableStart:{0}\"", data.TableName), "");

//Insert mergefield

builder.InsertField(String.Format("MERGEFIELD \"{0}\"", col.ColumnName), "");

//If column is last we should insert TableEnd

if (col.Equals(data.Columns[data.Columns.Count - 1]))

builder.InsertField(String.Format("MERGEFIELD \"TableEnd:{0}\"", data.TableName), "");

}

builder.EndRow();

builder.EndTable();

//Now we ca execute mail merge

doc.MailMerge.ExecuteWithRegions(data);

//Save output document

doc.Save(@"Test165\out.doc");

}

private DataTable GetDataTable()

{

//Create dummy datasource that contains randome number of culumns

Random rnd = new Random();

DataTable data = new DataTable("data");

int colCount = rnd.Next(1, 10);

//Add columns

for (int colIdx = 0; colIdx < colCount; colIdx++)

{

data.Columns.Add(string.Format("Column_{0}", colIdx));

}

//Add random number of rows

int rowsCount = rnd.Next(1, 100);

for (int rowIdx = 0; rowIdx < rowsCount; rowIdx++)

{

DataRow row = data.NewRow();

foreach (DataColumn col in data.Columns)

{

row[col] = string.Format("Column:{0}; Row:{1}", col.ColumnName, rowIdx);

}

data.Rows.Add(row);

}

return data;

}

Hope this helps.

Best regards.

Thanks for the quick response alexey

Our report contains several tables. Some are predefined (hence, we can format them manually in word), and some are dynamically created (as mentioned in the above question). How can we carry over the same formatting from the fixed tables to the dynamically created ones?

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thanks for your inquiry. I think you can achieve this using cloning. Concept is following:

1. Create table in MS Word that has one column and two rows. This will be template. Customize the table. In addition, insert bookmark at the firs cell (this will be table identifier).

2. In the code, move document builder cursor and clone cells.

For example, see the following code and attached template:

public void Test165()

{

//Get dummy datasource (data table with random number of rows)

DataTable data = GetDataTable();

//Open or create document and create DocumentBuilder

Document doc = new Document(@"Test165\in.doc");

//Document builder will be needed to build table in the document

DocumentBuilder builder = new DocumentBuilder(doc);

//move documentBuilder cursor to the bookmark inside table

builder.MoveToBookmark("myTable");

//Get table

Table myTable = (Table)builder.CurrentNode.GetAncestor(NodeType.Table);

//build header row

foreach (DataColumn col in data.Columns)

{

//Clone first cell of first row to build header of the table

Cell hCell = (Cell)myTable.FirstRow.FirstCell.Clone(true);

//Insert cell into the first row

myTable.FirstRow.AppendChild(hCell);

//Move document builder cursor to the cell

builder.MoveTo(hCell.FirstParagraph);

//Insert text

builder.Write(col.ColumnName);

}

//Build entire table

foreach (DataColumn col in data.Columns)

{

//Clone first cell of the second row to build header of the table

Cell bCell = (Cell)myTable.Rows[1].FirstCell.Clone(true);

//Insert cell into the second row

myTable.Rows[1].AppendChild(bCell);

//Move document builder cursor to the cell

builder.MoveTo(bCell.FirstParagraph);

//If it is first column we should insert tableStart mergefield

if (col.Equals(data.Columns[0]))

builder.InsertField(String.Format("MERGEFIELD \"TableStart:{0}\"", data.TableName), "");

//Insert mergefield

builder.InsertField(String.Format("MERGEFIELD \"{0}\"", col.ColumnName), "");

//If column is last we should insert TableEnd

if (col.Equals(data.Columns[data.Columns.Count - 1]))

builder.InsertField(String.Format("MERGEFIELD \"TableEnd:{0}\"", data.TableName), "");

}

//Here we remove first empty column

myTable.Rows[0].Cells[0].Remove();

myTable.Rows[1].Cells[0].Remove();

//Now we ca execute mail merge

doc.MailMerge.ExecuteWithRegions(data);

//Save output document

doc.Save(@"Test165\out.doc");

}

Hope this helps.

Best regards.

Very use full code.

I want to display the multiple headers for the table(by merging two coulmns n rows ...please see the attached)

Please tell me ho wto modify the above code to display multple headers for my dynamic table.and also is there anyway we can remove the header and report only rows and columns??

Hi Anju,


Thanks for your inquiry. Please read following documentation link for your kind reference.
http://www.aspose.com/docs/display/wordsnet/Working+with+Merged+Cells

The code shared in this thread generate table dynamically without any cell merge and do mail merge. The code generates table with only two rows. You can insert code shared here before for loop in above code.

//build header row<o:p></o:p>

builder.RowFormat.HeadingFormat = true;

//build header row

// insert code here…

foreach (DataColumn col in data.Columns)<o:p></o:p>

{<o:p></o:p>

builder.InsertCell();<o:p></o:p>

builder.Write(col.ColumnName);<o:p></o:p>

}<o:p></o:p>

builder.EndRow();

Please manually create your expected Word document using Microsoft Word and attach it here for our reference. We will investigate as to how you want your final Word output be generated like. We will then provide you more information on this along with code.

Please also share some more detail about your scenario what exact you want to achieve by using Aspose.Words.