Excelauto import

[hicham_elbaz6949F]

[ FlexSim 23.2.2 ]

I have excel file which contains multiple sheets that has parts and weight for that part. Is there any way to import automatic all sheets in excel file for empirical distribution for each sheet. instead of one by one as it will take a lot of time to do so

[moehlmann_fe · Accepted solution]

You can use the various "excel...()" commands to write a script that imports the data from each sheet. You will still need an array that contains all sheet names and an automatic import is only going to be feasible if the data is formatted in the same way on each sheet.

https://docs.flexsim.com/en/24.1/Reference/CodingInFlexSim/CommandReference/Commands.html#excelopen

// Importing a block of data
excelopen(path);
excelsetsheet(sheetName);
excelimporttable(table, startRow, startCol, numRows, numCols);

[hicham_elbaz6949F]

@Felix Möhlmann is there any way to import directly into empirical distribution with the weight. the sheets in the excel file all have 2 columns, but each sheet has a different rows

[moehlmann_fe]

Sure. The table of the empirical distribution is saved as a bundle node. Cast it as a table and then write data to it as you would to a global table.

Here's some example code. If you have an array of sheet and distribution names you can run everything from line 4 up to the last line in a for-loop for each array entry.

string filePath = "C:/Users/felix/Downloads/DistributionExample.xlsx";
excelopen(filePath);

string empDistName = "Test1";
string sheetName = "Test1";
Table data = Model.find("Tools/EmpiricalDistributions/" + empDistName + ">variables/data");

excelsetsheet(sheetName);
int row = 1;
while(excelreadstr(row, 1) != "")
{
    while(data.numRows < row)
    {
        data.addRow();
    }
    data[row][1] = excelreadstr(row, 1);
    data[row][2] = excelreadnum(row, 2);
    row += 1;
}
while(data.numRows >= row)
{
    data.deleteRow(row);
}

excelclose(0);

empirical-distribution-from-excel.fsm

DistributionExample.xlsx

(If you download the files to try for yourself, you obviously have to update the file path in the code.)

[hicham_elbaz6949F]

Thank you @Felix Möhlmann I changed your code to work for multiple array. just wondering if it can be done without creating empty empirical distribution array first so it can created automatically?

string filePath = "C:/Users/Hikel/Downloads/testss.xlsx";
excelopen(filePath);


Array empDistNames = ["Test1", "Test2"];
Array sheetNames = ["Test1", "Test2"];


int maxRows = empDistNames.length;
for (int i = 1; i <= maxRows; i++) {
    string empDistName = empDistNames;
    string sheetName = sheetNames;


    Table data = Model.find("Tools/EmpiricalDistributions/" + empDistName + ">variables/data");
    excelsetsheet(sheetName);


    int row = 1;
    while (excelreadstr(row, 1) != "" && row < 100) {
        while (data.numRows < row) {
            data.addRow();
        }
        data[row][1] = excelreadstr(row, 1);
        data[row][2] = excelreadnum(row, 2);
        row += 1;
    }


    while (data.numRows >= row) {
        data.deleteRow(row);
    }
}

[moehlmann_fe]

I'd setup a single distribution with the correct settings (string data, weighted, ...). In the code you can then check if a distribution with the same name as the sheet exists. If not, you create a copy of that 'base' distribution.

treenode baseDistribution = Model.find("Tools/EmpiricalDistributions/1");

string sheetName = "Test1";
string empDistName = sheetName;
treenode empDist = Model.find("Tools/EmpiricalDistributions/" + empDistName);
if(!objectexists(empDist))
{
    empDist = createcopy(baseDistribution, baseDistribution.up);
    empDist.name = empDistName;
}
Table data = empDist.find(">variables/data");

[hicham_elbaz6949F]

Thank you @Felix Möhlmann Here is the updated code if anyone wants to use it

string filePath = "C:/Users/Hikel/Downloads/testss.xlsx";
excelopen(filePath);

Array sheetNames = ["Test1", "Test2"];
Array empDistNames = sheetNames;

// Base distribution to clone from if needed
treenode baseDistribution = Model.find("Tools/EmpiricalDistributions/1");

int maxRows = empDistNames.length;
for (int i = 1; i <= maxRows; i++) {
    string empDistName = empDistNames;
    string sheetName = sheetNames;

    // Check if the distribution exists; if not, create it
    treenode empDist = Model.find("Tools/EmpiricalDistributions/" + empDistName);
    if (!objectexists(empDist)) {
        empDist = createcopy(baseDistribution, baseDistribution.up);
        empDist.name = empDistName;
    }

    Table data = empDist.find(">variables/data");
    excelsetsheet(sheetName);

    int row = 1;
    while (excelreadstr(row, 1) != "" && row < 100) {
        while (data.numRows < row) {
            data.addRow();
        }
        data[row][1] = excelreadstr(row, 1);
        data[row][2] = excelreadnum(row, 2);
        row += 1;
    }

    // Clean up any excess rows
    while (data.numRows >= row) {
        data.deleteRow(row);
    }
}