Excel Template Use

Following sample demonstrates creating new workbooks by using existing 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.

When using templates you can also use preservation (preservation of Excel objects not directly supported in the API), but only when source and destination format are the same.

For example, if you have a source XLSX file that contains a chart, then the chart will be preserved if the output file is also XLSX. However, if the output file format in that case is XLS or ODS, the chart will not be preserved.

Screenshot

Template Use Screenshot

See the full code below, use RUN EXAMPLE to execute.

Upload your file (Drag files here)

1using System;
2using System.Drawing;
3using System.Text;
4using GemBox.Spreadsheet;
5
6class Sample
7{
8    [STAThread]
9    static void Main(string[] args)
10    {
11        // If using Professional version, put your serial key below.
12        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
13
14        ExcelFile ef = ExcelFile.Load("TemplateUse.xlsx");
15
16        object[,] data = new object[,]
17        {
18            {new DateTime(2004,12,6), 8},
19            {new DateTime(2004,12,7), 9},
20            {new DateTime(2004,12,8), 8},
21            {new DateTime(2004,12,9), 7},
22            {new DateTime(2004,12,10), 7},
23
24            {new DateTime(2004,12,13), 9},
25            {new DateTime(2004,12,14), 8},
26            {new DateTime(2004,12,15), 9},
27            {new DateTime(2004,12,16), 9},
28            {new DateTime(2004,12,17), 6}
29        };
30
31        ExcelWorksheet ws = ef.Worksheets[0];
32
33        ws.Cells["J5"].SetValue(14);
34        ws.Cells["J6"].SetValue(new DateTime(2004, 12, 20));
35
36        ws.Cells["D12"].Value = "ACME Corp";
37        ws.Cells["D13"].Value = "240 Old Country Road, Springfield, IL";
38        ws.Cells["D14"].Value = "USA";
39        ws.Cells["D15"].Value = "Joe Smith";
40
41        ws.Cells["E18"].Value = "6-Dec-04 until 17-Dec-04.";
42
43        double sum = 0;
44        for (int i = 0; i < data.GetLength(0); i++)
45        {
46            ws.Cells[21 + i, 1].Value = data[i, 0];
47            ws.Cells[21 + i, 4].Value = data[i, 1];
48            int total = (int)data[i, 1] * (int)ws.Cells[21 + i, 5].Value;
49            ws.Cells[21 + i, 8].SetValue(total);
50            sum += total;
51        }
52        ws.Cells[21 + data.GetLength(0), 9].SetValue(sum);
53
54        ws.Cells["B36"].Value = "Payment via check.";
55
56        ef.Save("Template Use.xls");
57    }
58}
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("TemplateUse.xlsx")
13
14        Dim data(,) As Object = New Object(,) _
15        {
16            {New DateTime(2004, 12, 6), 8},
17            {New DateTime(2004, 12, 7), 9},
18            {New DateTime(2004, 12, 8), 8},
19            {New DateTime(2004, 12, 9), 7},
20            {New DateTime(2004, 12, 10), 7},
21            {New DateTime(2004, 12, 13), 9},
22            {New DateTime(2004, 12, 14), 8},
23            {New DateTime(2004, 12, 15), 9},
24            {New DateTime(2004, 12, 16), 9},
25            {New DateTime(2004, 12, 17), 6}
26        }
27
28        Dim ws As ExcelWorksheet = ef.Worksheets(0)
29
30        ws.Cells("J5").SetValue(14)
31        ws.Cells("J6").SetValue(New DateTime(2004, 12, 20))
32
33        ws.Cells("D12").Value = "ACME Corp"
34        ws.Cells("D13").Value = "240 Old Country Road, Springfield, IL"
35        ws.Cells("D14").Value = "USA"
36        ws.Cells("D15").Value = "Joe Smith"
37
38        ws.Cells("E18").Value = "6-Dec-04 until 17-Dec-04."
39
40        Dim sum As Double = 0
41        For i As Integer = 0 To data.GetLength(0) - 1
42            ws.Cells(21 + i, 1).Value = data(i, 0)
43            ws.Cells(21 + i, 4).Value = data(i, 1)
44            Dim total As Integer = data(i, 1) * ws.Cells(21 + i, 5).Value
45            ws.Cells(21 + i, 8).SetValue(total)
46            sum += total
47        Next
48        ws.Cells(21 + data.GetLength(0), 9).SetValue(sum)
49
50        ws.Cells("B36").Value = "Payment via check."
51
52        ef.Save("Template Use.xls")
53
54    End Sub
55
56End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.