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