ReplaceValuesInExcel Method

Open an excel file and replace values in it and save it to another file. This is useful for having an Excel template and creating a report from it like mail merge

Syntax

Visual Basic (Declaration)
Visual Basic (Usage)
C#
C++/CLI

Public Sub ReplaceValuesInExcel( _
   ByVal sourceFilePath As String, _
   ByVal destFilePath As String, _
   ByVal replacementValues As Dictionary(Of String,String) _
)

Dim instance As ExcelReportsGenerator
Dim sourceFilePath As String
Dim destFilePath As String
Dim replacementValues As Dictionary(Of String,String)
 
instance.ReplaceValuesInExcel(sourceFilePath, destFilePath, replacementValues)

public void ReplaceValuesInExcel( 
   string sourceFilePath,
   string destFilePath,
   Dictionary<string,string> replacementValues
)

public:
void ReplaceValuesInExcel( 
   String^ sourceFilePath,
   String^ destFilePath,
   Dictionary<String^,String^>^ replacementValues
)

Parameters

sourceFilePath: The Excel File to Read
destFilePath: The Excel File to Write
replacementValues: The values to search for in cells and replace

Example

ExcelReportsGenerator generator = new ExcelReportsGenerator(); //Trial Mode
//ExcelReportsGenerator generator = new ExcelReportsGenerator("place user name here", "place license key here"); //License Mode
 
string inputTemplatePath = "InputTemplateExample.xlsx";
 
Workbook workbook = generator.WorkbookFactory(inputTemplatePath);
 
workbook.Author = "John Smith";
workbook.CreatedTime = DateTime.Now;
workbook.LastSavedBy = "John Smith";
workbook.Subject = "This is a subject";
workbook.Title = "This is a title";
 
Worksheet worksheet = generator.AddWorksheet(workbook, "Employees");
 
List<Employee> employees = new List<Employee>();
 
Employee employee1 = new Employee();
employee1.FirstName = "John";
employee1.LastName = "Smith";
employee1.Married = true;
employee1.NumberOfChildren = 3;
employee1.Salary = 60000;
employee1.HealthSavingsContribution = 6250;
employee1.EmployeeType = EmployeeType.LeadAccountant;
employee1.RetirementContributionPercent = .06F;
employee1.YearsOfService = 4;
employee1.QuarterlyMeetingAttendance = 15;
employee1.CompanyCarMileage = 152000;
employee1.VacationHours = 120;
employee1.SickHours = 40;
employee1.PersonalHours = 8;
employee1.BirthDate = new DateTime(1977, 12, 15);
employees.Add(employee1);
 
Employee employee2 = new Employee();
employee2.FirstName = "Joe";
employee2.LastName = "Jones";
employee2.Married = false;
employee2.NumberOfChildren = 0;
employee2.Salary = 30000;
employee2.HealthSavingsContribution = 3000;
employee2.EmployeeType = EmployeeType.MediumJanitor;
employee2.RetirementContributionPercent = .03F;
employee2.YearsOfService = 1;
employee2.QuarterlyMeetingAttendance = 4;
employee2.CompanyCarMileage = 0;
employee2.VacationHours = 80;
employee2.SickHours = 20;
employee2.PersonalHours = 8;
employee2.BirthDate = new DateTime(1988, 6, 25);
employees.Add(employee2);
 
generator.FillFromClassList(worksheet, employees);
 
generator.SaveWorkbook(workbook);
 
Dictionary<string,string> replacementValues = new Dictionary<string, string>();
replacementValues.Add("Smith", "Kennedy");
 
generator.ReplaceValuesInExcel(inputTemplatePath, "ReplaceValuesInExcelExample.xlsx", replacementValues);

VB.NET

Dim generator As New ExcelReportsGenerator() 'Trial Mode
'Dim generator As New ExcelReportsGenerator("place user name here", "place license key here") 'License Mode
 
Dim inputTemplatePath As String = "InputTemplateExample.xlsx"
 
Dim workbook As Workbook = generator.WorkbookFactory(inputTemplatePath)
 
workbook.Author = "John Smith"
workbook.CreatedTime = Date.Now
workbook.LastSavedBy = "John Smith"
workbook.Subject = "This is a subject"
workbook.Title = "This is a title"
 
Dim worksheet As Worksheet = generator.AddWorksheet(workbook, "Employees")
 
Dim employees As New List(Of Employee)()
 
Dim employee1 As New Employee()
employee1.FirstName = "John"
employee1.LastName = "Smith"
employee1.Married = True
employee1.NumberOfChildren = 3
employee1.Salary = 60000
employee1.HealthSavingsContribution = 6250
employee1.EmployeeType = EmployeeType.LeadAccountant
employee1.RetirementContributionPercent =.06F
employee1.YearsOfService = 4
employee1.QuarterlyMeetingAttendance = 15
employee1.CompanyCarMileage = 152000
employee1.VacationHours = 120
employee1.SickHours = 40
employee1.PersonalHours = 8
employee1.BirthDate = New Date(1977, 12, 15)
employees.Add(employee1)
 
Dim employee2 As New Employee()
employee2.FirstName = "Joe"
employee2.LastName = "Jones"
employee2.Married = False
employee2.NumberOfChildren = 0
employee2.Salary = 30000
employee2.HealthSavingsContribution = 3000
employee2.EmployeeType = EmployeeType.MediumJanitor
employee2.RetirementContributionPercent =.03F
employee2.YearsOfService = 1
employee2.QuarterlyMeetingAttendance = 4
employee2.CompanyCarMileage = 0
employee2.VacationHours = 80
employee2.SickHours = 20
employee2.PersonalHours = 8
employee2.BirthDate = New Date(1988, 6, 25)
employees.Add(employee2)
 
generator.FillFromClassList(worksheet, employees)
 
generator.SaveWorkbook(workbook)
 
Dim replacementValues As New Dictionary(Of String,String)()
replacementValues.Add("Smith", "Kennedy")
 
generator.ReplaceValuesInExcel(inputTemplatePath, "ReplaceValuesInExcelExample.xlsx", replacementValues)

Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

Reference

ExcelReportsGenerator Class
ExcelReportsGenerator Members