Page tree

Versions Compared

Key

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

Table of

...

Contents

Table of Contents
maxLevel1

Hello World with ExcelTemplate

ExcelWriter's ExcelTemplate 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. 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.

...

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

    Code Block
    languagec#
    using SoftArtisans.OfficeWriter.ExcelWriter;
    Code Block
    languagevb
     Imports SoftArtisans.OfficeWriter.ExcelWriter



  2. Instantiate the ExcelTemplate object.

    Code Block
    languagec#
    ExcelTemplate XLT = new ExcelTemplate();
    Code Block
    languagevb
    Dim XLT As New ExcelTemplate()

     

     

  3. Open the template file with ExcelTemplate.Open.

    The ExcelTemplate object corresponds to a single template file, so a given ExcelTemplate instance can only have one template file open.

    Code Block
    languagec#
    XLT.Open(Page.MapPath("templates\\Hello World.xlsx"));
    Code Block
    languagevb
    XLT.Open(Page.MapPath("templates\Hello World.xlsx"))

     

     

     

  4. Create a DataBindingProperties object

    Code Block
    languagec#
    DataBindingProperties DataProps = XLT.CreateDataBindingProperties();
    Code Block
    languagevb
    Dim DataProps As DataBindingProperties = XLT.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

    Code Block
    languagec#
    string value = DataValueBox.Text.Trim();
    XLT.BindCellData(value, "DataValue", DataProps);
    Code Block
    languagevb
    Dim value As String = DataValueBox.Text.Trim()
    XLT.BindCellData(value, "DataValue", DataProps)

    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.

    Code Block
    languagec#
    XLT.Process();
    Code Block
    languagevb
    XLT.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

    Code Block
    languagec#
    XLT.Save(Page.Response, "Output.xlsx", false);
    Code Block
    languagevb
    XLT.Save(Page.Response, "Output.xlsx", False)

    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.



...