Import Objects From Spreadsheet

Do you have objects, models, libraries, path decorators, textures, symbols, custom API code, documentation, or anything else others might find useful? Please attach it here with a brief description.
gtwirth
Simio Team Member
Posts: 156
Joined: Mon Jun 09, 2008 4:42 pm

Import Objects From Spreadsheet

Post by gtwirth » Tue Sep 04, 2012 9:10 pm

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 .
Attachments
ImportObjectsLinksVertices.xlsx
(13.02 KiB) Downloaded 161 times
ImportObjectsAndLinksFromSpreadsheet_Code.zip
(27.78 KiB) Downloaded 164 times
ImportObjectsAndLinksFromSpreadsheetAddIn_DLL.zip
(14.53 KiB) Downloaded 162 times
OutputPath.png
OutputPath.png (65.22 KiB) Viewed 20059 times
Last edited by gtwirth on Wed Mar 13, 2013 11:40 am, edited 4 times in total.
Glen Wirth
Simio LLC

gtwirth
Simio Team Member
Posts: 156
Joined: Mon Jun 09, 2008 4:42 pm

Re: Import Objects And Links From Spreadsheet

Post by gtwirth » Tue Nov 13, 2012 5:36 pm

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"

8) Choose the "ImportObjectsAndLinks.xlsx".
Glen Wirth
Simio LLC

gtwirth
Simio Team Member
Posts: 156
Joined: Mon Jun 09, 2008 4:42 pm

Re: Import Objects And Links From Spreadsheet

Post by gtwirth » Sun Dec 09, 2012 2:21 am

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.
Glen Wirth
Simio LLC

gtwirth
Simio Team Member
Posts: 156
Joined: Mon Jun 09, 2008 4:42 pm

Re: Import Objects And Links From Spreadsheet

Post by gtwirth » Mon Feb 25, 2013 3:59 pm

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.
Glen Wirth
Simio LLC

gtwirth
Simio Team Member
Posts: 156
Joined: Mon Jun 09, 2008 4:42 pm

Re: Import Objects And Links From Spreadsheet

Post by gtwirth » Thu Jun 06, 2013 11:15 am

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.
Last edited by gtwirth on Tue Jun 11, 2013 10:01 am, edited 1 time in total.
Glen Wirth
Simio LLC

gtwirth
Simio Team Member
Posts: 156
Joined: Mon Jun 09, 2008 4:42 pm

Re: Import Objects And Links From Spreadsheet

Post by gtwirth » Tue Jun 11, 2013 10:19 am

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
Glen Wirth
Simio LLC

JWolski
Posts: 3
Joined: Tue Mar 27, 2012 3:48 pm

Re: Import Objects And Links From Spreadsheet

Post by JWolski » Tue Jun 11, 2013 11:05 am

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

JW

willem
Posts: 110
Joined: Wed Jul 03, 2013 12:07 pm

Re: Import Objects And Links From Spreadsheet

Post by willem » Thu Jul 04, 2013 2:43 am

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

This will really help...

gtwirth
Simio Team Member
Posts: 156
Joined: Mon Jun 09, 2008 4:42 pm

Re: Import Objects And Links From Spreadsheet

Post by gtwirth » Fri Jul 12, 2013 3:31 pm

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
Glen Wirth
Simio LLC

MEagar
Posts: 344
Joined: Thu Feb 14, 2013 12:56 pm
Location: Australia
Contact:

Re: Import Objects And Links From Spreadsheet

Post by MEagar » Tue Jul 23, 2013 6:52 am

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

}
}
Mark

www.masterschedulingsolutions.com

ASagan
Posts: 356
Joined: Wed Oct 05, 2011 11:21 am
Location: Brisbane, QLD

Re: Import Objects And Links From Spreadsheet

Post by ASagan » Sun Sep 22, 2013 10:53 pm

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.
Solosi Pty Ltd -- www.solosi.com

rvasudev
Posts: 9
Joined: Fri Sep 26, 2014 2:08 pm

Re: Import Objects And Links From Spreadsheet

Post by rvasudev » Tue Sep 30, 2014 9:28 am

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
Posts: 9
Joined: Fri Sep 26, 2014 2:08 pm

Re: Import Objects And Links From Spreadsheet

Post by rvasudev » Wed Oct 01, 2014 9:59 am

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
Posts: 110
Joined: Wed Jul 03, 2013 12:07 pm

Re: Import Objects And Links From Spreadsheet

Post by willem » Wed Oct 01, 2014 10:10 am

Hi,

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

http://www.simio.com/forums/viewtopic.p ... port+Model

rvasudev
Posts: 9
Joined: Fri Sep 26, 2014 2:08 pm

Re: Import Objects And Links From Spreadsheet

Post by rvasudev » Wed Oct 01, 2014 10:51 am

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.

Post Reply