Instantiate Excel templates

Aspose.Cells Product Family
1

What is the correct way to create ‘instances’ of .xlt and .xltx workbook templates?

What I would like to do:
I have a .xltx file wich I would like to use as a template for multiple .xlsx output files.


A first try was:

ExcelTemplate excelTemplate = new Workbook(“C:\Temp\Test.xltx”);

Workbook newWorkbookInstance = new Workbook();

newWorkbookInstance.Copy(excelTemplate.WorkbookTemplate);

But ‘Copy’ copies only a small part of all workbook properties.

I could also load the template from the filesystem for each outputfile,
but that doesn’t feel right :wink:

Is there a better way?

2

Hi,

Well, you are doing fine, you may just load your template Excel file(XLTX etc.) and then save it as to other file formats e.g. XLSX by using Workbook.Save() method.

If you find any issue in the output XLSX file, let us know with all the details, template files, output files here, we will check your issue soon.

Thank you.

3

Hi,

And, there is no ExcelTemplate class in Aspose.Cells APIs set, so please only use Aspose.Cells.Workbook class:

e.g

Workbook excelTemplate = new Workbook("C:\\Temp\\Test.xltx");
excelTemplate.Save("C:\\Temp\\outTest1.xlsx", SaveFormat.Xlsx);

Thank you.

4

Hi Amjad,

thanks for your fast answer! So the basic steps:
Load template xltx
write data
store as xlsx
are correct.


The second part is that I have one template and I would like to fill it multiple times say 100 times with different data and save the results.

I could use a loop like:

foreach(data in datasets[100])
{
Load template xltx
write data
store as xlsx
}

But I would like to avoid to load the template 100 times.
So I would need a copy/clone/instantiate method to ‘duplicate’ the loaded template 100 times.

Is there a possiblity to do this or do I have to load the template 100 times?





5

Hi,

If you write or update data in the worksheet cells differently for different output XLSX files, you do not need to open the Excel file again and again. For example, if you need some part of data or contents should be modified or updated/inserted, you may just save the files for different output XLSX files, e.g. you may save Book1.xlsx using Workbook.Save() after inserting date/contents and then without reloading the original Excel template, you may directly update those cells' data and then again call Workbook.Save() to save Book2.xlsx copy and so on.

Alternatively you may use Workbook.Copy() method but again, it would load entire Excel template files' data into a new Workbook, anyways, you may use it if you want.

Thank you.

6

Hi Amjad,

acctually there is more done than just writing data (e.g. Formating, Setting Document Properties, …) the modifications differ form dataset to dataset so they are not automatically overwritten. Which is why it’s not possible to reuse the modified file.

The Workbook.Copy method only copies only data but not other attributes like Document Properties or Format Specifications. Thats why I can’t use it.

If these are the only options I’ll go with the reload of the template for each output.

Thanks for your help!

7

Hi Thomas,

Thank you for your feedback.

Workbook.Copy method copies all data, format specifications and the document properties to another instance of Workbook. Please try the simplest code snippet as provided below with your sample template, and compare the two workbooks on your end.

C#


Workbook excelTemplate = new Workbook(myDir+“book1.xltx”);
Workbook newWorkbookInstance = new Workbook();
newWorkbookInstance.Copy(excelTemplate);
newWorkbookInstance.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);


I would recommend you to use the latest version of Aspose.Cells for .NET v7.6.0 assemblies for your testing.
8

Hi Thomas,

Well, in that case, you have to reload the template file and input/update data with other formatting and other options accordingly to resave to XLSX file format.

Thank you.

9

Hi Amjad,

I tried the code

Workbook excelTemplate = new Workbook(@“C:\Temp\TestDocProperties2.xltx”);

Workbook newWorkbookInstance = new Workbook();

newWorkbookInstance.Copy(excelTemplate);

newWorkbookInstance.Save(@“C:\Temp\TestDocProperties2.xlsx”, SaveFormat.Xlsx);

With my sample template (see attached file).
But BuildInDocumentProperties, the background image and the custom table format
have not been copied from TestDocProperties2.xltx to TestDocProperties2.xlsx.

I used Aspose.Cells version 7.6
Is this a bug?

10

Hi,


Thanks for providing us the template XLTX file.

Yes, you are right, Workbook.Copy has limitations which does not copy Background image and custom document properties into the new workbook, so you 'd better to load the template file and save it as XLSX file format.

Thank you.