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)
{
    #AOT

    // 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;
    ;

    setPrefix(treeNodePath);

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

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

    excelApp = SysExcelApplication::construct();

    fileName = excelApp.getOpenFileName();

    workBooks = excelApp.workbooks();
    workBooks.open(filename);
    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);
                treeNode.AOTsave();
            }
        }

        row++;
        cell = cells.item(row,1);
    }
    excelApp.quit();

    treeNode = TreeNode::findNode(treeNodePath);
    treeNode.AOTsave();
}