X-Git-Url: https://gerrit.simantics.org/r/gitweb?a=blobdiff_plain;ds=sidebyside;f=bundles%2Forg.simantics.spreadsheet%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2FSpreadsheets.java;fp=bundles%2Forg.simantics.spreadsheet%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2FSpreadsheets.java;h=db9237e7a333fcf1c74c775ce071d4ae3ee211cc;hb=c07a3818f0024e932a27eb85cbfd3f2291475a65;hp=0000000000000000000000000000000000000000;hpb=6c99e980d250fb9201aba93be7dcb1f55564dccd;p=simantics%2Fplatform.git

diff --git a/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/Spreadsheets.java b/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/Spreadsheets.java
new file mode 100644
index 000000000..db9237e7a
--- /dev/null
+++ b/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/Spreadsheets.java
@@ -0,0 +1,486 @@
+package org.simantics.spreadsheet;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.simantics.databoard.binding.mutable.Variant;
+import org.simantics.scl.runtime.tuple.Tuple;
+import org.simantics.spreadsheet.solver.SpreadsheetCell;
+
+public class Spreadsheets {
+
+    public static final int SPREADSHEET_BTREE_SIZE = 100;
+    public static final String CELL_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Cell";
+    public static final String LINE_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Line";
+    public static final String LINES_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Lines";
+
+    public static boolean asBoolean(Object object) {
+        if(object instanceof Boolean) return (Boolean)object;
+        else if(object instanceof Number) return ((Number)object).doubleValue() != 0;
+        else if(object instanceof Variant) return asBoolean(((Variant)object).getValue());
+        else if(object instanceof String) {
+            Double d = asDoubleWhereEmptyStringIsZero((String)object);
+            if(d==null) return false;
+            else return d != 0;
+        }
+        return false;
+    }
+
+    public static String asString(Object object) {
+        if(object == null) return "";
+        if(object instanceof String) return (String)object;
+        if(object instanceof Number) {
+            double dVal = ((Number)object).doubleValue();
+            if(dVal == Math.floor(dVal)){
+                return ""+((Number)object).intValue();
+            } else {
+                return object.toString();
+            }
+        }
+        else if(object instanceof Variant) {
+            Object o = ((Variant) object).getValue();
+            if(o instanceof String) return (String)o;
+            else if(o instanceof Number) asString((Number)o);
+            else return o.toString();
+        }
+        return object.toString();
+    }
+
+    public static Double asDoubleWhereEmptyStringIsZero(Object object){
+        if(object instanceof Number)
+            return ((Number)object).doubleValue();
+        else if(object instanceof String) {
+            try {
+                if(((String)object).isEmpty())
+                    return 0.0;
+                return Double.parseDouble((String)object);
+            } catch (NumberFormatException e) {
+                return null;
+            }
+        } else if(object instanceof Variant) {
+            Object o = ((Variant) object).getValue();
+            return asDoubleWhereEmptyStringIsZero(o);
+        } else if (SpreadsheetCell.EMPTY == object) {
+            return null;
+        }
+        return null;
+    }
+
+    public static double asNumber(Object object) {
+        if(object instanceof Number) {
+            return ((Number)object).doubleValue();
+        } else if(object instanceof String) {
+            try {
+                String str = (String)object;
+                if(str.isEmpty()) return 0;
+                return Double.parseDouble((String)object);
+            } catch (NumberFormatException e) {
+                return 0;
+            }
+        } else if(object instanceof Variant) {
+            Object o = ((Variant) object).getValue();
+            return asNumber(o);
+        } else if (SpreadsheetCell.EMPTY == object) {
+            return 0.0;
+        }
+
+        return 0.0;
+
+    }
+
+    public static Number asValidNumber(Object object) {
+        if(object instanceof Number) {
+            return (Number)object;
+        } else if(object instanceof String) {
+            try {
+                return Double.parseDouble((String)object);
+            } catch (NumberFormatException e) {
+                return null;
+            }
+        } else if(object instanceof Variant) {
+            Object o = ((Variant) object).getValue();
+            return asNumber(o);
+        } else if (SpreadsheetCell.EMPTY == object) {
+            return null;
+        }
+
+        return null;
+
+    }
+
+    public static boolean matchCriteria(Object value, Object criteria) {
+        if(value==null || criteria==null) return false;
+
+        if(value instanceof Variant){
+            Double dVal = asDoubleWhereEmptyStringIsZero(value);
+            if(dVal==null) value = ((Variant)value).getValue();
+            else value = dVal;
+        }
+        if(criteria instanceof Variant){
+            Double dVal = asDoubleWhereEmptyStringIsZero(criteria);
+            if(dVal==null) criteria = ((Variant)criteria).getValue();
+            else criteria = dVal;
+        }
+
+        if(criteria instanceof Number && value instanceof Number) {
+            Number nc = (asNumber(criteria));
+            Number nv = (asNumber(value));
+            return nc.equals(nv);
+        }
+        if(criteria instanceof String){
+            boolean nums = false;
+            Object valueObj = null;
+            if(value instanceof Number){
+                valueObj = ((Number)value).doubleValue();
+                nums = true;
+            }
+            else valueObj = value.toString();
+
+            String sc = criteria.toString();
+            if(sc.length() >= 3){
+                String oper = sc.substring(0, 2);
+                String criteriaStr = sc.substring(2);
+                Double criteriaNum = null;
+                try {
+                    criteriaNum = Double.parseDouble(criteriaStr);
+                    if(oper.equals("<>")){
+                        if(!nums) return true;
+                    }
+                    else if(!nums) return false;
+                    nums = true;
+                } catch (NumberFormatException e){
+                    if(oper.equals("<>")){
+                        if(nums) return true;
+                    }
+                    else if(nums) return false;
+                    nums = false;
+                }
+
+                if(oper.equals(">=")){
+                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) >= 0 ;
+                    else return ((Number)valueObj).doubleValue() >= criteriaNum;
+                } else if(oper.equals("<=")){
+                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) <= 0 ;
+                    else return ((Number)valueObj).doubleValue() <= criteriaNum;
+                } else if(oper.equals("<>")){
+                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) != 0 ;
+                    else return ((Number)valueObj).doubleValue() != criteriaNum;
+                }
+            }
+            if(sc.length() >= 2){
+                String oper = sc.substring(0, 1);
+                String criteriaStr = sc.substring(1);
+                Double criteriaNum = null;
+
+                try {
+                    criteriaNum = Double.parseDouble(criteriaStr);
+                    if(!nums) return false;
+                    nums = true;
+                } catch (NumberFormatException e){
+                    if(nums) return false;
+                    nums = false;
+                }
+                if(oper.equals("<")){
+                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) < 0;
+                    else return ((Number)valueObj).doubleValue() < criteriaNum;
+                } else if(oper.equals(">")){
+                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) > 0;
+                    else return ((Number)valueObj).doubleValue() > criteriaNum;
+                } else if(oper.equals("=")){
+                    if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) == 0;
+                    else return ((Number)valueObj).doubleValue() == criteriaNum;
+                }
+            }
+            return sc.equals(valueObj);
+        }
+        else if (criteria instanceof Number){
+            return false;
+        }
+        throw new IllegalStateException();
+    }
+
+    public static boolean excelEquals(Object left, Object right) {
+        if(left instanceof String) {
+            if(right instanceof String) {
+                return ((String) left).toLowerCase().equals(((String) right).toLowerCase());
+            }
+        }
+        return left.equals(right);
+    }
+
+
+    //  1 kirjain, 'A' + column
+    //  2 kirjainta, 'A' + column % 26 , 'A' + int((column-26)/26)
+    //  3 kirjainta 'A' + column % 26 , 'A' + int(column-(26*(26+1)) / 26) % 26
+
+    //    0              26
+    //    26             26 + 26*26
+    //    26 + 26*26     26 + 26*26 + 26*26*26
+
+    public static String columnName(int column, int current, int limit, int chars) {
+
+        if(column < limit) {
+
+            char[] buf = new char[chars];
+            column -= current;
+            for(int i=chars-1;i>=0;i--) {
+                char rem = (char)(column % 26);
+                column = (column / 26);
+                buf[i] = (char)('A' + rem); 
+            }
+            return new String(buf);
+
+        } else return columnName(column, limit, 26*(limit+1), chars+1);
+
+    }
+
+    public static String columnName(int column) {
+        return columnName(column, 0, 26, 1);
+    }    
+
+    public static String cellName(int row, int column) {
+
+        String result = columnName(column);
+        result += (row+1);
+        return result;
+
+    }    
+
+    public static String offset(String location, int rowOffset, int columnOffset) {
+
+        Range range = decodeCellAbsolute(location);
+        String result = cellName(range.startRow + rowOffset, range.startColumn + columnOffset); 
+        //      System.err.println("offset " + location + "(" + rowOffset + " " + columnOffset + ") = >" + result);
+        return result;
+
+    }
+
+    public static Range decodeCellAbsolute(String identifier) {
+        long l = decodeCellCoded(identifier);
+        int row = (int)(l & 0xffffffff) - 1;
+        int column = (int)((l>>32) & 0xffffffff);
+        return new Range(row, row, column, column);
+    }
+
+    public static Range decodePossibleCellAbsolute(String identifier) {
+        try {
+            return decodeCellAbsolute(identifier);
+        } catch (CellParseException e) {
+            return null;
+        }
+    }
+
+    public static long decodeCellCoded(String identifier) {
+
+        //        System.out.println("decodecellabsolute " + identifier);
+
+        int row = 0;
+        int column = 0;
+
+//      identifier.
+
+        int position = 0;
+
+        // We skip $ here
+        if(identifier.charAt(position) == '$') position++;
+
+        int length = identifier.length();
+
+        while(position < length) {
+            char b = identifier.charAt(position);
+            if(b >= 'A' && b <= 'Z') column = column * 26 + (b-'A' + 1);
+            else break;
+            position++;
+        }
+
+        // We skip $ here
+        if(position < length)
+            if(identifier.charAt(position) == '$')
+                position++;
+
+        while(position < length) {
+            char b = identifier.charAt(position);
+            if(b >= '0' && b <= '9'){
+                row = row * 10 + b-'0';
+            }
+            else if(b=='-' && position < (length-1)){//identify use of full row range here.
+                position++;
+                char b2 = identifier.charAt(position);
+                if(b2=='1'){
+                    row = 0;
+                    position++;
+                    break;
+                }
+            }
+            else {
+                break;
+            }
+            position++;
+        }
+
+        if(position == length) {
+
+            // We need to be able to express -1 in row => report row + 1 here
+            column--;
+            //            System.err.println("ra " + identifier + " => " + row + " " + column);
+            return row + (((long)column)<<32);
+
+        } else {
+
+            throw new CellParseException("Cell identifier '" + identifier + "' is not a valid cell reference.");
+
+        }
+
+    }
+
+    public static Range decodeCellRelative(String identifier, int row, int column) {
+
+        int offset = Integer.valueOf(identifier.substring(1).trim());
+        //        System.out.println("offset=" + offset);
+
+        if(identifier.startsWith("L") || identifier.startsWith("l")) {
+            return new Range(row, row, column-offset, column-offset);
+        } else if(identifier.startsWith("R") || identifier.startsWith("r")) {
+            return new Range(row, row, column+offset, column+offset);
+        } else if(identifier.startsWith("U") || identifier.startsWith("u")) {
+            return new Range(row-offset, row-offset, column, column);
+        } else if(identifier.startsWith("D") || identifier.startsWith("d")) {
+            return new Range(row+offset, row+offset, column, column);
+        } else {
+            throw new CellParseException("Relative cell syntax must begin with L|R|U|D.");
+        }
+
+    }
+
+    public static Range decodeCell(String identifier, int row, int column) {
+
+        if(identifier.startsWith("_")) {
+            return decodeCellRelative(identifier.substring(1), row, column);
+        } else {
+            return decodeCellAbsolute(identifier);
+        }
+
+    }
+
+    public static Range decodeReference(String identifier, int row, int column) {
+        if(!identifier.startsWith("&")) throw new CellParseException("A reference cell was expected.");
+        return decodeRange(identifier.substring(1), row, column);
+    }
+
+    public static List<Range> decodeRanges(String ranges){
+        String[] splitted = ranges.split(",");
+        List<Range> result = new ArrayList<>();
+        for(String split : splitted){
+            result.add(decodeRange(split));
+        }
+        return result;
+    }
+
+    public static Object extract(Object object, int row, int column) {
+        if(object instanceof List) {
+            @SuppressWarnings("rawtypes")
+            List list = (List)object;
+            if(list.size() <= row) return null;
+            Object item = list.get(row);
+            if(item instanceof Tuple) {
+                Tuple tuple = (Tuple)item;
+                if(tuple.length() <= column) return null;
+                return tuple.get(column);
+            }
+        }
+        return null;
+    }
+
+
+
+    public static int startRow(List<Range> ranges){
+        int s = -1;
+        for(Range r : ranges){
+            if(r.startRow<s || s==-1){
+                s = r.startRow;
+            }
+        }
+        return s;
+    }
+
+    public static int startColumn(List<Range> ranges){
+        int s = -1;
+        for(Range r : ranges){
+            if(r.startColumn<s || s==-1){
+                s = r.startColumn;
+            }
+        }
+        return s;
+    }
+
+    public static int amountOfRows(Range r){
+        int endRow = -2;
+        int startRow = -2;
+        if(r.isFullRows()){
+            return Range.MAXROWSPEC;
+        }
+        if(endRow == -2 && startRow == -2){
+            endRow = r.endRow;
+            startRow = r.startRow;
+        }
+        if(r.startRow<startRow){
+            startRow = r.startRow;
+        }
+        if(r.endRow>endRow){
+            endRow = r.endRow;
+        }
+        return endRow - startRow +1;
+    }
+
+    public static int amountOfColumns(Range r){
+        int endColumn = -2;
+        int startColumn = -2;
+        if(r.isFullColumns()){
+            return Range.MAXCOLUMNSPEC;
+        }
+        if(endColumn == -2 && startColumn == -2){
+            endColumn = r.endColumn; 
+            startColumn = r.startColumn;
+        }
+        if(r.startColumn<startColumn){
+            startColumn = r.startColumn;
+        }
+        if(r.endColumn>endColumn){
+            endColumn = r.endColumn;
+        }
+        return endColumn - startColumn +1;
+    }
+
+    public static Range decodeRange(String rangeOrCell) {
+        if(rangeOrCell.isEmpty()) return fullRange();
+        return decodeRange(rangeOrCell, 0, 0);
+    }
+
+    public static Range fullRange() {
+        return new Range(0, -1, 0, -1);
+    }
+
+    public static Range decodeRange(String rangeOrCell, int row, int column) {
+
+        String[] parts = rangeOrCell.split(":");
+        if(parts.length == 1) {
+
+            return decodeCell(rangeOrCell, row, column);
+
+        } else if (parts.length == 2) {
+
+            Range from = decodeCell(parts[0].trim(), row, column);
+            //            System.out.println("decodefrom=" + from);
+            Range to = decodeCell(parts[1].trim(), row, column);
+            //            System.out.println("decodeto=" + to);
+            return Range.combine(from, to);
+
+        } else {
+
+            throw new CellParseException("The reference cell syntax was invalid. At most 1 occurrence of ':' is expected.");
+
+        }
+
+    }
+
+}