Table of Contents | ||||
---|---|---|---|---|
|
Introduction
Note |
---|
This is Part 2 of the two-part tutorial series Extended Sales Summary scenario. It is recommended that you complete Part 1 - Creating a Dynamic Template before starting this section. |
Info | ||
---|---|---|
| ||
There is a downloadable C# project ExcelWriter_Basic_Tutorials.zip with completed templates and code. The completed example of the template is available under templates/part2_template.xlsx. The code for this part of the tutorial can be found in Part2.aspx.cs. |
...
1. Define a method to contain the ExcelApplication
code to create a new worksheet and customize it. In the sample, there is an AddCoverSheet()
method that holds the code for the ExcelApplicattion
code in this part of the tutorial.
Code Block |
---|
protected void AddCoverSheet()
{
}
|
2. You should have already completed Part 1 of this tutorial. To include the AddCoverSheet()
method, just add a call in GenerateTemplate()
Code Block |
---|
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();
/*********Part 2*************/
AddCoverSheet();
/**************************/
}
|
...
4. Then insert the Picture
object using Pictures.CreatePicture
.
Code Block |
---|
Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc);
|
...
1. Set the Cell.Value
of cell A3 to the string "Date Executed :". In cell A4, set Cell.Value
to "Report For:". In cell A6, set the Cell.Value
to "Table of Contents".
Code Block |
---|
ws.Cells["A3"].Value = "Date Executed :";
ws.Cells["A4"].Value = "Report For :";
ws.Cells["A6"].Value = "Table of Contents";
|
2. Insert the data markers for the "Date Executed" and "Report For" data, which will be imported using ExcelTemplate
.
Code Block |
---|
ws.Cells["C3"].Value = "%%=WebFormData.Date";
ws.Cells["C4"].Value = "%%=WebFormData.Name";
|
...
Iterate through the country sheet names using a for loop, excluding the "Summary" and hidden "SimpleTemplate" sheets, which are the first two worksheets in the workbook.
Code Block |
---|
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+"\")";
}
|
...
2. Set the Font.Bold
to true
and Font.Size
to 12.
Code Block |
---|
labels.Font.Bold = true;
labels.Font.Size = 12;
|
3. Apply the labels style to the label cells with Cell.ApplyStyle(Style)
.
Code Block |
---|
ws.Cells["A3"].ApplyStyle(labels);
ws.Cells["A4"].ApplyStyle(labels);
ws.Cells["A6"].ApplyStyle(labels);
|
...
Code Block |
---|
ws.Cells["A6"].Style.Font.Underline = Font.UnderlineStyle.Single; |
105. The final code for AddCoverSheet()
should be:
Code Block |
---|
protected void AddCoverSheet()
{
Worksheet ws = wb.Worksheets.CreateWorksheet("Summary", 0);
ws.Select();
ws.ShowGridlines = false;
/*******Inserting the Image********/
ws.GetRowProperties(0).Height = 90;
string imagePath = RBImage.SelectedItem.Value;
Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0);
Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc);
/*******Writing values*************/
ws.Cells["A3"].Value = "Date Executed :";
ws.Cells["A4"].Value = "Report For :";
ws.Cells["A6"].Value = "Table of Contents";
ws.Cells["C3"].Value = "%%=WebFormData.Date";
ws.Cells["C4"].Value = "%%=WebFormData.Name";
for (int i = 2; i < wb.Worksheets.Count; i++)
{
string sheetName = wb.Worksheets[i].Name.ToString();
ws.Cells[7 + i, 1].Formula = "=HYPERLINK(\"#" + sheetName +
"!A1\", \"" + sheetName + "\")";
}
/******Adding Styles to Text*******/
GlobalStyle labels = wb.CreateStyle();
labels.Font.Bold = true;
labels.Font.Size = 12;
ws.Cells["A3"].ApplyStyle(labels);
ws.Cells["A4"].ApplyStyle(labels);
ws.Cells["A6"].ApplyStyle(labels);
ws.Cells["A6"].Style.Font.Underline = Font.UnderlineStyle.Single;
}
|
>>REVIEW HERE
Binding the coversheet data with ExcelTemplate
1. Following the lines in the code of In Part 1, we defined PopulateTemplate()
, which binds all of the data to the worksheet. We will add the calls to bind data to the summary sheet here.
2. In the sample, the user can provide their own recipient name. Retrieve this from the web form.
Code Block |
---|
forstring (intrecipient i = 0; i < wb.Worksheets.Count; i++) { BindCountryData(wb.Worksheets[i].Name); } |
...
TextBox1.Text; |
...
Info |
---|
This is another utility method. |
...
|
3. 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 , some of which 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.
Code Block |
---|
string[] coverData = {namerecipient, DateTime.Now.Date.ToString("M/dd/yyyy")}; string[] coverMarkers = { "Name", "Date" }; |
3. Use the ExcelTemplate.BindRowData method to bind the web form data to the data markers in the template file (%%=WebFormData.Name, %%=WebFormData.Date) with blank DataBindingProperties.BindRowDataBind this row of data to the summary sheet with ExcelTemplate.BindRowData
. BindRowData()
binds a single row of data to the template, but the data markers in the template do not need to be in a single row.
Code Block |
---|
xlt.BindRowData(coverData, coverMarkers, "WebFormData", xlt.CreateDataBindingProperties()); |
4. Now run your code.
Here is an example of what the form will look like.
Notice that the countries in the Table of Contents are hyperlinked to the corresponding sheets in the workbook.
Final Code
...
The final code for PopulateTemplate()
, including the code from Part 1 should look like this:
Code Block |
---|
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); } |
5. Now run your code.
Here is an example of what the form will look like.
Notice that the countries in the Table of Contents are hyperlinked to the corresponding sheets in the workbook.
Downloads
You can download the code for the Extended Sales Summary here.
- ExtendedSalesSummaryExcelWriter_CSharpBasic_Tutorials.zip