Mail merge from Excel to Word in C#, VB.NET
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
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
Step 3: Add an Excel file to you project and make it an embedded resource using the following steps.
- 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.
- 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.
- In the Properties window, change the Build Action property to Embedded Resource.
- Build the project. The Excel file will be compiled into your project’s assembly.
- 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
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.
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.