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.

 Contents ||
| {toc:maxLevel=2} |


h1. 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|EW8:Worksheets.CopySheet(Worksheet, Int32, String)]}} method. Then data is bound to the template using {{ExcelTemplate}}, again, based on a user's selections.

{note}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.{note}

h2. Setting up the template

{info:title=Following the Sample Code}In the downloadable [C# project|Basic Tutorials^ExcelWriter_BasicC#.zip], there is a completed template file located in _ExtendedSalesSummary/templates/template.xlsx_.

A copy of the completed template file is also available [here

 below.

!Actual_Template.png|border=1,width=700!


h2. Adding an ExcelWriter Reference in Visual Studio

{info:title=Following the Sample Code}
In the sample code, the reference to {{SoftArtisans.OfficeWriter.ExcelWriter.dll}} has already been added to the _ExtendedSalesSummary_ web application project.

{info}

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

 bin*

h1. Writing the Code

{info:title=Following the Sample Code}The code behind for this part of the tutorial can be found under _Extended Sales Summary/Part1.aspx.cs_.

{info}

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

h2. Getting Started

1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind:
{newcode}using SoftArtisans.Office.ExcelWriter;{newcode}

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

{newcode}
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.

{newcode}

{info:title=Following the Sample Code}
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. 
{info}

h2. Customizing the template

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

{newcode}
//Use ExcelApplication to make a copy of a regional worksheet for each
//country that is selected by the user. 
protected void GenerateTemplate()
{
            
}
{newcode}


2. Instantiate the {{ExcelApplication}} object.

{newcode}ExcelApplication xla = new ExcelApplication();{newcode}

3. Open the {{Workbook}} template file with {{[ExcelApplication.Open(ExcelTemplate)]}} method.

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

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()|EW8:Worksheets.CopySheet(Worksheet, Int32, String)]}}, place the copied worksheet at the end of the workbook, and give the new sheet a name.

 

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


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

]}}. 

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

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

|EW8:Worksheets.Select(Object())]}}.

{newcode}wb.Worksheets[1].Select();{newcode}


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

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

{newcode}


h2. Binding Data Dynamically

1. Define a method to contain the {{ExcelTemplate}} code for binding the data to the template. In the sample, this method is called {{PopulateTemplate()}}

{newcode}
//Use ExcelTemplate to bind data for each selected country
//to worksheets in the template, then populate the report
//with that data
protected void PopulateTemplate()
{
            
}
{newcode}

2. Instantiate a new {{ExcelTemplate}} object.

{newcode}ExcelTemplate xlt = new ExcelTemplate();

|EW8:ExcelTemplate.Open(ExcelApplication, Workbook)].

{newcode}xlt.Open(xla, wb);

{newcode}

4. Although not necessary, it may be useful to set {{[ExcelTemplate.RemoveExtraDataMarkers]}} to {{true}}. This will tell ExcelWriter to ignore any data markers that are not bound to data sets. This is helpful if you are adding the data binding calls iteratively.

{newcode}xlt.RemoveExtraDataMarkers = true;{newcode}

5. For each selected country, data needs to be bound to the corresponding worksheet. Define a {{[DataBindingProperties]}} object for future use and set up a {{for}} loop to go through all the selected countries. 

{newcode}
DataBindingProperties dataBindProps;

for (int i = 01; i < wbselectedCountries.Worksheets.Count; i++)
{

}
{newcode}

 BindCountryData(wb.Worksheets[i].Name);
}

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

xlt.Process();

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

protected void BindCountryData(string selection){}

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

string[] specificInfoThe next few steps relate to code contained in the {{for}} loop. 

*For each selected country:*

6. Retrieve the name of the country.
{newcode}string country = selectedCountries[i];{newcode}

7. Instantiate a new {{DataBindingProperties}} object. 
{newcode}dataBindProps = xlt.CreateDataBindingProperties();{newcode}

8. When a data set is bound to an Excel template, any data markers with matching syntax will be populated with the data from that data set. This can be problematic if your template contains copied sheets, where all the data markers are identical. 

To get around this, set the {{[DataBindingProperties.WorksheetName]}} to bind a data set only to a particular worksheet.
{newcode}dataBindProps.WorksheetName = country;{newcode}

9. 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.

{newcode}string[] headerValues = { "FY 2008", "Foreign Trade Division", selectioncountry };
string[] headerTitlesheaderNames = { "FiscalYear", "TradeDivision", "Country" };

{newcode}xlt.BindRowData(specificInfo, headerTitles, "Header", dbpdataBindProps);

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

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{newcode}

11. Get the data for the Top and Details Sales data sets. 

{info:title=Following the Sample} In the sample project, we are parsing CSV files with query results, rather than querying a live database. The CSV files are available under the _data_ directory. There is a copy of the CSV parser, {{GenericParsing.dll}} in the _bin_ directory of the project {{GetCSVData}} is defined in _Part1.aspx.cs_ in a {{region}} marked _Utility Methods_. {info}

These calls are to a helper method {{GetCSVData}} that parses the CSV files and returns a {{DataTable}} with the values.

{newcode}
DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));
{newcode}

If you are following in your own project and would like to parse the CSV files as well, you will need to:
* Add a reference to {{GenericParsing.dll}}
* Include {{GeneringParsing}} at the top of your code.
* Add the {{GetCSVData}} method that can be found in the sample code.


12. Use {{[EW8: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.

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

13. The final {{for}} loop should look like this:

{newcode}
for (int i = 1; i < selectedCountries.Count; i++)
{
    string country = selectedCountries[i];

    dataBindProps = xlt.CreateDataBindingProperties();

    dataBindProps.WorksheetName = country;

    string[] headerValues = { "FY 2008", "Foreign Trade Division", country };
    string[] headerNames = { "FiscalYear", "TradeDivision", "Country" };

    xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps);

    DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
    DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));

     xlt.BindData(dts, "Top", dataBindProps);
     xlt.BindData(dts2, "Details", dataBindProps);
}
{newcode}


14. Call [ExcelTemplate.Process()|EW8:ExcelTemplate.Process()] to import all data into the file.

{newcode}xlt.Process();{newcode}

15. Call [EW8: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.

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

16. The final code for {{PopulateTemplate()}} should look like this:
{new code}
protected void PopulateTemplate()
{
            xlt SqlCommand cmd = new SqlCommandExcelTemplate(queryString, connection); //Create a new ExcelTemplate object

            //SelectPass datathe adapterexisting ExcelApplication object and open Workbook
       SqlDataAdapter adapter = new SqlDataAdapter();
    //to the ExcelTemplate object
        adapter.SelectCommand = cmd    xlt.Open(xla, wb);

            //MakeRemoveExtraDataMarkers amakes newall table,data fillmarkers itoptional
and return it          //so ExcelWriter will DataTablenot dtGetthrow =an new DataTable();
  error if any data markers
         adapter.Fill(dtGet);   //aren't bound to data
      return dtGet;     xlt.RemoveExtraDataMarkers = true;

} 

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.

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

using SoftArtisans.Office.ExcelWriter;
using System.Data.SqlClient;
using System.Collections.Generic;
...
protected void Main(object sender, EventArgs e){     //For each country, retrieve and bind data to the worksheet
          ExcelApplication xla = new ExcelApplication()DataBindingProperties dataBindProps;

   Workbook wb = xla.Open(Page.MapPath(@"templates\template.xlsx"));      for (int i = 01; i < selectedCountries.Count; i++)
            {
         wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count,       string country = selectedCountries[i]);

   }      wb.Worksheets[0].Visibility = Worksheet.SheetVisibility.Hidden;     wb.Worksheets[1].Select();

    xlt = new ExcelTemplate();//Create a new DataBindProperties object
                dataBindProps = xlt.OpenCreateDataBindingProperties(xla, wb);

    for (int i = 0; i < wb.Worksheets.Count; i++)    //Since {the data markers for the copied sheets are all the  BindCountryData(wb.Worksheets[i].Name);same, 
    }      xlt.Process();     xlt.Save(Page.Response, "output.xlsx", false);
}

protected void BindCountryData(string selection)
  //specify the worksheet that the data should be bound to
      {          dataBindProps.WorksheetName = country;
DataBindingProperties
dbp = xlt.CreateDataBindingProperties();             dbp.WorksheetName = selection;

             //Retrieve the data and column names for the header on the sheet
                string[] specificInfoheaderValues = { "FY 2008", "Foreign Trade Division", selectioncountry };
                string[] headerTitlesheaderNames = { "FiscalYear", "TradeDivision", "Country" };

                //Bind the header row data to the worksheet
                xlt.BindRowData(specificInfoheaderValues, headerTitlesheaderNames, "Header", dbpdataBindProps);

                //Get the data for the top 5 sales and all the sales within the year.
                //In this example, GetCSVData method calls on the GenericParsing library
                //to parse and export data from the CSV files located in the //data directory
                DataTable dts = Sales(selection, "TOP 5")GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
                DataTable dts2 = Sales(selection, "")GetCSVData(Page.MapPath("//data//" + country + "All.csv"));

                //Call ExcelTemplate.BindData to bind the data to the template
                xlt.BindData(dts, "Top", dbpdataBindProps);
                xlt.BindData(dts2, "Details", dbpdataBindProps);
            }

public static DataTable Sales(string selection, string topORall)      //Process and stream {the final report back to the user
       //Create the query   xlt.Process();
         string queryString = "USE AdventureWorks2008R2; " + xlt.Save(Page.Response, "Output.xlsx", false);
        }
{new code}


17. Now you may run "DECLAREyour @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 " +
      code. Just call {{GenerateTemplate()}} and {{PopulateTemplate()}} to customize and populate your template. 

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

!Part1_Final.png|border=1,width=700!


Note that there are multiple worksheets, each with the tab name and data for that region.

h1. Final Code

{newcode}
using SoftArtisans.Office.ExcelWriter;
...
protected void Main(object sender, EventArgs e)
{
    GenerateTemplate();
    PopulateTemplate();
}

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


protected void PopulateTemplate()
{
         "INNER JOIN Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID " +
   xlt = new ExcelTemplate(); //Create a new ExcelTemplate object

           "INNER JOIN Sales.SalesTerritory ON Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +//Pass the existing ExcelApplication object and open Workbook
            //to the ExcelTemplate object
"WHERE DATEPART(yyyy, Sales.SalesOrderHeader.OrderDate) = 2008 " +            xlt.Open(xla, wb);

            //RemoveExtraDataMarkers makes all data markers optional
 "AND Sales.SalesOrderHeader.OnlineOrderFlag = 0 " +      //so ExcelWriter will not throw an error if any data markers
"AND Sales.SalesTerritory.Name = @find " +       //aren't bound to data
      "ORDER BY Sales DESC;";    xlt.RemoveExtraDataMarkers = true;

            //GetFor connectioneach country, retrieve and bind data to the worksheet
    SqlConnection connection = new SqlConnection(@"Data Source=TS-IH03\SQL2008R2;Initial Catalog=ExcelApp2;Integrated Security=True")     DataBindingProperties dataBindProps;

            for (int i = 1; i < selectedCountries.Count; i++)
            {
                string country = selectedCountries[i];

                //AssignCreate commanda andnew associatedDataBindProperties connectionobject
            SqlCommand cmd   dataBindProps = new SqlCommand(queryString, connectionxlt.CreateDataBindingProperties();

                //SelectSince the data adaptermarkers for the copied sheets are all the same, 
   SqlDataAdapter adapter = new SqlDataAdapter();         //specify the worksheet  adapter.SelectCommandthat the data should be bound to
                dataBindProps.WorksheetName = cmdcountry;

                //MakeRetrieve athe new table, fill it and return itdata and column names for the header on the sheet
                string[] headerValues = { "FY 2008", "Foreign Trade Division", country };
            DataTable dtGet    string[] headerNames = new DataTable(){ "FiscalYear", "TradeDivision", "Country" };

           adapter.Fill(dtGet     //Bind the header row data to the worksheet
                xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps);

           return dtGet;    //Get the data for the }top 5 sales protectedand List<string> GetListBoxSelections(){
   all the sales within the year.
                //Get In this example, GetCSVData method calls on the ListBoxGenericParsing selections and make the appropriate number of copies
  library
                //to parse and export data from the CSV files located in the //data directory
         List<string> countryNames = new List<string>()      DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
            foreach (int i in ListBox1.GetSelectedIndices())    DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));

           {     //Call ExcelTemplate.BindData to bind the data to the template
   countryNames.Add(ListBox1.Items[i].Text             xlt.BindData(dts, "Top", dataBindProps);
                xlt.BindData(dts2, "Details", dataBindProps);
            }

           return countryNames //Process and stream the final report back to the user
            xlt.Process();
        }