Table of Contents

Introduction

The ExcelTemplate object is used for template-driven document generation. This object opens an ExcelWriter template file, populates it with data from a specified data source, and generates a new Excel workbook. An Excel file uploaded as an ExcelTemplate object is not directly modifiable at runtime.

The ExcelApplication object is an Excel file engine that can be used to create, open, modify, and save workbooks. A single instance of ExcelApplication can generate multiple Excel workbooks.

This tutorial opens an Excel template file formatted as for ExcelTemplate (i.e. it contains data markers) and makes customizations using ExcelApplication object, based on a user's selections. In particular, it highlights the functionality of the CopySheet method. Then data is bound to the template using ExcelTemplate, again, based on a user's selections.

This tutorial assumes a basic understanding of the ExcelTemplate object. If you have not familiar with creating an Excel template and binding data with ExcelTemplate, please go through the Simple Expense Summary tutorial first.

Setting up the template

In the downloadable C# project, there is a completed template file located in ExtendedSalesSummary/templates/template.xlsx.
A copy of the completed template file is also available here.

The template file should look something like this:

In the template, the %%=Header.FiscalYear data marker is 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.

Adding an ExcelWriter Reference in Visual Studio

In the sample code, the reference to SoftArtisans.OfficeWriter.ExcelWriter.dll has already been added to the ExtendedSalesSummary web application project.

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

  1. Open Visual Studio and create a .NET project.
  2. Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll

Writing the Code

The code behind for this part of the tutorial can be found under Extended Sales Summary/Part1.aspx.cs.

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

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;
private ExcelTemplate xlt;
private Workbook wb;

In the sample code, you will also see List<string> selectedCountries defined with the global variables. This is the list that will contain the countries the user selects from the web form in the sample code.

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()
{
            
}

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 the workbook contains a worksheet named after each selected country in addition to the original worksheet. Hide the original template sheet by setting Worksheet.Visibility. 

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

7. Select the first visible worksheet to be displayed when the file first is opened using Worksheets.Select().

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.

ExcelTemplate xlt = new ExcelTemplate();

9. Open the ExcelApplication workbook using the ExcelTemplate.Open(ExcelApplication, Workbook) method.

xlt.Open(xla, wb);

10. The use of function in an ExcelWriter code file is sometimes the most efficient way to approach a situation. In this case, a separate function is created to bind the data. This function is called in a for loop that is contained with the other code. Let the function that deals with data binding be called BindCountryData() and takes a worksheets name's as a string parameter.

for (int i = 0; i < wb.Worksheets.Count; i++)
{
     BindCountryData(wb.Worksheets[i].Name);
}

We will return to this function after completing the main code.

11. Call ExcelTemplate.Process() to import all data into the file.

xlt.Process();

12. Call ExcelTemplate.Save to save the output file.

ExcelTemplate has several output options: save to disk, save to a stream, stream the output file in a page's Response inline or as an attachment.

xlt.Save(Page.Response, "output.xlsx", false);

BindCountryData function

1. Create a function called BindCountryData that takes a string parameter called selection.

protected void BindCountryData(string selection){}

2. Create a DataBindingProperty based on the Worksheet name. DataBindingProperties is like a tag used to specify how data is bound to a worksheet.

Other DataBindingProperties include MaxRows and Transpose.

DataBindingProperties dbp = xlt.CreateDataBindingProperties();
dbp.WorksheetName = selection;

3. Create an string array for the header values and a string array for the column names.

ExcelTemplate can be bound to numerous types of .NET data structures: single variables, arrays (1-D, jagged, multi-dimensional), DataSet, DataTable, IDataReader etc. The source of the data can come from anywhere.

Some of the aforementioned structures have built in column names, such as the DataTable. When working with arrays, which don't have built in column names, you have to define the column names in a separate string array.

string[] specificInfo = { "FY 2008", "Foreign Trade Division", selection };
string[] headerTitles = { "FiscalYear", "TradeDivision", "Country" };

4. Use the ExcelTemplate.BindRowData method to bind the header data to the data markers in the template file (%%=Header.FiscalYear, %%=Header.TradeDivision, %%=Header.Country).

BindRowData() binds a single row of data to the template, but the data markers in the template do not need to be in a single row.

xlt.BindRowData(specificInfo, headerTitles, "Header", dbp);

5. Get the data for the Top 5 Expenses and All Expenses data sets.

DataTable dts = Sales(selection, "TOP 5");
DataTable dts2 = Sales(selection, "");

In this tutorial, it is assumed that your machine is equipped with AdventureWorks2008R2, and therefore that a SQL query is a valid operation. As a utility method, the following function will be included as a function outside of the Main and BindCountryData functions.

public static DataTable Sales(string selection, string topORall)
        {

            //Create the query
            string queryString = "USE AdventureWorks2008R2; " +
                "DECLARE @find nvarchar(100); " +
                "SET @find ='" + selection + "'; " +
                "SELECT " + topORall + " Sales.Store.Name AS Description, Sales.SalesOrderHeader.TotalDue AS Sales FROM Sales.Store " +
                "INNER JOIN Sales.Customer ON Sales.Store.BusinessEntityID = Sales.Customer.StoreID " +
                "INNER JOIN Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID " +
                "INNER JOIN Sales.SalesTerritory ON Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +
                "WHERE DATEPART(yyyy, Sales.SalesOrderHeader.OrderDate) = 2008 " +
                "AND Sales.SalesOrderHeader.OnlineOrderFlag = 0 " +
                "AND Sales.SalesTerritory.Name = @find " +
                "ORDER BY Sales DESC;";

            //Get connection
            SqlConnection connection = new SqlConnection(@"Data Source=TS-IH03\SQL2008R2;Initial Catalog=ExcelApp2;Integrated Security=True");

            //Assign command and associated connection
            SqlCommand cmd = new SqlCommand(queryString, connection);

            //Select data adapter
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;

            //Make a new table, fill it and return it
            DataTable dtGet = new DataTable();
            adapter.Fill(dtGet);
            return dtGet;
        }

If you do not have AdventureWorks2008R2, you may download the sample file here. The sample code uses a utility method called GetCSVData to obtain data from the supplied CSV files.

6. Use ExcelTemplate.BindData to bind the data for the Top and Details Sales data sets.

Recall that the data source names (Top, Details) need to match the data marker names exactly.

xlt.BindData(dts, "Top", dbp);
xlt.BindData(dts2, "Details", dbp);

7. Now you may run your code.

Here is an example of what the sample will look like:

Note that there are multiple worksheets, each named for each country and containing it's specified data.

Final Code

using SoftArtisans.Office.ExcelWriter;
using System.Data.SqlClient;
using System.Collections.Generic;
...
protected void Main(object sender, EventArgs e){
    ExcelApplication xla = new ExcelApplication();
    Workbook 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[0].Visibility = Worksheet.SheetVisibility.Hidden;
    wb.Worksheets[1].Select();

    xlt = new ExcelTemplate();
    xlt.Open(xla, wb);

    for (int i = 0; i < wb.Worksheets.Count; i++)
    {
          BindCountryData(wb.Worksheets[i].Name);
    }

    xlt.Process();
    xlt.Save(Page.Response, "output.xlsx", false);
}

protected void BindCountryData(string selection)
        {
            DataBindingProperties dbp = xlt.CreateDataBindingProperties();
            dbp.WorksheetName = selection;

            string[] specificInfo = { "FY 2008", "Foreign Trade Division", selection };

            string[] headerTitles = { "FiscalYear", "TradeDivision", "Country" };

            xlt.BindRowData(specificInfo, headerTitles, "Header", dbp);

            DataTable dts = Sales(selection, "TOP 5");
            DataTable dts2 = Sales(selection, "");
            xlt.BindData(dts, "Top", dbp);
            xlt.BindData(dts2, "Details", dbp);
        }

public static DataTable Sales(string selection, string topORall)
        {

            //Create the query
            string queryString = "USE AdventureWorks2008R2; " +
                "DECLARE @find nvarchar(100); " +
                "SET @find ='" + selection + "'; " +
                "SELECT " + topORall + " Sales.Store.Name AS Description, Sales.SalesOrderHeader.TotalDue AS Sales FROM Sales.Store " +
                "INNER JOIN Sales.Customer ON Sales.Store.BusinessEntityID = Sales.Customer.StoreID " +
                "INNER JOIN Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID " +
                "INNER JOIN Sales.SalesTerritory ON Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +
                "WHERE DATEPART(yyyy, Sales.SalesOrderHeader.OrderDate) = 2008 " +
                "AND Sales.SalesOrderHeader.OnlineOrderFlag = 0 " +
                "AND Sales.SalesTerritory.Name = @find " +
                "ORDER BY Sales DESC;";

            //Get connection
            SqlConnection connection = new SqlConnection(@"Data Source=TS-IH03\SQL2008R2;Initial Catalog=ExcelApp2;Integrated Security=True");

            //Assign command and associated connection
            SqlCommand cmd = new SqlCommand(queryString, connection);

            //Select data adapter
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;

            //Make a new table, fill it and return it
            DataTable dtGet = new DataTable();
            adapter.Fill(dtGet);
            return dtGet;
        }


protected List<string> GetListBoxSelections(){
        //Get the ListBox selections and make the appropriate number of copies
            List<string> countryNames = new List<string>();
            foreach (int i in ListBox1.GetSelectedIndices())
            {
                countryNames.Add(ListBox1.Items[i].Text);
            }
            return countryNames;
        }

Downloads

You can download the code for the Extended Sales Summary here.

Next Steps

Continue on to Part 2 - Adding a Coversheet