--- /dev/null
+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.");
+
+ }
+
+ }
+
+}
Code Review / simantics / platform.git / blobdiff