Excel Template Use
GemBox.Spreadsheet is a .NET component that enables developers to read, write, convert and print spreadsheet files (XLSX, XLS, ODS, CSV, HTML, PDF or XPS) from their .NET applications using one simple API. GemBox.Spreadsheet Free is free of charge while GemBox.Spreadsheet Professional is a commercial version licensed per developer.
The following example demonstrates how to 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 demonstrates 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.
Screenshot
See the full code below, use RUN EXAMPLE to execute.
1using System;
2using System.Collections.Generic;
3using System.Drawing;
4using System.Text;
5using GemBox.Spreadsheet;
6
7class Sample
8{
9 [STAThread]
10 static void Main(string[] args)
11 {
12 // If using Professional version, put your serial key below.
13 SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
14
15 ExcelFile ef = ExcelFile.Load("Template.xlsx");
16
17 int workingDays = 8;
18
19 DateTime startDate = DateTime.Now.AddDays(-workingDays);
20 DateTime endDate = DateTime.Now;
21
22 ExcelWorksheet ws = ef.Worksheets[0];
23
24 // Find cells with placeholder text and set their values.
25 int row, column;
26 if (ws.Cells.FindText("[Company Name]", true, true, out row, out column))
27 ws.Cells[row, column].Value = "ACME Corp";
28 if (ws.Cells.FindText("[Company Address]", true, true, out row, out column))
29 ws.Cells[row, column].Value = "240 Old Country Road, Springfield, IL";
30 if (ws.Cells.FindText("[Start Date]", true, true, out row, out column))
31 ws.Cells[row, column].Value = startDate;
32 if (ws.Cells.FindText("[End Date]", true, true, out row, out column))
33 ws.Cells[row, column].Value = endDate;
34
35 // Copy template row.
36 row = 17;
37 ws.Rows.InsertCopy(row + 1, workingDays - 1, ws.Rows[row]);
38
39 // Fill inserted rows with sample data.
40 var random = new Random();
41 for (int i = 0; i < workingDays; i++)
42 {
43 ExcelRow currentRow = ws.Rows[row + i];
44 currentRow.Cells[1].SetValue(startDate.AddDays(i));
45 currentRow.Cells[2].SetValue(random.Next(1, 12));
46 }
47
48 // Calculate formulas in worksheet.
49 ws.Calculate();
50
51 ef.Save("Template Use.xls");
52 }
53}
1Imports System.Text
2Imports System.Drawing
3Imports GemBox.Spreadsheet
4
5Module Samples
6
7 Sub Main()
8
9 ' If using Professional version, put your serial key below.
10 SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
11
12 Dim ef As ExcelFile = ExcelFile.Load("Template.xlsx")
13
14 Dim workingDays As Integer = 8
15
16 Dim startDate As DateTime = DateTime.Now.AddDays(-workingDays)
17 Dim endDate As DateTime = DateTime.Now
18
19 Dim ws As ExcelWorksheet = ef.Worksheets(0)
20
21 ' Find cells with placeholder text and set their values.
22 Dim row As Integer, column As Integer
23 If ws.Cells.FindText("[Company Name]", True, True, row, column) Then
24 ws.Cells(row, column).Value = "ACME Corp"
25 End If
26 If ws.Cells.FindText("[Company Address]", True, True, row, column) Then
27 ws.Cells(row, column).Value = "240 Old Country Road, Springfield, IL"
28 End If
29 If ws.Cells.FindText("[Start Date]", True, True, row, column) Then
30 ws.Cells(row, column).Value = startDate
31 End If
32 If ws.Cells.FindText("[End Date]", True, True, row, column) Then
33 ws.Cells(row, column).Value = endDate
34 End If
35
36 ' Copy template row.
37 row = 17
38 ws.Rows.InsertCopy(row + 1, workingDays - 1, ws.Rows(row))
39
40 ' Fill inserted rows with sample data.
41 Dim random As New Random()
42 For i As Integer = 0 To workingDays - 1
43 Dim currentRow As ExcelRow = ws.Rows(row + i)
44 currentRow.Cells(1).SetValue(startDate.AddDays(i))
45 currentRow.Cells(2).SetValue(random.Next(1, 12))
46 Next
47
48 ' Calculate formulas in worksheet.
49 ws.Calculate()
50
51 ef.Save("Template Use.xls")
52
53 End Sub
54
55End Module
Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.