Even when using the default options (by DSD or by fDocs Template) fSheets first makes a template and then applies it to the data. So although a template is not essential it may be slightly faster than always using the default.

Unlike fDocs, there is no fSheets Excel Add-in. It is quite a simple process to design the layout and content using Excel itself, especially if you start by downloading a default template (see fSheets Designer).

fSheets uses Excel Named Ranges to identify where and what is to be included in the output.

 

Named Ranges are shown in the name box on the left side of the formula bar. Normally this shows the cell name (e.g. A2) but fSheets uses these to locate where data is to be placed. In these cases cells A2:C2 are used to place and repeat the Warnings data group records and cell A2 is used to place the WarningType field within the Warnings data group.

These will all be set up in a default template but you can add to them yourself by marking a cell or cells and typing the required name in the name box and pressing the return key. To edit named ranges go to the Formulas ribbon and click on Name Manager.

fSheets uses a naming convention for cells that it is to use. They all start will “fSeries.” Followed by various options:

fSeries.dg. followed by the id of a data group identifies a group of cells to be repeated for each record in the data group (e.g. fSeries.dg.Warnings).

fSeries.fld. followed by a data group and then a field name identifies a field from a data group that is to be placed at this position (e.g. fSeries.fld.Warnings.WarningType). These would normally be within a data group area but don’t have to be.

fSeries.ue.followed by the id of a user entry would insert the value of that user entry (e.g. fSeries.ue.ClientId)

There are some others that are used in the setup of fSheets. These are cover in advanced sections.

If you download your template from the Designer’s Edit Existing Template option, selecting the Include Schema option, the template will include a temporary worksheet called fSeries.Schema. This can be used to add cells for any field in the DSD by cutting and pasting named ceels from the worksheet into your template. This will ensure that the fields range name is correct and isnot duplicating any other use of the field. The worksheet also includes the label/heading for each field that can be copied into the template, and the range names to use if you wish to mark an area to be repeated for a data group, providing you with the next unique range name to use. The fSeries.Schema worksheet will be removed when a spreadsheet is generated.

In a default template all data groups are laid out in rows across the worksheet, with child data groups below, except for data groups marked as “Design at Top Level” in the fDocs settings which are laid out with labels in column A and values in column B. You can override this behaviour by appending “.h” or “.v” for horizontal rows or vertical columns (e.g. fSeries.dg.Warnings.v would lay out the Warning with labels in column A and value from column B across the sheet).

When you generate a default template the named ranges are created automatically but you can cut and paste them in your worksheets to any location you wish. The same name cannot appear more than once in a workbook so in order to have a value or data group more than once, simply suffix the name to make if unique, preserving the main structure (e.g. fSeries.dg.Warnings.2 and fSeries.fld.Warnings.WarningType.3)

When the spreadsheet is generated the data group cell ranges will be repeated for each record in the data group (shifting other cells down or right according to the layout) and the field ranges will be filled with the corresponding values.

If a data group is the parent of other data groups its child data groups are included within the parent’s named range but follow the same pattern as before. In this example there is a parent data group called “Family” that contains details of Family Activities.

Once you have specified where data is to be inserted you can apply whatever style and formatting you wish. fSheets will maintain your settings as it generates the document. For example, date fields may appear to be date/time (dd/mm/yy mm:hh:ss) but you may want only the date (dd MMM yyyy). Change the named range cell for the field by formatting the cell accordingly and the generated spreadsheet will use this format.

When a default template is generated it will contain a worksheet called fSeries.DSD. This contains some information about the template that is essential for the generation of outputs. In particular the named range “fSeries.DSD” contains the id of the DSD that is used to gather the data for the template. This named range MUST be present somewhere in the template, although it need not be in fSeries.DSD worksheet. In fact the fSeries.DSD worksheet does not have to be present, just the named ranges that the template generator inserts. The fSeries.DSD worksheet includes instructions on how to use it if required. It is automatically removed from the generated spreadsheet