Calc/Cream OpenOffice spreadsheet front-end for constraint - - PowerPoint PPT Presentation

Calc/Cream

OpenOffice spreadsheet front-end for constraint programming Naoyuki Tamura, Mutsunori Banbara Kobe University, Japan FJCP2005 2005/11/16

Outline of this Demonstration

• Calc/Cream: a constraint spreadsheet sys-

tem – Add-in of OpenOffice.org Calc spreadsheet – GPL open source software

• Cream:

a Java class library for constraint programming – LGPL open source software

Calc/Cream OpenOffice.org Calc Cream

FJCP2005 – 1

Features of Calc/Cream

Calc/Cream is a spreadsheet front-end for con- straint programming developed on OpenOffice.org Calc.

• User can solve his/her problem by specifying

the constraint variables and constraints in the spreadsheet.

• It is useful as a front-end of a CSP solving

system.

• Related works:

Frontline systems, Knowl- edgesheet, etc.

URL: http://bach.istc.kobe-u.ac.jp/cream/calc.html

FJCP2005 – 2

Features of Cream

Cream is a class library for finite-domain con- straint programming in Java.

• Natural Description: Various constraints can

be naturally described in Java syntax.

• Various Search Algorithms: Various search al-

gorithms are available.

• Cooperative

Local Search: Multiple local search solvers can be executed in parallel.

• Related works: ILOG JSolver, Koalog, ICS in

Java, JACK, JCL

URL: http://bach.istc.kobe-u.ac.jp/cream/

FJCP2005 – 3

Demonstration of Calc/Cream

• Example 1: Production Planning
• Example 2: 5 × 5 Semi Magic Square
• Example 3: Job Shop Scheduling Problem
• Example 4: N-Queens Problem
• Example 5: Map Coloring Problem
• Example 6: Number Place (Sudoku) Puzzle
• Summary of functions

FJCP2005 – 4

Example 1: Production Planning (1)

• Suppose you already have a spreadsheet to cal-

culate the total profit producing several prod- ucts from several materials.

• You want to determine the number of products

to be produced so that – the profit is maximized, and – the number to be consumed does not over the number in the stock for each material.

FJCP2005 – 5

Example 1: Production Planning (2)

Calc/Cream can solve this problem by adding fol- lowing cells in the spreadsheet.

• CVARIABLES(0; "MAX"; B2:B10)

declares the constraint variables (the number of products)

• CONSTRAINTS(B2:L13)

declares the constraints (the consumption does not

• ver the stock)
• COBJECTIVE(D11)

declares the objective variable (the profit)

• COPTIONS("MAXIMIZE")

declares the objective (to be maximized)

FJCP2005 – 6

Example 2: Semi Magic Square

• Place numbers 1–9 onto 3 × 3 cells so that the

sum of each row and column is equal to 15.

A B C D 1 =CNOTEQUALS(B2:D4) =SUM(B2:B4)=15 =SUM(C2:C4)=15 =SUM(D2:D4)=15 2 =SUM(B2:D2)=15 3 =SUM(B3:D3)=15 4 =SUM(B4:D4)=15 5 =CVARIABLES(1;9;B2:D4) 6 =CONSTRAINTS(A1:D4)

• Demonstration will be for 5 × 5 semi magic

square. – Place numbers 1–25 onto 5 × 5 cells so that each sum is equal to 65.

FJCP2005 – 7

Example 3: JSSP (1)

• JSSP (Job Shop Scheduling Problem) is one
• f the most difficult and typical optimization

problems. – The goal is to find the minimal end time to complete N jobs by using M machines. – Local search algorithms are widely used to solve JSSPs.

FJCP2005 – 8

Example 3: JSSP (2)

• m machines: M0, M1, . . . , Mm−1
• n jobs: J0, J1, . . . , Jn−1
• For each Jj, sequence of operations Oj0, Oj1,

. . . , Oj(m−1) is assigned

• Each Ojk is a pair (mjk, ljk): mjk is the ma-

chine number and ljk is its process time

• One machine can not be used at multiple jobs

at the same time

• A solution should satisfy all above conditions,

and an optimal solution is a solution with min- imal end time to complete all jobs

FJCP2005 – 9

Example 3: JSSP (3)

FT6 Benchmark problem (m = 6, n = 6) (mjk) =

   

2 1 3 5 4 1 2 4 5 3 2 3 5 1 4 1 2 3 4 5 2 1 4 5 3 1 3 5 4 2

   

(ljk) =

   

1 3 6 7 3 6 8 5 10 10 10 4 5 4 8 9 1 7 5 5 5 3 8 9 9 3 5 4 3 1 3 3 9 10 4 1

   

FJCP2005 – 10

Example 3: JSSP (4)

A Solution of FT6 (68) J0 J1 J2 J3 J4 J5

FJCP2005 – 11

Example 3: JSSP (5)

An Optimal Solution of FT6 (55) J0 J1 J2 J3 J4 J5

FJCP2005 – 12

Example 3: JSSP (6)

• Calc/Cream provides several local search al-

gorithms to solve this kind of problems. – SA (Simulated Annealing), TS (Taboo Search), etc. – any problems including a serialized con- straint which means that given intervals are not overlapped each other

• Calc/Cream also provides cooperative local

search in which multiple local search solvers work cooperatively in parallel.

FJCP2005 – 13

Example 4: N-Queens Problem

FJCP2005 – 14

Example 5: Map Coloring Problem

FJCP2005 – 15

Example 6: Sudoku Puzzle

FJCP2005 – 16

Summary of functions (1)

Constants and Variables

• Integers
• Any Cell or Range References:

A1, Sheet1.B2, A1:B2 Arithmetic Operators

• +, -, *, +, ABS(Cell)
• SUM(Range1;. . .;Rangen)

FJCP2005 – 17

Summary of functions (2)

Logical Operators and Predicates

• =, <>, <, <=, >, >=
• CEQUALS(Range1;. . .;Rangen)
• CNOTEQUALS(Range1;. . .;Rangen)
• CSERIALIZED(Range1;Range2)

intervals [xi, xi +di) do not overlap each other where Range1 specifies the start time xi and Range2 specifies the duration di

• CSEQUENTIAL(Range1;Range2)

intervals [xi, xi +di) do not overlap each other and sequentially arranged

FJCP2005 – 18

Summary of functions (3)

CSP specification functions

• CVARIABLES(Inf;Sup;Range1;. . .;Rangen):

Constraint variables

• CONSTRAINTS(Range1;. . .;Rangen):

Constraints

• COBJECTIVE(Cell): Objective variable
• COPTIONS(Opt1;. . .;Optn): Solver options

– "MINIMIZE", "MAXIMIZE" – "SA", "TABOO", etc. (to specify search algo- rithm)

• CTIMEOUT(Second): Timeout value

FJCP2005 – 19