Monday, August 20, 2007

Import setup of table collections

If you have an application where you want make use of table collections, you'll have to make an extensive effort to analyze which tables to include. During this analysis phase, you probably work with some sort of list of table names in a spreadsheet.

So how do you get from the list to setting up the table collection? Well, you open the AOT and add each and every table name manually to the table collection. I went through this tedious process today with a customer and when I got home I wrote the following small job to automate this task next time.

This is a very simple job, reading the first row of the spreadsheet, adding what is assumed to be table names to a table collection. As you can see, the job is in "proof on concept" state, so you'll have to add all the bells and whistles yourself.

static void ImportTableCollectionsFromExcel(Args _args)

// Import from Excel
SysExcelApplication excelApp;
SysExcelWorkbooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
FileName fileName;
Counter row;

// Create the new treenodes
TreeNodeName tableCollectionName = 'ImportedCollection2';
TreeNode treeNode;
TreeNodePath treeNodePath = #TableCollectionsPath+'\\'+tableCollectionName;
TreeNodeName treeNodeName;


// Create the table collection if it doesn't exist
treeNode = TreeNode::findNode(treeNodePath);

if (!treeNode)
treeNode = TreeNode::findNode(#TableCollectionsPath).AOTadd(tableCollectionName);

excelApp = SysExcelApplication::construct();

fileName = excelApp.getOpenFileName();

workBooks = excelApp.workbooks();;
workSheets = excelApp.worksheets();

workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();

row = 1;
cell = cells.item(1,1);

while (cell.value().bStr() != '')
treeNodeName = cell.value().bStr();

treeNode = TreeNode::findNode(treeNodePath+'\\'+treeNodeName);

if (!treeNode)
if (treeNode::findNode(#TablesPath+'\\'+treeNodeName))
info (treeNodeName);
treeNode = TreeNode::findNode(treeNodePath).AOTadd(treeNodeName);

cell = cells.item(row,1);

treeNode = TreeNode::findNode(treeNodePath);