Introduction to ExcelApplication API

This tutorial assumes that the coder knows how to set up an Excel Template using data markers and Excel cell formatting. The .XLSX template file is available here.

ExcelTemplate vs. ExcelApplication

This tutorial opens an Excel file formatted as an ExcelTemplate origin file (i.e. it contains formatted data markers) using ExcelApplication object. This means that the file can be copied based upon the user's specification using the ExcelApplication CopySheet method. This method copies a worksheet (including its formatted data markers) to another location in the workbook. The method has three parameters:

1. A Worksheet object representing the sheet to be copied,
2. An integer representing the new worksheet's position and,
3. The string representing the name of the new worksheet.

Setting up the template

The template file should look something like this:

One advanced data marker format has been used in the above template. The data marker entitled Sales Summary - %%=Header.FiscalYear references another cell for the content. The below image is the data marker that was input and is how ExcelWriter will identify the appropriate fields to bind data.

Cell N1 was hidden by using a custom format where the field was filled with ';;;'.

Adding an ExcelWriter Reference in Visual Studio

Create a .NET project and add a reference to the ExcelWriter library.

1. Open Visual Studio and create a .NET project.
   • The sample code uses a web application.
2. Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll
   • SoftArtisans.OfficeWriter.ExcelWriter.dll is located under Program Files > SoftArtisans > OfficeWriter > dotnet > bin

Writing the Code

1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind

2. In the method that will run the report, instantiate the ExcelApplication object.

3. Open the ExcelApplication with the ExcelApplication.Open() method and instantiate it as a Workbook object.

4. Assuming that you taking the selections from a ASP.NEt ListBox, using the following utility method, create a string list of all the countries that were selected.

5. ExcelApplication can make copies of a specified worksheet using the Worksheets.CopySheet() method. This method requires a Worksheet object representing the sheet to be copied; an integer representing the new worksheet's position and, the string representing the name of the new worksheet.

Insert the following code that iterates through the selected countries in the string list to make copies of the "SimpleTemplate" in the original template.xlsx file. Each new worksheet is placed after the "SimpleTemplate" sheet and is named based on the selected country from the string list. The <i>for<i> loop uses the <i>List<i>.Count method to determine how many copies must be made.

6. At this point, there is a worksheet named after each selected country and the original worksheet contained in the wb Workbook object. We need to hide the original worksheet using the Worksheet.Visibility method. This method can also make a worksheet 'visible' or 'very hidden.'

7. Select the first worksheet after the now hidden one to be displayed when the file first is opened using the Worksheets.Select() method.

8.