Spreadsheets are one of today’s most popular and convenient tools for storing data. They can also be very useful tools for manipulating and representing the results generated by your model. LINGO’s ability to link to spreadsheets allows you to take full advantage of the power and flexibility of the spreadsheet environment.
Read Values from Spreadsheets
You can store data for your LINGO model in one or more spreadsheet files where they can be easily entered and edited. Your LINGO model can reference spreadsheet data using simple, straightforward commands. For example, the LINGO language statement:
COST, CAPACITY = @OLE(‘SPECS.XLS’);
would access tables of data in an SPECS.XLS Excel file in ranges named COST and CAPACITY.
LINGO offers direct, real-time access to Excel spreadsheet values using OLE (Object Linking and Embedding) links. LINGO can also read data saved to a Lotus spreadsheet file.
Write Solution Information to Spreadsheets
In addition to reading spreadsheet information, you can also have LINGO write solution information to a spreadsheet file. Again, by doing this, spreadsheet users can easily create reports and analyze the solution in a convenient and familiar environment.
The process can be done automatically when the model is solved, or manually after solving. Much like reading spreadsheet data, the LINGO statement:
@OLE(‘ROUTING.XLS’) = USED, LOAD;
would automatically put the solved values for data sets USED and LOAD into the ranges of the same name in the Excel file named ROUTING.XLS.
Run LINGO from an Excel File
LINGO can be run as a server application from an Excel macro. This allows you to do things such as create a “Solve” button on an Excel sheet that directly calls LINGO and runs a series of commands.
Embed LINGO models and Excel Files
LINGO allows you the convenience of having your LINGO model formulation and Excel workbook in the same file. You can embed an Excel workbook in your LINGO model file or embed your LINGO model file into an Excel workbook. By opening a single file, you can view and edit both the formulation and data as well as run both LINGO and Excel. Similarly, you can embed LINGO in other supported applications and vice versa.