Table of Contents

maxLevel	3

Introduction

Infonote
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 SheetsCreating a Dynamic Template before starting this section.

The sample code template (template.xlsx), page (Part2_CSV.aspx), and code behind (Part2_CSV.aspx.cs) are included in the ExtendedSalesSummary project available for download as part of the ExcelWriter Basics Tutorials

Info

title	Following the Sample

There is a downloadable 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.

This part focuses on using ExcelApplication to create and populate a coversheet that includes user-supplied name, the current date, and a selected picturean image, hyperlinks, and formatted text. It also includes binding data binding with ExcelTemplate.

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 template 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:

Image Removed

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 Image Added

Info

title	Following the Sample Code

The code for this part of the tutorial can be found in Part2.aspx.cs

Setup

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();
	/**************************/

}

Adding the cover sheet

1. In AddCoverSheet, create a new worksheet called "Summary" at the beginning of the workbook with Worksheets.CreateWorksheet:

Code Block
Worksheet ws = wb.Worksheets.CreateWorksheet("Summary", 0);

2. Create a Worksheet object of the worksheet that was just created.

...

Select the "Summary" worksheet as the worksheet that will be active when the workbook opens with Worksheet.Select().

Code Block
ws.Select();

3. Hide the gridlines in the summary worksheet using Worksheet.ShowGridlines. By default, this property is set to true.

Code Block
ws.ShowGridlines = false;

4. Create a GlobalStyle using the Workbook.CreateStyle() method. ExcelWriter uses three Styles: CellStyle. GlobalStyle, and NamedStyle. The GlobalStyles type is a Style that can be applied to cells and areas throughout the workbook.

newcode}}GlobalStyle underlined = wb.CreateStyle();{{newcode

5. Set the underlined GlobalStyle to Font.UnderlineStyle to Single. ExcelApplication allows for various underline types.

Code Block
underlined.Font.Underline = Font.UnderlineStyle.Single;

6. Create another GlobalStyle using the Workbook.CreateStyle() method called 'boldAndFont12'.

Code Block
GlobalStyle boldAndFont12 = wb.CreateStyle();

7. Use the Style.Font to set the style font to 'Bold' and 'Size to 12.

Code Block
boldAndFont12.Font.Bold = true; boldAndFont12.Font.Size = 12;

...

Inserting an image

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;

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.

Code Block
string imagePath = RBImage.SelectedItem.Value;

3. Create an Anchor in cell A1 with Worksheet.CreateAnchor(). Set the offsets to 0.

Code Block
Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0);

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 A3 to the string "Date Executed :". In cell A4, set Cell.Value must be a string.Use the Cell.Value method again to set the value of cell A16 to the string "Report For:" and value of cell A18 . In cell A6, 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";

9. Create another GlobalStyle called "dateForm" and use the NumberFormat.DateFormat method to create the desired date format.

Code Block
GlobalStyle dateForm = wb.CreateStyle(); dateForm.NumberFormat = wb.NumberFormat.DateFormat.MonthDayYear;

10. Apply the styles to the various cells.

...

2. Insert the data markers for the "Date Executed" and "Report For" data, which will be imported using ExcelTemplate.

Code Block
ws.Cells["A15C3"].StyleValue = underlined"%%=WebFormData.Date"; ws.Cells["A16C4"].StyleValue = underlined; ws.Cells["A18"].Style = boldAndFont12; ws.Cells["B15"].Style = dateForm"%%=WebFormData.Name";

11. Insert the data markers for the WebFormData (Textbox inputted name, and today's date) as strings. Data markers must have the format %%=DataSource.ColumnName

Code Block
ws.Cells["C16"].Value = "%%=WebFormData.Name"; ws.Cells["C15"].Value = "%%=WebFormData.Date";

12. 3. 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!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.

Cells below the "Table of Contents" cell are given a Hyperlink formula to another sheet in the workbook. When hyperlinking to another page in a workbook, the formula must follow "=HYPERLINK(\"#DestinationSheet!DesitinationCell\", \"Text\")" and use the Cell.Formula method.

...

, 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+"\")"; }

13. Finally, we want to insert a picture into the cover sheet. This requires creating an Anchor object using the Worksheet.CreateAnchor() method.

Code Block
Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0);

14. We then want to create a Picture object using Pictures.CreatePicture method where the string describing the name of the image is pulled from the utility method RBImage.SelectedItem.Value.

Code Block
string image = RBImage.SelectedItem.Value; Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@image), anc);

15. We want to set the dimensions of the Picture object using Picture.Height and Picture.Width.

Code Block
logo.Height = 195; logo.Width = 290;

16. Finally, we want select the first sheet in the workbook (the "Summary" sheet) to be first viewed when the document is opened.

Code Block
wb.Worksheets[0].Select();

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.

Image Removed

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.

Image Added

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

Space Shortcuts

Page tree

Versions Compared

Old Version 8

New Version Current

Key

Introduction

Writing the Code

Creating the coversheet with ExcelApplication

Scoping the coversheet

Executing the coversheet

Setup

Adding the cover sheet

Inserting an image

Writing Text

Binding the coversheet data with ExcelTemplate

Final Code

Adding Styles to the Text

Binding the coversheet data with ExcelTemplate

Downloads

Space Shortcuts

Page tree

Page History

Versions Compared

Old Version 8

New Version Current

Key

Introduction

Writing the Code

Creating the coversheet with ExcelApplication

Scoping the coversheet

Executing the coversheet

Setup

Adding the cover sheet

Inserting an image

Writing Text

Binding the coversheet data with ExcelTemplate

Final Code

Adding Styles to the Text

Binding the coversheet data with ExcelTemplate

Downloads