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

   1 /*******************************************************************************
   2  * Copyright (c) 2007, 2010 Association for Decentralized Information Management
   3  * in Industry THTH ry.
   4  * All rights reserved. This program and the accompanying materials
   5  * are made available under the terms of the Eclipse Public License v1.0
   6  * which accompanies this distribution, and is available at
   7  * http://www.eclipse.org/legal/epl-v10.html
   8  *
   9  * Contributors:
  10  *     VTT Technical Research Centre of Finland - initial API and implementation
  11  *******************************************************************************/
  12 package org.simantics.spreadsheet.util;
  13
  14 import java.util.ArrayList;
  15 import java.util.Collection;
  16 import java.util.List;
  17
  18 import org.apache.poi.ss.usermodel.DataFormatter;
  19 import org.simantics.Simantics;
  20 import org.simantics.databoard.Bindings;
  21 import org.simantics.databoard.binding.error.BindingException;
  22 import org.simantics.databoard.binding.mutable.MutableVariant;
  23 import org.simantics.databoard.binding.mutable.Variant;
  24 import org.simantics.datatypes.utils.BTree;
  25 import org.simantics.db.ReadGraph;
  26 import org.simantics.db.Resource;
  27 import org.simantics.db.WriteGraph;
  28 import org.simantics.db.common.request.PossibleChild;
  29 import org.simantics.db.common.request.WriteRequest;
  30 import org.simantics.db.common.utils.Logger;
  31 import org.simantics.db.common.utils.NameUtils;
  32 import org.simantics.db.exception.AssumptionException;
  33 import org.simantics.db.exception.DatabaseException;
  34 import org.simantics.db.exception.VariableException;
  35 import org.simantics.db.layer0.util.Layer0Utils;
  36 import org.simantics.db.layer0.variable.ProxyVariables;
  37 import org.simantics.db.layer0.variable.Variable;
  38 import org.simantics.db.layer0.variable.VariableSpaceManipulator.PropertyCreationData;
  39 import org.simantics.db.layer0.variable.Variables;
  40 import org.simantics.db.request.Write;
  41 import org.simantics.db.service.SerialisationSupport;
  42 import org.simantics.document.server.io.IColor;
  43 import org.simantics.document.server.io.IFont;
  44 import org.simantics.document.server.io.RGBColor;
  45 import org.simantics.document.server.io.SimpleFont;
  46 import org.simantics.layer0.Layer0;
  47 import org.simantics.scl.runtime.function.Function1;
  48 import org.simantics.scl.runtime.tuple.Tuple;
  49 import org.simantics.spreadsheet.ClientModel;
  50 import org.simantics.spreadsheet.OperationMode;
  51 import org.simantics.spreadsheet.Range;
  52 import org.simantics.spreadsheet.Spreadsheets;
  53 import org.simantics.spreadsheet.Transaction;
  54 import org.simantics.spreadsheet.common.TableCell;
  55 import org.simantics.spreadsheet.common.cell.StringCellParser;
  56 import org.simantics.spreadsheet.resource.SpreadsheetResource;
  57 import org.simantics.utils.datastructures.Pair;
  58
  59 public class SpreadsheetUtils {
  60
  61         public static Pair<String, Collection<PropertyCreationData>> parse(String text, StringCellParser[] parsers) {
  62
  63                 try {
  64
  65                         for(StringCellParser parser : parsers) {
  66                                 Collection<PropertyCreationData> parsed = parser.parse(text);
  67                                 if(parsed != null) return Pair.make(parser.getType(), parsed);
  68                         }
  69
  70                 } catch (Throwable t) {
  71                         t.printStackTrace();
  72                 }
  73
  74                 return null;
  75
  76         }
  77
  78         public static boolean isImmutable(Object object) {
  79                 return !(object instanceof Resource) && !(object instanceof Variable);
  80         }
  81
  82         public static String getLabel(ReadGraph graph, Object object) throws DatabaseException {
  83
  84                 if(object == null) {
  85                         return "no data";
  86                 }
  87
  88                 if(object instanceof Resource) {
  89                         return NameUtils.getSafeName(graph, (Resource)object);
  90                 } else if (object instanceof Variable) {
  91                         try {
  92                                 Object value = ((Variable)object).getValue(graph);
  93                                 return value.toString();
  94                                 //return toString(value);
  95                         } catch (VariableException e) {
  96                                 Object value = ((Variable)object).getPropertyValue(graph, "Label");
  97                                 return value.toString();
  98                         }
  99                 } else if (object instanceof double[]) {
 100                         return object.toString();
 101                         //                      return toString(object);
 102                 } else {
 103                         return object.toString();
 104                 }
 105
 106         }
 107
 108         private static String toString(Object object) {
 109                 if(object instanceof double[]) {
 110                         try {
 111                                 return Bindings.DOUBLE_ARRAY.toString(object);
 112                         } catch (BindingException e) {
 113                                 return object.toString();
 114                         }
 115                 } else {
 116                         return object.toString();
 117                 }
 118         }
 119
 120         public static String getContent(ReadGraph graph, Object object) throws DatabaseException {
 121
 122                 if(object == null) {
 123                         return null;
 124                 }
 125
 126                 if(object instanceof Resource) {
 127                         SerialisationSupport support = graph.getService(SerialisationSupport.class);
 128                         return support.getResourceSerializer().createRandomAccessId((Resource)object);
 129                 } else if (object instanceof Variable) {
 130                         return ((Variable)object).getURI(graph);
 131                 } else {
 132                         return "";
 133                 }
 134
 135         }
 136
 137         public static void main(String[] args) {
 138                 for(int i=0;i<16384;i++) {
 139                         String name = Spreadsheets.columnName(i);
 140                         Range r = Spreadsheets.decodeCellAbsolute(name + "1");
 141                         System.err.println(i + " " + name + " " + r);
 142                 }
 143         }
 144
 145         public static String getLabel(ClientModel model, int row, int column) {
 146                 try {
 147                         String location = Spreadsheets.cellName(row, column);
 148                         String label = model.getPropertyAt(location, ClientModel.LABEL);
 149                         if(label != null) return label;
 150                         Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);
 151                         if(content != null) return SpreadsheetUtils.getContentString(content);
 152                         else return null;
 153                 } catch (Throwable e) {
 154                         e.printStackTrace();
 155                         return null;
 156                 }
 157         }
 158
 159         public static String getContentString(Variant content) {
 160                 return content.getValue().toString();
 161         }
 162
 163         public static boolean isInBounds(String base, String location, int wBounds, int hBounds) {
 164                 Range baseRange = Spreadsheets.decodeCellAbsolute(base);
 165                 Range locationRange = Spreadsheets.decodeCellAbsolute(location);
 166                 if(locationRange.startColumn < baseRange.startColumn) return false;
 167                 if(locationRange.startRow < baseRange.startRow) return false;
 168                 int wb = wBounds == -1 ? (Integer.MAX_VALUE / 3) : wBounds;
 169                 int hb = hBounds == -1 ? (Integer.MAX_VALUE / 3) : hBounds;
 170                 if(locationRange.startColumn > (baseRange.startColumn+wb-1)) return false;
 171                 if(locationRange.startRow > (baseRange.startRow+hb-1)) return false;
 172                 return true;
 173         }
 174
 175         public static void schedule(Transaction<?> transaction, Write write) {
 176
 177                 if(transaction == null) {
 178
 179                         TransactionImpl impl = (TransactionImpl)startTransaction(OperationMode.OPERATION);
 180                         impl.add(write);
 181                         impl.commit();
 182
 183                 } else {
 184
 185                         TransactionImpl impl = (TransactionImpl)transaction;
 186                         impl.add(write);
 187
 188                 }
 189
 190         }
 191
 192         public static Transaction<Write> startTransaction() {
 193                 return startTransaction(OperationMode.EDIT_MODE);
 194         }
 195
 196         public static Transaction<Write> startTransaction(OperationMode mode) {
 197                 return new TransactionImpl(mode);
 198         }
 199
 200         static class TransactionImpl implements Transaction<Write> {
 201
 202                 private ArrayList<Write> writes = new ArrayList<>();
 203                 private final OperationMode mode;
 204         private Object context;
 205         private List<Object> needSync;
 206
 207                 public TransactionImpl(OperationMode mode) {
 208                     this.mode = mode;
 209         }
 210
 211                 public void commit() {
 212
 213                         Simantics.async(new WriteRequest() {
 214
 215                                 @Override
 216                                 public void perform(WriteGraph graph) throws DatabaseException {
 217                             graph.markUndoPoint();
 218                             for(int i=0;i<writes.size();i++) {
 219                                 Write write = writes.get(i);
 220                                 try {
 221                                     write.perform(graph);
 222                                 } catch (DatabaseException e) {
 223                                     e.printStackTrace();
 224                                     Logger.defaultLogError(e);
 225                                 }
 226                                 // This can schedule more writes
 227                                 //graph.syncRequest(write);
 228                             }
 229                             writes.clear();
 230                                 }
 231                         });
 232                 }
 233
 234                 @Override
 235                 public void add(Write write) {
 236                         writes.add(write);
 237                 }
 238
 239         @Override
 240         public boolean isOperationMode() {
 241             return mode.equals(OperationMode.OPERATION);
 242         }
 243
 244         @Override
 245         public void setContext(Object context) {
 246             this.context = context;
 247         }
 248
 249         @Override
 250         public Object getContext() {
 251             return context;
 252         }
 253
 254         @Override
 255         public void needSynchronization(Object object) {
 256             if (needSync == null)
 257                 needSync = new ArrayList<>();
 258             needSync.add(object);
 259         }
 260
 261         @Override
 262         public List<Object> needSynchronization() {
 263             return needSync;
 264         }
 265         }
 266
 267         public static MutableVariant createVariant() {
 268                 return new MutableVariant();
 269         }
 270
 271         public static Variant getSafeClientVariant(ClientModel clientModel, String location, String property) {
 272                 try {
 273                         return clientModel.getPossiblePropertyAt(location, property);
 274                 } catch (Throwable t) {
 275                         Logger.defaultLogError(t);
 276                         return Variant.ofInstance(t.getMessage());
 277                 }
 278         }
 279
 280     public static Resource createSheet(WriteGraph graph, Resource book, String name) throws DatabaseException {
 281
 282         return createSheet(graph, book, name, new String[] {}, new int[] {});
 283
 284     }
 285
 286         public static Resource createSheet(WriteGraph graph, Resource book, String name, String[] colNames, int[] colWidths) throws DatabaseException {
 287
 288         Layer0 L0 = Layer0.getInstance(graph);
 289         SpreadsheetResource sr = SpreadsheetResource.getInstance(graph);
 290
 291         Resource result = graph.newResource();
 292         graph.claim(result, L0.InstanceOf, null, sr.Spreadsheet);
 293
 294         if(name == null) {
 295             name = NameUtils.findFreshEscapedName(graph, "Sheet", book, L0.ConsistsOf);
 296         }
 297         graph.claimLiteral(result, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING);
 298         graph.claim(book, L0.ConsistsOf, L0.PartOf, result);
 299
 300 //            Resource newCell = graph.newResource();
 301 //            graph.claim(newCell, L0.InstanceOf, null, sr.Lines);
 302 //            graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
 303 //            graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
 304 //            BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);
 305
 306         BTree bt = new BTree(graph, Spreadsheets.SPREADSHEET_BTREE_SIZE, sr.Lines, sr.LineNode, L0.PartOf, false);
 307 //        BTree bt = BTreeUtils.create(graph, sr.Lines, sr.LineNode, L0.PartOf, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, false);
 308         Resource lines = bt.rootOfBTree();
 309
 310         graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
 311         graph.claim(result, L0.ConsistsOf, L0.PartOf, lines);
 312
 313         {
 314             Resource newCell = graph.newResource();
 315             graph.claim(newCell, L0.InstanceOf, null, sr.Dimensions);
 316             graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);
 317             graph.addLiteral(newCell, sr.Dimensions_fitColumns, sr.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
 318             graph.addLiteral(newCell, sr.Dimensions_fitRows, sr.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
 319             graph.addLiteral(newCell, sr.Dimensions_columnCount, sr.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);
 320             graph.addLiteral(newCell, sr.Dimensions_rowCount, sr.Dimensions_rowCount_Inverse, L0.Integer, 256, Bindings.INTEGER);
 321             graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
 322         }
 323
 324         {
 325             Resource newCell = graph.newResource();
 326             graph.claim(newCell, L0.InstanceOf, null, sr.Headers);
 327             graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);
 328             graph.addLiteral(newCell, sr.Headers_columnLabels, sr.Headers_columnLabels_Inverse, L0.StringArray, colNames, Bindings.STRING_ARRAY);
 329             graph.addLiteral(newCell, sr.Headers_columnWidths, sr.Headers_columnWidths_Inverse, L0.IntegerArray, colWidths, Bindings.INT_ARRAY);
 330             graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
 331         }
 332
 333         return result;
 334
 335     }
 336
 337
 338     public static Variable getBookVariable(ReadGraph graph, Resource book) throws DatabaseException {
 339         Variable variable = Variables.getVariable(graph, book);
 340         return ProxyVariables.makeProxyVariable(graph, variable, variable);
 341     }
 342
 343     public static Variable sheetRun(ReadGraph graph, Resource book, Variable context) throws DatabaseException {
 344         Variable root = Variables.getVariable(graph, book);
 345         return ProxyVariables.makeProxyVariable(graph, root, context);
 346     }
 347
 348     private static TableCell constructCell(int row, int column, Object data) {
 349                 TableCell cell = new TableCell();
 350                 cell.row = row;
 351                 cell.column = column;
 352                 cell.text = data.toString();
 353                 return cell;
 354     }
 355
 356     public static List<TableCell> queryCells(Object data) {
 357         ArrayList<TableCell> result = new ArrayList<TableCell>();
 358         if(data instanceof List) {
 359                 List<?> list = (List<?>)data;
 360                 int row = 0;
 361                 for(Object o : list) {
 362                         if(o instanceof Tuple) {
 363                                 Tuple t = (Tuple)o;
 364                                 for(int i=0;i<t.length();i++) {
 365                                         result.add(constructCell(row, i, t.get(i)));
 366                                 }
 367                         } else if (o instanceof List) {
 368                                 List<?> rowList = (List<?>)o;
 369                                 int index = 0;
 370                                 for(Object obj : rowList) {
 371                                         result.add(constructCell(row, index++, obj));
 372                                 }
 373                         } else {
 374                                         result.add(constructCell(row, 0, o));
 375                         }
 376                         row++;
 377                 }
 378         }
 379         return result;
 380     }
 381
 382     public static List<TableCell> organizeCells(int columns, List<String> headers_, List<TableCell> cells) throws DatabaseException {
 383
 384         ArrayList<TableCell> result = new ArrayList<TableCell>();
 385
 386         int w = 0; // name + fields
 387         int h = 0; // number or rows excluding headers
 388
 389         if(columns < 2) throw new AssumptionException("organizeCells: number of columns needs to be greater than 1");
 390
 391         for(TableCell cell : cells) {
 392                 if((cell.column+1)>w) w = cell.column+1;
 393                 if((cell.row)>h) h = cell.row;
 394         }
 395
 396         int fields = w - 1;
 397
 398         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");
 399
 400         int fieldsPerRow = columns - 1;
 401
 402         int blocks = fields / fieldsPerRow;
 403         if(fields%fieldsPerRow > 0) blocks++;
 404
 405         TableCell[] names = new TableCell[h];
 406         TableCell[] headers = new TableCell[w];
 407
 408         for(TableCell cell : cells) {
 409
 410                 if(cell.row == 0) {
 411                         headers[cell.column] = cell;
 412                 } else if(cell.column == 0) {
 413                         names[cell.row-1] = cell;
 414                 } else {
 415                         TableCell copy = new TableCell(cell);
 416                         int block = (copy.column-1) / fieldsPerRow;
 417                         copy.row = block*(h+1) + copy.row;
 418                         copy.column = 1 + (copy.column-1) % fieldsPerRow;
 419                         result.add(copy);
 420                 }
 421
 422         }
 423
 424                 for(int j=0;j<blocks;j++) {
 425
 426                         int rowBase = j*(h+1);
 427
 428                         for(int i=0;i<h;i++) {
 429                         TableCell copy = new TableCell(names[i]);
 430                         copy.row = rowBase + copy.row;
 431                         result.add(copy);
 432                 }
 433
 434                         TableCell legend = new TableCell(headers[0]);
 435                         legend.row = rowBase;
 436                         result.add(legend);
 437
 438                         for(int i=1;i<columns;i++) {
 439
 440                                 int index = (j*fieldsPerRow) + i;
 441                                 if(index >= w) continue;
 442
 443                                 TableCell header = new TableCell(headers[index]);
 444                                 header.row = rowBase;
 445                                 header.column = i;
 446                                 result.add(header);
 447
 448                 }
 449
 450         }
 451
 452         return result;
 453
 454     }
 455
 456     public static List<TableCell> modifyCells1(List<TableCell> cells, Function1<TableCell, TableCell> fn) {
 457         ArrayList<TableCell> result = new ArrayList<TableCell>();
 458         for(TableCell cell : cells)
 459                 result.add(fn.apply(cell));
 460         return result;
 461     }
 462
 463     public static List<TableCell> modifyCells(List<TableCell> cells, List<Function1<TableCell, TableCell>> fns) {
 464         ArrayList<TableCell> result = new ArrayList<TableCell>();
 465         for(TableCell cell : cells) {
 466                 for(Function1<TableCell,TableCell> fn : fns)
 467                         cell = fn.apply(cell);
 468                 result.add(cell);
 469         }
 470         return result;
 471     }
 472
 473     public static TableCell applyFont(IFont font, Function1<TableCell,Boolean> filter, TableCell cell) {
 474         if(!filter.apply(cell)) return cell;
 475         TableCell result = new TableCell(cell);
 476         result.font = font;
 477         return result;
 478     }
 479
 480     public static TableCell applyAlign(int align, Function1<TableCell,Boolean> filter, TableCell cell) {
 481         if(!filter.apply(cell)) return cell;
 482         TableCell result = new TableCell(cell);
 483         result.align = align;
 484         return result;
 485     }
 486
 487     public static TableCell applyForeground(IColor color, Function1<TableCell,Boolean> filter, TableCell cell) {
 488         if(!filter.apply(cell)) return cell;
 489         TableCell result = new TableCell(cell);
 490         result.foreground = color;
 491         return result;
 492     }
 493
 494     public static TableCell applyBackground(IColor color,Function1<TableCell,Boolean> filter,  TableCell cell) {
 495         if(!filter.apply(cell)) return cell;
 496         TableCell result = new TableCell(cell);
 497         result.background = color;
 498         return result;
 499     }
 500
 501     public static IFont simpleFont(String family, String style, int height) {
 502         return new SimpleFont(family, style, height);
 503     }
 504
 505     public static IColor rgbColor(int r, int g, int b) {
 506         return new RGBColor(r, g, b);
 507     }
 508
 509     public static boolean selectRow(int row, TableCell cell) {
 510         return cell.row == row;
 511     }
 512
 513     public static boolean selectColumn(int column, TableCell cell) {
 514         return cell.column == column;
 515     }
 516
 517     public static void setSCLLine(WriteGraph graph, Resource spreadsheet, int row, String expression) throws DatabaseException {
 518
 519         Layer0 L0 = Layer0.getInstance(graph);
 520         Resource lines = graph.syncRequest(new PossibleChild(spreadsheet, "Lines"));
 521         BTree bt = new BTree(graph, lines);
 522         SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
 523
 524         Resource line = graph.newResource();
 525         graph.claim(line, L0.InstanceOf, SR.Line);
 526         graph.addLiteral(line, L0.HasName, L0.NameOf, "" + row, Bindings.STRING);
 527         Layer0Utils.setExpression(graph, line, SR.Line_content, null, "[spreadsheetCell ]", L0.SCLValue);
 528         bt.insertBTree(graph, Variant.ofInstance(row), line);
 529
 530     }
 531
 532     public static String getFormattedLabel(ClientModel model, int row, int column, int formatIndex, String formatString) {
 533         if (formatString == null)
 534             return getLabel(model, row, column);
 535         try {
 536             String location = Spreadsheets.cellName(row, column);
 537             Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);
 538             if(content != null) {
 539
 540                 String contentString = SpreadsheetUtils.getContentString(content);
 541                 if(contentString.equals("~CIRCULAR~REF~"))
 542                         return "0";
 543
 544                 double value = Double.valueOf(contentString);
 545                 if (Double.isNaN(value))
 546                         return getLabel(model, row, column);
 547
 548                 DataFormatter formatter = new DataFormatter();
 549                 return formatter.formatRawCellContents(value, formatIndex, formatString);
 550             }
 551             return null;
 552         } catch (NumberFormatException e) {
 553             return getLabel(model, row, column);
 554         } catch (Throwable e) {
 555             e.printStackTrace();
 556             return null;
 557         }
 558     }
 559
 560     /*
 561      *  Please use Spreadsheets.cellName instead
 562      */
 563     @Deprecated
 564     public static String cellName(int row, int column) {
 565         return Spreadsheets.cellName(row, column);
 566     }
 567
 568     /*
 569      *  Please use Spreadsheets.columnName instead
 570      */
 571     @Deprecated
 572     public static String columnName(int column) {
 573         return Spreadsheets.columnName(column);
 574     }
 575
 576     /*
 577      *  Please use Spreadsheets.decodeCellAbsolute instead
 578      */
 579     @Deprecated
 580     public static Range decodeCellAbsolute(String identifier) {
 581         return Spreadsheets.decodeCellAbsolute(identifier);
 582     }
 583
 584     /*
 585      *  Please use Spreadsheets.decodePossibleCellAbsolute instead
 586      */
 587     @Deprecated
 588     public static Range decodePossibleCellAbsolute(String identifier) {
 589         return Spreadsheets.decodePossibleCellAbsolute(identifier);
 590     }
 591
 592     /*
 593      *  Please use Spreadsheets.decodeRange instead
 594      */
 595     @Deprecated
 596     public static Range decodeRange(String rangeOrCell) {
 597         return Spreadsheets.decodeRange(rangeOrCell);
 598     }
 599
 600     /*
 601      *  Please use Spreadsheets.decodeRanges instead
 602      */
 603     @Deprecated
 604     public static List<Range> decodeRanges(String ranges) {
 605         return Spreadsheets.decodeRanges(ranges);
 606     }
 607
 608     /*
 609      *  Please use Spreadsheets.startColumn instead
 610      */
 611     @Deprecated
 612     public static int startColumn(List<Range> ranges) {
 613         return Spreadsheets.startColumn(ranges);
 614     }
 615
 616     /*
 617      *  Please use Spreadsheets.startRow instead
 618      */
 619     @Deprecated
 620     public static int startRow(List<Range> ranges) {
 621         return Spreadsheets.startRow(ranges);
 622     }
 623
 624     /*
 625      *  Please use Spreadsheets.offset instead
 626      */
 627     @Deprecated
 628     public static String offset(String location, int rowOffset, int columnOffset) {
 629         return Spreadsheets.offset(location, rowOffset, columnOffset);
 630     }
 631
 632
 633 }