Help version: 3.3.8

 

Open your table Project. In the Project window right click the Designs folder and select the Create Folder. Name the folder ‘Excel’.

 

Right Click the folder named ‘Excel’ and select ‘New Design’. Name it ‘ExcelTable’ and add ‘ExcelUtilities’ as its mixin.

 

 

Drag ExcelTable into MyTable (note that your assembly may be called Table)

 

 

Setting up your TableExcelTemplate

To create your own copy of the Excel template, you’ll need to harvest the template from the Table_KB_Inventor_Example project. Close your project and open ‘Table_KB_Inventor_Example’.

 

In the Project  window open the Resources folder, right click the ‘TableExcelTemplate.xlsx’ and select Export Resource.

 

If the file will looks like this:

 

Rename ‘TableExcelTemplate_xlsx.txt’ to ‘TableExcelTemplate.xlsx’ and put it in a folder whose location  you will remember.

 

Close this Project and re-open your Table Project and in the Project Editor, right click the Resources folder and select Import Resource. In the pop-up window, select Choose, navigate to the document, and select it. Then hit the Update button. ‘TableExcelTemplate.xlsx’ should now be in your Resources folder.

 

 

 

Review the TableExcelTemplate below. Each names in column A must be unique. Also the value for a name in column A will be the last value in its row.

(Birch = 4.8)

( BirchWeight = 3.667)

 

 

 

 

 

 

<<<<<<<<<<<<<<<Stopped Here...11/24/2020>>>>>>>>>>>>>>>>>>>

 

Review all below............

Setting up ExcelTable to Read an Excel Worksheet

3 basic Parameter Rules are needed to Read an Excel Worksheet

cellRange:        'A1:C50'        

//Paste or Type in. Make range greater than your needed range in case rows or columns are added.

 

workSheetName:                "TableData"                

//Paste or Type in.

 

* resourceFile:                (Example:)        { name: 'TableExcelTemplate.xlsx', 

id: '20aa1a34-af4d-4fba-c090-08d71b0d1d51' }

* In order to get the resourceFile id value in your project, while in the Child tab click on resourceFile and select the ‘Get RefChain’, Then click on ‘TableExcelTemplate.xlsx in the Resources folder. The value will automatically show up in the Parameter window.

 

 

Also if you make changes to the ‘TableExcelTemplate.xlsx’ file, the changes will not automatically show up in your kBridge program.

If you want the changes to show up in your kBridge program you will need to:

1)Open the Resources folder in Projects and right click on the ‘TableExcelTemplate.xlsx’ file and select delete.

2)Right click the Resources folder and select Import Resource. In the pop-up window, select Choose, navigate to the document, and select it. Then hit the Update button. The updated ‘TableExcelTemplate.xlsx’ should now be in your Resources folder.

3)while in the Child tab click on resourceFile and select the ‘Get RefChain’, then click on ‘TableExcelTemplate.xlsx in the Resources folder. The value will automatically show up in the Parameter window.

 

 

Add Rules and Parameter Rules to ExcelTable

These rules will gather values from the TableExcelTemplate.xlsx.

 

More information around the new Rules to add to ExcelTable0

SheetData:        Flags: cached,Parameter        Type: any                        Category: NewRule        

Rule Default Value:  

return this.getRangeDataObject(this.workSheet, this.cellRange);

 

MaterialSelected:        Flags: cached                Type: string                        Category: NewRule

Rule Default Value:        

this.parent.parent.WordDocument.WordDocument0.MaterialSelected;

 

CostBdFtExcel:        Flags: cached                Type: number                Category: NewRule

Rule Default Value:        

var Material = this.MaterialSelected;

var val1 = this.getObjKeyValueOrDefault(this.SheetData,Material,100);

return Math.round(val1*100)/100;

 

WeightExcel:                Flags: cached                Type: number                Category: NewRule

Rule Default Value:        

var MaterialWeight = this.MaterialSelected+'Weight';

var val1 = this.getObjKeyValueOrDefault(this.SheetData,MaterialWeight,100);

var val2 = this.TableBdFt*val1;

return Math.round(val2*100)/100;

 

 

 

TableBdFt:                Flags: cached                Type: number                Category: NewRule

Rule Default Value:        

this.parent.parent.WordDocument.WordDocument0.TableVolume

 

LaborHoursExcel:                Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

var val1 = this.getObjKeyValueOrDefault(this.SheetData,'ManufactureHoursFactor',100);

return Math.round(this.TableBdFt / val1 *100) / 100;

 

LaborUnitCostExcel:        Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

var val1 = this.getObjKeyValueOrDefault(this.SheetData,'ManufactureHourlyWage',100);

return Math.round( val1 *100) / 100;

 

StainAndSealExcel:                Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

Math.round( this.StainAndSealPerHourExcel*this.StainAndSealHoursExcel * 100) /100

 

StainAndSealPerHourExcel:Flags: cached        Type: number        Category: NewRule

Rule Default Value:        

var val1 = this.getObjKeyValueOrDefault(this.SheetData,'StainSealHourlyWage',100);

return Math.round( val1 * 100) /100;

 

StainAndSealHoursExcel:        Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

var val1 = this.getObjKeyValueOrDefault(this.SheetData,'StainSealHoursFactor',100);

return Math.round( this.TableBdFt / val1 * 100) /100;

 

LaborCostExcel:                Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

Math.round(this.LaborHoursExcel * this.LaborUnitCostExcel * 100) / 100;

 

CostExcel:                Flags: cached                Type: number                Category: NewRule

Rule Default Value:        

return Math.round(this.CostBdFtExcel * this.TableBdFt *100) / 100;

 

SubTotalExcel:                Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

Math.round((this.CostExcel + this.LaborCostExcel + this.StainAndSealExcel)*100)/100;

 

 

SalesTaxExcel:                Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

6.25

SalesTaxTotalExcel:                Flags: cached                Type: number        Category: NewRule

Rule Default Value:        

Math.round(this.SalesTaxExcel/100 * this.SubTotalExcel * 100)/100;

 

TotalExcel:                Flags: cached                Type: number                Category: NewRule

Rule Default Value:        

Math.round((this.SalesTaxTotalExcel + this.SubTotalExcel)*100)/100;

 

 

 

 

 

Evaluate the new rules just created

Make sure that the new rules you just created evaluate to the correct values. If your Rule references a rule from the WordDocument0, make sure you use the ‘Get RefChain’ check box to get the correct location.

Example: In ExcelTable0 select the Design tab and type in the name of the rule that you want to reference.

 

Place the curser at the start of ‘|.MaterialSelected;’ and put a check in the ‘Get RefChain box’ by selecting it, then select ‘WordDocument0’. The correct address will automatically show up in your rule window. Click Apply.

 

 

 

 

 

 

These values can be used to produce a word document or can be used to output an Excel Worksheet. In the next section we will output an Excel Worksheet.

 

Setting up ExcelTable to Write to an Excel Worksheet

 

Here are the steps to output an Excel Worksheet

 

 

inputArray:        

Parameter Value:        //In ExcelTable, Child tab, Paste or Type in the following:

 

var dt = this.parent.MyTable.WordDocument.WordDocument0.Date

var tbdim = this.parent.MyTable.WordDocument.WordDocument0.TableDimensions

return [["Date:", dt],

["Order Tab:", "0001"],

["Customer:", "John Smith"],

["Description:", tbdim+" " + child.MaterialSelected + " Table"],

["Weight:", child.WeightExcel.toString()+ " lbs"],

["", ""],

["QTY", "DESCRIPTION","UNIT PRICE","AMOUNT"],

["1", child.MaterialSelected +  " Table Material Cost" , child.CostExcel.toString(), child.CostExcel.toString()],

["1", "Labor (" +  child.LaborHoursExcel.toString()+" hrs)",child.LaborUnitCostExcel.toString(),child.LaborCostExcel.toString()],

["1", "Stain and Seal (" +  child.StainAndSealHoursExcel.toString()+" hrs)",child.StainAndSealPerHourExcel.toString(),child.StainAndSealExcel.toString()],

["", "","Subtotal",child.SubTotalExcel.toString()],

["", "",'Sales Tax ' + child.SalesTaxExcel.toString() +'%',child.SalesTaxTotalExcel.toString()],

["", "","TOTAL","$"+child.TotalExcel.toString()]]

 

writeNewWorkbookToFile:        

While in ExcelTable0 in the Model tab, Click on writeNewWorkbookToFile value. It will display ‘true’. This needs to happen before you can output a file link.

 

outputFileLink:        

While in ExcelTable0, in the immediate window, type in ‘this.outputFileLink’.

Copy the URL from the immediate window.

 

 

 

Create a new tab in your browser. Paste the URL into a new tab. Remove the quotation  marks and hit Enter.

The Excel output should appear at the bottom left of your browser.

 

 

Click on it. It should open in Microsoft Excel.