1 package org.simantics.spreadsheet;
3 import java.util.ArrayList;
6 import org.simantics.databoard.binding.mutable.Variant;
7 import org.simantics.scl.runtime.tuple.Tuple;
8 import org.simantics.spreadsheet.solver.SpreadsheetCell;
10 public class Spreadsheets {
12 public static final int SPREADSHEET_BTREE_SIZE = 100;
13 public static final String CELL_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Cell";
14 public static final String LINE_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Line";
15 public static final String LINES_TYPE_URI = "http://www.simantics.org/Spreadsheet-1.2/Lines";
17 public static boolean asBoolean(Object object) {
18 if(object instanceof Boolean) return (Boolean)object;
19 else if(object instanceof Number) return ((Number)object).doubleValue() != 0;
20 else if(object instanceof Variant) return asBoolean(((Variant)object).getValue());
21 else if(object instanceof String) {
22 Double d = asDoubleWhereEmptyStringIsZero((String)object);
23 if(d==null) return false;
29 public static String asString(Object object) {
30 if(object == null) return "";
31 if(object instanceof String) return (String)object;
32 if(object instanceof Number) {
33 double dVal = ((Number)object).doubleValue();
34 if(dVal == Math.floor(dVal)){
35 return ""+((Number)object).intValue();
37 return object.toString();
40 else if(object instanceof Variant) {
41 Object o = ((Variant) object).getValue();
42 if(o instanceof String) return (String)o;
43 else if(o instanceof Number) asString((Number)o);
44 else return o.toString();
46 return object.toString();
49 public static Double asDoubleWhereEmptyStringIsZero(Object object){
50 if(object instanceof Number)
51 return ((Number)object).doubleValue();
52 else if(object instanceof String) {
54 if(((String)object).isEmpty())
56 return Double.parseDouble((String)object);
57 } catch (NumberFormatException e) {
60 } else if(object instanceof Variant) {
61 Object o = ((Variant) object).getValue();
62 return asDoubleWhereEmptyStringIsZero(o);
63 } else if (SpreadsheetCell.EMPTY == object) {
69 public static double asNumber(Object object) {
70 if(object instanceof Number) {
71 return ((Number)object).doubleValue();
72 } else if(object instanceof String) {
74 String str = (String)object;
75 if(str.isEmpty()) return 0;
76 return Double.parseDouble((String)object);
77 } catch (NumberFormatException e) {
80 } else if(object instanceof Variant) {
81 Object o = ((Variant) object).getValue();
83 } else if (SpreadsheetCell.EMPTY == object) {
91 public static Number asValidNumber(Object object) {
92 if(object instanceof Number) {
93 return (Number)object;
94 } else if(object instanceof String) {
96 return Double.parseDouble((String)object);
97 } catch (NumberFormatException e) {
100 } else if(object instanceof Variant) {
101 Object o = ((Variant) object).getValue();
103 } else if (SpreadsheetCell.EMPTY == object) {
111 public static boolean matchCriteria(Object value, Object criteria) {
112 if(value==null || criteria==null) return false;
114 if(value instanceof Variant){
115 Double dVal = asDoubleWhereEmptyStringIsZero(value);
116 if(dVal==null) value = ((Variant)value).getValue();
119 if(criteria instanceof Variant){
120 Double dVal = asDoubleWhereEmptyStringIsZero(criteria);
121 if(dVal==null) criteria = ((Variant)criteria).getValue();
122 else criteria = dVal;
125 if(criteria instanceof Number && value instanceof Number) {
126 Number nc = (asNumber(criteria));
127 Number nv = (asNumber(value));
128 return nc.equals(nv);
130 if(criteria instanceof String){
131 boolean nums = false;
132 Object valueObj = null;
133 if(value instanceof Number){
134 valueObj = ((Number)value).doubleValue();
137 else valueObj = value.toString();
139 String sc = criteria.toString();
140 if(sc.length() >= 3){
141 String oper = sc.substring(0, 2);
142 String criteriaStr = sc.substring(2);
143 Double criteriaNum = null;
145 criteriaNum = Double.parseDouble(criteriaStr);
146 if(oper.equals("<>")){
147 if(!nums) return true;
149 else if(!nums) return false;
151 } catch (NumberFormatException e){
152 if(oper.equals("<>")){
153 if(nums) return true;
155 else if(nums) return false;
159 if(oper.equals(">=")){
160 if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) >= 0 ;
161 else return ((Number)valueObj).doubleValue() >= criteriaNum;
162 } else if(oper.equals("<=")){
163 if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) <= 0 ;
164 else return ((Number)valueObj).doubleValue() <= criteriaNum;
165 } else if(oper.equals("<>")){
166 if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) != 0 ;
167 else return ((Number)valueObj).doubleValue() != criteriaNum;
170 if(sc.length() >= 2){
171 String oper = sc.substring(0, 1);
172 String criteriaStr = sc.substring(1);
173 Double criteriaNum = null;
176 criteriaNum = Double.parseDouble(criteriaStr);
177 if(!nums) return false;
179 } catch (NumberFormatException e){
180 if(nums) return false;
183 if(oper.equals("<")){
184 if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) < 0;
185 else return ((Number)valueObj).doubleValue() < criteriaNum;
186 } else if(oper.equals(">")){
187 if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) > 0;
188 else return ((Number)valueObj).doubleValue() > criteriaNum;
189 } else if(oper.equals("=")){
190 if(!nums) return (valueObj.toString().toLowerCase()).compareTo(criteriaStr.toLowerCase()) == 0;
191 else return ((Number)valueObj).doubleValue() == criteriaNum;
194 return sc.equals(valueObj);
196 else if (criteria instanceof Number){
199 throw new IllegalStateException();
202 public static boolean excelEquals(Object left, Object right) {
203 if(left instanceof String) {
204 if(right instanceof String) {
205 return ((String) left).toLowerCase().equals(((String) right).toLowerCase());
208 return left.equals(right);
212 // 1 kirjain, 'A' + column
213 // 2 kirjainta, 'A' + column % 26 , 'A' + int((column-26)/26)
214 // 3 kirjainta 'A' + column % 26 , 'A' + int(column-(26*(26+1)) / 26) % 26
218 // 26 + 26*26 26 + 26*26 + 26*26*26
220 public static String columnName(int column, int current, int limit, int chars) {
224 char[] buf = new char[chars];
226 for(int i=chars-1;i>=0;i--) {
227 char rem = (char)(column % 26);
228 column = (column / 26);
229 buf[i] = (char)('A' + rem);
231 return new String(buf);
233 } else return columnName(column, limit, 26*(limit+1), chars+1);
237 public static String columnName(int column) {
238 return columnName(column, 0, 26, 1);
241 public static String cellName(int row, int column) {
243 String result = columnName(column);
249 public static String offset(String location, int rowOffset, int columnOffset) {
251 Range range = decodeCellAbsolute(location);
252 String result = cellName(range.startRow + rowOffset, range.startColumn + columnOffset);
253 // System.err.println("offset " + location + "(" + rowOffset + " " + columnOffset + ") = >" + result);
258 public static Range decodeCellAbsolute(String identifier) {
259 long l = decodeCellCoded(identifier);
260 int row = (int)(l & 0xffffffff) - 1;
261 int column = (int)((l>>32) & 0xffffffff);
262 return new Range(row, row, column, column);
265 public static Range decodePossibleCellAbsolute(String identifier) {
267 return decodeCellAbsolute(identifier);
268 } catch (CellParseException e) {
273 public static long decodeCellCoded(String identifier) {
275 // System.out.println("decodecellabsolute " + identifier);
285 if(identifier.charAt(position) == '$') position++;
287 int length = identifier.length();
289 while(position < length) {
290 char b = identifier.charAt(position);
291 if(b >= 'A' && b <= 'Z') column = column * 26 + (b-'A' + 1);
297 if(position < length)
298 if(identifier.charAt(position) == '$')
301 while(position < length) {
302 char b = identifier.charAt(position);
303 if(b >= '0' && b <= '9'){
304 row = row * 10 + b-'0';
306 else if(b=='-' && position < (length-1)){//identify use of full row range here.
308 char b2 = identifier.charAt(position);
321 if(position == length) {
323 // We need to be able to express -1 in row => report row + 1 here
325 // System.err.println("ra " + identifier + " => " + row + " " + column);
326 return row + (((long)column)<<32);
330 throw new CellParseException("Cell identifier '" + identifier + "' is not a valid cell reference.");
336 public static Range decodeCellRelative(String identifier, int row, int column) {
338 int offset = Integer.valueOf(identifier.substring(1).trim());
339 // System.out.println("offset=" + offset);
341 if(identifier.startsWith("L") || identifier.startsWith("l")) {
342 return new Range(row, row, column-offset, column-offset);
343 } else if(identifier.startsWith("R") || identifier.startsWith("r")) {
344 return new Range(row, row, column+offset, column+offset);
345 } else if(identifier.startsWith("U") || identifier.startsWith("u")) {
346 return new Range(row-offset, row-offset, column, column);
347 } else if(identifier.startsWith("D") || identifier.startsWith("d")) {
348 return new Range(row+offset, row+offset, column, column);
350 throw new CellParseException("Relative cell syntax must begin with L|R|U|D.");
355 public static Range decodeCell(String identifier, int row, int column) {
357 if(identifier.startsWith("_")) {
358 return decodeCellRelative(identifier.substring(1), row, column);
360 return decodeCellAbsolute(identifier);
365 public static Range decodeReference(String identifier, int row, int column) {
366 if(!identifier.startsWith("&")) throw new CellParseException("A reference cell was expected.");
367 return decodeRange(identifier.substring(1), row, column);
370 public static List<Range> decodeRanges(String ranges){
371 String[] splitted = ranges.split(",");
372 List<Range> result = new ArrayList<>();
373 for(String split : splitted){
374 result.add(decodeRange(split));
379 public static Object extract(Object object, int row, int column) {
380 if(object instanceof List) {
381 @SuppressWarnings("rawtypes")
382 List list = (List)object;
383 if(list.size() <= row) return null;
384 Object item = list.get(row);
385 if(item instanceof Tuple) {
386 Tuple tuple = (Tuple)item;
387 if(tuple.length() <= column) return null;
388 return tuple.get(column);
396 public static int startRow(List<Range> ranges){
398 for(Range r : ranges){
399 if(r.startRow<s || s==-1){
406 public static int startColumn(List<Range> ranges){
408 for(Range r : ranges){
409 if(r.startColumn<s || s==-1){
416 public static int amountOfRows(Range r){
420 return Range.MAXROWSPEC;
422 if(endRow == -2 && startRow == -2){
424 startRow = r.startRow;
426 if(r.startRow<startRow){
427 startRow = r.startRow;
432 return endRow - startRow +1;
435 public static int amountOfColumns(Range r){
437 int startColumn = -2;
438 if(r.isFullColumns()){
439 return Range.MAXCOLUMNSPEC;
441 if(endColumn == -2 && startColumn == -2){
442 endColumn = r.endColumn;
443 startColumn = r.startColumn;
445 if(r.startColumn<startColumn){
446 startColumn = r.startColumn;
448 if(r.endColumn>endColumn){
449 endColumn = r.endColumn;
451 return endColumn - startColumn +1;
454 public static Range decodeRange(String rangeOrCell) {
455 if(rangeOrCell.isEmpty()) return fullRange();
456 return decodeRange(rangeOrCell, 0, 0);
459 public static Range fullRange() {
460 return new Range(0, -1, 0, -1);
463 public static Range decodeRange(String rangeOrCell, int row, int column) {
465 String[] parts = rangeOrCell.split(":");
466 if(parts.length == 1) {
468 return decodeCell(rangeOrCell, row, column);
470 } else if (parts.length == 2) {
472 Range from = decodeCell(parts[0].trim(), row, column);
473 // System.out.println("decodefrom=" + from);
474 Range to = decodeCell(parts[1].trim(), row, column);
475 // System.out.println("decodeto=" + to);
476 return Range.combine(from, to);
480 throw new CellParseException("The reference cell syntax was invalid. At most 1 occurrence of ':' is expected.");