Tag Archives: data markers

OfficeWriter

Side-by-Side Data Markers (3 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 1: Updating displayed ranges from hidden ranges
PART 2: Clearing out bad content

Part 3: Putting it together

For each named range in the workbook, UpdateRange() will need to be called. For each group of side-by-side data markers, ClearBadDisplay() will need to be called. I put UpdateRange() and ClearBadDisplay() in a module and called the following code in the WorkbookOpen() event: Continue reading Side-by-Side Data Markers (3 of 3) →

OfficeWriter

Side-by-Side Data Markers (2 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 1: Updating displayed ranges from hidden ranges

Part 3: Putting it together

Part 2: Clearing out bad content

THE PROBLEM:

ExcelWriter will try to populate any data markers that it finds in the template. This means that if the macro updates the ranges on the destination worksheet before the data markers are populated, the data markers will be displayed on the destination sheet and ExcelWriter will try to populate those data markers as well. The macro will still update the ranges, but it will just write over the bad formatting, so the end result still has extra rows: Continue reading Side-by-Side Data Markers (2 of 3) →

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 →

Excel, OfficeWriter

Grouping and Nesting on Multiple Columns

Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers to display flat data in a grouped and nested format using the ExcelTemplate object and a OOXML (.xlsx) template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!

Introduction

In my previous post, I showed how José, a salesperson at PostOne Bicycles, was able to easily pick out the sales made by the sales team in different regions by using a %%group marker. His coworker, Jillian, wants to go further than that, breaking down sales first by region, then by Salesperson, so that she can see from a glance whose sales are strong or weak in each region, and whether she needs to improve her performance. That’s easy to do thanks to the nesting feature of ExcelTemplate grouping and nesting.

Nested grouping with multiple groups

Jillian can group by multiple columns with nesting by placing additional %%group markers in more columns. Continue reading Grouping and Nesting on Multiple Columns →

Excel, OfficeWriter

Using ExcelTemplate Grouping

Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers to display flat data in a grouped and nested format using the ExcelTemplate object and a OOXML (.xlsx) template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!

Introduction

If you have hierarchical data in an Excel report, grouping the data is a good way to make it easier to read. ExcelTemplate now features grouping a nesting options that make it easy to group hierarchical data. The basic feature of ExcelTemplate grouping and nesting is removing unnecessary repeated values from a field if it is given hierarchical data in a flat format. We’ll focus on a single group in this post; for information on grouping and nesting multiple groups, you can check our documentation, and be sure to check back here for further blog posts. Advanced features such as headers and footers are also discussed in our documentation and will also be described in a future blog post.

Creating a Data Set for grouping

Though grouping and nesting is meant to show hierarchical data, the input data for a template with grouping and nesting must be in flat format. This means that a data set for nesting and grouping will have repeated values for fields that are higher in the hierarchy. In this case, salesperson José Saraiva of PostOne Bicycles is faced with sales data that looks like this: Continue reading Using ExcelTemplate Grouping →