]> gerrit.simantics Code Review - simantics/platform.git/blob - bundles/org.simantics.spreadsheet.common/src/org/simantics/spreadsheet/util/SpreadsheetUtils.java
ListIndexRoots request
[simantics/platform.git] / 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 }