topic Re: Flexscript to import a Excel File in FlexSim Forum

Flexscript to import a Excel File

Amanda_Prado8XCSE — Fri, 08 Nov 2024 17:11:54 GMT

[ FlexSim 24.2.1 ]

Hello All,

Could anyone help with a Script walkthrough how can I import data from Excel to a Global Table?

I would like my script read a excel table and generate a Global Table as per my excel.

I was trying with this following code, but it´s not working.

/**Custom Code*/ {

// Definir o diretrio inicial para buscar o arquivo Excel

string directory = modeldir();

if (stringlen(directory) < 3) directory = documentsdir();

// Abrir um dilogo para o usurio selecionar um arquivo Excel

string filename = filebrowse("*.xls*","Excel Files",directory);

// Verificar se o usurio cancelou a seleo

if (stringlen(filename) == 0) {

return 0; // Termina o script se nenhum arquivo foi selecionado

}

// Abrir o arquivo Excel

excelopen(filename);

excelsetsheet("Sheet1"); // Definir a planilha ativa (ajuste conforme necessrio)

// Criar uma tabela global

treenode table = applicationcommand("addglobaltable");

table.name = "Imported Data"; // Nome da tabela global

Table myTable = Table("Imported Data");

// Ler os cabealhos da primeira linha do Excel

int colCount = 0; // Contador de colunas

string header;

while (colCount < 256) { // Limitar a leitura a 256 colunas

header = excelreadstr(1, colCount + 1); // Ler cabealho da coluna

if (header == "") { // Se no houver mais cabealhos, sair do loop

break;

}

myTable.addCol(colCount + 1); // Adicionar coluna tabela

myTable.setColHeader(colCount + 1, header); // Definir cabealho da coluna

colCount++; // Incrementar contador de colunas

}

// Ler os dados do Excel e preencher a tabela

int StationRow = 1; // Iniciar na primeira linha da tabela global

for (int row = 2; row <= 1000; row++) { // Começar na linha 2 para ignorar cabealhos

string value1 = excelreadstr(row, 1); // Ler valor da primeira coluna

if (value1 == "") { // Se o valor estiver vazio, interromper a leitura

break;

}

// Adicionar uma nova linha tabela global

myTable.addRow(StationRow);

myTable.setRowHeader(StationRow, value1); // Definir cabealho da linha

// Ler e armazenar valores das colunas

for (int col = 1; col <= colCount; col++) { // Usar o contador de colunas

string value = excelreadstr(row, col);

if (stringlen(value) < 1) {

myTable[StationRow][col] = 0; // Se vazio, definir como 0

} else {

// Tentar converter o valor para nmero, se possvel

}

StationRow++; // Avanar para a prxima linha da tabela global

}

// Fechar o arquivo Excel

excelclose(0);

// Mensagem de concluso

msg("Excel Import", "Importao concluda com sucesso!", 1);

}

Re: Flexscript to import a Excel File

joerg_vogel_HsH — Fri, 08 Nov 2024 17:29:44 GMT

@Amanda Prado, do you need more data, than you can import by Excel import tool

Re: Flexscript to import a Excel File

Amanda_Prado8XCSE — Fri, 08 Nov 2024 17:34:53 GMT

But If want import by script?

Re: Flexscript to import a Excel File

Amanda_Prado8XCSE — Mon, 11 Nov 2024 17:42:04 GMT

Could you tell me where I'm going wrong in my code to import the excel file into a global table?

Thank you very much !

Re: Flexscript to import a Excel File

FelipeCapalbo — Fri, 22 Nov 2024 13:50:30 GMT

Hello, Amanda.

The best approach is to configure the Excel Import/Export interface within FlexSim to ensure proper communication with the desired file and sheet. Once the setup is complete, you can use the excelmultitableimport(); command in the "On Model Reset" trigger to automate the process.

excelmultitableimport();

This command executes the Excel import based on the configuration you’ve already established. Let me know if you need further assistance!

You can see how to setup the Excel Interface here:
https://docs.flexsim.com/en/23.2/Reference/Tools/ExcelInterface/ExcelInterface.html

Re: Flexscript to import a Excel File

Amanda_Prado8XCSE — Mon, 25 Nov 2024 19:19:09 GMT

Hello Felipe,

Thanks for your support, but unfortunately I have not understood.

Have you saw the code that I have sent here?

Could you tell me if I should just add this "excelmultitableimport();" command to the end of the code I sent?

Because I tried and it's not working.

Thank you very much !