Excel Template Use
The following example shows how you can create new workbooks using an existing Excel workbook as a template.
The procedure is simple:
- load a file,
- fill it in with your data,
- and then save it as a new file.
The example also shows how to use the InsertCopy method for copying an Excel row.
The template row can define the desired styling, number formatting, formulas, etc. When GemBox.Spreadsheet is inserting copied rows, it will automatically adjust the formulas of those rows and also any other formula in the workbook that is affected by that insertion.
using System;
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
int workingDays = %WorkingDays%;
var startDate = DateTime.Now.AddDays(-workingDays);
var endDate = DateTime.Now;
var worksheet = workbook.Worksheets[0];
// Find cells with placeholder text and set their values.
int row, column;
if (worksheet.Cells.FindText("[Company Name]", true, true, out row, out column))
worksheet.Cells[row, column].Value = "ACME Corp";
if (worksheet.Cells.FindText("[Company Address]", true, true, out row, out column))
worksheet.Cells[row, column].Value = "240 Old Country Road, Springfield, IL";
if (worksheet.Cells.FindText("[Start Date]", true, true, out row, out column))
worksheet.Cells[row, column].Value = startDate;
if (worksheet.Cells.FindText("[End Date]", true, true, out row, out column))
worksheet.Cells[row, column].Value = endDate;
// Copy template row.
row = 17;
worksheet.Rows.InsertCopy(row + 1, workingDays - 1, worksheet.Rows[row]);
// Fill inserted rows with sample data.
var random = new Random();
for (int i = 0; i < workingDays; i++)
{
var currentRow = worksheet.Rows[row + i];
currentRow.Cells[1].SetValue(startDate.AddDays(i));
currentRow.Cells[2].SetValue(random.Next(1, 12));
}
// Calculate formulas in worksheet.
worksheet.Calculate();
workbook.Save("Template Use.%OutputFileType%");
}
}Imports System
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
Dim workingDays As Integer = %WorkingDays%
Dim startDate = DateTime.Now.AddDays(-workingDays)
Dim endDate = DateTime.Now
Dim worksheet = workbook.Worksheets(0)
' Find cells with placeholder text and set their values.
Dim row As Integer, column As Integer
If worksheet.Cells.FindText("[Company Name]", True, True, row, column) Then
worksheet.Cells(row, column).Value = "ACME Corp"
End If
If worksheet.Cells.FindText("[Company Address]", True, True, row, column) Then
worksheet.Cells(row, column).Value = "240 Old Country Road, Springfield, IL"
End If
If worksheet.Cells.FindText("[Start Date]", True, True, row, column) Then
worksheet.Cells(row, column).Value = startDate
End If
If worksheet.Cells.FindText("[End Date]", True, True, row, column) Then
worksheet.Cells(row, column).Value = endDate
End If
' Copy template row.
row = 17
worksheet.Rows.InsertCopy(row + 1, workingDays - 1, worksheet.Rows(row))
' Fill inserted rows with sample data.
Dim random As New Random()
For i As Integer = 0 To workingDays - 1
Dim currentRow = worksheet.Rows(row + i)
currentRow.Cells(1).SetValue(startDate.AddDays(i))
currentRow.Cells(2).SetValue(random.Next(1, 12))
Next
' Calculate formulas in worksheet.
worksheet.Calculate()
workbook.Save("Template Use.%OutputFileType%")
End Sub
End Module