Excelauto import

Question

question

hikel asked Nov 26, '24 hikel commented Dec 3, '24

Excelauto import

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

Software Version:

FlexSim 23.2.2

excel import distributions excelauto

1732641327104.png (113.4 KiB)

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2024-11-28T07:11:18Z

Felix Möhlmann answered Nov 28, '24 hikel commented Dec 3, '24

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

· 5

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

hikel commented · Nov 28 at 05:08 PM

@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

0 ·

1732813659490.png (38.1 KiB)

Felix Möhlmann hikel commented · Nov 29 at 12:31 PM

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.)

0 ·

distributionexample.xlsx (8.8 KiB)

empirical-distribution-from-excel.fsm (33.7 KiB)

hikel Felix Möhlmann commented · Dec 02 at 04:18 PM

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[i];
    string sheetName = sheetNames[i];


    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);
    }
}

0 ·

Show more comments

question