bundles/org.simantics.spreadsheet.common/src/org/simantics/spreadsheet/util/SpreadsheetUtils.java

   1 /*******************************************************************************\r
   2  * Copyright (c) 2007, 2010 Association for Decentralized Information Management\r
   3  * in Industry THTH ry.\r
   4  * All rights reserved. This program and the accompanying materials\r
   5  * are made available under the terms of the Eclipse Public License v1.0\r
   6  * which accompanies this distribution, and is available at\r
   7  * http://www.eclipse.org/legal/epl-v10.html\r
   8  *\r
   9  * Contributors:\r
  10  *     VTT Technical Research Centre of Finland - initial API and implementation\r
  11  *******************************************************************************/\r
  12 package org.simantics.spreadsheet.util;\r
  13 \r
  14 import java.util.ArrayList;\r
  15 import java.util.Collection;\r
  16 import java.util.List;\r
  17 \r
  18 import org.apache.poi.ss.usermodel.DataFormatter;\r
  19 import org.simantics.Simantics;\r
  20 import org.simantics.databoard.Bindings;\r
  21 import org.simantics.databoard.binding.error.BindingException;\r
  22 import org.simantics.databoard.binding.mutable.MutableVariant;\r
  23 import org.simantics.databoard.binding.mutable.Variant;\r
  24 import org.simantics.datatypes.utils.BTree;\r
  25 import org.simantics.datatypes.utils.BTreeUtils;\r
  26 import org.simantics.db.ReadGraph;\r
  27 import org.simantics.db.Resource;\r
  28 import org.simantics.db.WriteGraph;\r
  29 import org.simantics.db.common.request.PossibleChild;\r
  30 import org.simantics.db.common.request.WriteRequest;\r
  31 import org.simantics.db.common.utils.Logger;\r
  32 import org.simantics.db.common.utils.NameUtils;\r
  33 import org.simantics.db.exception.DatabaseException;\r
  34 import org.simantics.db.layer0.exception.VariableException;\r
  35 import org.simantics.db.layer0.util.Layer0Utils;\r
  36 import org.simantics.db.layer0.variable.ProxyVariables;\r
  37 import org.simantics.db.layer0.variable.Variable;\r
  38 import org.simantics.db.layer0.variable.VariableSpaceManipulator.PropertyCreationData;\r
  39 import org.simantics.db.layer0.variable.Variables;\r
  40 import org.simantics.db.request.Write;\r
  41 import org.simantics.db.service.SerialisationSupport;\r
  42 import org.simantics.document.server.io.IColor;\r
  43 import org.simantics.document.server.io.IFont;\r
  44 import org.simantics.document.server.io.RGBColor;\r
  45 import org.simantics.document.server.io.SimpleFont;\r
  46 import org.simantics.layer0.Layer0;\r
  47 import org.simantics.scl.runtime.function.Function1;\r
  48 import org.simantics.scl.runtime.tuple.Tuple;\r
  49 import org.simantics.spreadsheet.CellEditor;\r
  50 import org.simantics.spreadsheet.CellEditor.Transaction;\r
  51 import org.simantics.spreadsheet.ClientModel.OperationMode;\r
  52 import org.simantics.spreadsheet.ClientModel;\r
  53 import org.simantics.spreadsheet.Range;\r
  54 import org.simantics.spreadsheet.common.TableCell;\r
  55 import org.simantics.spreadsheet.common.cell.StringCellParser;\r
  56 import org.simantics.spreadsheet.common.exception.CellParseException;\r
  57 import org.simantics.spreadsheet.resource.SpreadsheetResource;\r
  58 import org.simantics.utils.datastructures.Pair;\r
  59 \r
  60 public class SpreadsheetUtils {\r
  61     \r
  62     public static final int SPREADSHEET_BTREE_SIZE = 100;\r
  63         \r
  64         public static String offset(String location, int rowOffset, int columnOffset) {\r
  65 \r
  66                 Range range = decodeCellAbsolute(location);\r
  67                 String result = cellName(range.startRow + rowOffset, range.startColumn + columnOffset); \r
  68                 //      System.err.println("offset " + location + "(" + rowOffset + " " + columnOffset + ") = >" + result);\r
  69                 return result;\r
  70 \r
  71         }\r
  72 \r
  73         public static Object extract(Object object, int row, int column) {\r
  74                 if(object instanceof List) {\r
  75                         List list = (List)object;\r
  76                         if(list.size() <= row) return null;\r
  77                         Object item = list.get(row);\r
  78                         if(item instanceof Tuple) {\r
  79                                 Tuple tuple = (Tuple)item;\r
  80                                 if(tuple.length() <= column) return null;\r
  81                                 return tuple.get(column);\r
  82                         }\r
  83                 }\r
  84                 return null;\r
  85         }\r
  86 \r
  87         //      1 kirjain, 'A' + column\r
  88         //      2 kirjainta, 'A' + column % 26 , 'A' + int((column-26)/26)\r
  89         //      3 kirjainta 'A' + column % 26 , 'A' + int(column-(26*(26+1)) / 26) % 26\r
  90 \r
  91         //    0              26\r
  92         //    26             26 + 26*26\r
  93         //    26 + 26*26     26 + 26*26 + 26*26*26\r
  94 \r
  95         public static String columnName(int column, int current, int limit, int chars) {\r
  96 \r
  97                 if(column < limit) {\r
  98 \r
  99                         char[] buf = new char[chars];\r
 100                         column -= current;\r
 101                         for(int i=chars-1;i>=0;i--) {\r
 102                                 char rem = (char)(column % 26);\r
 103                                 column = (column / 26);\r
 104                                 buf[i] = (char)('A' + rem); \r
 105                         }\r
 106                         return new String(buf);\r
 107 \r
 108                 } else return columnName(column, limit, 26*(limit+1), chars+1);\r
 109 \r
 110         }\r
 111 \r
 112         public static String columnName(int column) {\r
 113                 return columnName(column, 0, 26, 1);\r
 114         }    \r
 115 \r
 116         public static String cellName(int row, int column) {\r
 117 \r
 118                 String result = columnName(column);\r
 119                 result += (row+1);\r
 120                 return result;\r
 121 \r
 122         }    \r
 123 \r
 124         public static Range decodeCellAbsolute(String identifier) {\r
 125                 long l = decodeCellCoded(identifier);\r
 126                 int row = (int)(l & 0xffffffff) - 1;\r
 127                 int column = (int)((l>>32) & 0xffffffff);\r
 128                 return new Range(row, row, column, column);\r
 129         }\r
 130 \r
 131         public static Range decodePossibleCellAbsolute(String identifier) {\r
 132             try {\r
 133                 return decodeCellAbsolute(identifier);\r
 134             } catch (CellParseException e) {\r
 135                 return null;\r
 136             }\r
 137         }\r
 138 \r
 139         public static long decodeCellCoded(String identifier) {\r
 140 \r
 141             //        System.out.println("decodecellabsolute " + identifier);\r
 142 \r
 143             int row = 0;\r
 144                 int column = 0;\r
 145                 \r
 146 //              identifier.\r
 147                 \r
 148                 int position = 0;\r
 149                 \r
 150                 // We skip $ here\r
 151                 if(identifier.charAt(position) == '$') position++;\r
 152                 \r
 153                 int length = identifier.length();\r
 154                 \r
 155                 while(position < length) {\r
 156                         char b = identifier.charAt(position);\r
 157                         if(b >= 'A' && b <= 'Z') column = column * 26 + (b-'A' + 1);\r
 158                         else break;\r
 159                         position++;\r
 160                 }\r
 161 \r
 162                 // We skip $ here\r
 163                 if(position < length)\r
 164                         if(identifier.charAt(position) == '$')\r
 165                                 position++;\r
 166 \r
 167                 while(position < length) {\r
 168                         char b = identifier.charAt(position);\r
 169                         if(b >= '0' && b <= '9'){\r
 170                                 row = row * 10 + b-'0';\r
 171                         }\r
 172                         else if(b=='-' && position < (length-1)){//identify use of full row range here.\r
 173                                 position++;\r
 174                                 char b2 = identifier.charAt(position);\r
 175                                 if(b2=='1'){\r
 176                                         row = 0;\r
 177                                         position++;\r
 178                                         break;\r
 179                                 }\r
 180                         }\r
 181                         else {\r
 182                                 break;\r
 183                         }\r
 184                         position++;\r
 185                 }\r
 186 \r
 187                 if(position == length) {\r
 188 \r
 189                         // We need to be able to express -1 in row => report row + 1 here\r
 190                         column--;\r
 191                         //            System.err.println("ra " + identifier + " => " + row + " " + column);\r
 192                         return row + (((long)column)<<32);\r
 193 \r
 194                 } else {\r
 195 \r
 196                         throw new CellParseException("Cell identifier '" + identifier + "' is not a valid cell reference.");\r
 197 \r
 198                 }\r
 199 \r
 200         }\r
 201 \r
 202         public static Range decodeCellRelative(String identifier, int row, int column) {\r
 203 \r
 204                 int offset = Integer.valueOf(identifier.substring(1).trim());\r
 205                 //        System.out.println("offset=" + offset);\r
 206 \r
 207                 if(identifier.startsWith("L") || identifier.startsWith("l")) {\r
 208                         return new Range(row, row, column-offset, column-offset);\r
 209                 } else if(identifier.startsWith("R") || identifier.startsWith("r")) {\r
 210                         return new Range(row, row, column+offset, column+offset);\r
 211                 } else if(identifier.startsWith("U") || identifier.startsWith("u")) {\r
 212                         return new Range(row-offset, row-offset, column, column);\r
 213                 } else if(identifier.startsWith("D") || identifier.startsWith("d")) {\r
 214                         return new Range(row+offset, row+offset, column, column);\r
 215                 } else {\r
 216                         throw new CellParseException("Relative cell syntax must begin with L|R|U|D.");\r
 217                 }\r
 218 \r
 219         }\r
 220 \r
 221         public static Range decodeCell(String identifier, int row, int column) {\r
 222 \r
 223                 if(identifier.startsWith("_")) {\r
 224                         return decodeCellRelative(identifier.substring(1), row, column);\r
 225                 } else {\r
 226                         return decodeCellAbsolute(identifier);\r
 227                 }\r
 228 \r
 229         }\r
 230 \r
 231         public static Range decodeReference(String identifier, int row, int column) {\r
 232                 if(!identifier.startsWith("&")) throw new CellParseException("A reference cell was expected.");\r
 233                 return decodeRange(identifier.substring(1), row, column);\r
 234         }\r
 235 \r
 236         public static List<Range> decodeRanges(String ranges){\r
 237                 String[] splitted = ranges.split(",");\r
 238                 List<Range> result = new ArrayList<>();\r
 239                 for(String split : splitted){\r
 240                         result.add(decodeRange(split));\r
 241                 }\r
 242                 return result;\r
 243         }\r
 244         \r
 245         public static int startRow(List<Range> ranges){\r
 246                 int s = -1;\r
 247                 for(Range r : ranges){\r
 248                         if(r.startRow<s || s==-1){\r
 249                                 s = r.startRow;\r
 250                         }\r
 251                 }\r
 252                 return s;\r
 253         }\r
 254         \r
 255         public static int startColumn(List<Range> ranges){\r
 256                 int s = -1;\r
 257                 for(Range r : ranges){\r
 258                         if(r.startColumn<s || s==-1){\r
 259                                 s = r.startColumn;\r
 260                         }\r
 261                 }\r
 262                 return s;\r
 263         }\r
 264         \r
 265         public static int amountOfRows(Range r){\r
 266                 int endRow = -2;\r
 267                 int startRow = -2;\r
 268                 if(r.isFullRows()){\r
 269                         return Range.MAXROWSPEC;\r
 270                 }\r
 271                 if(endRow == -2 && startRow == -2){\r
 272                         endRow = r.endRow;\r
 273                         startRow = r.startRow;\r
 274                 }\r
 275                 if(r.startRow<startRow){\r
 276                         startRow = r.startRow;\r
 277                 }\r
 278                 if(r.endRow>endRow){\r
 279                         endRow = r.endRow;\r
 280                 }\r
 281                 return endRow - startRow +1;\r
 282         }\r
 283         \r
 284         public static int amountOfColumns(Range r){\r
 285                 int endColumn = -2;\r
 286                 int startColumn = -2;\r
 287                 if(r.isFullColumns()){\r
 288                         return Range.MAXCOLUMNSPEC;\r
 289                 }\r
 290                 if(endColumn == -2 && startColumn == -2){\r
 291                         endColumn = r.endColumn; \r
 292                         startColumn = r.startColumn;\r
 293                 }\r
 294                 if(r.startColumn<startColumn){\r
 295                         startColumn = r.startColumn;\r
 296                 }\r
 297                 if(r.endColumn>endColumn){\r
 298                         endColumn = r.endColumn;\r
 299                 }\r
 300                 return endColumn - startColumn +1;\r
 301         }\r
 302         \r
 303         public static Range decodeRange(String rangeOrCell) {\r
 304                 if(rangeOrCell.isEmpty()) return fullRange();\r
 305                 return decodeRange(rangeOrCell, 0, 0);\r
 306         }\r
 307         \r
 308         public static Range fullRange() {\r
 309                 return new Range(0, -1, 0, -1);\r
 310         }\r
 311 \r
 312         public static Range decodeRange(String rangeOrCell, int row, int column) {\r
 313         \r
 314                 String[] parts = rangeOrCell.split(":");\r
 315                 if(parts.length == 1) {\r
 316 \r
 317                         return decodeCell(rangeOrCell, row, column);\r
 318 \r
 319                 } else if (parts.length == 2) {\r
 320 \r
 321                         Range from = decodeCell(parts[0].trim(), row, column);\r
 322                         //            System.out.println("decodefrom=" + from);\r
 323                         Range to = decodeCell(parts[1].trim(), row, column);\r
 324                         //            System.out.println("decodeto=" + to);\r
 325                         return Range.combine(from, to);\r
 326 \r
 327                 } else {\r
 328 \r
 329                         throw new CellParseException("The reference cell syntax was invalid. At most 1 occurrence of ':' is expected.");\r
 330 \r
 331                 }\r
 332 \r
 333         }\r
 334 \r
 335         public static Pair<String, Collection<PropertyCreationData>> parse(String text, StringCellParser[] parsers) {\r
 336 \r
 337                 try {\r
 338 \r
 339                         for(StringCellParser parser : parsers) { \r
 340                                 Collection<PropertyCreationData> parsed = parser.parse(text);\r
 341                                 if(parsed != null) return Pair.make(parser.getType(), parsed);\r
 342                         }\r
 343 \r
 344                 } catch (Throwable t) {\r
 345                         t.printStackTrace();\r
 346                 }\r
 347 \r
 348                 return null;\r
 349 \r
 350         }\r
 351 \r
 352         public static boolean isImmutable(Object object) {\r
 353                 return !(object instanceof Resource) && !(object instanceof Variable); \r
 354         }\r
 355 \r
 356         public static String getLabel(ReadGraph graph, Object object) throws DatabaseException {\r
 357 \r
 358                 if(object == null) {\r
 359                         return "no data";\r
 360                 }\r
 361 \r
 362                 if(object instanceof Resource) {\r
 363                         return NameUtils.getSafeName(graph, (Resource)object);\r
 364                 } else if (object instanceof Variable) {\r
 365                         try {\r
 366                                 Object value = ((Variable)object).getValue(graph);\r
 367                                 return value.toString();\r
 368                                 //return toString(value);\r
 369                         } catch (VariableException e) {\r
 370                                 Object value = ((Variable)object).getPropertyValue(graph, "Label"); \r
 371                                 return value.toString();\r
 372                         }\r
 373                 } else if (object instanceof double[]) {\r
 374                         return object.toString();\r
 375                         //                      return toString(object); \r
 376                 } else {\r
 377                         return object.toString();\r
 378                 }\r
 379 \r
 380         }\r
 381 \r
 382         private static String toString(Object object) {\r
 383                 if(object instanceof double[]) {\r
 384                         try {\r
 385                                 return Bindings.DOUBLE_ARRAY.toString(object);\r
 386                         } catch (BindingException e) {\r
 387                                 return object.toString();\r
 388                         }\r
 389                 } else {\r
 390                         return object.toString();\r
 391                 }\r
 392         }\r
 393 \r
 394         public static String getContent(ReadGraph graph, Object object) throws DatabaseException {\r
 395 \r
 396                 if(object == null) {\r
 397                         return null;\r
 398                 }\r
 399 \r
 400                 if(object instanceof Resource) {\r
 401                         SerialisationSupport support = graph.getService(SerialisationSupport.class);\r
 402                         return support.getResourceSerializer().createRandomAccessId((Resource)object);\r
 403                 } else if (object instanceof Variable) {\r
 404                         return ((Variable)object).getURI(graph);\r
 405                 } else {\r
 406                         return "";\r
 407                 }\r
 408 \r
 409         }\r
 410 \r
 411         public static void main(String[] args) {\r
 412                 for(int i=0;i<16384;i++) {\r
 413                         String name = columnName(i);\r
 414                         Range r = decodeCellAbsolute(name + "1");\r
 415                         System.err.println(i + " " + name + " " + r);\r
 416                 }\r
 417         }\r
 418 \r
 419         public static String getLabel(ClientModel model, int row, int column) {\r
 420                 try {\r
 421                         String location = SpreadsheetUtils.cellName(row, column);\r
 422                         String label = model.getPropertyAt(location, ClientModel.LABEL);\r
 423                         if(label != null) return label;\r
 424                         Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);\r
 425                         if(content != null) return SpreadsheetUtils.getContentString(content);\r
 426                         else return null;\r
 427                 } catch (Throwable e) {\r
 428                         e.printStackTrace();\r
 429                         return null;\r
 430                 }\r
 431         }\r
 432 \r
 433         public static String getContentString(Variant content) {\r
 434                 return content.getValue().toString();\r
 435         }\r
 436 \r
 437         public static boolean isInBounds(String base, String location, int wBounds, int hBounds) {\r
 438                 Range baseRange = decodeCellAbsolute(base);\r
 439                 Range locationRange = decodeCellAbsolute(location);\r
 440                 if(locationRange.startColumn < baseRange.startColumn) return false;\r
 441                 if(locationRange.startRow < baseRange.startRow) return false;\r
 442                 int wb = wBounds == -1 ? (Integer.MAX_VALUE / 3) : wBounds;\r
 443                 int hb = hBounds == -1 ? (Integer.MAX_VALUE / 3) : hBounds;\r
 444                 if(locationRange.startColumn > (baseRange.startColumn+wb-1)) return false;\r
 445                 if(locationRange.startRow > (baseRange.startRow+hb-1)) return false;\r
 446                 return true;\r
 447         }\r
 448 \r
 449         public static void schedule(CellEditor.Transaction<?> transaction, Write write) {\r
 450 \r
 451                 if(transaction == null) {\r
 452 \r
 453                         TransactionImpl impl = (TransactionImpl)startTransaction(OperationMode.OPERATION);\r
 454                         impl.add(write);\r
 455                         impl.commit();\r
 456 \r
 457                 } else {\r
 458 \r
 459                         TransactionImpl impl = (TransactionImpl)transaction;\r
 460                         impl.add(write);\r
 461 \r
 462                 }\r
 463 \r
 464         }\r
 465         \r
 466         public static Transaction<Write> startTransaction() {\r
 467                 return startTransaction(OperationMode.EDIT_MODE);\r
 468         }\r
 469 \r
 470         public static Transaction<Write> startTransaction(OperationMode mode) {\r
 471                 return new TransactionImpl(mode);\r
 472         }       \r
 473 \r
 474         static class TransactionImpl implements CellEditor.Transaction<Write> {\r
 475 \r
 476                 private ArrayList<Write> writes = new ArrayList<>();\r
 477                 private final OperationMode mode;\r
 478         private Object context;\r
 479         private List<Object> needSync;\r
 480                 \r
 481                 public TransactionImpl(OperationMode mode) {\r
 482                     this.mode = mode;\r
 483         }\r
 484 \r
 485                 public void commit() {\r
 486 \r
 487                         Simantics.async(new WriteRequest() {\r
 488 \r
 489                                 @Override\r
 490                                 public void perform(WriteGraph graph) throws DatabaseException {\r
 491                             graph.markUndoPoint();\r
 492                             for(int i=0;i<writes.size();i++) {\r
 493                                 Write write = writes.get(i);\r
 494                                 try {\r
 495                                     write.perform(graph);\r
 496                                 } catch (DatabaseException e) {\r
 497                                     e.printStackTrace();\r
 498                                     Logger.defaultLogError(e);\r
 499                                 }\r
 500                                 // This can schedule more writes\r
 501                                 //graph.syncRequest(write);\r
 502                             }\r
 503                             writes.clear();\r
 504                                 }\r
 505                         });\r
 506                 }\r
 507 \r
 508                 @Override\r
 509                 public void add(Write write) {\r
 510                         writes.add(write);\r
 511                 }\r
 512 \r
 513         @Override\r
 514         public boolean isOperationMode() {\r
 515             return mode.equals(OperationMode.OPERATION);\r
 516         }\r
 517 \r
 518         @Override\r
 519         public void setContext(Object context) {\r
 520             this.context = context;\r
 521         }\r
 522 \r
 523         @Override\r
 524         public Object getContext() {\r
 525             return context;\r
 526         }\r
 527 \r
 528         @Override\r
 529         public void needSynchronization(Object object) {\r
 530             if (needSync == null)\r
 531                 needSync = new ArrayList<>();\r
 532             needSync.add(object);\r
 533         }\r
 534 \r
 535         @Override\r
 536         public List<Object> needSynchronization() {\r
 537             return needSync;\r
 538         }\r
 539         }\r
 540         \r
 541         public static MutableVariant createVariant() {\r
 542                 return new MutableVariant();\r
 543         }\r
 544 \r
 545         public static Variant getSafeClientVariant(ClientModel clientModel, String location, String property) {\r
 546                 try {\r
 547                         return clientModel.getPossiblePropertyAt(location, property);\r
 548                 } catch (Throwable t) {\r
 549                         Logger.defaultLogError(t);\r
 550                         return Variant.ofInstance(t.getMessage());\r
 551                 }\r
 552         }\r
 553 \r
 554     public static Resource createSheet(WriteGraph graph, Resource book, String name) throws DatabaseException {\r
 555         \r
 556         return createSheet(graph, book, name, new String[] {}, new int[] {});\r
 557         \r
 558     }\r
 559 \r
 560         public static Resource createSheet(WriteGraph graph, Resource book, String name, String[] colNames, int[] colWidths) throws DatabaseException {\r
 561 \r
 562         Layer0 L0 = Layer0.getInstance(graph);\r
 563         SpreadsheetResource sr = SpreadsheetResource.getInstance(graph);\r
 564 \r
 565         Resource result = graph.newResource();\r
 566         graph.claim(result, L0.InstanceOf, null, sr.Spreadsheet);\r
 567 \r
 568         if(name == null) {\r
 569             name = NameUtils.findFreshEscapedName(graph, "Sheet", book, L0.ConsistsOf);\r
 570         }\r
 571         graph.claimLiteral(result, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING);\r
 572         graph.claim(book, L0.ConsistsOf, L0.PartOf, result);\r
 573 \r
 574 //            Resource newCell = graph.newResource();\r
 575 //            graph.claim(newCell, L0.InstanceOf, null, sr.Lines);\r
 576 //            graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);\r
 577 //            graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);\r
 578 //            BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);\r
 579         \r
 580         BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, sr.Lines, sr.LineNode, L0.PartOf, false);\r
 581 //        BTree bt = BTreeUtils.create(graph, sr.Lines, sr.LineNode, L0.PartOf, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, false);\r
 582         Resource lines = bt.rootOfBTree();\r
 583         \r
 584         graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);\r
 585         graph.claim(result, L0.ConsistsOf, L0.PartOf, lines);\r
 586         \r
 587         {\r
 588             Resource newCell = graph.newResource();\r
 589             graph.claim(newCell, L0.InstanceOf, null, sr.Dimensions);\r
 590             graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);\r
 591             graph.addLiteral(newCell, sr.Dimensions_fitColumns, sr.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);\r
 592             graph.addLiteral(newCell, sr.Dimensions_fitRows, sr.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);\r
 593             graph.addLiteral(newCell, sr.Dimensions_columnCount, sr.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);\r
 594             graph.addLiteral(newCell, sr.Dimensions_rowCount, sr.Dimensions_rowCount_Inverse, L0.Integer, 256, Bindings.INTEGER);\r
 595             graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);\r
 596         }\r
 597 \r
 598         {\r
 599             Resource newCell = graph.newResource();\r
 600             graph.claim(newCell, L0.InstanceOf, null, sr.Headers);\r
 601             graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);\r
 602             graph.addLiteral(newCell, sr.Headers_columnLabels, sr.Headers_columnLabels_Inverse, L0.StringArray, colNames, Bindings.STRING_ARRAY);\r
 603             graph.addLiteral(newCell, sr.Headers_columnWidths, sr.Headers_columnWidths_Inverse, L0.IntegerArray, colWidths, Bindings.INT_ARRAY);\r
 604             graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);\r
 605         }\r
 606 \r
 607         return result;\r
 608 \r
 609     }\r
 610     \r
 611 \r
 612     public static Variable getBookVariable(ReadGraph graph, Resource book) throws DatabaseException {\r
 613         Variable variable = Variables.getVariable(graph, book);\r
 614         return ProxyVariables.makeProxyVariable(graph, variable, variable);\r
 615     }\r
 616 \r
 617     public static Variable sheetRun(ReadGraph graph, Resource book, Variable context) throws DatabaseException {\r
 618         Variable root = Variables.getVariable(graph, book);\r
 619         return ProxyVariables.makeProxyVariable(graph, root, context);\r
 620     }\r
 621 \r
 622     private static TableCell constructCell(int row, int column, Object data) {\r
 623                 TableCell cell = new TableCell();\r
 624                 cell.row = row;\r
 625                 cell.column = column;\r
 626                 cell.text = data.toString();\r
 627                 return cell;\r
 628     }\r
 629     \r
 630     public static List<TableCell> queryCells(Object data) {\r
 631         ArrayList<TableCell> result = new ArrayList<TableCell>();\r
 632         if(data instanceof List) {\r
 633                 List<?> list = (List<?>)data;\r
 634                 int row = 0;\r
 635                 for(Object o : list) {\r
 636                         if(o instanceof Tuple) {\r
 637                                 Tuple t = (Tuple)o;\r
 638                                 for(int i=0;i<t.length();i++) {\r
 639                                         result.add(constructCell(row, i, t.get(i)));\r
 640                                 }\r
 641                         } else if (o instanceof List) {\r
 642                                 List<?> rowList = (List<?>)o;\r
 643                                 int index = 0;\r
 644                                 for(Object obj : rowList) {\r
 645                                         result.add(constructCell(row, index++, obj));\r
 646                                 }\r
 647                         } else {\r
 648                                         result.add(constructCell(row, 0, o));\r
 649                         }\r
 650                         row++;\r
 651                 }\r
 652         }\r
 653         return result;\r
 654     }\r
 655     \r
 656     public static List<TableCell> organizeCells(int columns, List<String> headers_, List<TableCell> cells) throws DatabaseException {\r
 657         \r
 658         ArrayList<TableCell> result = new ArrayList<TableCell>();\r
 659         \r
 660         int w = 0; // name + fields \r
 661         int h = 0; // number or rows excluding headers\r
 662         \r
 663         if(columns < 2) throw new DatabaseException("organizeCells: number of columns needs to be greater than 1");\r
 664         \r
 665         for(TableCell cell : cells) {\r
 666                 if((cell.column+1)>w) w = cell.column+1;\r
 667                 if((cell.row)>h) h = cell.row;\r
 668         }\r
 669         \r
 670         int fields = w - 1;\r
 671         \r
 672         if(columns > (fields + 1)) columns = fields + 1;//throw new DatabaseException("organizeCells: number of block columns cannot be greater than the amount of columns in data");\r
 673         \r
 674         int fieldsPerRow = columns - 1;\r
 675         \r
 676         int blocks = fields / fieldsPerRow;\r
 677         if(fields%fieldsPerRow > 0) blocks++;\r
 678 \r
 679         TableCell[] names = new TableCell[h];\r
 680         TableCell[] headers = new TableCell[w];\r
 681         \r
 682         for(TableCell cell : cells) {\r
 683                 \r
 684                 if(cell.row == 0) {\r
 685                         headers[cell.column] = cell;\r
 686                 } else if(cell.column == 0) {\r
 687                         names[cell.row-1] = cell;\r
 688                 } else {\r
 689                         TableCell copy = new TableCell(cell);\r
 690                         int block = (copy.column-1) / fieldsPerRow;\r
 691                         copy.row = block*(h+1) + copy.row;\r
 692                         copy.column = 1 + (copy.column-1) % fieldsPerRow;\r
 693                         result.add(copy);\r
 694                 }\r
 695                 \r
 696         }\r
 697         \r
 698                 for(int j=0;j<blocks;j++) {\r
 699 \r
 700                         int rowBase = j*(h+1);\r
 701                         \r
 702                         for(int i=0;i<h;i++) {\r
 703                         TableCell copy = new TableCell(names[i]);\r
 704                         copy.row = rowBase + copy.row;\r
 705                         result.add(copy);\r
 706                 }\r
 707                         \r
 708                         TableCell legend = new TableCell(headers[0]);\r
 709                         legend.row = rowBase;\r
 710                         result.add(legend);\r
 711 \r
 712                         for(int i=1;i<columns;i++) {\r
 713 \r
 714                                 int index = (j*fieldsPerRow) + i;\r
 715                                 if(index >= w) continue;\r
 716                                 \r
 717                                 TableCell header = new TableCell(headers[index]);\r
 718                                 header.row = rowBase;\r
 719                                 header.column = i;\r
 720                                 result.add(header);\r
 721                                 \r
 722                 }\r
 723                         \r
 724         }\r
 725         \r
 726         return result;\r
 727         \r
 728     }\r
 729     \r
 730     public static List<TableCell> modifyCells1(List<TableCell> cells, Function1<TableCell, TableCell> fn) {\r
 731         ArrayList<TableCell> result = new ArrayList<TableCell>();\r
 732         for(TableCell cell : cells)\r
 733                 result.add(fn.apply(cell));\r
 734         return result;\r
 735     }\r
 736 \r
 737     public static List<TableCell> modifyCells(List<TableCell> cells, List<Function1<TableCell, TableCell>> fns) {\r
 738         ArrayList<TableCell> result = new ArrayList<TableCell>();\r
 739         for(TableCell cell : cells) {\r
 740                 for(Function1<TableCell,TableCell> fn : fns)\r
 741                         cell = fn.apply(cell);\r
 742                 result.add(cell);\r
 743         }\r
 744         return result;\r
 745     }\r
 746 \r
 747     public static TableCell applyFont(IFont font, Function1<TableCell,Boolean> filter, TableCell cell) {\r
 748         if(!filter.apply(cell)) return cell;\r
 749         TableCell result = new TableCell(cell);\r
 750         result.font = font;\r
 751         return result;\r
 752     }\r
 753 \r
 754     public static TableCell applyAlign(int align, Function1<TableCell,Boolean> filter, TableCell cell) {\r
 755         if(!filter.apply(cell)) return cell;\r
 756         TableCell result = new TableCell(cell);\r
 757         result.align = align;\r
 758         return result;\r
 759     }\r
 760 \r
 761     public static TableCell applyForeground(IColor color, Function1<TableCell,Boolean> filter, TableCell cell) {\r
 762         if(!filter.apply(cell)) return cell;\r
 763         TableCell result = new TableCell(cell);\r
 764         result.foreground = color;\r
 765         return result;\r
 766     }\r
 767 \r
 768     public static TableCell applyBackground(IColor color,Function1<TableCell,Boolean> filter,  TableCell cell) {\r
 769         if(!filter.apply(cell)) return cell;\r
 770         TableCell result = new TableCell(cell);\r
 771         result.background = color;\r
 772         return result;\r
 773     }\r
 774 \r
 775     public static IFont simpleFont(String family, String style, int height) {\r
 776         return new SimpleFont(family, style, height);\r
 777     }\r
 778 \r
 779     public static IColor rgbColor(int r, int g, int b) {\r
 780         return new RGBColor(r, g, b);\r
 781     }\r
 782 \r
 783     public static boolean selectRow(int row, TableCell cell) {\r
 784         return cell.row == row;\r
 785     }\r
 786 \r
 787     public static boolean selectColumn(int column, TableCell cell) {\r
 788         return cell.column == column;\r
 789     }\r
 790 \r
 791     public static void setSCLLine(WriteGraph graph, Resource spreadsheet, int row, String expression) throws DatabaseException {\r
 792 \r
 793         Layer0 L0 = Layer0.getInstance(graph);\r
 794         Resource lines = graph.syncRequest(new PossibleChild(spreadsheet, "Lines"));\r
 795         BTree bt = new BTree(graph, lines);\r
 796         SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);\r
 797         \r
 798         Resource line = graph.newResource();\r
 799         graph.claim(line, L0.InstanceOf, SR.Line);\r
 800         graph.addLiteral(line, L0.HasName, L0.NameOf, "" + row, Bindings.STRING);\r
 801         Layer0Utils.setExpression(graph, line, SR.Line_content, null, "[spreadsheetCell ]", L0.SCLValue);\r
 802         bt.insertBTree(graph, Variant.ofInstance(row), line);\r
 803         \r
 804     }\r
 805 \r
 806     public static String getFormattedLabel(ClientModel model, int row, int column, int formatIndex, String formatString) {\r
 807         if (formatString == null)\r
 808             return getLabel(model, row, column); \r
 809         try {\r
 810             String location = SpreadsheetUtils.cellName(row, column);\r
 811             Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);\r
 812             if(content != null) {\r
 813                 \r
 814                 String contentString = SpreadsheetUtils.getContentString(content);\r
 815                 if(contentString.equals("~CIRCULAR~REF~"))\r
 816                         return "0";\r
 817                 \r
 818                 double value = Double.valueOf(contentString);\r
 819                 if (Double.isNaN(value))\r
 820                         return getLabel(model, row, column);\r
 821                 \r
 822                 DataFormatter formatter = new DataFormatter();\r
 823                 return formatter.formatRawCellContents(value, formatIndex, formatString);\r
 824             }\r
 825             return null;\r
 826         } catch (NumberFormatException e) {\r
 827             return getLabel(model, row, column);\r
 828         } catch (Throwable e) {\r
 829             e.printStackTrace();\r
 830             return null;\r
 831         }\r
 832     }\r
 833     \r
 834 }\r