LINGO TUTORIAL:
CONNECTING WITH EXCEL
OUTLINE
• Interfacing with External Files
• Interfacing With Spreadsheets
• Preparing Excel Files
• Example
• Create the file
• Create range
• Lingo Code
• Solution
INTERFACING WITH EXTERNAL
FILES
• It can be cumbersome and impractical to try to
maintain your data in a LINGO model file.
• In most cases, your model's data will reside externally
in files, spreadsheets, and databases.
• Also, a solution generated by LINGO is of little use if
you can't export it to other applications.
INTERFACING WITH
SPREADSHEETS
• LINGO provides the @OLE function for importing
data from spreadsheets.
• This function is only available under Windows versions
of LINGO.
• @OLE performs direct OLE transfers of data between
LINGO and Excel.
INTERFACING WITH
SPREADSHEETS
• @OLE is an interface function for moving data back and forth from
Excel using OLE based transfers.
• When using @OLE, LINGO loads Excel, tells Excel to load the desired
spreadsheet, and requests ranges of data from the sheet.
• The @OLE function may be used in the data and init sections to
import data.
• @OLE can read one or two-dimensional ranges (ranges on a single
worksheet in Excel)
• Ranges are read left-to-right, top-to-bottom.
INTERFACING WITH
SPREADSHEETS
• Lingo Syntax:
• object_list = @OLE( [‘spreadsheet_file’],
[‘range_name_list’]); IMPORT DATA
• @OLE( [‘spreadsheet_file’], [‘range_name_list’]) =
object_list; EXPORT DATA
• Example:
• COST = @OLE( 'SPECS.XLSX', 'COST'); IMPORT DATA
• @OLE( 'SPECS.XLSX', ‘X') = X; EXPORT DATA
INTERFACING WITH
SPREADSHEETS
• If object_list is identical to the ‘range_name_list’ then
no need to specify the range
• object_list = @OLE( [‘spreadsheet_file’],); IMPORT DATA
• @OLE( [‘spreadsheet_file’],) = object_list; EXPORT DATA
• Example:
• COST = @OLE( 'SPECS.XLSX',); IMPORT DATA
• @OLE( 'SPECS.XLSX',) = X; EXPORT DATA
INTERFACING WITH
SPREADSHEETS
• If ‘spreadsheet_file’ is the last open file, then no
need to specify the file name
• object_list = @OLE(, [‘range_name_list’]); IMPORT DATA
• @OLE( , [‘range_name_list’]) = object_list; EXPORT DATA
• Example:
• COST = @OLE( , COST); IMPORT DATA
• @OLE( ,X) = X; EXPORT DATA
INTERFACING WITH
SPREADSHEETS
• If ‘spreadsheet_file’ is the last open file, AND object_list is
identical to the ‘range_name_list’, then no need to
specify neither the file name nor the range
• object_list = @OLE(); IMPORT DATA
• @OLE() = object_list; EXPORT DATA
• Example:
• COST = @OLE(); IMPORT DATA
• @OLE() = X; EXPORT DATA
INTERFACING WITH
SPREADSHEETS
• Excel File:
• File Name: SPECS.XLSX
• Range:
• COST
•X
• How to create range in Excel??
INTERFACING WITH
SPREADSHEETS
• Create Range in Excel
INTERFACING WITH
SPREADSHEETS
• Create Range in Excel
INTERFACING WITH
SPREADSHEETS
• Create Range in Excel
EXAMPLE
• Formulation
• Minimize
• 2 𝑥1 + 3 𝑥2 + 0.5𝑥3
• Subject to:
• 𝑥1 + 3𝑥3 ≥ 10
• 2𝑥2 + 2𝑥3 ≥ 15
• 𝑥1 + 𝑥2 ≥ 12
EXAMPLE
• Data
• Number of Product = 3
• Number of Constraint = 3
•𝐶 = 2 3 0.5
• 𝑏1 = 10 15 12
• 𝐴1 = 1 0 3
• 𝐴2 = 0 2 2
• 𝐴3 = 1 1 0
EXAMPLE
• Excel Ranges
EXAMPLE
• Lingo Code
Defining set Import Data from
excel
EXAMPLE
• Lingo Code
Formulation
Export Result to
excel
EXAMPLE
• Solution
HOMEWORK 1
• Toy Production Problem • 𝑥1 : number of units of product
type A
Product Man- Machine Profit
Power • 𝑥2 : number of units of product
Type A 3h 1h 25 SR type B
Type B 2h 4h 15 SR
Availability 70 h 110 h • Total Profit: 25 𝑥1 + 15 𝑥2
• Man-Power availability: 3 𝑥1 +
2 𝑥2 ≤ 70
• Machine availability: 𝑥1 + 4 𝑥2 ≤
110
HOMEWORK 1
• Solve the toy production problem using Lingo that
interfaced with excel!
TO BE CONT..
