.
...
Table |
|---|
...
of |
|---|
...
Contents | |
|---|---|
|
Hello World with ExcelTemplate
ExcelWriter's
...
ExcelTemplate
...
approach
...
allows
...
you
...
to
...
write
...
data
...
to
...
a
...
template
...
file
...
that
...
contains
...
...
...
.
...
The
...
data
...
markers
...
tell
...
ExcelWriter
...
where
...
to
...
bind
...
specific
...
sets
...
of
...
data.
...
This
...
tutorial
...
will
...
show
...
you
...
the
...
basics
...
on
...
how
...
to
...
dynamically
...
insert
...
data
...
into
...
a
...
worksheet
...
using
...
ExcelTemplate
...
by
...
taking
...
custom
...
text
...
from
...
a
...
web
...
form
...
textbox
...
and
...
inserting
...
it
...
into
...
a
...
template
...
file.
...
Setting up the template file
The template file for Hello World can be found under \templates\Hello
...
World.xlsx
...
.
...
We
...
are
...
going
...
to
...
bind
...
a
...
single
...
string
...
value
...
to
...
a
...
cell
...
in
...
a
...
template
...
file.
...
To
...
do
...
this,
...
we
...
will
...
first
...
need
...
to
...
add
...
a
...
data
...
marker
...
to
...
the
...
cell
...
where
...
we
...
want
...
the
...
value
...
to
...
appear.
...
We've
...
already
...
added
...
the
...
single
...
data
...
marker
...
'%%=$DataValue'
...
to
...
the
...
Hello
...
World.xlsx
...
template
...
file.
...
All
...
ExcelWriter
...
data
...
markers
...
are
...
prefaced
...
with
...
'%%='
...
and
...
the
...
additional
...
'$'
...
sign
...
means
...
that
...
the
...
data
...
source
...
for
...
this
...
data
...
marker
...
is
...
1-dimensional
...
(e.g.
...
1-dimensional
...
array
...
or
...
single
...
value).
...
'DataValue'
...
is
...
the
...
data
...
marker
...
ID
...
we'll
...
use
...
to
...
bind
...
the
...
data
...
to
...
this
...
data
...
marker.
...
SCREEN
...
SHOT
...
Now
...
the
...
template
...
file
...
is
...
done.
...
Next
...
is
...
writing
...
the
...
code
...
to
...
bind
...
the
...
string
...
value
...
to
...
the
...
data
...
marker.
...
Writing the code
This section refers to the code-behind
...
for
...
the
...
ExcelTemplate
...
Hello
...
World
...
sample:
...
ExcelTemplate_HelloWorld.aspx.
...
[cs/vb
...
]
...
.
...
The
...
corresponding
...
web
...
form
...
is
...
ExcelTemplate_HelloWorld.aspx
...
.
...
1.
...
Include
...
the
...
SoftArtisans.OfficeWriter.ExcelWriter
...
namespace
...
in
...
the
...
code
...
behind.
...
| Csharp | ||||||
|---|---|---|---|---|---|---|
| }
| |||||
using SoftArtisans.OfficeWriter.ExcelWriter;
{csharp}
{vbnet}
Imports |
| Vbnet |
|---|
Imports SoftArtisans.OfficeWriter.ExcelWriter {vbnet} |
2.
...
Instantiate
...
the
...
...
object.
...
| Csharp | ||||||
|---|---|---|---|---|---|---|
| }
| |||||
ExcelTemplate oXLT = new ExcelTemplate();
{csharp}
{vbnet}
{vbnet}
|
| Vbnet |
|---|
3.
...
Open
...
the
...
template
...
file.
...
The
...
ExcelTemplate
...
object
...
corresponds
...
to
...
a
...
single
...
template
...
file,
...
so
...
a
...
given
...
ExcelTemplate
...
instance
...
can
...
only
...
have
...
one
...
template
...
file
...
open.
...
| Csharp | ||||||
|---|---|---|---|---|---|---|
| }
| |||||
oXLT.Open(Page.MapPath("Hello World.xlsx"));
{csharp}
{vbnet}
{vbnet}
|
| Vbnet |
|---|
4.
...
Create
...
a
...
...
object
| Csharp | ||||
|---|---|---|---|---|
| ||||
DataBindingProperties oDataProps = oXLT.CreateDataBindingProperties();
{csharp}
{vbnet}
{vbnet}
The {{DataBindingProperties}} object can be used to change the behavior of how data is imported. For example, if we were importing multiple rows of data, we can use the {{[DataBindingProperties.MaxRows]}} property to limit the number of rows that are imported. In this sample, we won't be changing any of the import properties, but we still need the {{DataBindingProperties}} object to bind data.
5. Get the data and call {{[ExcelTemplate.BindCellData|http://wiki.softartisans.com/display/EW8/ExcelTemplate.BindCellData%28Object%2C+String%2C+DataBindingProperties%29]}} to bind the data to the data marker
{csharp:5} |
| Vbnet |
|---|
The DataBindingProperties object can be used to change the behavior of how data is imported. For example, if we were importing multiple rows of data, we can use the DataBindingProperties.MaxRows property to limit the number of rows that are imported. In this sample, we won't be changing any of the import properties, but we still need the DataBindingProperties object to bind data.
5. Get the data and call ExcelTemplate.BindCellData to bind the data to the data marker
| Csharp | ||||
|---|---|---|---|---|
| ||||
string value = DataValueBox.Text.Trim();
oXLT.BindCellData(value, "DataValue", oDataProps);
{csharp}
{vbnet}
{vbnet}
In this sample, |
| Vbnet |
|---|
In this sample, we're
...
pulling
...
the
...
single
...
value
...
from
...
the
...
text
...
box
...
on
...
the
...
web
...
form.
...
Since
...
we're
...
binding
...
a
...
single
...
value,
...
we
...
use
...
BindCellData()
...
and
...
specify
...
the
...
data
...
marker
...
ID.
...
Note
...
that
...
we
...
need
...
to
...
pass
...
the
...
DataBindingProperties
...
object,
...
even
...
though
...
none
...
of
...
the
...
DataBindingProperties
...
are
...
active.
...
6.
...
Call
...
...
to
...
insert
...
the
...
data
...
into
...
the
...
file
...
| Csharp | ||||||
|---|---|---|---|---|---|---|
| }
| |||||
oXLT.Process();
{csharp}
{vbnet}
{vbnet}
{{ |
| Vbnet |
|---|
ExcelTemplate.Process()
...
handles
...
everything
...
relating
...
to
...
inserting
...
the
...
data
...
into
...
the
...
file.
...
If
...
we
...
were
...
importing
...
multiple
...
rows
...
of
...
data,
...
Process()
...
would
...
handle
...
inserting
...
the
...
new
...
physical
...
rows
...
into
...
the
...
Excel
...
worksheet.
...
7.
...
Save
...
the
...
output
...
| Csharp | ||||||
|---|---|---|---|---|---|---|
| }
| |||||
oXLT.Save(Page.Response, "Output.xlsx", false);
{csharp}
{vbnet}
{vbnet}
There are several options for {{[ExcelTemplate.Save]}} including: save to disk, save to memory stream, stream back to the client inline, and stream back to the client as an attachment. In this case, we're streaming the workbook back to the client as an attachment.
Also, ExcelWriter does not convert between file formats, so it is important that the file extension on the output file matches the file extension of the original template file.
8. Go to the web form page, *ExcelTemplate_HelloWorld.aspx*, to try out the sample. In the output file, you will see that the data marker has been replaced with the custom text entered in the form.
SCREENSHOT
Congratulations, you have completed _Hello World_ for ExcelTemplate!
h1. Final Product
{csharp} |
| Vbnet |
|---|
There are several options for ExcelTemplate.Save including: save to disk, save to memory stream, stream back to the client inline, and stream back to the client as an attachment. In this case, we're streaming the workbook back to the client as an attachment.
Also, ExcelWriter does not convert between file formats, so it is important that the file extension on the output file matches the file extension of the original template file.
8. Go to the web form page, ExcelTemplate_HelloWorld.aspx, to try out the sample. In the output file, you will see that the data marker has been replaced with the custom text entered in the form.
SCREENSHOT
Congratulations, you have completed Hello World for ExcelTemplate!
Final Product
| Csharp |
|---|
using SoftArtisans.OfficeWriter.ExcelWriter;
ExcelTemplate oXLT = new ExcelTemplate();
oXLT.Open(Page.MapPath("Hello World.xlsx"));
DataBindingProperties oDataProps = oXLT.CreateDataBindingProperties();
string value = DataValueBox.Text.Trim();
oXLT.BindCellData(value, "DataValue", oDataProps);
oXLT.Process();
oXLT.Save(Page.Response, "Output.xlsx", false);
{chsarp}
{vbnet}
|
| Vbnet |
|---|
... {vbnet} |