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 system – 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 Cream OpenOffice.org Calc

FJCP2005 – 1

Features of Calc/Cream Calc/Cream is a spreadsheet front-end for constraint 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, Knowledgesheet, 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 constraint programming in Java. • Natural Description: Various constraints can be naturally described in Java syntax. • Various Search Algorithms: Various search algorithms 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 calculate the total profit producing several products 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 following 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 over 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. 1 2 3 4 5 6

A B C D =CNOTEQUALS(B2:D4) =SUM(B2:B4)=15 =SUM(C2:C4)=15 =SUM(D2:D4)=15 =SUM(B2:D2)=15 0 0 0 =SUM(B3:D3)=15 0 0 0 =SUM(B4:D4)=15 0 0 0 =CVARIABLES(1;9;B2:D4) =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 of 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 machine 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 minimal end time to complete all jobs FJCP2005 – 9

Example 3: JSSP (3) FT6 Benchmark problem  2  12 (mjk ) =   1 2 1 (ljk )

=

   

1 8 5 5 9 3

(m = 6, 0 1 2 4 3 5 0 2 1 4 3 5 3 5 4 5 3 3

6 10 8 5 5 9

n= 3 5 0 3 5 0 7 10 9 3 4 10

6) 5 0 1 4 0 4

4 3 4 5 3 2 3 10 1 8 3 4

    6 4 7 9 1 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 algorithms to solve this kind of problems. – SA (Simulated Annealing), TS (Taboo Search), etc. – any problems including a serialized constraint 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 algorithm) • CTIMEOUT(Second): Timeout value FJCP2005 – 19