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