...

In the template, the %%=Header.FiscalYear data marker is referenced in a formula. concatenated with "Sales Summary -". Data markers cannot be used directly in formulas; the data marker needs to be in a separate cell, which can be referenced in an Excel formula.

In the header, there is a formula =CONCATENATE("Sales Summary - ", N1), where N1 is the cell that actually contains the %%=Header.FiscalYear data marker. This is shown in the image below.

START HERE

Adding an ExcelWriter Reference in Visual Studio

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

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

Writing the Code

...

Main function

There are two main sections of code that will be covered:

• ExcelApplication code to customize the template based on a list of countries that a user has selected
  • In the sample, this code is contained in the GenerateTemplate() method
• ExcelTemplate code to bind data to the customized template
  • In the sample, this code is contained in the PopulateTemplate() method

Getting Started

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

using SoftArtisans.Office.ExcelWriter;

2. At the top of the class definition, define global variations for the ExcelApplication, ExcelTemplate, and Workbook objects:

private ExcelApplication xla;

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

ExcelApplication xla = new ExcelApplication();

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

Workbook wb = xla.Open(Page.MapPath(@"templates\template.xlsx"));

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

protected List<string> GetListBoxSelections(){
        //Get the ListBox selections and make the appropriate number of copies
            List<string> countryNames = new List<string>();
private ExcelTemplate xlt;
private Workbook wb;

Customizing the template

1. Define a method to contain the ExcelApplication code for customizing the sheet. In the sample, this method is called GenerateTemplate()

//Use ExcelApplication to make a copy of a regional worksheet for each
//country that is selected by the user. 
protected void GenerateTemplate()
{
           foreach (int
i in ListBox1.GetSelectedIndices())
            {
                countryNames.Add(ListBox1.Items[i].Text);
            }
            return countryNames;
        }

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.

...

}

2. Instantiate the ExcelApplication object.

ExcelApplication xla = new ExcelApplication();

3. Open the Workbook template file with ExcelApplication.Open(ExcelTemplate) method.

Workbook wb = xla.Open(Page.MapPath(@"templates\template.xlsx"));

4. In the sample, the user selects anywhere from 1-4 countries to include in the report. The selected countries are stored in the List<string> object, selectedCountries. For each country, make a copy of the basic template sheet with Worksheets.CopySheet(), place the copied worksheet at the end of the workbook, and give the new sheet a name.

In this example, the sheet that needs to be copied is the first worksheet in the template file. It can be accessed through Workbook.Worksheets by index (0) or by name ("SimpleTemplate").

for (int i = 0; i < selectedCountries.Count; i++)
{
    wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count, selectedCountries[i]);
}

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

wb.Worksheets[0].Visibility = Worksheet.SheetVisibility.Hidden;

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

wb.Worksheets[1].Select();

8. The final code for the GenerateTemplate() method should look like this:

protected void GenerateTemplate()
{
	xla = new ExcelApplication();
	wb = xla.Open(Page.MapPath(@"templates\template.xlsx"));

	for (int i = 0; i < selectedCountries.Count; i++)
	{
		wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count, selectedCountries[i]);
	}
						
	wb.Worksheets["SimpleTemplate"].Visibility = Worksheet.SheetVisibility.Hidden;
						
	wb.Worksheets[1].Select();
}

8. Instantiate a new ExcelTemplate object.

...