Articles in this section
Category / Section

Mail merge from Excel to Word in C#, VB.NET

3 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can export data from Excel worksheet to a Word document through MailMerge.

Steps to export data from Excel worksheet to a Word document through MailMerge:

Step 1: Create a new C# console application project.

Create a new C# console application project

Create a new C# console application project

Step 2: Install the Syncfusion.XlsIO.WinForms and Syncfusion.DocIO.WinForms NuGet packages as reference to your .NET Framework application from NuGet.org.

Install NuGet package to the project

Install NuGet package to the project

Install NuGet package to the project

Install NuGet package to the project

Step 3: Add an Excel file to you project and make it an embedded resource using the following steps.

  1. In Visual Studio, click the Project menu and select Add Existing Item. Find and select the Excel file you want to add to your project.
  2. In the Solution Explorer window, right-click on the Excel file you just added to your project and select Properties from the popup menu. The Properties tool window appears.
  3. In the Properties window, change the Build Action property to Embedded Resource.
  4. Build the project. The Excel file will be compiled into your project’s assembly.
  5. In the same way browse and add the Word document to your project and make it an Embedded Resource.

Step 4: To export the data from Excel document to Word document, first you should load the existing Excel document and export the data in that Excel document into DataTable. This can be achieved through ExportDataTable method of IWorksheet.

C#

//Export the data in Excel worksheet into DataTable and assign table name
dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

 

VB.NET

'Export the data in Excel worksheet into DataTable and assign table name
dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)

 

Step 5: Now load the existing Word document and export the data in DataTable into that Word document using Execute method available under MailMerge.

C#

//Export the data in DataTable into Word document through MailMerge
wordDocument.MailMerge.Execute(dataTable);

 

VB.NET

'Export the data in DataTable into Word document through MailMerge
wordDocument.MailMerge.Execute(dataTable)

 

Step 6: Include the following namespaces in Program.cs file.

C#

using Syncfusion.DocIO.DLS;
using Syncfusion.XlsIO;
using System.Data;
using System.IO;
using System.Reflection;

 

VB.NET

Imports Syncfusion.DocIO.DLS
Imports Syncfusion.XlsIO
Imports System.IO
Imports System.Reflection

 

Step 7: Include the following code snippet in main method of Program.cs file to export data from Excel document to Word document through MailMerge.

C#

using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the Excel application object
    IApplication application = excelEngine.Excel;
 
    //Load an existing Excel file into IWorkbook
    Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
    Stream excelStream = assembly.GetManifestResourceStream("ExportExcelToWord.NorthwindDataTemplate.xls");
    IWorkbook workbook = application.Workbooks.Open(excelStream, ExcelOpenType.Automatic);
 
    //Get the first worksheet in workbook into IWorksheet
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Initialize the DataTable
    DataTable dataTable = new DataTable();
 
    //Export the data in Excel worksheet into DataTable and assign table name
    dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
    dataTable.TableName = "Customers";
 
    //Load an existing Word document into WordDocument
    Stream wordStream = assembly.GetManifestResourceStream("ExportExcelToWord.CustomersReport.doc");
    WordDocument wordDocument = new WordDocument(wordStream);
 
    //Export the data in DataTable into Word document through MailMerge
    wordDocument.MailMerge.Execute(dataTable);
 
    //Save the Word document and close the instance of WrodDocument
    wordDocument.Save("Output.docx");
    wordDocument.Close();
}

 

VB.NET

Using excelEngine As ExcelEngine = New ExcelEngine()
    'Instantiate the Excel application object
    Dim application As IApplication = excelEngine.Excel
 
    'Load an existing Excel file into IWorkbook
    Dim assembly As Assembly = GetType(Module1).GetTypeInfo.Assembly
    Dim excelStream As Stream = assembly.GetManifestResourceStream("ExportExcelToWord.NorthwindDataTemplate.xls")
    Dim workbook As IWorkbook = application.Workbooks.Open(excelStream, ExcelOpenType.Automatic)
 
    'Get the first worksheet in workbook into IWorksheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Initialize the DataTable
    Dim dataTable As DataTable = New DataTable
 
    'Export the data in Excel worksheet into DataTable and assign table name
    dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
    dataTable.TableName = "Customers"
 
    'Load an existing Word document into WordDocument
    Dim wordStream As Stream = assembly.GetManifestResourceStream("ExportExcelToWord.CustomersReport.doc")
    Dim wordDocument As WordDocument = New WordDocument(wordStream)
 
    'Export the data in DataTable into Word document through MailMerge
    wordDocument.MailMerge.Execute(dataTable)
 
    'Save the Word document and close the instance of WrodDocument
    wordDocument.Save("Output.docx")
    wordDocument.Close()
End Using

 

A complete working sample to export data from Excel document to Word document can be downloaded from Export-ExcelToWord.zip.

By executing the program, you will get the output Word document as follows.

Output Word document

Output Word document

Take a moment to peruse the documentation where you will find other options like importing data to worksheets, exporting data from worksheet to DataTable, exporting data from worksheet to Collection Objects, importing data from Microsoft Grid Controls to worksheet with code examples.

Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments
Please sign in to leave a comment
Access denied
Access denied