Build a document template

An XL Docs document starts from a template: an Excel workbook that you design, stored on a record in your app's Document Templates table. When someone selects the XL Docs button on a record, XL Docs opens a copy of the workbook, writes that record's field values into the cells you mapped, adds any lists of related records, and saves the result as an Excel, PDF, or CSV file.

This article walks through building a template end to end: the workbook, the Document Templates record, mapping fields to cells (the clist), choosing the sheet, choosing PDF pages, adding images and rich text, and building a CSV export. Lists of related records, such as line items, are covered in Data tables (subtables).

Note

The XL Docs Configurator is the easier way to do everything in this article. You pick a table, type a cell position next to each field, and save; the Configurator writes the clist and the other settings for you. See Configure a template. This article explains what the Configurator writes, so you can read, check, or fine-tune a template by hand.

How it fits together

• Master table: the table whose records you generate documents for, such as Projects, Orders, or Invoices. The XL Docs button lives here.

• Template workbook: the Excel file with your layout, attached to a Document Templates record.

• Document Templates record: the template file plus the clist, which maps master-record fields to cells, and settings such as Excel Sheet # and PDF Pages. Every field is described in Document Templates table reference.

• Document Subtable Excel records (optional): one per list of related records to include. See Data tables (subtables).

• The XL Docs button: a formula field on the master table that tells XL Docs which template to use, which record to process, and where to put the result. See Formula URL parameters and Output and storage.

Building a template by hand uses field IDs and table IDs from your app. If you don't know where to find them, see Find your Client ID, table IDs, and field IDs.

Step 1: Create the template workbook

Design the workbook in Excel so it looks the way the finished document should look. Then save it in one of these formats:

• .xlsx: Excel workbook.

• .xlsm: Excel macro-enabled workbook. You can use an .xlsm template, but not all macros are supported, so some macros may not work.

• .xls: Excel 97-2003 workbook.

No other file type is accepted, including Word documents. XL Docs reports This version of Excel Generator only supports .xls, .xlsx, or .xlsm template files. if the attached file is anything else. By default the generated document has the same file type as the template; you can also produce a PDF or CSV (see Output and storage).

What you can put in the workbook:

• Static content: logos, images, headings, labels, borders, colors, and fonts. Anything you don't map stays exactly as you designed it.

• Placeholder text such as [Name] or [Company Name] to help you lay out the sheet. It's optional; mapped cells are overwritten with the field values.

• Formulas, formatting, charts, and conditional formatting. XL Docs writes values into the cells in place, so these are kept and recalculate with the new values (see Formulas and Excel features).

• Several sheets (tabs). You choose which sheet each set of data goes on.

Note the cell address (such as B12) of every place where data should go. You'll use these addresses in the clist.

Step 2: Create the Document Templates record

1. Go to the Document Templates table in your app.

2. Add a new record and enter a Name, for example Employee Expense Template.

3. In Excel Template File, select Choose File and upload your workbook.

4. Save the record. The XL Docs Configurator button appears on the saved record.

5. Either select XL Docs Configurator to map the fields (recommended), or fill in clist, Master table name, Master table dbid, Excel Sheet #, and PDF Pages yourself as described below. Master table dbid is the master table's table ID (see Find your Client ID, table IDs, and field IDs).

To change the layout later, edit the workbook and upload it to the same record again. The mapping stays as long as the cell addresses don't change.

Step 3: Map fields to cells (clist)

The clist (column list) field on the Document Templates record says which field of the master record goes into which cell. The same syntax is used in the clist of a Document Subtable Excel record, where it gives the starting row of a list of records.

Format

A clist is a list of entries separated by periods (.). Each entry joins a cell and a field ID with a colon:

Cell[-N|D|I|H|F]:FID[:Height|Width]

• Cell: the Excel cell, such as A1, B5, or C10.

• -N, -D, -I, -H, -F (optional): a type suffix directly after the cell, described below.

• FID: the field ID of the Quickbase field to put in that cell. To look up a field ID, see Find your Client ID, table IDs, and field IDs.

• Height|Width (optional, images only): the image size in pixels.

For example, this clist puts four fields into cells B12 through B15:

B12:53.B13:17.B14:49.B15:54

XL Docs reads the clist without regard to case. Don't put spaces in the clist. To put the same field in two cells, add two entries, for example B6:6.E10:6. (In the Configurator you can type B6,E10 in Cell Position and it writes both entries.)

Type suffixes

Note

Some older configurations write a date as A12-D:D6 or A12:D6. XL Docs ignores a D in front of the field ID, so A12-D:D6 works the same as A12-D:6, but A12:D6 is a plain value with no date handling. Use A12-D:6.

Variables and column-letter references

An entry whose cell part has letters but no row number isn't written to a cell. Instead, it holds the field's value as a variable that data-table queries can use. Examples:

• RK:3 makes the master record's Record ID# (field 3) available as [RK] ("related key").

• AA:7.BB:10 make fields 7 and 10 available as [AA] and [BB].

• A:12 makes field 12 available as [A]. The Configurator adds column-letter entries like this automatically when a data-table query compares against a master field ("the value in the field (parent)").

A Document Subtable Excel record can then use the variable in its query, for example {'12'.EX.'[RK]'} to find the records whose field 12 (say, Related Order) equals the master record's Record ID#. Add variables at the end of the clist. See Data tables: filter the records.

Step 4: Choose the sheet (Excel Sheet #)

Excel Sheet # is the sheet the template clist writes to. It's positional: 1 is the first tab from the left, 2 the second, regardless of the tab names. For example, if the first tab is named Expense statement, it's still sheet 1. If the field is blank, XL Docs uses the first sheet.

To put master-record data on more than one sheet, add a Document Subtable Excel record for each extra sheet (see Which records do I need?).

Step 5: Choose the PDF pages (optional)

When the XL Docs button creates a PDF (docfmt=pdf), every sheet of the generated workbook becomes part of the PDF unless you set PDF Pages. Enter the sheet numbers to include, counted from the left starting at 1:

• 1,3,5: sheets 1, 3, and 5.

• 1-3: sheets 1 through 3.

• 1-5,8,10-12: sheets 1 through 5, then 8, then 10 through 12, as consecutive pages of the PDF.

XL Docs sorts the list and ignores duplicates. Listing more sheets than the workbook has stops generation with Something's wrong with the pages you selected. You entered more than the sheets you have in your template. In the PDF, each sheet's columns are fitted to the page width, and charts are included as images.

Images

To place an image, use the -I suffix. The field's value can be:

• a file attachment field in any Quickbase table (for example a logo, photo, or signature), including a lookup of one;

• a URL to an image on a web server, such as a formula URL field that returns a QR code image;

• a formula field that returns an <img> tag.

For the last option, create a Formula - Text field with Allow some HTML tags checked, and return the image tag without height or width attributes. For example, this shows a green circle from the Quickbase image library:

"<div><img src=\"https://images.quickbase.com/si/24/222-point_green.png\"></div>"

Image size. Add the size after the field ID as :Height|Width in pixels, height first. Leave one side blank to keep the image's proportions:

• E22-I:21:20|20: field 21 at E22, 20 pixels high and 20 wide.

• M12-I:275:200|200: field 275 at M12, 200 by 200.

• B2-I:15:200|: field 15 at B2, 200 pixels high, width in proportion.

• B2-I:63: field 63 at B2, original size.

In the Configurator, set the field's Type to Image on the Fields tab. The field then appears on the Images tab, where you enter Width (px or Auto) and Height (px or Auto). Leave a side blank or Auto for default sizing.

Images in data tables work the same way: each record's image is placed in its own row. If an image in the template clist can't be loaded, the cell shows Image Error. For an optional image such as a signature, see Tips and examples.

Rich text and HTML

A field that holds HTML, such as a Rich Text field or a Formula - Rich Text field, can be written with its formatting by using the -H suffix in the clist of the Document Templates or Document Subtable Excel record:

{ColumnLetterRowNumber}-H:FieldID

For example, to show field 12 in cell A5 with its formatting, use A5-H:12. The HTML is converted into formatted text in the Excel or PDF output.

Supported tags:

• <p></p>: paragraph.

• <font></font>: font, color, and size of text.

• <h1> through <h6>: headings.

• <a href></a>: hyperlink.

• <i></i>: italic.

• <u></u>: underline.

• <b></b>: bold.

• <strong></strong>: important (bold) text.

• <sub></sub>: subscript.

• <sup></sup>: superscript.

• <br>: line break.

• <strike></strike>: strikethrough.

Supported styling: the color, font-family, text-decoration, and font-size style properties, and the size, face, and color attributes of <font>. XL Docs removes <div> and <em> tags before converting, so use <p> and <i> instead. For example, in a Formula - Rich Text field:

"<font style=\"color:red;font-family:Magneto;font-size:12px;\">some text goes here</font>"

HTML tables. If the value is an HTML <table>, XL Docs writes it as a grid of cells starting at the mapped cell, one table row per sheet row. In a data table, each record's table goes in its own rows, and rows are inserted if a table is longer than Max Line items.

If the HTML can't be converted, the raw text is written instead. In the Configurator, choose the Type HTML Text.

Formulas and Excel features

XL Docs fills a copy of your workbook cell by cell, so the rest of the workbook works as designed:

• Formulas in the template are kept and calculate from the values XL Docs writes. For example, a TOTAL cell with =SUM(D12:D21) totals the line items. Map numbers with -N so formulas treat them as numbers.

• Formatting, merged cells, conditional formatting, and charts are kept.

• Pivot tables on the sheets XL Docs writes to are set to refresh when the file is opened.

• Macros: save the template as .xlsm to keep them; the output is .xlsm too. Not all macros are supported, so some macros may not work. Test the generated file before you rely on a macro.

• Subtotal and average rows that XL Docs adds to data tables are real =SUM() and average formulas (see Data tables: subtotals).

CSV output holds values only; formulas don't carry over into a CSV file.

Create a CSV export file

A popular use of XL Docs is to create an export file in comma-separated value (CSV) format to exchange with partners or other software systems. Export formats often need more than detail rows: for example one or more header rows, the detail records, and a footer row with totals, all generated on the fly. You build this the same way as any template, then set the button's docfmt to csv.

Example: weekly time sheet export

This export file has two header rows, one detail row per time sheet, and a footer:

The data comes from a Projects table with child tables: Projects → Project Employees → Weekly Time Sheets → Time Cards. Time cards are summed by day onto each weekly time sheet, and summary fields on Projects provide the header and footer totals.

1. Add the document template. Add a Document Templates record whose template file is a blank Excel workbook. Set the master table to Projects. No master fields need cell positions (in the example the clist holds only the variable C:3).

2. Add one data table per kind of row, all on sheet 1, using Tab sub report order 01, 02, 03, and 04 so they stack in that order:

   Tab sub report order	Table	Description	clist	Returns
   01	Projects	Header: total time sheets	A1:255 (a summary/formula field that returns the text and count)	The one project record
   02	Weekly Time Sheets	Column headers	A2:257.B2:258.C2:259.D2-D:104.E2-D:126.F2-D:127.G2-D:128.H2-D:129.I2-D:130… (static header-text fields and the week's dates)	One record
   03	Weekly Time Sheets	Time sheet entries	A3:25.B3:24.C3:16.D3-N:37.E3-N:38.F3-N:39.G3-N:40.H3-N:41.I3-N:42… (employee, project, task, hours per day)	Every time sheet for the project
   04	Projects	Footer	A4:256 (a summary/formula field for TOTAL HOURS)	The one project record

   Even though Projects is the master table, the header and footer rows are queried as data tables of the Projects table (filtered to the current project) so that they're placed as rows in the stack.

3. Start each data table on the next row. The data tables start in rows 1, 2, 3, and 4. Data table 03 returns a varying number of rows, but you still point 04 at row 4: because of the Tab sub report order, XL Docs places the footer right after the last detail row (row 29 in the example). See Stack data tables.

4. Create the button on the Projects table with docfmt=csv.

The CSV is written with commas between values and includes a UTF-8 byte order mark (BOM) unless you add noutf8bom=y, which some importing systems need. Use one sheet for a CSV export.

This button (a Formula - Text field with Allow some HTML tags turned on) saves the CSV into a file attachment field on the project, using the progress-window version of XL Docs (ExcelGen_LP.aspx) with stream=n:

var Text url = "https://.../ExcelGen_LP.aspx?"  // your XL Docs server URL, created by the installation wizard
& "clientid=Q#"
& "&appid=" & AppID()
& "&dbid=" & Dbid()
& "&tpdbid=" & [_DBID_DOCUMENT_TEMPLATES]
& "&tpid=1"  // Record ID# of your template
& "&fn=" & URLEncode("Weekly Time Sheets extract")
& "&msid=" & [Record ID#]
& "&faid=264"  // your file attachment field ID
& "&docfmt=csv"
& "&stream=n"
& "&apptoken=[Your App Token]";

"<a class='Vibrant Success OpenAsPopup POSTTempToken' data-height=1000 data-width=1000 data-refresh=true data-temptokendbid='" & Dbid() & "' href=\"" & $url & "\">Create Time Sheets CSV</a>"

To download the CSV in the browser instead, use the stream-to-browser version of XL Docs (ExcelGen_UA.aspx) with stream=y; XL Docs returns a real CSV file. All parameters are described in Formula URL parameters.

Worked example: invoice address block

An invoice template has a TO block in cells B12 through B15 with placeholders. The template record's clist is:

B12:53.B13:17.B14:49.B15:54

The logo, the word INVOICE, and all the bold labels are static content in the template file itself.

Worked example: expense statement

An Employee Expense Template uses the Projects table as its master table and sheet 1 (named Expense statement) of Employee Expenses v4.xlsx. Its clist is:

B6:6.B7:7.B8:8.K6:104.K7:105.M12-I:275:200|200.A38-H:276.RK:3.AA:7.BB:10

The expense lines in rows 12 and below come from a data table on the Expense Sheets table; that part is shown in Data tables (subtables).

Next steps

• Add lists of related records: Data tables (subtables).

• Create the button and choose the output: Formula URL parameters and Output and storage.

• Field-by-field details: Document Templates table reference.

• If something doesn't come out as expected: XL Docs messages and troubleshooting.