Quick Start: How do I create a basic export template?
Bommer can use pre-formatted templates to format your exported bill of materials. These templates are defined in Excel files and can contain any number of sheets, images, excel formulas, etc; the only requirement for Bommer is to specify a region of cells that it can use to insert BOM data. This guide will show you how to specify that region, and how to load and use templates inside of Bommer.
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 requester 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:
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.
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
We load the template into Bommer by opening Bommer in SOLIDWORKS, and clicking on the icon. Then, click on the Templates tab to bring up the template configuration interface:
This works similarly to other configuration interfaces within Bommer: use the button to add template files to Bommer, the button to remove a template, and the button to save your changes. The and buttons let you undo or redo any changes, and the and buttons let you import and export template configurations from/to a file. We're going to use the button to add our new template. Pressing that button pulls up a File Open dialog. Select the template, and press Open. Once the file is opened, it will display the name and path of the file below the list box, and will populate the target drop down list with sheets that contain template tags. Select Sheet1 (or whatever you named your sheet in this example) from the list. Here's what the screen will look like when we're done:
Press the to save, close the Settings window, and we're ready to export!
A quick note about template files: Bommer stores the path of the template file you added, but does not copy the file itself. This means that after adding a template to Bommer, you can edit the template to make tweaks to the formatting or template data just by editing the file. It also means that if you delete the original file, Bommer will not be able to use the template anymore, so keep your templates in a safe place!
Exporting a pre-formatted bill of materials
Once we have our template loaded, we can use it when we export a bill of materials. From here, it's very similar to exporting a basic spreadsheet: Open your model of choice, press the open the Bommer task pane window, and then press or to open the Export dialog. You can select your template from the Template drop down list, and press OK to export your bill of materials into your pre-formatted template:
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!