How can I use excel to do calculations in the simulation?

Question

question

Scott Ainsworth asked Oct 16, '18 tannerp edited Nov 9, '18

How can I use excel to do calculations in the simulation?

My client has some fairly extensive calculations for product manufacture in excel. I am currently attempting to simulate the manufacturing line. My thought is to use excel commands to write values to the spreadsheet and then read back the results. I have this bit of code after an excel open:

excelsetsheet("SH");

excelwritenum(12,3,token.parent.Width+.5);

double myvalue = excelreadnum(17,8);

I am able to read the correct value in myvalue and assert it to a label. However the excelwritenum command does not seem to do anything. I'm guessing the excel sheet needs a recalculate triggered or something. Does anyone have any ideas?

I really do not want to duplicate all the equations in flexsim code.

Software Version:

FlexSim 18.2.2

excel excel export excel interface

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 · 2018-10-16T14:49:47Z

Jordan Johnson answered Oct 16, '18 Scott Ainsworth commented Oct 16, '18

I would not recommend accessing Excel during the model run. It will slow down your model, likely to an unusable point, if it is accessed frequently. Besides performance, you will be unable to use the Experimenter or Optimizer with this approach, since a single Excel worksheet is not designed to be accessed by multiple programs at once, especially where you are writing to the file.

Is there a way you could make a lookup table? You could make a new sheet in Excel, where each row contains input values, and also the output values for those inputs. Even if that table had 100000 rows, this approach would be faster and better than trying to get Excel to do it from FlexSim.

· 1

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.

Scott Ainsworth commented · Oct 16, 2018 at 06:32 PM

That would be tough. There are multiple variable received with the schedule. 10 to 20 part lengths need to be calculated per item from about a dozen different spreadsheets depending on product type. My thought was to read in the schedule, process the tokens doing all calculations then send tokens on to create items in the 3D sim. Sending values to excel to do the calcs seems like the easy button if it worked. I put all the sheets in one file and the tokens from a couples days runtime rifle through in a few seconds. Its the open/close commands that kill the performance. Thanks for the feedback.

0 ·

question