Problem

Using the CONTINUE modifier in a template to generate a report may show an unexpected behavior. Breaking down the different scenarios, we find that the behavior changes with data source type and amount of data available.

As described in our documentation, the Continue modifier enables data from a single data source to span multiple worksheets using the ExcelTemplate object. Without the modifier, if the data source for the ExcelTemplate uses a forward-only cursor, such as with a DataReader, the second worksheet with the same data marker will automatically start from the next row of data. If the data source is scrollable, however, the second worksheet with the same data marker will rewind to the first row of data. The Continue modifier tells the ExcelTemplate object to start from the next row even if the data source is scrollable. More about data markers and modifiers here.

In version 7.1 and below, the above description is accurate if there is enough data to reach the last data marker with a continue modifier before the data source has exhausted. However, if the data source gets exhausted prior to that point, the ExcelTemplate object will try to rewind the data source which may result in an exception thrown (for forward-only data sources) or duplication of data (for scrollable data sources).

Here are the two specific cases:

1. When using a forward-only data source, the ExcelTemplate object will throw a runtime exception on the Process method: “Exhausted Data Markers” because it has encountered a new data marker but there is no more data available.
2. When using a scrollable data source, the ExcelTemplate object will rewind the data source and will result in duplicated data on both worksheets.

Solution

Option 1: Upgrade to ExcelWriter 7.5 or later (recommended)

This issue was fixed in version 7.5 by applying more reasonable behaviors to the edge cases. With the new behavior, if the data has exhausted before reaching all data markers with a CONTINUE modifier, ExcelTemplate will not attempt to rewind the data source and will simply remove the extra data markers (similar to the behavior of the “optional” modifier).

Here is a table comparing the old and new behaviors in all scenarios:

Option 2: Divide the data

If the template file cannot be modified at runtime, divide the data according to the number of sheets containing data markers with the CONTINUE modifier. This can be done by adjusting the MaxRows setting so there will be data available to the last of these data markers.

Option 3: Use ExcelApplication

Alternatively, if we have the ExcelApplication object available (included in OfficeWriter Enterprise Edition) we can modify the template to contain the correct number of sheets in the workbook. This way we can keep MaxRows a constant. It is possible to pass a template document between the application object and the template object in memory.

Follow

Related posts: