.
Table of Contents | ||||
|---|---|---|---|---|
|
Introduction
| Info |
|---|
This is Part 2 of the two-part tutorial series <i>Extended Sales Summary<i> scenario. It is recommended that you complete Part 1 - Copying Sheets before starting this section. |
...
Because this part of the tutorial demonstrates further capabilities of ExcelApplication, the code behind will be modified.
Writing the Code
Writing the code for the cover sheet comes in two parts. the first part uses ExcelApplication to create a coversheet; and the second part uses ExcelTemplate to bind the appropriate data to the data markers on the cover sheet.
Creating the coversheet with ExcelApplication
Scoping the coversheet
We want to create a cover sheet that looks like the following Excel worksheet:
...
Ultimately, the cells below the Table of Contents will be populated with hyperlinks to country sheets in the workbook. The logo will be selected from the front end. The "Report For :" field will correspond with the supplied name, and finally, the "Date Executed :" will correspond with today's date.
Executing the coversheet
1. Following the line {{wb.Worksheets0.Visibility = Worksheet.SheetVisibility.Hidden; }}, use the Worksheets.CreateWorksheet method to create a new worksheet called "Summary" at the beginning of the workbook:
...
| Code Block |
|---|
wb.Worksheets[0].Select(); |
Binding the coversheet data with ExcelTemplate
1. Following the lines in the code of Part 1,
...
Notice that the countries in the Table of Contents are hyperlinked to the corresponding sheets in the workbook.
Final Code
| 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();
wb.Worksheets.CreateWorksheet("Summary", 0);
Worksheet ws = wb.Worksheets["Summary"];
ws.ShowGridlines = false;
GlobalStyle underlined = wb.CreateStyle();
underlined.Font.Underline = Font.UnderlineStyle.Single;
GlobalStyle boldAndFont12 = wb.CreateStyle();
boldAndFont12.Font.Bold = true;
boldAndFont12.Font.Size = 12;
ws.Cells["A15"].Value = "Date Executed :";
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 = 2; i < wb.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 name = TextBox1.Text;
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();
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.
...