Using templates in excel with C#

Question

As part of the c# program that I am producing I need to generate 1 workbook containing; 2 different worksheets and a 3rd that could be produced any number of times, what is the most effective way of doing this? I have looked into using templates although I am unsure how to repeat certain worksheets whilst only displaying others once. Any help or advice would be appreciated.

Answer 1

A simple way is to make a hands off template example workbook with the three worksheets. Then make a copy of it. Open both and re-copy worksheet number 3 on to the working workbook as a new worksheet as needed.

In response to the comment:

There are a couple of excel engines in a .net component products our there like spreadsheet gear or aspose cells. But if your application is a windows form based and where the application is guaranteed to run has office you can use office automation. You can't legally use office automation on a web server, but it is just as possible on a web server as on a client desktop. I've used the aspose cells and it's very easy to work with and very capable and a little less expensive than spreadsheet gear, but spreadsheet gear does also have a good reputation. Both of those components have very good documentation on how to do anything with excel. But if you have excel and want to use office automation, be sure to look for example code on the web on how to properly close excel from c# or vb.net. There are some tricks to getting it to close properly.

Answer 2

SpreadsheetGear for .NET has ISheet.CopyAfter / CopyBefore methods which enable you to copy an entire worksheet within a workbook or between workbooks.

You can see an example of duplicating a single worksheet multiple times in the Worksheet with Chart to Multiple Worksheets with Charts sample on the SpreadsheetGear / Excel Reporting Samples page here.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

I have done this before with templates. I would create a template xls with the first two worksheets that you don’t want changed, then add a 3rd worksheet that you could copy to the end of the workbook (as you need more worksheets).

If you know ahead of time how many of the 3rd worksheet you need, then you can copy them to the end and delete the template 3rd slot.

     ExcelTemplateManager t = new ExcelTemplateManager(template_path, log_path);
     t.CopyWorksheetToEnd(3);
     t.CopyWorksheetToEnd(3);
     t.RemoveAtIndexWorksheet(3);
     t.SetSomeValue(3);
     t.SetSomeValue(4);
     t.Close();

If you don’t know, then keep the template around to copy it to the end as needed, then when you are done, just remove the 3rd worksheet template.

     ExcelTemplateManager t = new ExcelTemplateManager(template_path, log_path);
     t.CopyWorksheetToEnd(3);
     t.SetSomeValue(4);
     t.CopyWorksheetToEnd(3);
     t.SetSomeValue(5);
     t.RemoveAtIndexWorksheet(3);
     t.Close();

I used the Microsoft.Office.Interop.Excel dll to create my ExcelTemplateManger class. The basic idea is to create a copy of the template excel file, and work off the copy. Let me know if you need help setting that part up, but it should be too bad.