Import Objects From Spreadsheet

[gtwirth]

We have added a Simio add-in that will import a model (objects and links) from a spreadsheet. The most DLL, spreadsheet and code are provided in a later post.. NOTE: You do not need the code to run the add-in. All you need is the ImportObjectsAndLinksFromSpreadsheetAddInDLL_V5.zip and ImportObjectsAndLinks_v5.xlsx. The code is also provided in case you wish to make changes to the add-in.

You can run the addin just using the DLL and spreadsheet. All you need to unzip the DLL into C:\Users\\Documents\SimioUserExtensions. You might need to add the SimioUserExtensions under MyDocuments (C:\Users\\Documents) in case it does not already exist. Once you run the add-in in Simio, use the attached spreadsheet. It should create a Source, Server, Sink with an Entity and a Vehicle.

If you want to use the code, you will need to update the project properties..under build. The output path needs to be C:\Users\\Documents\SimioUserExtensions...See attached screen shot.

Give it a try.

Let us know if you have any questions.

Updated 2106-10-24.....Added support for repeating properties and wrapped import using BulkUpdate method to speed up import .

ImportObjectsAndLinksFromSpreadsheetAddIn_DLL.zip

ImportObjectsAndLinksFromSpreadsheet_Code.zip

ImportObjectsLinksVertices.xlsx

[gtwirth]

Here are some quick instructions:

1) You will need to have Microsoft Excel on your computer.

2) Download the "ImportsObjectsAndLinks.xlsx" and populate it with your model.

3) Extract "ImportObjectsAndLinksFromSpreadsheetAddIn.dll" from the "ImportObjectsAndLinksFromSpreadsheetAddInDLL_V5.zip" into a temp folder (e.g. c:\temp).

4) Right-click the assembly and select properties. Then choose to "unblock" button and press apply. (only needed for Windows 7 and later).

5) Copy "ImportObjectsAndLinksFromSpreadsheetAddIn.dll" into "C:\Users\\Documents\SimioUserExtensions". You might need to add the SimioUserExtensions folder under MyDocuments (C:\Users\\Documents) if it does not already exist.

6) Open Simio. Create a new Model.

7) From the Project Home...Select Add-In button, select "Load Objects and Links from an external spreadsheet"

Choose the "ImportObjectsAndLinks.xlsx".

[gtwirth]

We have expanded our API with Simio Version 5....Now we have the ability to add links to networks. This should drastically reduce the time needed to create a network. We also have the ability to modify the size of object through the API. The new files for version 5 are attached to this post.

NOTE: If you are downloading just the assembly, there are couple of extra steps:

1) Extract "ImportObjectsAndLinksFromSpreadsheetAddIn.dll" from the "ImportObjectsAndLinksFromSpreadsheetDLL_V5.zip" into a temp folder (e.g. c:\temp).

2) Right-click the assembly and select properties. Then choose to "unblock" button and press apply. (only needed for Windows 7 and later).

3) Copy the assembly into "C:\Users\\Documents\SimioUserExtensions"

2016-01-27...Remove attachments...Use attachments in Alan Sagan's post.

[gtwirth]

Here are some quick notes on the spreadsheet import add-in:

- The entire model can be imported through the spreadsheet.

- There are 2 sheets; one for objects and one for links.

- The first 12 columns on the objects sheet and first 9 columns on the links sheet are fixed. Don't add / change column name / delete the columns.

- Starting with PropertyA column on both sheets, you can define add / change column name/ delete these columns. The name defined on these columns is associated to the property name on the object; if the property name exists on the object.

- The add-in is also setup to perform updates as well. If an object already exists in the model, the add-in will just update the existing object; not add a new one.

[gtwirth]

Here are 3 updates to the spreadsheet import:

1) There is a new sheet called "Verticies1" to the attached workbook. Using this sheet, you can add multiple vertices to a link.

2) If a link already exists in a model, the import will now deletes and re-adds the link using the new information from the spreadsheet. Previously, links were not updated, only added.

3) If you set a coordinate in the "Objects1" sheet to blank (or not a number), the value will be 0 if the object is being added. If the object is being updated, the coordinates for the object will be ignored and not updated on the object.

Everything you should need is attach to this post.

UPDATED: 2012-06-11.....Fix was made so a top level Repeating Group Property can be updated through the spreadsheet. There is not a mechanism to update the children(except for AssignmentsOnEntering and SecondaryResources repeating groups used by the RPsixample model...See post below).

2016-01-27...Remove attachments...Use attachments in Alan Sagan's post.

2016-10-24....See first post for latest files.

Edited June 11, 2013 by Guest

[gtwirth]

For those Enterprise users, you can use the spreadsheet import to build an RPS model.

Just copy the attached files into the C:\Users\Public\Documents\Simio\Examples folder.

Then open the RPsixampleForImportBlank.spfx. Use the spreadsheet import to load the objects and links from the RPsixampleImportWithTableData.xlsx (Updated spreadsheet on 2014-07-23 to fix 'LogResourceUsage' column name).

The table data will be imported when you run the model since the data binding on all the tables is set to automatic....

2016-01-27...Removed attachments....With Simio 8, the table structures have changed. We suggest taking a look at the following Simio Scheduling documentation and examples that are provide when you install Simio.

C:\Program Files (x86)\Simio\PlanningAndSchedulingWithSimio.pdf

C:\Users\Public\Documents\Simio\Examples\SchedulingDiscretePartProduction.spfx

C:\Users\Public\Documents\Simio\Examples\SchedulingBatchBeverageProduction.spfx

C:\Users\Public\Documents\Simio\Examples\SchedulingBicycleAssembly.spfx

[JWolski]

Thank you! Very useful tool for quickly generating / updating a "large" network model

JW

[willem]

Does anyone have a way of exporting the data changed in Simio back into excel to change again?

This will really help...

[gtwirth]

See "Export Import Model" add-in. It will export the model so you can modify it and then you can read the updates back in.

http://www.simio.com/forums/viewtopic.php?f=36&t=1389

[MEagar]

Glen,

how are we doing with exposing the property which contains the excel file's name used to bind tables...It would be fantastic if we could change ALL tables linking to the same excel file one shot with....

ITables objects = context.ActiveModel.Tables as ITables;

if (objects != null)

{

foreach (var ob in objects)

{

WriteLine(String.Format("{0} ",ob.Name));

WriteLine(String.Format("{0} ",ob.Columns.Count));

WriteLine(String.Format("{0} ",ob.Properties.BINDCOLUMN_NAME ??));

}

}

[ASagan]

The attached add-in up above didn't support changing the width/height of links. I modified the source code to include this capability.

Updated 2016-01-27...Updated DLL / Code to use Microsoft .NET Framework 4.5. I also included Alan's changes for changing the width / height on links. (Glen Wirth..Simio).

2016-10-24....See first post for latest files.

[rvasudev]

Hey Glen,

A couple of questions. They might be simple but I am completely new to this and I want to get it right

1. You say "Right-click the assembly and select properties. Then choose to "unblock" button and press apply". Can you tell me where in Simio is the assembly option?

2. Also, can we add properties like type of distribution for each work station (eg: exponential, uniform, etc.) in the excel file? If we can, would that be automatically reflected in simio when we automatically generate the model?

3. Can you give the C# code for this automatic model generation from excel to simio procedure?

Thanks

[rvasudev]

Hey Guys,

Couple of questions-

1. Which one is the Simio Assembly file that I have to copy? Is it the .exe file?

2. In the excel file, would I be able to change the properties to each workstation like exponential distribution, normal distribution, etc.?

Thanks

[willem]

Hi,

This might help, to import and export properties...

http://www.simio.com/forums/viewtopic.php?f=36&t=1389&hilit=Export+Import+Model

[rvasudev]

Thanks Willem,

Also I had a few other doubts that I was hoping you could clarify.

Firstly, in Simio, under select add-in, I do not have the "Load Objects and Links from an external spreadsheet" option. All I have is the "Place Source, Server, Sink" option.

Secondly, I am not able to set the output directory file path on Visual Studio. Can you help me out with that as well?

Also, can you tell me which one is the assembly file for Simio? is it the .exe file?

Guys, any help would be appreciated as I am completely new to this.

Thanks.

[willem]

Not sure about the last 2 questions, but you have to copy the .dll files into the "C:\Users\\Documents\SimioUserExtensions" on you computer for the add-ins to appear in Simio. Also be sure to close and reopen Simio.

Ps. Simio is not a spelling mistake Simio Forum Spell Checker!

[liyunjun]

I hope it is available to import following information in the future :

1. "Lock Edits" property of model graphics.

2. The "Queue" animation of Station or Storage, even including "Alignment" and "Vertices" properties.

[mjansen]

Hi Glen,

I ran into a modelling challenge where i have to build a network based upon user input.

First i was planning to create complete graph(fully connected)in simio and using input to activate/deactivate nodes and links. ( But i wasnt looking forward to creating this graph( around 20 nodes and 20*19 edges))

Then i ran into this add-in. This seems like a way more elegant way to solve this! Getting it working was surprisingly easy!

I only have one issue, i need the object to be automatically imported upon opening the model( so without the need to click "Load Objects and Links from an external spreadsheet" ). Since opening and and running the model is done through the api in the solution that i am trying to build.

I was thinking that it could be possible to create a custom step to call the loading of the object and links, but i guess it wouldn't be possible to add these types of objects to a already running model.

Could you advice me on the best way to achieve this? Any help/input is greatly appreciated!

[gtwirth]

If you are running an experiment of the model through the api, you can add the nodes and links to the model right before the experiment is run.

You just need to add your code for adding nodes and links to the following example.

http://www.simio.com/forums/viewtopic.php?f=35&t=836

Adding nodes and links using steps will not work. The nodes and links need to be added during design time. They cannot be added during runtime.

[vhgreeff]

Hi guys,

Great add in!

Is it possible to define the add-on processes for servers, links etc. using this addin? That is, would it be possible to a few columns with Add-on processes as headings, lets say "Add on process: When entering" and then have "KeepEntityUntilX" as a process?

Thanks.

[gtwirth]

Add-On Process Triggers can be defined using the spreadsheet as well. Just change / add a column in the spreadsheet with the first row referencing the add-on process trigger property name on the object. Then, add the add-on process trigger name to the appropriate row in the spreadsheet.

In the attached example, I changed the name of "PropertyA" in the Objects1 sheet to "ProcessingAddOnProcess". I then added "Server1_Processing" to the row hat references Server1 (e.g. "Server1_Processing"). Once imported, Server1 has a add-on process trigger to the "Server1_Processing" process.

ImportObjectsLinksVertices_WithProcess.xlsx

[TobiasS]

Hi Glen,

great Add-In!

I ran into a small problem however: I would like to set the physical location of the Input & Output Nodes of a custom object of mine.

Such as..

Object Class|ObjectName|X|Y

TransferNode|Output@MyObject|5|10

BasicNode|Input@MyObject|6|10

Links are set perfectly to the objects, but I cant change their location.- They will jsut remain in place.

Any tips on that?

Best Regards

[tulach]

Hi Glen,

I used the plugin to import some hundreds of objects and it was too slow and frequently made SIMIO unresponding.

I used our programmers to change the logic of plugin (problem was frequent comunication between Excel and SIMIO, so they used an array or so instead)

and we added small lag between inserting objects (something like math.epsilon in simio) to preven freezing.

And a new progressbar, that will not leave the user in darkness about whats going on.

Feel free to use it.

ImportObjectsAndLinksFromSpreadsheetLOGIO.rar

[JWolski]

Petr,

I tried out your updated version, as I had used Glen's original in the past. There is definitely a speed boost with this version. My "test" import file is the 25 X 25 grid attached (625 BasicNode and 1200 Path Objects.) On my old laptop this imports in about 60 seconds.

I noticed that the progress bar works great for the Objects sheet, however it does not appear to update when the Links are imported. The progress remains at 0 while the links are imported, though the routine works and I see the dialogue at the end with no errors. The nodes only take about 10 seconds to import and the links constitute the remaining ~ 50 seconds. I tried turning off visibility of the links/arrows, but this made no difference. I haven’t tried vertices yet.

Also, note that I had to re-build the add-in in Visual Studio, as I seem to have an issue with adding add-in .dll's from an outside source to my ..\SimioUserExtensions directory. Hopefully I did not break the application in the process.

Thank you very much - this is very useful!

25 X 25 Grid Import.xlsx

[AubinMacquart]

Hi Petr and Glen,

I have been trying to use both your add-ins. However, I continuously receive the same error message when importing the 'ImportObjectsLinksVertices.xlsx' spreadsheet (see picture below). Does one of you know how this can be fixed?

Kind regards,

Aubin Macquart