Hello World with

...

ExcelApplication

ExcelWriter's ExcelTemplate ExcelApplication approach allows you to write data to a template file that contains data markers. The data markers tell ExcelWriter where to bind specific sets of data. provides you with full programmatic control over the Excel file formats (XLS, XLSX, XLSM). This includes the ability to create and modify: charts, formulas, formatting, data validation, conditional formatting, worksheet protection, images and more! 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 create a new workbook and write a 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.

Diving right into the code

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

...

1. Code Block

...

1. language

...

1. c#

...

1. using SoftArtisans.OfficeWriter.ExcelWriter;

...

...

1. Code Block
   language vb
   Imports SoftArtisans.OfficeWriter.ExcelWriter

...

1. 
   
   

2. Instantiate the

...

1. ExcelApplication object

   Code Block
   language c#
   ExcelApplication XLAPP = new

...

1. ExcelApplication();

   Code Block
   language vb
   Dim

...

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.

...

oXLT.Open(Page.MapPath("Hello World.xlsx"));

...

4. Create a DataBindingProperties object

...

DataBindingProperties oDataProps = oXLT.CreateDataBindingProperties();

...

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

...

string value = DataValueBox.Text.Trim();
oXLT.BindCellData(value, "DataValue", oDataProps);

...

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 ExcelTemplate.Process() to insert the data into the file

...

oXLT.Process();

...

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

...

1. XLAPP As New ExcelApplication()

   Unlike the ExcelTemplate object, which represents a single file, the ExcelApplication works as a file generation engine. The ExcelApplication object can be used to create, open, and save multiple workbooks.

    

    

2. Create a new workbook with ExcelApplication.Create(FileFormat)

   Code Block
   language c#
   Workbook WB = XLAPP.Create(ExcelApplication.FileFormat.Xlsx);

   Code Block
   language vb
   Dim WB As Workbook = XLAPP.Create(ExcelApplication.FileFormat.Xlsx)

    

   ExcelWriter has the ability to create Excel 2003 (XLS) files and Excel 2007 (XLSX) files, but cannot convert between formats. The file format must be declared when the workbook is created and the file extension of the output file must match when the file is saved.

   ExcelApplication.Create(FileFormat) automatically creates the first worksheet in the workbook for you.

   
    

3. Access the first worksheet through the Workbook.Worksheets collection

   Code Block
   language c#
   Worksheet WKST = WB.Worksheets[0];

   Code Block
   language vb
   Dim WKST As Worksheet = WB.Worksheets(0)

   You can access worksheets by name (e.g. "Sheet1") or by index (shown above), but ExcelWriter will throw an exception if you attempt to access a worksheet that does not exist.

    

    

4. Write the value from the web form into a cell

   Code Block
   language c#
   string value = DataValueBox.Text.Trim(); WKST.Cells[0,0].Value = value;

   Code Block
   language vb
   Dim value As String = DataValueBox.Text.Trim() WKST.Cells(0, 0).Value = value

   It is important to note that ExcelWriter indices are all 0-indexed, unlike Excel indices, which are 1-indexed. This is importing when working with cells, rows, columns, and worksheet positions in ExcelWriter. You can also reference cells by name; in this case, it would be "A1".

    

    

5. Save the workbook

   Code Block
   language c#
   XLAPP.Save(WB, Page.Response, "Output.xlsx", false);

   Code Block
   language vb
   XLAPP.Save(WB,

...

1. Page.Response, "Output.xlsx", False)

   ExcelApplication.Save has the same output options as ExcelTemplate: 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.

...

1. Remember that the file extension

...

1. must match the

...

1. file

...

1. format specified when the workbook was created.

    

    

2. Run the code. In the output file, you will see that the

...

1. custom text

...

1. from the form has been inserted into cell "A1".
   

SCREENSHOT

Congratulations, you have completed Hello World for ExcelTemplate!

1. Image Added 

Final Code

using SoftArtisans.OfficeWriter.ExcelWriter;
...
ExcelApplication XLAPP = new ExcelApplication();
Workbook WB = XLAPP.Create(ExcelApplication.FileFormat.Xlsx);
Worksheet WKST = WB.Worksheets[0];
string value = DataValueBox.Text.Trim();
WKST.Cells[0,0].Value = value;
XLAPP.Save(WB, Page.Response, "Output.xlsx", false);

Include SoftArtisans.OfficeWriter.ExcelWriter
...
Dim XLAPP As New ExcelApplication()
Dim WB As Workbook = XLAPP.Create(ExcelApplication.FileFormat.Xlsx)
Dim WKST As Worksheet = WB.Worksheets(0)
Dim value As String = DataValueBox.Text.Trim()
WKST.Cells(0, 0).Value = value
XLAPP.Save(WB, Page.Response, "Output.xlsx", False)

Downloads

You can download the code for the _Hello World_ tutorial as a Visual Studio solution.

• [ExcelWriter_HelloWorldC#.zip]

• [ExcelWriter_HelloWorldVB.zip]