.
...
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.
| Note |
|---|
This tutorial assumes a basic understanding of the |
Setting up the template
| Info | ||
|---|---|---|
| ||
In the downloadable ExcelWriter_Basic_Tutorials.zip, there is a completed template file located in ExtendedSalesSummary/templates/template.xlsx _.
copy of the completed template file is also available [|Part 1 - Creating a Dynamic Template^template.xlsx].{info} The template file should look something like this: !Final_Template.png|border=1,width=700! In the template, the {{. |
The template file should look something like this:
In the template, the %%=Header.FiscalYear
...
data
...
marker
...
is
...
concatenated
...
with
...
"Sales
...
Summary
...
-".
...
Data
...
markers
...
cannot
...
be
...
used
...
directly
...
in
...
formulas;
...
the
...
data
...
marker
...
needs
...
to
...
be
...
in
...
a
...
separate
...
cell,
...
which
...
can
...
be
...
referenced
...
in
...
an
...
Excel
...
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.
Adding an ExcelWriter Reference in Visual Studio
| Info | ||
|---|---|---|
| ||
In the sample code, the reference to has already been added to the _ExtendedSalesSummary _web application project. {info} |
To
...
create
...
a
...
.NET
...
project
...
and
...
add
...
a
...
reference
...
to
...
the
...
ExcelWriter
...
library:
...
- Open
...
- Visual
...
- Studio
...
- and
...
- create
...
- a
...
- .NET
...
- project.
...
- Add
...
- a
...
- reference
...
- to
...
- SoftArtisans.OfficeWriter.ExcelWriter.dll
...
SoftArtisans.OfficeWriter.ExcelWriter.dll
...
- is
...
- located
...
- under
...
- Program
...
- Files
...
- >
...
- SoftArtisans
...
- >
...
- OfficeWriter
...
- >
...
- dotnet
...
- >
...
- bin
Writing the Code
| Info | ||
|---|---|---|
| ||
The code behind for this part of the tutorial can be found under Extended Sales Summary/Part1.aspx.cs _. {info} |
There
...
are
...
two
...
main
...
sections
...
of
...
code
...
that
...
will
...
be
...
covered:
...
ExcelApplicationcode to customize the template based on a list of countries that a user has selected- In the sample, this code is contained in the
GenerateTemplate()method
- In the sample, this code is contained in the
ExcelTemplatecode to bind data to the customized template- In the sample, this code is contained in the
PopulateTemplate()method
- In the sample, this code is contained in the
Getting Started
1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind:
| Code Block |
|---|
using SoftArtisans.Office.ExcelWriter;{newcode}
|
2.
...
At
...
the
...
top
...
of
...
the
...
class
...
definition,
...
define
...
global
...
variations
...
for
...
the
...
ExcelApplication
...
,
...
ExcelTemplate
...
,
...
and
...
...
objects:
...
| Code Block |
|---|
private ExcelApplication xla;
private ExcelTemplate xlt;
private Workbook wb;
{newcode}
{info:title=Following the Sample Code}
In the sample code, you will also see {{List<string> selectedCountries}} defined with the global variables. This is the list that will contain the countries the user selects from the web form in the sample code.
{info}
h2. Customizing the template
1. Define a method to contain the {{ExcelApplication}} code for customizing the sheet. In the sample, this method is called {{GenerateTemplate()}}
{newcode}
|
| Info | ||
|---|---|---|
| ||
In the sample code, you will also see |
Customizing the template
1. Define a method to contain the ExcelApplication code for customizing the sheet. In the sample, this method is called GenerateTemplate()
| Code Block |
|---|
//Use ExcelApplication to make a copy of a regional worksheet for each
//country that is selected by the user.
protected void GenerateTemplate()
{
}
{newcode}
|
2.
...
In the helper method, instantiate the ExcelApplication object.
| Code Block |
|---|
ExcelApplication xla = new ExcelApplication();{newcode}
|
3.
...
Open
...
the
...
Workbook
...
template
...
file
...
with
...
...
method.
...
| Code Block |
|---|
Workbook wb = xla.Open(Page.MapPath(@"templates\template.xlsx"));{newcode}
|
4.
...
In
...
the
...
sample,
...
the
...
user
...
selects
...
anywhere
...
from
...
1-4
...
countries
...
to
...
include
...
in
...
the
...
report.
...
The
...
selected
...
countries
...
are
...
stored
...
in
...
the
...
List<string>
...
object,
...
selectedCountries
...
.
...
For
...
each
...
country,
...
make
...
a
...
copy
...
of
...
the
...
basic
...
template
...
sheet
...
with
...
...
,
...
place
...
the
...
copied
...
worksheet
...
at
...
the
...
end
...
of
...
the
...
workbook,
...
and
...
give
...
the
...
new
...
sheet
...
a
...
name.
...
In
...
this
...
example,
...
the
...
sheet
...
that
...
needs
...
to
...
be
...
copied
...
is
...
the
...
first
...
worksheet
...
in
...
the
...
template
...
file.
...
It
...
can
...
be
...
accessed
...
through
...
...
by
...
index
...
(0)
...
or
...
by
...
name
...
("SimpleTemplate").
...
| Code Block |
|---|
for (int i = 0; i < selectedCountries.Count; i++)
{
wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count, selectedCountries[i]);
}
{newcode}
|
6.
...
At
...
this
...
point
...
the
...
workbook
...
contains
...
a
...
worksheet
...
named
...
after
...
each
...
selected
...
country
...
in
...
addition
...
to
...
the
...
original
...
worksheet.
...
Hide
...
the
...
original
...
template
...
sheet
...
by
...
setting
...
...
.
| Code Block |
|---|
wb.Worksheets[0].Visibility = Worksheet.SheetVisibility.Hidden;{newcode}
|
7.
...
Select
...
the
...
first
...
visible
...
worksheet
...
to
...
be
...
displayed
...
when
...
the
...
file
...
first
...
is
...
opened
...
using
...
...
.
| Code Block |
|---|
wb.Worksheets[1].Select();{newcode}
|
8.
...
The
...
final
...
code
...
for
...
the
...
GenerateTemplate()
...
method
...
should
...
look
...
like
...
this:
...
| 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();
}
{newcode}
h2. Binding Data Dynamically
1. Define a method to contain the {{ExcelTemplate}} code for binding the data to the template. In the sample, this method is called {{PopulateTemplate()}}
{newcode}
|
Binding Data Dynamically
1. Define a method to contain the ExcelTemplate code for binding the data to the template. In the sample, this method is called PopulateTemplate()
| Code Block |
|---|
//Use ExcelTemplate to bind data for each selected country
//to worksheets in the template, then populate the report
//with that data
protected void PopulateTemplate()
{
}
{newcode}
2. Instantiate a new {{ExcelTemplate}} object.
{newcode} |
2. In the helper method, instantiate a new ExcelTemplate object.
| Code Block |
|---|
ExcelTemplate xlt = new ExcelTemplate();{newcode}
|
3.
...
Pass
...
the
...
existing
...
ExcelApplication
...
and
...
Workbook
...
to
...
the
...
ExcelTemplate
...
object
...
using
...
ExcelTemplate.Open(ExcelApplication,
...
...
.
| Code Block |
|---|
xlt.Open(xla, wb);{newcode}
|
4.
...
Although
...
not
...
necessary,
...
it
...
may
...
be
...
useful
...
to
...
set
...
ExcelTemplate.RemoveExtraDataMarkers
...
to
...
true
...
.
...
This
...
will
...
tell
...
ExcelWriter
...
to
...
ignore
...
any
...
data
...
markers
...
that
...
are
...
not
...
bound
...
to
...
data
...
sets.
...
This
...
is
...
helpful
...
if
...
you
...
are
...
adding
...
the
...
data
...
binding
...
calls to the code one at a time.
| Code Block |
|---|
xlt.RemoveExtraDataMarkers = true;{newcode}
|
5.
...
For
...
each
...
selected
...
country,
...
data
...
needs
...
to
...
be
...
bound
...
to
...
the
...
corresponding
...
worksheet.
...
Define
...
a
...
...
object
...
for
...
future
...
use
...
and
...
set
...
up
...
a
...
for
...
loop
...
to
...
go
...
through
...
all
...
the
...
selected
...
countries.
...
| Code Block |
|---|
DataBindingProperties dataBindProps; for (int i = 10; i < selectedCountries.Count; i++) { } {newcode} |
The
...
next
...
few
...
steps
...
relate
...
to
...
code
...
contained
...
in
...
the
...
for
...
loop.
...
For
...
each
...
selected
...
country:
...
6.
...
Retrieve
...
the
...
name
...
of
...
the
...
country.
...
| Code Block |
|---|
string country = selectedCountries[i];{newcode}
|
7.
...
Instantiate
...
a
...
new
...
DataBindingProperties
...
object.
...
| Code Block |
|---|
dataBindProps = xlt.CreateDataBindingProperties();{newcode}
|
8.
...
When
...
a
...
data
...
set
...
is
...
bound
...
to
...
an
...
Excel
...
template,
...
any
...
data
...
markers
...
with
...
matching
...
syntax
...
will
...
be
...
populated
...
with
...
the
...
data
...
from
...
that
...
data
...
set.
...
This
...
can
...
be
...
problematic
...
if
...
your
...
template
...
contains
...
copied
...
sheets,
...
where
...
all
...
the
...
data
...
markers
...
are
...
identical.
...
To
...
get
...
around
...
this,
...
set
...
the
...
DataBindingProperties.WorksheetName
...
to
...
bind
...
a
...
data
...
set
...
only
...
to
...
a
...
particular
...
worksheet.
...
| Code Block |
|---|
dataBindProps.WorksheetName = country;{newcode}
|
9.
...
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[] headerValues = { "FY 2008", "Foreign Trade Division", country };
string[] headerNames = { "FiscalYear", "TradeDivision", "Country" };{newcode}
|
10.
...
Use
...
the
...
...
method
...
to
...
bind
...
the
...
header
...
data
...
to
...
the
...
data
...
markers
...
in
...
the
...
template
...
file
...
(i.e.
...
%%=Header.FiscalYear
...
).
...
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(specificInfo, headerTitles, "Header", dataBindProps);{newcode}
|
11.
...
Get
...
the
...
data
...
for
...
the
...
Top
...
and
...
Details
...
Sales
...
data
...
sets.
...
| Info | ||
|---|---|---|
| ||
In the sample project, we are parsing CSV files with query results, rather than querying a live database. The CSV files are available under the data directory. There is a copy of the CSV parser, |
These calls are to a helper method GetCSVData that parses the CSV files and returns a DataTable with the values.
| Code Block |
|---|
DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));
{newcode}
|
If
...
you
...
are
...
following
...
in
...
your
...
own
...
project
...
and
...
would
...
like
...
to
...
parse
...
the
...
CSV
...
files
...
as
...
well,
...
you
...
will
...
need
...
to:
...
- Add
...
- a
...
- reference
...
- to
...
GenericParsing.dll
...
- Include
GeneringParsingat the top of your code. - Add the
GetCSVDatamethod that can be found in the sample code.
12. 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.
| Code Block |
|---|
xlt.BindData(dts, "Top", dbp);
xlt.BindData(dts2, "Details", dbp);
{newcode}
|
13.
...
The
...
final
...
for
...
loop
...
should
...
look
...
like
...
this:
...
| Code Block |
|---|
for (int i = 10; i < selectedCountries.Count; i++) { string country = selectedCountries[i]; dataBindProps = xlt.CreateDataBindingProperties(); dataBindProps.WorksheetName = country; string[] headerValues = { "FY 2008", "Foreign Trade Division", country }; string[] headerNames = { "FiscalYear", "TradeDivision", "Country" }; xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps); DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv")); DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv")); xlt.BindData(dts, "Top", dataBindProps); xlt.BindData(dts2, "Details", dataBindProps); } {newcode} |
14.
...
Call
...
...
to
...
import
...
all
...
data
...
into
...
the
...
file.
...
| Code Block |
|---|
xlt.Process();{newcode}
|
15.
...
Call
...
...
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.
...
| Code Block |
|---|
xlt.Save(Page.Response, "output.xlsx", false);{newcode}
|
16.
...
The
...
final
...
code
...
for
...
PopulateTemplate()
...
should
...
look
...
like
...
this:
...
| Code Block |
|---|
protected void PopulateTemplate()
{
xlt = new ExcelTemplate(); //Create a new ExcelTemplate object
//Pass the existing ExcelApplication object and open Workbook
//to the ExcelTemplate object
xlt.Open(xla, wb);
//RemoveExtraDataMarkers makes all data markers optional
//so ExcelWriter will not throw an error if any data markers
//aren't bound to data
xlt.RemoveExtraDataMarkers = true;
//For each country, retrieve and bind data to the worksheet
DataBindingProperties dataBindProps;
for (int i = 10; i < selectedCountries.Count; i++)
{
string country = selectedCountries[i];
//Create a new DataBindProperties object
dataBindProps = xlt.CreateDataBindingProperties();
//Since the data markers for the copied sheets are all the same,
//specify the worksheet that the data should be bound to
dataBindProps.WorksheetName = country;
//Retrieve the data and column names for the header on the sheet
string[] headerValues = { "FY 2008", "Foreign Trade Division", country };
string[] headerNames = { "FiscalYear", "TradeDivision", "Country" };
//Bind the header row data to the worksheet
xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps);
//Get the data for the top 5 sales and all the sales within the year.
//In this example, GetCSVData method calls on the GenericParsing library
//to parse and export data from the CSV files located in the //data directory
DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));
//Call ExcelTemplate.BindData to bind the data to the template
xlt.BindData(dts, "Top", dataBindProps);
xlt.BindData(dts2, "Details", dataBindProps);
}
//Process and stream the final report back to the user
xlt.Process();
xlt.Save(Page.Response, "Output.xlsx", false);
}
{new code}
|
17.
...
Now
...
you
...
may
...
run
...
your
...
code.
...
Just
...
call
...
GenerateTemplate()
...
and
...
PopulateTemplate()
...
to
...
customize
...
and
...
populate
...
your
...
template.
...
Here
...
is
...
an
...
example
...
of
...
what
...
the
...
sample
...
will
...
look
...
like:
Note that there are multiple worksheets, each with the tab name and data for that region.
Downloads
You can download the code for the Extended Sales Summary here.