protected void PopulateTemplate()
{
    ExcelApplication xla = new ExcelApplication();    xlt 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])= new ExcelTemplate(); 
   }      wb.Worksheets[0].Visibility = Worksheet.SheetVisibility.Hidden;
    wb.Worksheets[1].Select();

    wb.Worksheets.CreateWorksheet("Summary", 0);
    Worksheet ws = wb.Worksheets["Summary"];
    ws.ShowGridlines = false;

    GlobalStyle underlined = wb.CreateStyle( xlt.Open(xla, wb);

   underlined.Font.Underline = Font.UnderlineStyle.Single;     GlobalStyle boldAndFont12 = wb.CreateStyle();
    boldAndFont12.Font.Bold xlt.RemoveExtraDataMarkers = true;

   boldAndFont12.Font.Size = 12;      ws.Cells["A15"].Value = "Date Executed :" DataBindingProperties dataBindProps;
    ws.Cells["A16"].Value = "Report For :";     ws.Cells["A18"].Value = "Table of Contents";

    GlobalStyle dateForm = wb.CreateStyle();
    dateForm.NumberFormat = wb.NumberFormat.DateFormat.MonthDayYear;

    ws.Cells["A15"].Style = underlined;
    ws.Cells["A16"].Style = underlined;
    ws.Cells["A18"].Style = boldAndFont12;
    ws.Cells["B15"].Style = dateForm;

    ws.Cells["C16"].Value = "%%=WebFormData.Name";
    ws.Cells["C15"].Value = "%%=WebFormData.Date";

    for (int i = 20; i < wbselectedCountries.Worksheets.Count; i++)
    {        {
string sheetName = wb.Worksheets[i].Name.ToString();         ws.Cells[16 + i, 1].Formula = "=HYPERLINK(\"#"+sheetName+"!A1\", \""+sheetName+"\")";
     }

    Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0);

    string image = RBImage.SelectedItem.Value;

    Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@image), anc);

    logo.Height = 195;
    logo.Width = 290;

    wb.Worksheets[0].Select();

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

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

   }      string name = TextBox1.Text;    dataBindProps  string[] coverData = {name, DateTime.Now.Date.ToString("M/dd/yyyy")};

    string[] coverMarkers = { "Name", "Date" };

    xlt.BindRowData(coverData, coverMarkers, "WebFormData", xlt.CreateDataBindingProperties());

    xlt.Process();     xlt.Save(Page.Response, "output.xlsx", false); }  protected void BindCountryData(string selection)
        {
            DataBindingProperties dbp = xlt.CreateDataBindingProperties() dataBindProps.WorksheetName = country;

           dbp.WorksheetName = selection;              string[] specificInfoheaderValues = { "FY 2008", "Foreign Trade Division", selectioncountry };
                string[] headerTitlesheaderNames = { "FiscalYear", "TradeDivision", "Country" };

                xlt.BindRowData(specificInfoheaderValues, headerTitlesheaderNames, "Header", dbpdataBindProps);

            DataTable dts = Sales(selection, "TOP 5");
            DataTable dts2dts = Sales(selection, "");
            xlt.BindData(dts, "Top", dbp);
            xlt.BindData(dts2, "Details", dbp)GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
        }  public static DataTable Sales(string selection, string topORall)DataTable dts2        {

            //Create the query
            string queryString = "USE AdventureWorks2008R2; " +
                "DECLARE @find nvarchar(100); " += GetCSVData(Page.MapPath("//data//" + country + "All.csv"));

                xlt.BindData(dts, "SET @find ='" + selection + "'; " +Top", dataBindProps);
                xlt.BindData(dts2,  "SELECT " + topORall + " Sales.Store.Name AS Description, Sales.SalesOrderHeader.TotalDue AS Sales FROM Sales.Store " +Details", dataBindProps);
            }

        "INNER JOIN Sales.Customer ON Sales.Store.BusinessEntityID = Sales.Customer.StoreID " + /**********Part 2***********/
            string       "INNER JOIN Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID " +recipient = TextBox1.Text;

            string[] coverValues = {  "INNER JOIN Sales.SalesTerritory ON Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +
                "WHERE DATEPART(yyyy, Sales.SalesOrderHeader.OrderDate) = 2008 " +recipient, DateTime.Now.Date.ToString("M/dd/yyyy") };
            string[]       "AND Sales.SalesOrderHeader.OnlineOrderFlag coverNames = 0{ " +
                "AND Sales.SalesTerritory.Name = @find " +
                "ORDER BY Sales DESC;";, "Date" };

            //Get connection
            SqlConnection connection = new SqlConnection(@"Data Source=TS-IH03\SQL2008R2;Initial Catalog=ExcelApp2;Integrated Security=True");xlt.BindRowData(coverValues, coverNames, "WebFormData",
             //Assign command and associated connection
            SqlCommand cmd = new SqlCommand(queryString, connection) xlt.CreateDataBindingProperties());

            //Select data adapter***************************/

             SqlDataAdapter adapter = new SqlDataAdapterxlt.Process();
            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;
        }
xlt.Save(Page.Response, "Output.xlsx", false);
}