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 Cream OpenOffice.org Calc 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. Search : Multiple local • Cooperative 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 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. 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 0 0 0 3 =SUM(B3:D3)=15 0 0 0 4 =SUM(B4:D4)=15 0 0 0 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 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: M 0 , M 1 , . . . , M m − 1 • n jobs: J 0 , J 1 , . . . , J n − 1 • For each J j , sequence of operations O j 0 , O j 1 , . . . , O j ( m − 1) is assigned • Each O jk is a pair ( m jk , l jk ): m jk is the ma- chine number and l jk 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)   2 0 1 3 5 4 1 2 4 5 0 3 2 3 5 0 1 4   ( m jk ) =   1 0 2 3 4 5   2 1 4 5 0 3 1 3 5 0 4 2   1 3 6 7 3 6 8 5 10 10 10 4 5 4 8 9 1 7   ( l jk ) =   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) J 0 J 1 J 2 J 3 J 4 J 5 FJCP2005 – 11

Example 3: JSSP (5) An Optimal Solution of FT6 (55) J 0 J 1 J 2 J 3 J 4 J 5 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( Range 1 ; . . . ; Range n ) FJCP2005 – 17

Summary of functions (2) Logical Operators and Predicates • = , <> , < , <= , > , >= • CEQUALS( Range 1 ; . . . ; Range n ) • CNOTEQUALS( Range 1 ; . . . ; Range n ) • CSERIALIZED( Range 1 ; Range 2 ) intervals [ x i , x i + d i ) do not overlap each other where Range 1 specifies the start time x i and Range 2 specifies the duration d i • CSEQUENTIAL( Range 1 ; Range 2 ) intervals [ x i , x i + d i ) do not overlap each other and sequentially arranged FJCP2005 – 18

Summary of functions (3) CSP specification functions • CVARIABLES( Inf ; Sup ; Range 1 ; . . . ; Range n ) : Constraint variables • CONSTRAINTS( Range 1 ; . . . ; Range n ) : Constraints • COBJECTIVE( Cell ) : Objective variable • COPTIONS( Opt 1 ; . . . ; Opt n ) : Solver options – "MINIMIZE" , "MAXIMIZE" – "SA" , "TABOO" , etc. (to specify search algo- rithm) • CTIMEOUT( Second ) : Timeout value FJCP2005 – 19