SmartMarker Task Tutorial for Template-Based Document Generation

Introduction

In this tutorial, you’ll learn how to use the SmartMarker task in Aspose.Cells Cloud API to generate dynamic Excel documents from templates. SmartMarker is a powerful feature that allows you to create Excel templates with special markers that get replaced with actual data during processing, enabling efficient report generation and document automation.

Prerequisites

Before starting this tutorial, make sure you have:

  • An Aspose Cloud account (sign up for free here)
  • Your Client ID and Client Secret from the Aspose Cloud dashboard
  • Basic understanding of REST API concepts
  • Familiarity with Excel and XML data structures
  • A text editor for creating/editing XML files

Understanding SmartMarker

SmartMarker is a template engine for Excel documents. It works by:

  1. Creating an Excel template with special markers (e.g., &=DataSet.Table.Column&)
  2. Providing data (typically in XML format) that matches the structure expected by the markers
  3. Processing the template to replace markers with actual data values

This approach separates your data from your document design, making it easy to generate different documents using the same template but different data.

Tutorial: Basic SmartMarker Template Processing

Step 1: Create a Simple Excel Template

First, create an Excel template (Designer.xlsx) with SmartMarker tags:

  1. Open Excel and create a new workbook
  2. Add headers like “Product”, “Quantity”, “Price”, “Total” in row 1
  3. In row 2, add SmartMarker tags:
    • A2: &=Products.Product.Name&
    • B2: &=Products.Product.Quantity&
    • C2: &=Products.Product.Price&
    • D2: &=Products.Product.Price*Products.Product.Quantity&

Step 2: Create XML Data File

Create an XML file (DataSet.xml) with data that matches your SmartMarker structure:

<?xml version="1.0" encoding="utf-8" ?>
<dataroot>
  <Products>
    <Product>
      <Name>Laptop</Name>
      <Quantity>5</Quantity>
      <Price>1200</Price>
    </Product>
    <Product>
      <Name>Smartphone</Name>
      <Quantity>10</Quantity>
      <Price>800</Price>
    </Product>
    <Product>
      <Name>Tablet</Name>
      <Quantity>7</Quantity>
      <Price>600</Price>
    </Product>
  </Products>
</dataroot>

Step 3: Structure Your SmartMarker Task Request

Now, create the API request to process the template with your data:

<TaskData>
  <Tasks>
    <TaskDescription>
      <TaskType>SmartMarker</TaskType>
      <SmartMarkerTaskParameter>
        <SourceWorkbook>
          <FileSourceType>CloudFileSystem</FileSourceType>
          <FilePath>Designer.xlsx</FilePath>
        </SourceWorkbook>
        <DestinationWorkbook>
          <FileSourceType>InMemoryFiles</FileSourceType>
          <FilePath>Temp.xlsx</FilePath>
        </DestinationWorkbook>
        <xmlFile>
          <FileSourceType>CloudFileSystem</FileSourceType>
          <FilePath>DataSet.xml</FilePath>
        </xmlFile>
      </SmartMarkerTaskParameter>
    </TaskDescription>
    <TaskDescription>
      <TaskType>SaveResult</TaskType>
      <SaveResultTaskParameter>
        <ResultSource>InMemoryFiles</ResultSource>
        <ResultDestination>
          <DestinationType>OutputStream</DestinationType>
          <InputFile>Temp.xlsx</InputFile>
          <OutputFile>Output.xlsx</OutputFile>
        </ResultDestination>
      </SaveResultTaskParameter>
    </TaskDescription>
  </Tasks>
</TaskData>

Step 4: Execute the API Request

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/task/runtask" \
  -H "accept: application/json" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -d "YOUR_XML_REQUEST"

Using C# SDK

var xml = @"<TaskData>
  <Tasks>
    <TaskDescription>
      <TaskType>SmartMarker</TaskType>
      <SmartMarkerTaskParameter>
        <SourceWorkbook>
          <FileSourceType>CloudFileSystem</FileSourceType>
          <FilePath>Designer.xlsx</FilePath>
        </SourceWorkbook>
        <DestinationWorkbook>
          <FileSourceType>InMemoryFiles</FileSourceType>
          <FilePath>Temp.xlsx</FilePath>
        </DestinationWorkbook>
        <xmlFile>
          <FileSourceType>CloudFileSystem</FileSourceType>
          <FilePath>DataSet.xml</FilePath>
        </xmlFile>
      </SmartMarkerTaskParameter>
    </TaskDescription>
    <TaskDescription>
      <TaskType>SaveResult</TaskType>
      <SaveResultTaskParameter>
        <ResultSource>InMemoryFiles</ResultSource>
        <ResultDestination>
          <DestinationType>OutputStream</DestinationType>
          <InputFile>Temp.xlsx</InputFile>
          <OutputFile>Output.xlsx</OutputFile>
        </ResultDestination>
      </SaveResultTaskParameter>
    </TaskDescription>
  </Tasks>
</TaskData>";

ServiceHelper helper = new ServiceHelper(sid, key);
using (HttpWebResponse response = helper.CallPost("https://api.aspose.cloud/v3.0/cells/task/runtask", xml, "application/xml"))
{
    if (response.StatusCode == HttpStatusCode.OK)
    {
        System.Console.WriteLine("OK");
        Stream st = response.GetResponseStream();
        FileStream fs = new FileStream("Output.xlsx", FileMode.OpenOrCreate);
        st.CopyTo(fs);
    }
}

Understanding the Result

After processing, you’ll receive an Excel file where:

  • The SmartMarker tags have been replaced with actual data
  • Each product appears in its own row (SmartMarker automatically repeats rows for list data)
  • The calculated total is computed for each product

Advanced SmartMarker Features

Nested Data and Complex Templates

SmartMarker can handle nested data structures. For example, you could group products by category:

XML Data:

<dataroot>
  <Categories>
    <Category>
      <Name>Electronics</Name>
      <Products>
        <Product>
          <Name>Laptop</Name>
          <Quantity>5</Quantity>
          <Price>1200</Price>
        </Product>
        <!-- More products -->
      </Products>
    </Category>
    <Category>
      <Name>Accessories</Name>
      <Products>
        <!-- Products in this category -->
      </Products>
    </Category>
  </Categories>
</dataroot>

Template SmartMarkers:

  • &=Categories.Category.Name& (Category name)
  • &=Categories.Category.Products.Product.Name& (Product name within category)

Dynamic Ranges and Tables

You can create dynamic ranges that expand based on your data:

&=TableStart:Products.Product&
&=Products.Product.Name& | &=Products.Product.Quantity& | &=Products.Product.Price&
&=TableEnd:Products.Product&

Formulas and Calculations

SmartMarker supports Excel formulas in your templates:

&=SUM(D2:D100)& (Will calculate the sum of all product totals)

Try It Yourself

  1. Create a more complex template with:

    • Multiple tables from the same dataset
    • Formulas for subtotals and grand totals
    • Formatting based on data values

  2. Modify the XML data to test how your template handles different data scenarios:

    • More or fewer records
    • Different values
    • Missing data

Tutorial: Creating a Monthly Sales Report Template

Let’s create a practical example: a monthly sales report template.

Step 1: Create the Excel Template

Design a template with:

  • Title: “Monthly Sales Report for &=Report.Month& &=Report.Year&”
  • Headers: Region, Sales Rep, Products Sold, Revenue, Commission
  • SmartMarker row:
    • &=Sales.Region.Name&
    • &=Sales.Region.SalesRep.Name&
    • &=Sales.Region.SalesRep.ProductsSold&
    • &=Sales.Region.SalesRep.Revenue&
    • &=Sales.Region.SalesRep.Revenue*0.05& (5% commission calculation)
  • Summary area with markers for total sales, average sales, etc.

Step 2: Create the XML Data

<?xml version="1.0" encoding="utf-8" ?>
<dataroot>
  <Report>
    <Month>September</Month>
    <Year>2023</Year>
  </Report>
  <Sales>
    <Region>
      <Name>North</Name>
      <SalesRep>
        <Name>John Smith</Name>
        <ProductsSold>45</ProductsSold>
        <Revenue>28500</Revenue>
      </SalesRep>
      <SalesRep>
        <Name>Alice Jones</Name>
        <ProductsSold>52</ProductsSold>
        <Revenue>32400</Revenue>
      </SalesRep>
    </Region>
    <Region>
      <Name>South</Name>
      <SalesRep>
        <Name>Bob Johnson</Name>
        <ProductsSold>38</ProductsSold>
        <Revenue>24300</Revenue>
      </SalesRep>
    </Region>
    <!-- More regions and sales reps -->
  </Sales>
</dataroot>

Step 3: Process with SmartMarker Task

Use the same task structure as in the basic example, but with your new template and data files.

Common Issues and Troubleshooting

Issue: Markers Not Being Replaced

If your markers aren’t being replaced with data:

  • Verify the marker syntax: &=DataSet.Table.Column&
  • Check that XML data structure matches the marker structure
  • Ensure there’s no whitespace in your marker names

Issue: Missing or Incomplete Data

If some data is missing:

  • Verify all expected data exists in your XML
  • Check for case sensitivity issues (XML is case-sensitive)
  • Look for structural issues in your XML hierarchy

Best Practices for SmartMarker Templates

  1. Consistent Naming: Use consistent naming conventions in both templates and XML data
  2. Documentation: Document the expected XML structure in comments or a separate file
  3. Testing: Test your templates with various data scenarios to ensure they handle all cases
  4. Formatting: Apply Excel formatting to cells before adding SmartMarker tags
  5. Reusability: Design templates to be reusable with different data sources

What You’ve Learned

In this tutorial, you’ve learned:

  • How to create Excel templates with SmartMarker syntax
  • How to structure XML data for use with SmartMarker
  • How to process templates using the SmartMarker task
  • Advanced features like nested data and dynamic ranges
  • Best practices for template-based document generation

Further Practice

  1. Create a complex financial report template with multiple sections and calculations
  2. Design a template that generates charts based on the provided data
  3. Build a template that conditionally formats cells based on data values
  4. Create a multi-sheet template where each sheet represents different aspects of the same data

Next Steps

Now that you’ve mastered SmartMarker for template-based document generation, you might want to explore:

Helpful Resources