Creating a template
To start, we need to create a template. As mentioned above, your template can contain any number of sheets, images, excel formulas, etc. For this guide, we're going to start small with a simple one-sheet RFQ template that includes company information, requester information, and rows of part names, configurations, and quantities. We'll also include a sum total of the number of parts at the bottom, and we'll add some formatting to highlight key information. Start by creating a new Excel workbook with one sheet; we'll add to it in steps over the course of this guide.
Company and requester information
Adding the company and requested information to our template is no different than adding it to any other Excel file: drag out an image (a company logo for example) on to the sheet and position it as desired, and then add text to cells to indicate company name, address, requester name, and email address. Your template at this point might look something like this:

Bommer region
For Bommer to insert bill of materials data, we need to add some specific text to a range of cells in the sheet (what we call a region or block). A Bommer region is a 3 x n range of cells (where n is the number if columns in the region); the row count will grow or shrink when populated with actual data.
The start of a Bommer region will always be a Bommer tag, in the form of <Bommer columns="n"> where n is the number of columns that Bommer should populate. This can go anywhere in your sheet, but be remember that Bommer may grow or shrink the rows below this tag to make room for the BOM data, so plan your sheet accordingly. Similarly, the end of a region is marked with an <End> tag, placed 2 rows directly below the Bommer tag.
We use the cell directly under the Bommer tag for our first column definition. To define a column, enter the name of a Bommer property that you want to output into that cell. This can be just the name (e.g. Name, Description) or the category and name (e.g. General.Quantity). If the former, Bommer will look for the first property with that name and ignore duplicates; the latter unambiguously identifies a property. Let's define General.Name for our first column, General.Configuration for our second column, then General.Description, then lastly General.Quantity. Once done, your Bommer region should look something like this:

Don't worry about the names that run over into the neighboring cells; the names will be read in correctly by Bommer. You might want to widen the cell for General.Name and shrink the column for General.Quantity just to tidy up the resulting spreadsheet.
Sum total
We can define formulas in our sheet that refer to the Bommer region; when defined correctly, these formulas will automatically grow when Bommer inserts data. For this guide, we're going pick 2 cells below our Bommer region. We're going to assume that the General.Quantity column in our region is in the E column, with the property name on E17, and a blank cell on E18 (i.e. the row with the end tag).
In the first cell, enter "Total Parts" and in the second cell enter the formula =SUM(E17:E18). Bommer will always write the first row of data over the column definition row (the Bommer tag row is used for column headers), and it will insert rows in between that row and the end row (i.e. row with the end tag) until it has enough to hold all of the bill of materials data. Because of this, formulas defined over the range of the middle and bottom rows of the range will grow when Bommer inserts rows, and those formulas will therefore include any data Bommer inserts into the sheet. Here's the current state of the template with the sum total defined:

Make it pretty
Finally, we can add formatting to the columns inside of the Bommer region, which will be applied to all cells in that column. To do this in most cases, you just need to format the cells that contain the property names (i.e. the middle row). The exception to this is if you want to define borders for the top row and bottom row. In this case, you can set the borders directly on the top and bottom rows, and those borders will be preserved in the resulting export.
Go ahead and add a border around the entire region, and then let's add a border between General.Quantity and the rest of the columns. We're also going to bolden the General.Name column, and italicized the General.Quantity column. Your final template should look something like this:

Using the template in Bommer
Now that we have a template created, we'll want to use it. The way to use this template is to load it into Bommer, then load the model you wish to export, then use the export dialog to select the template we just loaded.
Load the template












Exporting a pre-formatted bill of materials



Note that when you do this, the options to use the Current View or All Properties are greyed out; this is because the template already defines what columns are to be exported. The end result - using the model included in this example - is:

And thats it! Use this functionality to build reports, cover letters, and other reports that make use of your BOM data!
Example Files
To download the completed template and example model used in this guide, click RFQ Template Example.xlsx and ExampleAssem.zip.
Comments
0 comments
Please sign in to leave a comment.