This example demonstrates how to create an employees report using mail merg.
Creating an employee report using mail merge , suppose when there are many employees this reporting feature of mail merge is very helpfull
Using tags we map so here we first we need to specify table from which data will be populated
Specifying a table is easy using start and end tag like «TableStart:Employees» and end tag like «TableEnd:Employees»
Now we need to specify fields , using start and end tag we do it like «FirstName» «LastName» but it has to be inside its parent «TableStart:Employees»«TableEnd:Employees»
After clicking generate button a word file is generated with abover choosen word version , file contains report of employees with fields populated with data from employee table
//#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.SqlServerCe;
using System.Data;
namespace OfficeComponent.Samples
{
class NorthWindReportExample : WordExampleBase
{
public NorthWindReportExample(string commonDataPath, string outputDir)
: base(commonDataPath, outputDir)
{
}
public NorthWindReportExample(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 + "\\NorthWind_Employees.doc");
#if !usesql
DataTable table = new DataTable("Employees");
table.Columns.Add("EmployeeID", typeof(int));
table.Columns.Add("FirstName", typeof(string));
table.Columns.Add("LastName", typeof(string));
table.Columns.Add("Extension", typeof(string));
table.Columns.Add("Title", typeof(string));
table.Columns.Add("Address", typeof(string));
table.Columns.Add("City", typeof(string));
table.Columns.Add("Country", typeof(string));
table.Columns.Add("Photo", typeof(byte[]));
AddEmpoyees(table);
#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();
SqlCeDataAdapter adapter = new SqlCeDataAdapter("Select TOP(5) * from EmployeesReport", conn);
DataTable table = new DataTable();
adapter.Fill(table);
table.TableName = "Employees";
adapter.Dispose();
conn.Close();
#endif
// Execute Mail Merge with groups.
document.MailMerge.ExecuteGroup(table);
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
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(),
File.ReadAllBytes(CommonDataPath + "\\Avatar.bmp")
);
}
}
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.Data.SqlServerCe
Namespace OfficeComponent.Samples
Friend Class NorthWindReportExample
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 & "\NorthWind_Employees.doc")
#If Not usesql Then
Dim table As New DataTable("Employees")
table.Columns.Add("EmployeeID", GetType(Integer))
table.Columns.Add("FirstName", GetType(String))
table.Columns.Add("LastName", GetType(String))
table.Columns.Add("Extension", GetType(String))
table.Columns.Add("Title", GetType(String))
table.Columns.Add("Address", GetType(String))
table.Columns.Add("City", GetType(String))
table.Columns.Add("Country", GetType(String))
table.Columns.Add("Photo", GetType(Byte()))
AddEmpoyees(table)
#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()
Dim adapter As New SqlCeDataAdapter("Select TOP(5) * from EmployeesReport", conn)
Dim table As New DataTable()
adapter.Fill(table)
table.TableName = "Employees"
adapter.Dispose()
conn.Close()
#End If
' Execute Mail Merge with groups.
document.MailMerge.ExecuteGroup(table)
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 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(), File.ReadAllBytes(CommonDataPath & "\Avatar.bmp")) ' city - Extension - last name. use "" since we already have full name in FirstName field - first name - Employee 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