OfficeWriter Documentation
.
ExcelWriter v8 Docs
Space Shortcuts
Page tree
ConfigureSpace tools
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 18 Next »

Table of Contents

Introduction

The ExcelTemplate object is used for template-driven document generation. This object opens an ExcelWriter template file, populates it with data from a specified data source, and generates a new Excel workbook. An Excel file uploaded as an ExcelTemplate object is not directly modifiable at runtime.

The ExcelApplication object is an Excel file engine that can be used to create, open, modify, and save workbooks. A single instance of ExcelApplication can generate multiple Excel workbooks.

This tutorial opens an Excel template file formatted as for ExcelTemplate (i.e. it contains data markers) and makes customizations using ExcelApplication object, based on a user's selections. In particular, it highlights the functionality of the CopySheet method. Then data is bound to the template using ExcelTemplate, again, based on a user's selections.

Icon

This tutorial assumes a basic understanding of the ExcelTemplate object. If you have not familiar with creating an Excel template and binding data with ExcelTemplate, please go through the Simple Expense Summary tutorial first.

Setting up the template

Following the Sample Code

Icon

In the downloadable C# project, there is a completed template file located in ExtendedSalesSummary/templates/template.xlsx.
A copy of the completed template file is also available here.

The template file should look something like this:

In the template, the %%=Header.FiscalYear data marker is referenced in a formula. In the header, there is a formula =CONCATENATE("Sales Summary - ", N1), where N1 is the cell that actually contains the %%=Header.FiscalYear data marker. This is shown in the image below.

START HERE

Adding an ExcelWriter Reference in Visual Studio

Icon

In the sample code, the reference to SoftArtisans.OfficeWriter.ExcelWriter.dll and to System.Data.SqlClient has already been added to the ExtendedSalesSummary project.

Create a .NET project and add a reference to the ExcelWriter library.

  1. Open Visual Studio and create a .NET project.
    • The sample code uses a web application.
  2. Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll
    • SoftArtisans.OfficeWriter.ExcelWriter.dll is located under Program Files > SoftArtisans > OfficeWriter > dotnet > bin

Writing the Code

The code for this report uses two functions- the Main function and the BindCountryData functions. The Main function calls the BindCountryData function taking each country's worksheet as a parameter. See below for further explanation.

Main function

1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind

Icon

If you are following along and intend to use AdventureWorks2008R2, you will also want to make a reference to System.Data.SqlClient namespace as well as System.Collections.Generic. To do so, type into the code behind

2. In the method that will run the report, instantiate the ExcelApplication object.

3. Open the ExcelApplication object with the ExcelApplication.Open() method and instantiate it as a Workbook object.

4. Assuming that you taking the selections from a ASP.NET ListBox, use the following utility method to create a string list of all the countries that were selected.

Icon

A utility method refers to a piece of code/function that is not affected by the ExcelWriter API framework. For the purpose of this tutorial, utility methods will be included as a separate function outside of the Main function.

5. ExcelApplication can make copies of a specified worksheet using the Worksheets.CopySheet() method. This method requires a Worksheet object representing the sheet to be copied; an integer representing the new worksheet's position and, the string representing the name of the new worksheet.

Insert the following code that iterates through the selected countries in the string list to make copies of the "SimpleTemplate" in the original template.xlsx file. Each new worksheet is placed after the "SimpleTemplate" sheet and is named based on the selected country from the string list. The <i>for<i> loop uses the <i>List<i>.Count method to determine how many copies must be made.

6. At this point, there is a worksheet named after each selected country and the original worksheet contained in the wb Workbook object. We need to hide the original worksheet using the Worksheet.Visibility method. This method can also make a worksheet 'visible' or 'very hidden.'

7. Select the first worksheet after the now hidden one to be displayed when the file first is opened using the Worksheets.Select() method.

8. Instantiate a new ExcelTemplate object.

9. Open the ExcelApplication workbook using the ExcelTemplate.Open(ExcelApplication, Workbook) method.

10. The use of function in an ExcelWriter code file is sometimes the most efficient way to approach a situation. In this case, a separate function is created to bind the data. This function is called in a for loop that is contained with the other code. Let the function that deals with data binding be called BindCountryData() and takes a worksheets name's as a string parameter.

Icon

We will return to this function after completing the main code.

11. Call ExcelTemplate.Process() to import all data into the file.

12. Call ExcelTemplate.Save to save the output file.

ExcelTemplate has several output options: save to disk, save to a stream, stream the output file in a page's Response inline or as an attachment.

BindCountryData function

1. Create a function called BindCountryData that takes a string parameter called selection.

2. Create a DataBindingProperty based on the Worksheet name. DataBindingProperties is like a tag used to specify how data is bound to a worksheet.

Other DataBindingProperties include MaxRows and Transpose.

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 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.

4. Use the ExcelTemplate.BindRowData method to bind the header data to the data markers in the template file (%%=Header.FiscalYear, %%=Header.TradeDivision, %%=Header.Country).

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.

5. Get the data for the Top 5 Expenses and All Expenses data sets.

In this tutorial, it is assumed that your machine is equipped with AdventureWorks2008R2, and therefore that a SQL query is a valid operation. As a utility method, the following function will be included as a function outside of the Main and BindCountryData functions.

Icon

If you do not have AdventureWorks2008R2, you may download the sample file here. The sample code uses a utility method called GetCSVData to obtain data from the supplied CSV files.

6. Use ExcelTemplate.BindData to bind the data for the Top and Details Sales data sets.

Recall that the data source names (Top, Details) need to match the data marker names exactly.

7. Now you may run your code.

Here is an example of what the sample will look like:

Note that there are multiple worksheets, each named for each country and containing it's specified data.

Final Code

Downloads

You can download the code for the Extended Sales Summary here.

Next Steps

Continue on to Part 2 - Adding a Coversheet

  • No labels