Sometime there are document with template having fields in nested group .
When dealing with nested groups we specify a group name with start tag like "«BeginGroup:Employees»" and a closing tag like " «EndGroup:Employees»"
Child groups will be nested inside parent using the same concept of start and end tag
These fields are mapped from a default database.
After clicking generate button a word document file is generated with above choosen word version .Nested groups mapped fields are populated with data
//#define usesql
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Text;
using OfficeComponent.Word;
using System.Data;
using System.Collections;
namespace OfficeComponent.Samples
{
class NestedGroupExample : WordExampleBase
{
public NestedGroupExample(string commonDataPath, string outputDir)
: base(commonDataPath, outputDir)
{
}
public NestedGroupExample(string commonDataPath, string outputDir, string xmlFile) : base(commonDataPath, outputDir, xmlFile)
{
}
public override string Execute()
{
// Create a new instance of PdfDocument class.
WordDocument document = new WordDocument(CommonDataPath + "\\Nested Group Report Template.doc");
#if !usesql
DataSet dataSet = GetDataSet();
// Arraylist contains the list of commands
ArrayList commands = new ArrayList();
// DictionaryEntry contain "Source table" (KEY) and "Command" (VALUE)
DictionaryEntry entry = new DictionaryEntry("Employees", "");
commands.Add(entry);
// To retrieve order details
entry = new DictionaryEntry("Orders",
"EmployeeID = %Employees.EmployeeID% AND CustomerID = %Customers.CustomerID%"
);
commands.Add(entry);
// To retrive customer details
entry = new DictionaryEntry("Customers", "CustomerID = %Orders.CustomerID% AND %Orders.EmployeeID% = %Employees.EmployeeID%");
commands.Add(entry);
// Execute Mail merge
document.MailMerge.ExecuteNestedGroup(dataSet, commands);
#else
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
string connectionString = "Data Source = " + CommonDataPath + "\\NorthwindIO.sdf";
// Get Data from the Database.
SqlCeConnection conn = new SqlCeConnection(connectionString);
conn.Open();
// Arraylist contains the list of commands
ArrayList commands = new ArrayList();
// DictionaryEntry contain "Source table" (KEY) and "Command" (VALUE)
DictionaryEntry entry = new DictionaryEntry("Employees", "Select TOP(6) * from Employees");
commands.Add(entry);
// To retrive customer details
entry = new DictionaryEntry("Customers", "SELECT DISTINCT TOP(6) Employees.EmployeeID, Customers.CustomerID,Customers.CompanyName, Customers.ContactName, Customers.Address, Customers.City, Customers.Country FROM ((Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) WHERE Employees.EmployeeID = %Employees.EmployeeID%");
commands.Add(entry);
// To retrieve order details
entry = new DictionaryEntry("Orders", "SELECT DISTINCT TOP(6) OrderID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE Orders.CustomerID = '%Customers.CustomerID%' AND Orders.EmployeeID = %Employees.EmployeeID%");
commands.Add(entry);
//Execute Mail merge
document.MailMerge.ExecuteNestedGroup(conn, commands);
conn.Close();
#endif
string fileName = Path.Combine(OutputDir, this.GetType().Name + "_" + Guid.NewGuid().ToString() + GetExtension(SaveAsFormat));
// Save the document.
document.Save(fileName, SaveAsFormat);
// Close the document.
document.Close();
return fileName;
}
#if !usesql
DataSet GetDataSet()
{
DataSet ds = new DataSet();
DataTable employees = new DataTable("Employees");
ds.Tables.Add(employees);
employees.Columns.Add("EmployeeID", typeof(int));
employees.Columns.Add("FirstName", typeof(string));
employees.Columns.Add("LastName", typeof(string));
employees.Columns.Add("Extension", typeof(string));
employees.Columns.Add("Title", typeof(string));
employees.Columns.Add("Address", typeof(string));
employees.Columns.Add("City", typeof(string));
employees.Columns.Add("Country", typeof(string));
AddEmpoyees(employees);
DataTable customers = new DataTable("Customers");
ds.Tables.Add(customers);
customers.Columns.Add("CustomerID", typeof(int));
customers.Columns.Add("CustomerName", typeof(string));
customers.Columns.Add("ContactName", typeof(string));
customers.Columns.Add("City", typeof(string));
customers.Columns.Add("Country", typeof(string));
AddCustomers(customers);
DataTable orders = new DataTable("Orders");
ds.Tables.Add(orders);
orders.Columns.Add("OrderID", typeof(int));
orders.Columns.Add("CustomerID", typeof(int));
orders.Columns.Add("EmployeeID", typeof(int));
orders.Columns.Add("OrderDate", typeof(DateTime));
orders.Columns.Add("ShipDate", typeof(DateTime));
orders.Columns.Add("RequiredDate", typeof(DateTime));
int orderId = 1000;
foreach (DataRow employee in employees.Rows)
{
foreach (DataRow customer in customers.Rows)
{
AddOrders(orderId++, orders, (int)employee["EmployeeID"], (int)customer["CustomerID"]);
}
}
return ds;
}
void AddEmpoyees(DataTable dt)
{
for (int i = 0; i < 10; i++)
{
dt.Rows.Add(
1000 + i, // Employee ID
GetRandomName(), // first name
"", // last name. use "" since we already have full name in FirstName field
123 + random.Next(300), // Extension
"Mr.", // city
GetRandomAddress(),
GetRandomCity(),
GetRandomCountry()
);
}
}
void AddOrders(int id, DataTable dt, int employeeId, int customerId)
{
for (int i = 0; i < random.Next(3) + 1; i++)
{
dt.Rows.Add(
id, // Customer ID
customerId, // customer
employeeId, // employee
DateTime.Now.AddYears(-1),// order date
DateTime.Now.AddMonths(-11), // shipped date
DateTime.Now.AddMonths(-10) // required date
);
}
}
void AddCustomers(DataTable dt)
{
for (int i = 0; i < 6; i++)
{
dt.Rows.Add(
1000 + i, // Customer ID
GetRandomName(), // name
GetRandomName(), // contact name
GetRandomCity(), // city
GetRandomCountry()
);
}
}
Random random = new Random(Environment.TickCount);
private string GetRandomAddress()
{
string[] list = new string[]
{
"Avda. de la Constituci�n 2222",
"Mataderos 2312",
"120 Hanover Sq.",
"Berguvsv�gen 8",
"Forsterstr. 57",
"24, place Kl�ber",
"C/ Araquil, 67",
"12, rue des Bouchers",
"23 Tsawassen Blvd.",
"Fauntleroy Circus",
};
return list[random.Next(list.Length)];
}
private string GetRandomName()
{
string[] list = new string[]
{
"Maria Anders",
"Ana Trujillo",
"Antonio Moreno",
"Thomas Hardy",
"Christina Berglund",
"Hanna Moos",
"Fr�d�rique Citeaux",
"Mart�n Sommer",
"Laurence Lebihan",
"Elizabeth Lincoln",
"Victoria Ashworth",
"Francisco Chang",
"Yang Wang",
};
return list[random.Next(list.Length)];
}
private string GetRandomCompanyName()
{
string[] list = new string[]
{
"Alfreds Futterkiste",
"Around the Horn",
"Berglunds snabbk�p",
"Blauer See Delikatessen",
"Bon app'",
"Bottom-Dollar Markets",
"B's Beverages",
"Consolidated Holdings",
"Ernst Handel",
"Folies gourmandes",
"Galer�a del gastr�nomo",
"Hanari Carnes",
"Island Trading",
};
return list[random.Next(list.Length)];
}
private string GetRandomCity()
{
string[] list = new string[]
{
"Cork",
"Cowes",
"Versailles",
"Vancouver",
"Walla Walla",
"Frankfurt a.M.",
"San Francisco",
"Barquisimeto",
"Portland",
"Bergamo",
"Bruxelles",
"Montr�al",
"Leipzig",
"London",
};
return list[random.Next(list.Length)];
}
private string GetRandomCountry()
{
string[] list = new string[]
{
"Germany",
"Venezuela",
"USA",
"Canada",
"Belgium",
"Italy",
"Argentina",
"UK",
"France",
"Mexico",
"Austria",
"Portugal",
"Brazil",
"Switzerland",
};
return list[random.Next(list.Length)];
}
private string GetRandomPostalCode()
{
string[] list = new string[]
{
"PO31 7PJ",
"14776",
"78000",
"V3F 2K1",
"99362",
"94117",
"3508",
"24100",
"B-1180",
"SW7 1RZ",
"99508",
"50739",
"05033",
"02389-673",
};
return list[random.Next(list.Length)];
}
#endif
}
}
'#define usesql
Imports System.Drawing.Imaging
Imports System.IO
Imports System.Text
Imports OfficeComponent.Word
Imports System.Collections
Namespace OfficeComponent.Samples
Friend Class NestedGroupExample
Inherits WordExampleBase
Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String)
MyBase.New(commonDataPath, outputDir)
End Sub
Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String, ByVal xmlFile As String)
MyBase.New(commonDataPath, outputDir, xmlFile)
End Sub
Public Overrides Function Execute() As String
' Create a new instance of PdfDocument class.
Dim document As New WordDocument(CommonDataPath & "\Nested Group Report Template.doc")
#If Not usesql Then
Dim dataSet As DataSet = GetDataSet()
' Arraylist contains the list of commands
Dim commands As New ArrayList()
' DictionaryEntry contain "Source table" (KEY) and "Command" (VALUE)
Dim entry As New DictionaryEntry("Employees", "")
commands.Add(entry)
' To retrieve order details
entry = New DictionaryEntry("Orders", "EmployeeID = %Employees.EmployeeID% AND CustomerID = %Customers.CustomerID%")
commands.Add(entry)
' To retrive customer details
entry = New DictionaryEntry("Customers", "CustomerID = %Orders.CustomerID% AND %Orders.EmployeeID% = %Employees.EmployeeID%")
commands.Add(entry)
' Execute Mail merge
document.MailMerge.ExecuteNestedGroup(dataSet, commands)
#Else
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", True)
Dim connectionString As String = "Data Source = " & CommonDataPath & "\NorthwindIO.sdf"
' Get Data from the Database.
Dim conn As New SqlCeConnection(connectionString)
conn.Open()
' Arraylist contains the list of commands
Dim commands As New ArrayList()
' DictionaryEntry contain "Source table" (KEY) and "Command" (VALUE)
Dim entry As New DictionaryEntry("Employees", "Select TOP(6) * from Employees")
commands.Add(entry)
' To retrive customer details
entry = New DictionaryEntry("Customers", "SELECT DISTINCT TOP(6) Employees.EmployeeID, Customers.CustomerID,Customers.CompanyName, Customers.ContactName, Customers.Address, Customers.City, Customers.Country FROM ((Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) WHERE Employees.EmployeeID = %Employees.EmployeeID%")
commands.Add(entry)
' To retrieve order details
entry = New DictionaryEntry("Orders", "SELECT DISTINCT TOP(6) OrderID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE Orders.CustomerID = '%Customers.CustomerID%' AND Orders.EmployeeID = %Employees.EmployeeID%")
commands.Add(entry)
'Execute Mail merge
document.MailMerge.ExecuteNestedGroup(conn, commands)
conn.Close()
#End If
Dim fileName As String = Path.Combine(OutputDir, Me.GetType().Name & "_" & Guid.NewGuid().ToString() & GetExtension(SaveAsFormat))
' Save the document.
document.Save(fileName, SaveAsFormat)
' Close the document.
document.Close()
Return fileName
End Function
#If Not usesql Then
Private Function GetDataSet() As DataSet
Dim ds As New DataSet()
Dim employees As New DataTable("Employees")
ds.Tables.Add(employees)
employees.Columns.Add("EmployeeID", GetType(Integer))
employees.Columns.Add("FirstName", GetType(String))
employees.Columns.Add("LastName", GetType(String))
employees.Columns.Add("Extension", GetType(String))
employees.Columns.Add("Title", GetType(String))
employees.Columns.Add("Address", GetType(String))
employees.Columns.Add("City", GetType(String))
employees.Columns.Add("Country", GetType(String))
AddEmpoyees(employees)
Dim customers As New DataTable("Customers")
ds.Tables.Add(customers)
customers.Columns.Add("CustomerID", GetType(Integer))
customers.Columns.Add("CustomerName", GetType(String))
customers.Columns.Add("ContactName", GetType(String))
customers.Columns.Add("City", GetType(String))
customers.Columns.Add("Country", GetType(String))
AddCustomers(customers)
Dim orders As New DataTable("Orders")
ds.Tables.Add(orders)
orders.Columns.Add("OrderID", GetType(Integer))
orders.Columns.Add("CustomerID", GetType(Integer))
orders.Columns.Add("EmployeeID", GetType(Integer))
orders.Columns.Add("OrderDate", GetType(Date))
orders.Columns.Add("ShipDate", GetType(Date))
orders.Columns.Add("RequiredDate", GetType(Date))
Dim orderId As Integer = 1000
For Each employee As DataRow In employees.Rows
For Each customer As DataRow In customers.Rows
AddOrders(orderId, orders, CInt(Fix(employee("EmployeeID"))), CInt(Fix(customer("CustomerID"))))
orderId += 1
Next customer
Next employee
Return ds
End Function
Private Sub AddEmpoyees(ByVal dt As DataTable)
For i As Integer = 0 To 9
dt.Rows.Add(1000 + i, GetRandomName(), "", 123 + random.Next(300), "Mr.", GetRandomAddress(), GetRandomCity(), GetRandomCountry()) ' city - Extension - last name. use "" since we already have full name in FirstName field - first name - Employee ID
Next i
End Sub
Private Sub AddOrders(ByVal id As Integer, ByVal dt As DataTable, ByVal employeeId As Integer, ByVal customerId As Integer)
For i As Integer = 0 To random.Next(3)
dt.Rows.Add(id, customerId, employeeId, Date.Now.AddYears(-1), Date.Now.AddMonths(-11), Date.Now.AddMonths(-10)) ' required date - shipped date - order date - employee - customer - Customer ID
Next i
End Sub
Private Sub AddCustomers(ByVal dt As DataTable)
For i As Integer = 0 To 5
dt.Rows.Add(1000 + i, GetRandomName(), GetRandomName(), GetRandomCity(), GetRandomCountry()) ' city - contact name - name - Customer ID
Next i
End Sub
Private random As New Random(Environment.TickCount)
Private Function GetRandomAddress() As String
Dim list() As String = { "Avda. de la Constituci�n 2222", "Mataderos 2312", "120 Hanover Sq.", "Berguvsv�gen 8", "Forsterstr. 57", "24, place Kl�ber", "C/ Araquil, 67", "12, rue des Bouchers", "23 Tsawassen Blvd.", "Fauntleroy Circus" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomName() As String
Dim list() As String = { "Maria Anders", "Ana Trujillo", "Antonio Moreno", "Thomas Hardy", "Christina Berglund", "Hanna Moos", "Fr�d�rique Citeaux", "Mart�n Sommer", "Laurence Lebihan", "Elizabeth Lincoln", "Victoria Ashworth", "Francisco Chang", "Yang Wang" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomCompanyName() As String
Dim list() As String = { "Alfreds Futterkiste", "Around the Horn", "Berglunds snabbk�p", "Blauer See Delikatessen", "Bon app'", "Bottom-Dollar Markets", "B's Beverages", "Consolidated Holdings", "Ernst Handel", "Folies gourmandes", "Galer�a del gastr�nomo", "Hanari Carnes", "Island Trading" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomCity() As String
Dim list() As String = { "Cork", "Cowes", "Versailles", "Vancouver", "Walla Walla", "Frankfurt a.M.", "San Francisco", "Barquisimeto", "Portland", "Bergamo", "Bruxelles", "Montr�al", "Leipzig", "London" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomCountry() As String
Dim list() As String = { "Germany", "Venezuela", "USA", "Canada", "Belgium", "Italy", "Argentina", "UK", "France", "Mexico", "Austria", "Portugal", "Brazil", "Switzerland" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomPostalCode() As String
Dim list() As String = { "PO31 7PJ", "14776", "78000", "V3F 2K1", "99362", "94117", "3508", "24100", "B-1180", "SW7 1RZ", "99508", "50739", "05033", "02389-673" }
Return list(random.Next(list.Length))
End Function
#End If
End Class
End Namespace