outputing an excel in a matrix-like style, how to solve Range error?

Posted by Ana Sequeira
Posted on Tue, 01/10/2017 - 11:14 in Reports & Macros
Hello, I am having some trouble making an excel with this output (matrix-like) => image will be below.
The yellow part is the part that I am trying to write automatically.
I can write if I use with a single tablecell function, but If i try to to write and create all the cells automatically (with for cycles) it gives me this error:
My code until now:

var activeDB;
var selectedLanguage = Context.getSelectedLanguage();
var dummyOutput = Context.createOutputObject();
var foldersList = new java.util.Vector();

main();
dummyOutput.WriteReport();

function main() {

    activeDB = ArisData.getActiveDatabase();

    foldersList = ArisData.getActiveDatabase().GroupList(ArisData.getSelectedGroups()[0]);
    dummyOutput.BeginTable(100, Constants.C_BLACK, Constants.C_TRANSPARENT, Constants.FMT_LEFT | Constants.FMT_REPEAT_HEADER, 0);
    dummyOutput.TableRow();
    dummyOutput.TableRow();
    dummyOutput.TableCell("PROJECTO JUMP", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableCell("NOTA: Os subprocessos na coluna D são inputs e outputs dos subprocessos na linha 3", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableRow();
    dummyOutput.TableCell("imagens", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableCell("imagens", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableCell("imagens", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableCell("imagens", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

    for (var i = 0; i < foldersList.length; i++) {
        var currentFolder = foldersList[i];
        var currentFolderName = currentFolder.Name(selectedLanguage);
        var folderParent = currentFolder.Parent();
        var folderParentName = folderParent.Name(selectedLanguage)
        var folderParentChildList = folderParent.Childs(false);
        for (var j = 0; j < folderParentChildList.length; j++) {
            var currentChild = folderParentChildList[j];
            var folderParentGrandChild = currentChild.Childs(false);
            for (var h = 0; h < folderParentGrandChild.length; h++) {
                var currentGrandChild = folderParentGrandChild[h];
                var grandGrandChildList = currentGrandChild.Childs(false);

                if (folderParentName == "5. Gestão de ativos") {
                    dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                    if (folderParentName == "6. Gestão de Energia") {
                        dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                        if (folderParentName == "7. Gestão Comercial") {
                            dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                            if (folderParentName == "8. Gestão e Operação de Rede de Distribuição") {
                                dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                if (folderParentName == "10. Service Provider") {
                                    dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                }
                            }
                        }
                    }
                }
            }
        }

    }

    dummyOutput.TableRow();
    dummyOutput.TableCell("Capacidade", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableCell("Grupo de Processo", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableCell("Processo", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
    dummyOutput.TableCell("Sub-Processo", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

    dummyOutput.TableCell("TO WRITE HERE ", 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

    writeColumns(foldersList);

    //antes de fechar a funcao main
    dummyOutput.EndTable("FIM", 100, "Arial", 10, Constants.C_BLACK, Constants.C_BLACK, 0, Constants.FMT_LEFT | Constants.FMT_ITALIC, 0);

}

/*
 * Writes in the columns "Capacidade, Grupo de Processo, Processo e Sub-Processo"
 */
function writeColumns(foldersList) {

    for (var i = 0; i < foldersList.length; i++) {
        var currentFolder = foldersList[i];
        var currentFolderName = currentFolder.Name(selectedLanguage);
        var folderParent = currentFolder.Parent();
        var folderParentName = folderParent.Name(selectedLanguage);
        var folderParentChildList = folderParent.Childs(false); //false é sem recursividade, ou seja, apenas os childs do nivel abaixo
        for (var j = 0; j < folderParentChildList.length; j++) {
            var currentChild = folderParentChildList[j];
            var folderParentGrandChild = currentChild.Childs(false);
            for (var h = 0; h < folderParentGrandChild.length; h++) {
                var currentGrandChild = folderParentGrandChild[h];
                var grandGrandChildList = currentGrandChild.Childs(false);
                for (var k = 0; k < grandGrandChildList.length; k++) {
                    var currentGrandGrandChild = grandGrandChildList[k];
                    if (folderParentName == "5. Gestão de ativos") {
                        dummyOutput.TableRow();
                        dummyOutput.TableCell(folderParentName, 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                        dummyOutput.TableCell(currentChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                        dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                        dummyOutput.TableCell(currentGrandGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

                        if (folderParentName == "6. Gestão de Energia") {
                            dummyOutput.TableRow();
                            dummyOutput.TableCell(folderParentName, 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                            dummyOutput.TableCell(currentChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                            dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

                            if (folderParentName == "7. Gestão Comercial") {
                                dummyOutput.TableRow();
                                dummyOutput.TableCell(folderParentName, 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                dummyOutput.TableCell(currentChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

                                if (folderParentName == "8. Gestão e Operação de Rede de Distribuição") {
                                    dummyOutput.TableRow();
                                    dummyOutput.TableCell(folderParentName, 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                    dummyOutput.TableCell(currentChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                    dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

                                    if (folderParentName == "10. Service Provider") {
                                        dummyOutput.TableRow();
                                        dummyOutput.TableCell(folderParentName, 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                        dummyOutput.TableCell(currentChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);
                                        dummyOutput.TableCell(currentGrandChild.Name(selectedLanguage), 20, "Arial", 10, Constants.C_BLACK, Constants.C_WHITE, 0, Constants.FMT_CENTER, 0);

                                    }
                                }
                            }
                        }
                    }
                }

            }
        }
    }
}