1 /*******************************************************************************
2 * Copyright (c) 2007, 2010 Association for Decentralized Information Management
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
10 * VTT Technical Research Centre of Finland - initial API and implementation
11 *******************************************************************************/
12 package org.simantics.spreadsheet.util;
14 import java.util.ArrayList;
15 import java.util.Collection;
16 import java.util.List;
18 import org.apache.poi.ss.usermodel.DataFormatter;
19 import org.simantics.Simantics;
20 import org.simantics.databoard.Bindings;
21 import org.simantics.databoard.binding.error.BindingException;
22 import org.simantics.databoard.binding.mutable.MutableVariant;
23 import org.simantics.databoard.binding.mutable.Variant;
24 import org.simantics.datatypes.utils.BTree;
25 import org.simantics.db.ReadGraph;
26 import org.simantics.db.Resource;
27 import org.simantics.db.WriteGraph;
28 import org.simantics.db.common.request.PossibleChild;
29 import org.simantics.db.common.request.WriteRequest;
30 import org.simantics.db.common.utils.Logger;
31 import org.simantics.db.common.utils.NameUtils;
32 import org.simantics.db.exception.AssumptionException;
33 import org.simantics.db.exception.DatabaseException;
34 import org.simantics.db.exception.VariableException;
35 import org.simantics.db.layer0.util.Layer0Utils;
36 import org.simantics.db.layer0.variable.ProxyVariables;
37 import org.simantics.db.layer0.variable.Variable;
38 import org.simantics.db.layer0.variable.VariableSpaceManipulator.PropertyCreationData;
39 import org.simantics.db.layer0.variable.Variables;
40 import org.simantics.db.request.Write;
41 import org.simantics.db.service.SerialisationSupport;
42 import org.simantics.document.server.io.IColor;
43 import org.simantics.document.server.io.IFont;
44 import org.simantics.document.server.io.RGBColor;
45 import org.simantics.document.server.io.SimpleFont;
46 import org.simantics.layer0.Layer0;
47 import org.simantics.scl.runtime.function.Function1;
48 import org.simantics.scl.runtime.tuple.Tuple;
49 import org.simantics.spreadsheet.ClientModel;
50 import org.simantics.spreadsheet.OperationMode;
51 import org.simantics.spreadsheet.Range;
52 import org.simantics.spreadsheet.Spreadsheets;
53 import org.simantics.spreadsheet.Transaction;
54 import org.simantics.spreadsheet.common.TableCell;
55 import org.simantics.spreadsheet.common.cell.StringCellParser;
56 import org.simantics.spreadsheet.resource.SpreadsheetResource;
57 import org.simantics.utils.datastructures.Pair;
59 public class SpreadsheetUtils {
61 public static Pair<String, Collection<PropertyCreationData>> parse(String text, StringCellParser[] parsers) {
65 for(StringCellParser parser : parsers) {
66 Collection<PropertyCreationData> parsed = parser.parse(text);
67 if(parsed != null) return Pair.make(parser.getType(), parsed);
70 } catch (Throwable t) {
78 public static boolean isImmutable(Object object) {
79 return !(object instanceof Resource) && !(object instanceof Variable);
82 public static String getLabel(ReadGraph graph, Object object) throws DatabaseException {
88 if(object instanceof Resource) {
89 return NameUtils.getSafeName(graph, (Resource)object);
90 } else if (object instanceof Variable) {
92 Object value = ((Variable)object).getValue(graph);
93 return value.toString();
94 //return toString(value);
95 } catch (VariableException e) {
96 Object value = ((Variable)object).getPropertyValue(graph, "Label");
97 return value.toString();
99 } else if (object instanceof double[]) {
100 return object.toString();
101 // return toString(object);
103 return object.toString();
108 private static String toString(Object object) {
109 if(object instanceof double[]) {
111 return Bindings.DOUBLE_ARRAY.toString(object);
112 } catch (BindingException e) {
113 return object.toString();
116 return object.toString();
120 public static String getContent(ReadGraph graph, Object object) throws DatabaseException {
126 if(object instanceof Resource) {
127 SerialisationSupport support = graph.getService(SerialisationSupport.class);
128 return support.getResourceSerializer().createRandomAccessId((Resource)object);
129 } else if (object instanceof Variable) {
130 return ((Variable)object).getURI(graph);
137 public static void main(String[] args) {
138 for(int i=0;i<16384;i++) {
139 String name = Spreadsheets.columnName(i);
140 Range r = Spreadsheets.decodeCellAbsolute(name + "1");
141 System.err.println(i + " " + name + " " + r);
145 public static String getLabel(ClientModel model, int row, int column) {
147 String location = Spreadsheets.cellName(row, column);
148 String label = model.getPropertyAt(location, ClientModel.LABEL);
149 if(label != null) return label;
150 Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);
151 if(content != null) return SpreadsheetUtils.getContentString(content);
153 } catch (Throwable e) {
159 public static String getContentString(Variant content) {
160 return content.getValue().toString();
163 public static boolean isInBounds(String base, String location, int wBounds, int hBounds) {
164 Range baseRange = Spreadsheets.decodeCellAbsolute(base);
165 Range locationRange = Spreadsheets.decodeCellAbsolute(location);
166 if(locationRange.startColumn < baseRange.startColumn) return false;
167 if(locationRange.startRow < baseRange.startRow) return false;
168 int wb = wBounds == -1 ? (Integer.MAX_VALUE / 3) : wBounds;
169 int hb = hBounds == -1 ? (Integer.MAX_VALUE / 3) : hBounds;
170 if(locationRange.startColumn > (baseRange.startColumn+wb-1)) return false;
171 if(locationRange.startRow > (baseRange.startRow+hb-1)) return false;
175 public static void schedule(Transaction<?> transaction, Write write) {
177 if(transaction == null) {
179 TransactionImpl impl = (TransactionImpl)startTransaction(OperationMode.OPERATION);
185 TransactionImpl impl = (TransactionImpl)transaction;
192 public static Transaction<Write> startTransaction() {
193 return startTransaction(OperationMode.EDIT_MODE);
196 public static Transaction<Write> startTransaction(OperationMode mode) {
197 return new TransactionImpl(mode);
200 static class TransactionImpl implements Transaction<Write> {
202 private ArrayList<Write> writes = new ArrayList<>();
203 private final OperationMode mode;
204 private Object context;
205 private List<Object> needSync;
207 public TransactionImpl(OperationMode mode) {
211 public void commit() {
213 Simantics.async(new WriteRequest() {
216 public void perform(WriteGraph graph) throws DatabaseException {
217 graph.markUndoPoint();
218 for(int i=0;i<writes.size();i++) {
219 Write write = writes.get(i);
221 write.perform(graph);
222 } catch (DatabaseException e) {
224 Logger.defaultLogError(e);
226 // This can schedule more writes
227 //graph.syncRequest(write);
235 public void add(Write write) {
240 public boolean isOperationMode() {
241 return mode.equals(OperationMode.OPERATION);
245 public void setContext(Object context) {
246 this.context = context;
250 public Object getContext() {
255 public void needSynchronization(Object object) {
256 if (needSync == null)
257 needSync = new ArrayList<>();
258 needSync.add(object);
262 public List<Object> needSynchronization() {
267 public static MutableVariant createVariant() {
268 return new MutableVariant();
271 public static Variant getSafeClientVariant(ClientModel clientModel, String location, String property) {
273 return clientModel.getPossiblePropertyAt(location, property);
274 } catch (Throwable t) {
275 Logger.defaultLogError(t);
276 return Variant.ofInstance(t.getMessage());
280 public static Resource createSheet(WriteGraph graph, Resource book, String name) throws DatabaseException {
282 return createSheet(graph, book, name, new String[] {}, new int[] {});
286 public static Resource createSheet(WriteGraph graph, Resource book, String name, String[] colNames, int[] colWidths) throws DatabaseException {
288 Layer0 L0 = Layer0.getInstance(graph);
289 SpreadsheetResource sr = SpreadsheetResource.getInstance(graph);
291 Resource result = graph.newResource();
292 graph.claim(result, L0.InstanceOf, null, sr.Spreadsheet);
295 name = NameUtils.findFreshEscapedName(graph, "Sheet", book, L0.ConsistsOf);
297 graph.claimLiteral(result, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING);
298 graph.claim(book, L0.ConsistsOf, L0.PartOf, result);
300 // Resource newCell = graph.newResource();
301 // graph.claim(newCell, L0.InstanceOf, null, sr.Lines);
302 // graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
303 // graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
304 // BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);
306 BTree bt = new BTree(graph, Spreadsheets.SPREADSHEET_BTREE_SIZE, sr.Lines, sr.LineNode, L0.PartOf, false);
307 // BTree bt = BTreeUtils.create(graph, sr.Lines, sr.LineNode, L0.PartOf, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, false);
308 Resource lines = bt.rootOfBTree();
310 graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
311 graph.claim(result, L0.ConsistsOf, L0.PartOf, lines);
314 Resource newCell = graph.newResource();
315 graph.claim(newCell, L0.InstanceOf, null, sr.Dimensions);
316 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);
317 graph.addLiteral(newCell, sr.Dimensions_fitColumns, sr.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
318 graph.addLiteral(newCell, sr.Dimensions_fitRows, sr.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
319 graph.addLiteral(newCell, sr.Dimensions_columnCount, sr.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);
320 graph.addLiteral(newCell, sr.Dimensions_rowCount, sr.Dimensions_rowCount_Inverse, L0.Integer, 256, Bindings.INTEGER);
321 graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
325 Resource newCell = graph.newResource();
326 graph.claim(newCell, L0.InstanceOf, null, sr.Headers);
327 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);
328 graph.addLiteral(newCell, sr.Headers_columnLabels, sr.Headers_columnLabels_Inverse, L0.StringArray, colNames, Bindings.STRING_ARRAY);
329 graph.addLiteral(newCell, sr.Headers_columnWidths, sr.Headers_columnWidths_Inverse, L0.IntegerArray, colWidths, Bindings.INT_ARRAY);
330 graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
338 public static Variable getBookVariable(ReadGraph graph, Resource book) throws DatabaseException {
339 Variable variable = Variables.getVariable(graph, book);
340 return ProxyVariables.makeProxyVariable(graph, variable, variable);
343 public static Variable sheetRun(ReadGraph graph, Resource book, Variable context) throws DatabaseException {
344 Variable root = Variables.getVariable(graph, book);
345 return ProxyVariables.makeProxyVariable(graph, root, context);
348 private static TableCell constructCell(int row, int column, Object data) {
349 TableCell cell = new TableCell();
351 cell.column = column;
352 cell.text = data.toString();
356 public static List<TableCell> queryCells(Object data) {
357 ArrayList<TableCell> result = new ArrayList<TableCell>();
358 if(data instanceof List) {
359 List<?> list = (List<?>)data;
361 for(Object o : list) {
362 if(o instanceof Tuple) {
364 for(int i=0;i<t.length();i++) {
365 result.add(constructCell(row, i, t.get(i)));
367 } else if (o instanceof List) {
368 List<?> rowList = (List<?>)o;
370 for(Object obj : rowList) {
371 result.add(constructCell(row, index++, obj));
374 result.add(constructCell(row, 0, o));
382 public static List<TableCell> organizeCells(int columns, List<String> headers_, List<TableCell> cells) throws DatabaseException {
384 ArrayList<TableCell> result = new ArrayList<TableCell>();
386 int w = 0; // name + fields
387 int h = 0; // number or rows excluding headers
389 if(columns < 2) throw new AssumptionException("organizeCells: number of columns needs to be greater than 1");
391 for(TableCell cell : cells) {
392 if((cell.column+1)>w) w = cell.column+1;
393 if((cell.row)>h) h = cell.row;
398 if(columns > (fields + 1)) columns = fields + 1;//throw new DatabaseException("organizeCells: number of block columns cannot be greater than the amount of columns in data");
400 int fieldsPerRow = columns - 1;
402 int blocks = fields / fieldsPerRow;
403 if(fields%fieldsPerRow > 0) blocks++;
405 TableCell[] names = new TableCell[h];
406 TableCell[] headers = new TableCell[w];
408 for(TableCell cell : cells) {
411 headers[cell.column] = cell;
412 } else if(cell.column == 0) {
413 names[cell.row-1] = cell;
415 TableCell copy = new TableCell(cell);
416 int block = (copy.column-1) / fieldsPerRow;
417 copy.row = block*(h+1) + copy.row;
418 copy.column = 1 + (copy.column-1) % fieldsPerRow;
424 for(int j=0;j<blocks;j++) {
426 int rowBase = j*(h+1);
428 for(int i=0;i<h;i++) {
429 TableCell copy = new TableCell(names[i]);
430 copy.row = rowBase + copy.row;
434 TableCell legend = new TableCell(headers[0]);
435 legend.row = rowBase;
438 for(int i=1;i<columns;i++) {
440 int index = (j*fieldsPerRow) + i;
441 if(index >= w) continue;
443 TableCell header = new TableCell(headers[index]);
444 header.row = rowBase;
456 public static List<TableCell> modifyCells1(List<TableCell> cells, Function1<TableCell, TableCell> fn) {
457 ArrayList<TableCell> result = new ArrayList<TableCell>();
458 for(TableCell cell : cells)
459 result.add(fn.apply(cell));
463 public static List<TableCell> modifyCells(List<TableCell> cells, List<Function1<TableCell, TableCell>> fns) {
464 ArrayList<TableCell> result = new ArrayList<TableCell>();
465 for(TableCell cell : cells) {
466 for(Function1<TableCell,TableCell> fn : fns)
467 cell = fn.apply(cell);
473 public static TableCell applyFont(IFont font, Function1<TableCell,Boolean> filter, TableCell cell) {
474 if(!filter.apply(cell)) return cell;
475 TableCell result = new TableCell(cell);
480 public static TableCell applyAlign(int align, Function1<TableCell,Boolean> filter, TableCell cell) {
481 if(!filter.apply(cell)) return cell;
482 TableCell result = new TableCell(cell);
483 result.align = align;
487 public static TableCell applyForeground(IColor color, Function1<TableCell,Boolean> filter, TableCell cell) {
488 if(!filter.apply(cell)) return cell;
489 TableCell result = new TableCell(cell);
490 result.foreground = color;
494 public static TableCell applyBackground(IColor color,Function1<TableCell,Boolean> filter, TableCell cell) {
495 if(!filter.apply(cell)) return cell;
496 TableCell result = new TableCell(cell);
497 result.background = color;
501 public static IFont simpleFont(String family, String style, int height) {
502 return new SimpleFont(family, style, height);
505 public static IColor rgbColor(int r, int g, int b) {
506 return new RGBColor(r, g, b);
509 public static boolean selectRow(int row, TableCell cell) {
510 return cell.row == row;
513 public static boolean selectColumn(int column, TableCell cell) {
514 return cell.column == column;
517 public static void setSCLLine(WriteGraph graph, Resource spreadsheet, int row, String expression) throws DatabaseException {
519 Layer0 L0 = Layer0.getInstance(graph);
520 Resource lines = graph.syncRequest(new PossibleChild(spreadsheet, "Lines"));
521 BTree bt = new BTree(graph, lines);
522 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
524 Resource line = graph.newResource();
525 graph.claim(line, L0.InstanceOf, SR.Line);
526 graph.addLiteral(line, L0.HasName, L0.NameOf, "" + row, Bindings.STRING);
527 Layer0Utils.setExpression(graph, line, SR.Line_content, null, "[spreadsheetCell ]", L0.SCLValue);
528 bt.insertBTree(graph, Variant.ofInstance(row), line);
532 public static String getFormattedLabel(ClientModel model, int row, int column, int formatIndex, String formatString) {
533 if (formatString == null)
534 return getLabel(model, row, column);
536 String location = Spreadsheets.cellName(row, column);
537 Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);
538 if(content != null) {
540 String contentString = SpreadsheetUtils.getContentString(content);
541 if(contentString.equals("~CIRCULAR~REF~"))
544 double value = Double.valueOf(contentString);
545 if (Double.isNaN(value))
546 return getLabel(model, row, column);
548 DataFormatter formatter = new DataFormatter();
549 return formatter.formatRawCellContents(value, formatIndex, formatString);
552 } catch (NumberFormatException e) {
553 return getLabel(model, row, column);
554 } catch (Throwable e) {
561 * Please use Spreadsheets.cellName instead
564 public static String cellName(int row, int column) {
565 return Spreadsheets.cellName(row, column);
569 * Please use Spreadsheets.columnName instead
572 public static String columnName(int column) {
573 return Spreadsheets.columnName(column);
577 * Please use Spreadsheets.decodeCellAbsolute instead
580 public static Range decodeCellAbsolute(String identifier) {
581 return Spreadsheets.decodeCellAbsolute(identifier);
585 * Please use Spreadsheets.decodePossibleCellAbsolute instead
588 public static Range decodePossibleCellAbsolute(String identifier) {
589 return Spreadsheets.decodePossibleCellAbsolute(identifier);
593 * Please use Spreadsheets.decodeRange instead
596 public static Range decodeRange(String rangeOrCell) {
597 return Spreadsheets.decodeRange(rangeOrCell);
601 * Please use Spreadsheets.decodeRanges instead
604 public static List<Range> decodeRanges(String ranges) {
605 return Spreadsheets.decodeRanges(ranges);
609 * Please use Spreadsheets.startColumn instead
612 public static int startColumn(List<Range> ranges) {
613 return Spreadsheets.startColumn(ranges);
617 * Please use Spreadsheets.startRow instead
620 public static int startRow(List<Range> ranges) {
621 return Spreadsheets.startRow(ranges);
625 * Please use Spreadsheets.offset instead
628 public static String offset(String location, int rowOffset, int columnOffset) {
629 return Spreadsheets.offset(location, rowOffset, columnOffset);