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();
/**************************/
}
|
...
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:
Code Block |
---|
ws.GetRowProperties(0).Height = 90; |
...
4. Then insert the Picture
object using Pictures.CreatePicture
.
Code Block |
---|
Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc);
|
Writing
...
Text
1. Set the Cell.Value
of cell A15 A3 to the string "Date Executed :". In cell A16A4, set Cell.Value
to "Report For:". In cell A18A6, set the Cell.Value
to "Table of Contents".
Code Block |
---|
ws.Cells["A15A3"].Value = "Date Executed :"; ws.Cells["A16A4"].Value = "Report For :"; ws.Cells["A18A6"].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["C15C3"].Value = "%%=WebFormData.Date"; ws.Cells["C16C4"].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}}.
Code Block |
---|
underlined.Font.Underline = Font.UnderlineStyle.Single; |
5. Create another GlobalStyle
called boldAndFont12
.
Code Block |
---|
GlobalStyle boldAndFont12 = wb.CreateStyle(); |
6. Set Font.Bold
to true
and Font.Size
to 12.
Code Block |
---|
boldAndFont12.Font.Bold = true;
boldAndFont12.Font.Size = 12;
|
7. Create another GlobalStyle
called dateForm
and use NumberFormat.DateFormat
to specify the desired NumberFormat.DateFormat
.
Code Block |
---|
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.
Code Block |
---|
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#DestinationSheet!DesitinationCell\DestinationCell", \ "Display 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.
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+"\")";
}
|
Binding the coversheet data with ExcelTemplate
1. Following the lines in the code of Part 1,
Code Block |
---|
for (int i = 0; i < wb.Worksheets.Count; i++)
{
BindCountryData(wb.Worksheets[i].Name);
}
|
string name = TextBox1.Text;
Info |
---|
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.
Code Block |
---|
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.
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
...
Adding Styles to the Text
For more about using styles in ExcelWriter, see Effective Use of Styles.
1. Create a GlobalStyle
using the Workbook.CreateStyle()
method. This style will be applied to the labels on the summary sheet.
Code Block |
---|
GlobalStyle labels = wb.CreateStyle(); |
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);
|
4. Set the Font.UnderlineStyle
of cell A6 to be UnderlineStyle.Single
.
Code Block |
---|
ws.Cells["A6"].Style.Font.Underline = Font.UnderlineStyle.Single; |
5. 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["A16A4"].Value = "Report For :"; ws.Cells["A18A6"].Value = "Table of Contents"; GlobalStyle dateForm = wb.CreateStyle(); dateForm.NumberFormat = wb.NumberFormat.DateFormat.MonthDayYear; ws.Cells["A15C3"].StyleValue = underlined; ws.Cells["A16"].Style = underlined; ws.Cells["A18"].Style = boldAndFont12"%%=WebFormData.Date"; ws.Cells["B15"].Style = dateForm; ws.Cells["C16C4"].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[167 + i, 1].Formula = "=HYPERLINK(\"#" + sheetName + "!A1\", \"" + sheetName + "\")"; } Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0); /******Adding Styles to string image = RBImage.SelectedItem.Value;Text*******/ Picture logo GlobalStyle labels = wswb.Pictures.CreatePicture(Page.MapPath(@image), anc)CreateStyle(); logo.Height = 195; logolabels.WidthFont.Bold = 290true; wb.Worksheets[0].Select() labels.Font.Size = 12; xlt = new ExcelTemplate(); xlt.Open(xla, wbws.Cells["A3"].ApplyStyle(labels); for (int i = 0; i < wb.Worksheets.Count; i++)ws.Cells["A4"].ApplyStyle(labels); { BindCountryData(wb.Worksheets[i].Namews.Cells["A6"].ApplyStyle(labels); } string name ws.Cells["A6"].Style.Font.Underline = TextBox1Font.TextUnderlineStyle.Single; } |
Binding the coversheet data with ExcelTemplate
1. 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 |
---|
string recipient = TextBox1.Text; |
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, 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. Bind 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());
xlt.Process();
xlt.Save(Page.Response, "output.xlsx", false);
}
|
4. The final code for PopulateTemplate()
, including the code from Part 1 should look like this:
Code Block |
---|
protected void BindCountryDataPopulateTemplate(string selection) { DataBindingPropertiesxlt dbp = xlt.CreateDataBindingPropertiesnew ExcelTemplate(); dbp.WorksheetName = selection; string[] specificInfo = { "FY 2008", "Foreign Trade Division", selection } xlt.Open(xla, wb); string[] headerTitles = { "FiscalYear", "TradeDivision", "Country" }xlt.RemoveExtraDataMarkers = true; xlt.BindRowData(specificInfo, headerTitles, "Header", dbp)DataBindingProperties dataBindProps; for DataTable dts = Sales(selection, "TOP 5"); DataTable dts2 = Sales(selection, ""); xlt.BindData(dts, "Top", dbp); xlt.BindData(dts2, "Details", dbp);(int i = 0; i < selectedCountries.Count; i++) } public static DataTable Sales(string selection, string topORall) { //Create the query string queryStringcountry = "USE AdventureWorks2008R2selectedCountries[i]; " + dataBindProps "DECLARE @find nvarchar(100= xlt.CreateDataBindingProperties(); " + "SET @find ='" + selection + "'; " +dataBindProps.WorksheetName = country; string[] headerValues = { "SELECTFY 2008", +"Foreign topORallTrade + " Sales.Store.Name AS Description, Sales.SalesOrderHeader.TotalDue AS Sales FROM Sales.Store " +Division", country }; string[] "INNER JOIN Sales.Customer ON Sales.Store.BusinessEntityID = Sales.Customer.StoreID " + "INNER JOIN Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID " +headerNames = { "FiscalYear", "TradeDivision", "Country" }; "INNER JOIN Sales.SalesTerritory ON Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " + "WHERE DATEPART(yyyy, Sales.SalesOrderHeader.OrderDate) = 2008 " +xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps); DataTable dts "AND Sales.SalesOrderHeader.OnlineOrderFlag = 0 "= GetCSVData(Page.MapPath("//data//" + country "AND Sales.SalesTerritory.Name = @find " ++ "5.csv")); DataTable "ORDERdts2 BY Sales DESC;"; //Get connection SqlConnection connection = new SqlConnection(@"Data Source=TS-IH03\SQL2008R2;Initial Catalog=ExcelApp2;Integrated Security=True")= GetCSVData(Page.MapPath("//data//" + country + "All.csv")); //Assign command and associated connection SqlCommand cmd = new SqlCommand(queryString, connection xlt.BindData(dts, "Top", dataBindProps); //Select data adapter xlt.BindData(dts2, SqlDataAdapter adapter = new SqlDataAdapter("Details", dataBindProps); adapter.SelectCommand} = cmd; //Make a new table, fill it and return it/**********Part 2***********/ string DataTable dtGet = new DataTable(); adapter.Fill(dtGet)recipient = TextBox1.Text; return dtGet;string[] coverValues = { } protected List<string> GetListBoxSelections(){ recipient, DateTime.Now.Date.ToString("M/dd/yyyy") }; //Get the ListBox selections and makestring[] thecoverNames appropriate= number of copies { "Name", "Date" }; List<string> countryNames = new List<string>(); xlt.BindRowData(coverValues, coverNames, "WebFormData", foreach (int i in ListBox1xlt.GetSelectedIndicesCreateDataBindingProperties()); { /***************************/ countryNamesxlt.Add(ListBox1.Items[i].TextProcess(); } 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