The sample code template (template.xlsx), page (Part1_CSV.aspx), and code behind (Part1_CSV.aspx.cs) are included in the ExtendedSalesSummary project available for download as part of the ExcelWriter Basics Tutorials.

ExcelTemplate is the main class 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. ExcelApplication is the main class for pure code-based workbook generation. This class is an engine used to open, create, and write (save or stream to a browser) workbooks. A single instance of ExcelApplication can generate multiple Excel workbooks.

In the sample code, the complete template file is located in ExtendedSalesSummary/templates/template.xlsx .The template is also available here.

In the sample code, the reference to SoftArtisans.OfficeWriter.ExcelWriter.dll and to System.Data.SqlClient has already been added to the ExtendedSalesSummary project.

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

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