...
Code Block |
---|
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;
}
|
...
You can download the code for the Extended Sales Summary here.