Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Wiki Markupexcerpt

An

ExcelWriter *template* is an Excel spreadsheet that contains ExcelWriter data markers. A [data marker|Creating Data Markers] is a cell value beginning with *%%=* or *%%=$* that specifies a database column, variable, or array to insert in the spreadsheet column. A data marker may include [modifiers|Creating Data Markers#modifiers]. Refer to the [ExcelTemplate Code Samples|http://windemo.softartisans.com/OfficeWriter/latest/aspnet.aspx#xlt] to see live demonstrations of various ExcelTemplate applications. Click the *View Template* link on each sample page to see the template. You can create an ExcelWriter template in Microsoft Excel or in script using the [ExcelApplication|Passing ExcelApplication to ExcelTemplate] object. Include data markers where you want to insert values. For example, if cell *B6* contains the data marker

ExcelWriter template is an Excel spreadsheet that contains ExcelWriter data markers. A data marker is a cell value beginning with %%= or %%=$ that specifies a database column, variable, or array to insert in the spreadsheet column. A data marker may include modifiers.

Refer to the ExcelTemplate Code Samples to see live demonstrations of various ExcelTemplate applications. Click the View Template link on each sample page to see the template.

You can create an ExcelWriter template in Microsoft Excel or in script using the ExcelApplication object. Include data markers where you want to insert values. For example, if cell *B6* contains the data marker *%%=Orders.OrderID*,

...

where

...

Orders

...

represents

...

the

...

Orders

...

table

...

in

...

a

...

database,

...

ExcelWriter

...

will

...

import

...

the

...

*OrderID*

...

column

...

to

...

column

...

*B*

...

in

...

the

...

spreadsheet.

...

 

TemplateSpreadsheet generated from the template
Image AddedImage Added

A data marker binds in script to a *data source* which may be an array, DataTable, DataSet, or DataReader, and may include modifiers. Data source and field numbers are 1-based. If ExcelWriter encounters *%%=#0[.field]* or *%%=[DataSource.]#0*, an error will occur.

Note
In ExcelWriter versions before 3.1, data source and field numbers were 0-based.  If you upgraded from an earlier version, you may need to modify your code.{note}
|| Data Marker Formats || Code ||
| Database data marker | {code}
Data Marker FormatsCode

Database data marker

%%=[DataSourceNameOrNumber.]FieldNameOrNumber[(modifier)

...

]

Variable data marker

%%=$DataSourceNameOrNumber[(modifier)|(modifier)]

...

One-Dimensional

...

Array

...

data

...

marker

%%=$DataSourceNameOrNumber[(modifier)|(modifier)]

...

Two-dimensional

...

Array

...

data

...

marker

%%=$DataSourceNameOrNumber.ColumnNameOrNumber[(modifier)|(modifier)]

...

Example

The following example generates a new spreadsheet from a template that contains two data markers: *%%=$RecipientName*

...

and

...

*%%=$RecipientCompany*.

...

ExcelWriter

...

sets

...

the

...

data

...

sources

...

for

...

the

...

template

...

data

...

markers

...

to

...

two

...

simple

...

string

...

variables.

...

The

...

ExcelTemplate

...

object

...

represents

...

the

...

template

...

Excel

...

file.

...

ExcelTemplate

...

is

...

in

...

the

...

SoftArtisans.OfficeWriter.ExcelWriter

...

namespace.

...

The

...

object

...

can

...

be

...

referenced

...

as

...

SoftArtisans.OfficeWriter.ExcelWriter.ExcelTemplate.

...

To

...

minimize

...

typing

...

and

...

errors,

...

use

...

an

...

Import

...

directive

...

to

...

import

...

the

...

namespace

...

to

...

the

...

.aspx

...

page

...

and

...

reference

...

the

...

object

...

as

...

ExcelTemplate

...

without

...

the

...

namespace

...

prefix.

...

If

...

you

...

are

...

coding

...

directly

...

in

...

the

...

.aspx

...

page

...

include

...

the

...

following

...

after

...

the

...

Page

...

directive:

Code Block
languagexml
<%@ Import Namespace="SoftArtisans.OfficeWriter.ExcelWriter" %>
{newcode}

If you are coding in the 

If you are coding in the code-behind

...

page,

...

include

...

a

...

using

...

or

...

Imports

...

statement

...

at

...

the

...

top

...

of

...

the

...

code-behind

...

page:

...

Code Block
languagec#
 using SoftArtisans.OfficeWriter.ExcelWriter;
{csharp}

{vbnet}
Imports 
Code Block
languagevb
 Imports SoftArtisans.OfficeWriter.ExcelWriter
{vbnet}

To generate a new spreadsheet with ExcelWriter:
# Create an instance of ExcelTemplate, for example:

{csharp:2}
ExcelTemplate 

To generate a new spreadsheet with ExcelWriter:

  1. Create an instance of ExcelTemplate, for example:

    Code Block
    languagec#
     ExcelTemplate xlt = new ExcelTemplate();

...

  1. Code Block
    languagevb
    Dim xlt As New ExcelTemplate()

...



  1. Call ExcelTemplate.Open()

...

  1. to

...

  1. open

...

  1. a

...

  1. template

...

  1. Excel

...

  1. file,

...

  1. for

...

  1. example:

...

  1. Code Block
    languagec#
    xlt.Open(Application["templatepath"] + 

...

  1. @"\DataBinding\StringBindingTemplate.xls");

...

  1. Code Block
    languagevb
    xlt.Open(Application("templatepath") & _

...

  1. "\DataBinding\StringBindingTemplate.xls")

...

  1. The Open method takes the file path and name of the template .xls file to open.

     

     

  2. Use the ExcelTemplate.BindCellData() method to assign data sources to bind to the template's data markers, for example:

    Code Block
    languagec#
    //--- Bind the variables to the template data markers
    //--- %%=$RecipientName and %%=$RecipientCompany
    xlt.BindCellData("J. Smith", 

...

  1. "RecipientName", 

...

  1. xlt.CreateDataBindingProperties());
    xlt.BindCellData("SoftArtisans", 

...

  1. "RecipientCompany", 

...

  1. xlt.CreateDataBindingProperties());

...

  1. Code Block
    languagevb
    '--- Bind the variables to the template data markers
    '--- %%=$RecipientName and %%=$RecipientCompany
    xlt.BindCellData("J. Smith", 

...

  1. "RecipientName", 

...

  1. xlt.CreateDataBindingProperties())
    xlt.BindCellData("SoftArtisans", 

...

  1. "RecipientCompany", 

...

  1. xlt.CreateDataBindingProperties())

...

  1.  

     

  2. Call ExcelTemplate.Process

...

  1. ()

...

  1. to

...

  1. populate

...

  1. the

...

  1. template's

...

  1. data

...

  1. markers

...

  1. with

...

  1. data

...

  1. source

...

  1. values:

...

  1. Code Block
    languagec#
    xlt.Process();

...

  1. Code Block
    languagevb
    xlt.Process()

    The Process method enters data source values in the template's merge fields, and creates the output file (the new spreadsheet) in memory.

     

     

  2. Call ExcelTemplate.Save to generate a new spreadsheet:

    Code Block
    languagec#
    xlt.Save(Page.Response, "StringBinding.xls", false);

...

  1. Code Block
    languagevb
    xlt.Save(Page.Response, "StringBinding.xls", false)

    If you pass Save a Page.Response object, ExcelWriter will stream the generated file to the client. Save's second parameter specifies a name for the generated Excel file; this name will be displayed in the download dialog when the file is streamed to the browser. If the third parameter is set to true and the user chooses to open the file, the file will open in the browser window; if it is set to false (as in the example) and the user chooses to open the file, the file will open in Microsoft Excel.

    ExcelWriter allows you to save the generated file on the server or stream it to the client. For more information, see ExcelTemplate Output Options.