Table of Contents |
---|
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. |
There is a downloadable C# project 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. |
This part focuses on using ExcelApplication
to create and a coversheet that an image, hyperlinks, and formatted text. It also includes binding data with ExcelTemplate
.
Writing the code for the cover sheet comes in two parts. the first part uses ExcelApplication
to create a template coversheet; and the second part uses ExcelTemplate
to bind the appropriate data to the data markers on the cover sheet.
We want to create a cover sheet that looks like the following Excel worksheet:
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.
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()
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(); /**************************/ } |
1. In AddCoverSheet
, create a new worksheet called "Summary" at the beginning of the workbook with Worksheets.CreateWorksheet
:
Worksheet ws = wb.Worksheets.CreateWorksheet("Summary", 0); |
2. Select the "Summary" worksheet as the worksheet that will be active when the workbook opens with Worksheet.Select()
.
ws.Select(); |
3. Hide the gridlines in the summary worksheet using Worksheet.ShowGridlines
. By default, this property is set to true
.
ws.ShowGridlines = false; |
1. The image will be inserted in cell A1
. Adjust the height of row 1 to accommodate the image by retrieving the RowProperties object of row 1:
ws.GetRowProperties(0).Height = 90; |
Note: The row height is set in units of 1/72 of an inch.
2. In the example, the user can select an image to insert into the file. The path of that image is dynamically retrieved and stored in variable imagePath
.
string imagePath = RBImage.SelectedItem.Value; |
3. Create an Anchor
in cell A1
with Worksheet.CreateAnchor()
. Set the offsets to 0.
Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0); |
4. Then insert the Picture
object using Pictures.CreatePicture
.
Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc); |
1. Set the Cell.Value
of cell A15 to the string "Date Executed :". In cell A16, set Cell.Value
to "Report For:". In cell A18, set the Cell.Value
to "Table of Contents".
ws.Cells["A15"].Value = "Date Executed :"; ws.Cells["A16"].Value = "Report For :"; ws.Cells["A18"].Value = "Table of Contents"; |
2. Insert the data markers for the "Date Executed" and "Report For" data, which will be imported using ExcelTemplate
.
ws.Cells["C15"].Value = "%%=WebFormData.Date"; ws.Cells["C16"].Value = "%%=WebFormData.Name"; |
3. Create a GlobalStyle
using the Workbook.CreateStyle()
method. For more about using styles in ExcelWriter, see Effective Use of Styles.
newcode}}GlobalStyle underlined = wb.CreateStyle();{{newcode
4. Set the Font.UnderlineStyle
of underlined
to{{UnderlineStyle.Single}}.
underlined.Font.Underline = Font.UnderlineStyle.Single; |
5. Create another GlobalStyle
called boldAndFont12
.
GlobalStyle boldAndFont12 = wb.CreateStyle(); |
6. Set Font.Bold
to true
and Font.Size
to 12.
boldAndFont12.Font.Bold = true; boldAndFont12.Font.Size = 12; |
7. Create another GlobalStyle
called dateForm
and use NumberFormat.DateFormat
to specify the desired NumberFormat.DateFormat
.
GlobalStyle dateForm = wb.CreateStyle(); dateForm.NumberFormat = wb.NumberFormat.DateFormat.MonthDayYear; |
8. Apply the underlined
style to A15 and A16. Apply boldAndFont12
to A18 and apply dateForm
to B15.
ws.Cells["A15"].Style = underlined; ws.Cells["A16"].Style = underlined; ws.Cells["A18"].Style = boldAndFont12; ws.Cells["B15"].Style = dateForm; |
9. Next we will add hyperlinks to each of the worksheets in the workbook. To do this, we will use Excel's native HYPERLINK
formula to point to the other worksheets. The format for the HYPERLINK
formula will be:
=HYPERLINK(\"#DestinationSheet!DesitinationCell\", \"Text\")
This will be set to the Cell.Formula
property.
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.
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+"\")"; } |
1. Following the lines in the code of Part 1,
for (int i = 0; i < wb.Worksheets.Count; i++) { BindCountryData(wb.Worksheets[i].Name); } |
string name = TextBox1.Text;
This is another utility method. |
2. 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 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.
string[] coverData = {name, 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.
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.
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.
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; } |
You can download the code for the Extended Sales Summary here.