Tag Archives: exceltemplate

OfficeWriter

Side-by-Side Data Markers (1 of 3)

BACKGOUND:

The ExcelTemplate method of creating Excel files is to design a template in Excel that contains data markers. Data markers are cell values that begin with %%= or %%=$ that specifies a database column, variable, or array to insert into the spreadsheet column. ExcelWriter does this by inserting a new row into the worksheet for each row of data being imported. This means that anything below the data marker in the template will be pushed down as the new rows of data are imported.

Data markers from the same data set can be placed next to each other, but placing data markers from different data sets side-by-side in an ExcelTemplate can cause extra rows to be inserted into the smaller data sets.

This series of posts explains how to get rid of the extra rows in output files that result from placing data markers side by side. If you’re not a current ExcelWriter user, you can download a free evaluation and follow along!

Part 2: Clearing out bad content

Part 3: Putting it altogether

Part 1: Updating Displayed Ranges from Hidden Ranges

THE PROBLEM:

Putting data markers from different data sets next to each other can be tricky, especially when the data sets are different sizes. ExcelWriter will automatically insert enough rows to accommodate the largest set of data. This means that sometimes, it ends up looking okay. Continue reading Side-by-Side Data Markers (1 of 3) →

Excel, OfficeWriter

Adding Headers to a Grouped Spreadsheet

Introduction

In previous blog posts, I showed how José and Jillian, salespersons with PostOne Bicycles, used the new %%group marker to group and nest hierarchical data from a flat data set. In this tutorial, they will expand upon the concept by introducing headers to help separate each group, using the %%value() marker to show data values in headers and the (hide) modifier to hide data that is already shown in a header.

If you’re not a current ExcelWriter user, you can download a free evaluation and follow along!

Setup

This tutorial uses the same code and data as the original blog post. The template used in this tutorial starts where the previous blog post left off.

Adding Headers

Headers in ExcelWriter grouping and nesting are repeated for every new value in a grouped column. José marks the beginning of a header block is by using a %%header marker; the block is marked by the next %%header block (if you have nested headers) or the data marker row, as in this case. Continue reading Adding Headers to a Grouped Spreadsheet →