Mail Merge from Excel in C# | Excel Mail Merge to Excel

Mail merge makes it easy to create personalized documents in bulk, such as letters, emails, invoices, or certificates. You start with a template and fill it using data from a spreadsheet. Each row in the Excel file generates a new document with the correct details in the right places. It is a smart way to automate repetitive tasks like sending out invoices or generating reports. In this post, we will show you how to perform a mail merge from Excel using C#. Let’s begin.

This article covers the following topics:

C# Mail Merge Excel Library

We will use the Aspose.Cells for .NET API to perform mail merge from Excel in C#. It offers powerful tools to work with Excel files directly in your code. It lets developers create, read, and update spreadsheets with ease. You can also handle advanced tasks like mail merge without hassle. The library makes Excel automation fast, flexible, and reliable.

To get started with Aspose.Cells for .NET, follow these simple installation instructions:

  1. Please download it from the releases.
  2. Install the library using NuGet Package Manager with the following command:
PM> Install-Package Aspose.Cells

How Does Mail Merge Work with Aspose.Cells?

Aspose.Cells doesn’t support “mail merge” in the same way as Word. It adds mail merge to Excel using Smart Markers. These markers act as placeholders in your spreadsheet, and the library replaces them with real data at runtime. As it processes each row from your data source, it fills in the template and expands the content automatically, letting you generate fully personalized documents with just a few lines of C# code.

&=DataSource.ColumnName

During processing, Aspose.Cells replaces these with actual data from a DataTable, List<T>, or any IEnumerable. For example, the library replaces &=Data.Name with values from the Name column in the Data table.

Prepare the Excel Template

Create an Excel file (e.g., Template.xlsx) with smart markers. Here’s a sample layout:

NameEmailAmount
&=Data.Name&=Data.Email&=Data.Amount

Step-by-Step Guide to Perform Mail Merge from Excel in C#

To perform a mail merge from Excel using Aspose.Cells for .NET, simply follow these steps:

Step 1: Load the Excel Template

Load the Excel template file that contains Smart Markers using the Workbook class. The Workbook class represents the entire Excel file (workbook). The loaded template file acts as the layout for your merged output.

Workbook workbook = new Workbook("Template.xlsx");

Step 2: Create a DataTable

Here, we create a DataTable that simulates your Excel data source. Each column matches a smart marker, and each row represents one set of values to insert. The DataTable class from System.Data holds the data you want to merge. Each column matches a smart marker, and each row provides a unique set of values.

DataTable dt = new DataTable("Data");
dt.Columns.Add("Name");
dt.Columns.Add("Email");
dt.Columns.Add("Amount");

dt.Rows.Add("Alice", "alice@example.com", 1000);
dt.Rows.Add("Bob", "bob@example.com", 1500);

We use the WorkbookDesigner class to connect the template with the data. The WorkbookDesigner is a special class in Aspose.Cells designed to handle smart markers and data binding. It links your data source (like a DataTable) with the Excel template. The SetDataSource() method binds the DataTable (Data) to the smart markers in the workbook.

WorkbookDesigner designer = new WorkbookDesigner(workbook);
designer.SetDataSource(dt);
  • SetDataSource() attaches the DataTable to the Smart Markers in the workbook.
  • "Data" is the name used in the markers like &=Data.Name.

Step 4: Execute the Mail Merge

Once you’ve set the data source, calling Process() fills in all the Smart Markers with real values from the DataTable. This command executes the mail merge. It goes through the Smart Markers and replaces them with actual values from the data source.

designer.Process();

Step 5: Save the File

Finally, we save the completed file with all the data merged in.

workbook.Save("MergedOutput.xlsx");

Save the Merged Output as a PDF

You can easily save the merged Excel document as a PDF with a single line of code.

// Set PDF options
PdfSaveOptions options = new PdfSaveOptions();

// Save as PDF
workbook.Save("MergedDocument.pdf", SaveFormat.Pdf);

Complete C# Code to Mail Merge Data

Output

The output Excel file contains a row for each entry with personalized data populated in place of smart markers.

Mail Merge from Excel in C# - Output

Mail Merge from Excel in C# - Output

Get a Free License

Get a free temporary license that lets you test the full features of Aspose.Cells for .NET without limitations. It is ideal if you are evaluating the API for automation tasks like mail merge, Excel reporting, or PDF export.

Excel to Excel Mail Merge: Free Resources

In addition to Excel mail merge, we provide various resources to help you deepen your understanding of Aspose.Cells for .NET. Check out our documentation, tutorials, and community forums for more insights.

Frequently Asked Questions (FAQs)

Q1: Can I use Excel mail merge without Microsoft Office installed?

Yes, mail merge does not require Microsoft Office. Aspose.Cells runs independently and handles everything through its own APIs.

Q2: What is the difference between Smart Markers and Word Merge Fields?

Smart Markers work within Excel and are more customizable for row-wise data expansion. Merge fields are Word-specific.

Q3: Can I merge from Excel to Word using Aspose?

Yes, but you’ll need Aspose.Words for that scenario.

Q4: Is it possible to automate PDF generation from Excel merges?

Absolutely. Just save the final workbook in PDF format using Aspose.Cells.

Conclusion

Mail merge from Excel offers a powerful way to generate dynamic, personalized documents using the familiar spreadsheet interface. In this blog post, we walked through the mail merge from Excel using C#. By using Aspose.Cells Smart Markers, you gain the flexibility to automate this process completely and generate thousands of invoices, certificates, or any structured reports.

If you have any questions or need further assistance, please feel free to reach out at our free support forum.

See Also