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\<YOUR USERNAME>\Documents\SimioUserExtensions. You might need to add the SimioUserExtensions under MyDocuments (C:\Users\<YOUR USERNAME>\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\<YOUR USERNAME>\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 .

[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\<YOUR USERNAME>\Documents\SimioUserExtensions". You might need to add the SimioUserExtensions folder under MyDocuments (C:\Users\<YOUR USERNAME>\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\<Username>\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 RPSExample 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.

[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 RPSExampleForImportBlank.spfx. Use the spreadsheet import to load the objects and links from the RPSExampleImportWithTableData.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.p ... port+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.