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.CellEditor;
50 import org.simantics.spreadsheet.CellEditor.Transaction;
51 import org.simantics.spreadsheet.ClientModel;
52 import org.simantics.spreadsheet.ClientModel.OperationMode;
53 import org.simantics.spreadsheet.Range;
54 import org.simantics.spreadsheet.Spreadsheets;
55 import org.simantics.spreadsheet.common.TableCell;
56 import org.simantics.spreadsheet.common.cell.StringCellParser;
57 import org.simantics.spreadsheet.resource.SpreadsheetResource;
58 import org.simantics.spreadsheet.synchronization.LineContentBean;
59 import org.simantics.utils.datastructures.Pair;
61 public class SpreadsheetUtils {
63 public static Pair<String, Collection<PropertyCreationData>> parse(String text, StringCellParser[] parsers) {
67 for(StringCellParser parser : parsers) {
68 Collection<PropertyCreationData> parsed = parser.parse(text);
69 if(parsed != null) return Pair.make(parser.getType(), parsed);
72 } catch (Throwable t) {
80 public static boolean isImmutable(Object object) {
81 return !(object instanceof Resource) && !(object instanceof Variable);
84 public static String getLabel(ReadGraph graph, Object object) throws DatabaseException {
90 if(object instanceof Resource) {
91 return NameUtils.getSafeName(graph, (Resource)object);
92 } else if (object instanceof Variable) {
94 Object value = ((Variable)object).getValue(graph);
95 return value.toString();
96 //return toString(value);
97 } catch (VariableException e) {
98 Object value = ((Variable)object).getPropertyValue(graph, "Label");
99 return value.toString();
101 } else if (object instanceof double[]) {
102 return object.toString();
103 // return toString(object);
105 return object.toString();
110 private static String toString(Object object) {
111 if(object instanceof double[]) {
113 return Bindings.DOUBLE_ARRAY.toString(object);
114 } catch (BindingException e) {
115 return object.toString();
118 return object.toString();
122 public static String getContent(ReadGraph graph, Object object) throws DatabaseException {
128 if(object instanceof Resource) {
129 SerialisationSupport support = graph.getService(SerialisationSupport.class);
130 return support.getResourceSerializer().createRandomAccessId((Resource)object);
131 } else if (object instanceof Variable) {
132 return ((Variable)object).getURI(graph);
139 public static void main(String[] args) {
140 for(int i=0;i<16384;i++) {
141 String name = Spreadsheets.columnName(i);
142 Range r = Spreadsheets.decodeCellAbsolute(name + "1");
143 System.err.println(i + " " + name + " " + r);
147 public static String getLabel(ClientModel model, int row, int column) {
149 String location = Spreadsheets.cellName(row, column);
150 String label = model.getPropertyAt(location, ClientModel.LABEL);
151 if(label != null) return label;
152 Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);
153 if(content != null) return SpreadsheetUtils.getContentString(content);
155 } catch (Throwable e) {
161 public static String getContentString(Variant content) {
162 return content.getValue().toString();
165 public static boolean isInBounds(String base, String location, int wBounds, int hBounds) {
166 Range baseRange = Spreadsheets.decodeCellAbsolute(base);
167 Range locationRange = Spreadsheets.decodeCellAbsolute(location);
168 if(locationRange.startColumn < baseRange.startColumn) return false;
169 if(locationRange.startRow < baseRange.startRow) return false;
170 int wb = wBounds == -1 ? (Integer.MAX_VALUE / 3) : wBounds;
171 int hb = hBounds == -1 ? (Integer.MAX_VALUE / 3) : hBounds;
172 if(locationRange.startColumn > (baseRange.startColumn+wb-1)) return false;
173 if(locationRange.startRow > (baseRange.startRow+hb-1)) return false;
177 public static void schedule(CellEditor.Transaction<?> transaction, Write write) {
179 if(transaction == null) {
181 TransactionImpl impl = (TransactionImpl)startTransaction(OperationMode.OPERATION);
187 TransactionImpl impl = (TransactionImpl)transaction;
194 public static Transaction<Write> startTransaction() {
195 return startTransaction(OperationMode.EDIT_MODE);
198 public static Transaction<Write> startTransaction(OperationMode mode) {
199 return new TransactionImpl(mode);
202 static class TransactionImpl implements CellEditor.Transaction<Write> {
204 private ArrayList<Write> writes = new ArrayList<>();
205 private final OperationMode mode;
206 private Object context;
207 private List<Object> needSync;
209 public TransactionImpl(OperationMode mode) {
213 public void commit() {
215 Simantics.async(new WriteRequest() {
218 public void perform(WriteGraph graph) throws DatabaseException {
219 graph.markUndoPoint();
220 for(int i=0;i<writes.size();i++) {
221 Write write = writes.get(i);
223 write.perform(graph);
224 } catch (DatabaseException e) {
226 Logger.defaultLogError(e);
228 // This can schedule more writes
229 //graph.syncRequest(write);
237 public void add(Write write) {
242 public boolean isOperationMode() {
243 return mode.equals(OperationMode.OPERATION);
247 public void setContext(Object context) {
248 this.context = context;
252 public Object getContext() {
257 public void needSynchronization(Object object) {
258 if (needSync == null)
259 needSync = new ArrayList<>();
260 needSync.add(object);
264 public List<Object> needSynchronization() {
269 public static MutableVariant createVariant() {
270 return new MutableVariant();
273 public static Variant getSafeClientVariant(ClientModel clientModel, String location, String property) {
275 return clientModel.getPossiblePropertyAt(location, property);
276 } catch (Throwable t) {
277 Logger.defaultLogError(t);
278 return Variant.ofInstance(t.getMessage());
282 public static Resource createSheet(WriteGraph graph, Resource book, String name) throws DatabaseException {
284 return createSheet(graph, book, name, new String[] {}, new int[] {});
288 public static Resource createSheet(WriteGraph graph, Resource book, String name, String[] colNames, int[] colWidths) throws DatabaseException {
290 Layer0 L0 = Layer0.getInstance(graph);
291 SpreadsheetResource sr = SpreadsheetResource.getInstance(graph);
293 Resource result = graph.newResource();
294 graph.claim(result, L0.InstanceOf, null, sr.Spreadsheet);
297 name = NameUtils.findFreshEscapedName(graph, "Sheet", book, L0.ConsistsOf);
299 graph.claimLiteral(result, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING);
300 graph.claim(book, L0.ConsistsOf, L0.PartOf, result);
302 // Resource newCell = graph.newResource();
303 // graph.claim(newCell, L0.InstanceOf, null, sr.Lines);
304 // graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
305 // graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
306 // BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);
308 BTree bt = new BTree(graph, Spreadsheets.SPREADSHEET_BTREE_SIZE, sr.Lines, sr.LineNode, L0.PartOf, false);
309 // BTree bt = BTreeUtils.create(graph, sr.Lines, sr.LineNode, L0.PartOf, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, false);
310 Resource lines = bt.rootOfBTree();
312 graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
313 graph.claim(result, L0.ConsistsOf, L0.PartOf, lines);
316 Resource newCell = graph.newResource();
317 graph.claim(newCell, L0.InstanceOf, null, sr.Dimensions);
318 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);
319 graph.addLiteral(newCell, sr.Dimensions_fitColumns, sr.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
320 graph.addLiteral(newCell, sr.Dimensions_fitRows, sr.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
321 graph.addLiteral(newCell, sr.Dimensions_columnCount, sr.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);
322 graph.addLiteral(newCell, sr.Dimensions_rowCount, sr.Dimensions_rowCount_Inverse, L0.Integer, 256, Bindings.INTEGER);
323 graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
327 Resource newCell = graph.newResource();
328 graph.claim(newCell, L0.InstanceOf, null, sr.Headers);
329 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);
330 graph.addLiteral(newCell, sr.Headers_columnLabels, sr.Headers_columnLabels_Inverse, L0.StringArray, colNames, Bindings.STRING_ARRAY);
331 graph.addLiteral(newCell, sr.Headers_columnWidths, sr.Headers_columnWidths_Inverse, L0.IntegerArray, colWidths, Bindings.INT_ARRAY);
332 graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell);
340 public static Variable getBookVariable(ReadGraph graph, Resource book) throws DatabaseException {
341 Variable variable = Variables.getVariable(graph, book);
342 return ProxyVariables.makeProxyVariable(graph, variable, variable);
345 public static Variable sheetRun(ReadGraph graph, Resource book, Variable context) throws DatabaseException {
346 Variable root = Variables.getVariable(graph, book);
347 return ProxyVariables.makeProxyVariable(graph, root, context);
350 private static TableCell constructCell(int row, int column, Object data) {
351 TableCell cell = new TableCell();
353 cell.column = column;
354 cell.text = data.toString();
358 public static List<TableCell> queryCells(Object data) {
359 ArrayList<TableCell> result = new ArrayList<TableCell>();
360 if(data instanceof List) {
361 List<?> list = (List<?>)data;
363 for(Object o : list) {
364 if(o instanceof Tuple) {
366 for(int i=0;i<t.length();i++) {
367 result.add(constructCell(row, i, t.get(i)));
369 } else if (o instanceof List) {
370 List<?> rowList = (List<?>)o;
372 for(Object obj : rowList) {
373 result.add(constructCell(row, index++, obj));
376 result.add(constructCell(row, 0, o));
384 public static List<TableCell> organizeCells(int columns, List<String> headers_, List<TableCell> cells) throws DatabaseException {
386 ArrayList<TableCell> result = new ArrayList<TableCell>();
388 int w = 0; // name + fields
389 int h = 0; // number or rows excluding headers
391 if(columns < 2) throw new AssumptionException("organizeCells: number of columns needs to be greater than 1");
393 for(TableCell cell : cells) {
394 if((cell.column+1)>w) w = cell.column+1;
395 if((cell.row)>h) h = cell.row;
400 if(columns > (fields + 1)) columns = fields + 1;//throw new DatabaseException("organizeCells: number of block columns cannot be greater than the amount of columns in data");
402 int fieldsPerRow = columns - 1;
404 int blocks = fields / fieldsPerRow;
405 if(fields%fieldsPerRow > 0) blocks++;
407 TableCell[] names = new TableCell[h];
408 TableCell[] headers = new TableCell[w];
410 for(TableCell cell : cells) {
413 headers[cell.column] = cell;
414 } else if(cell.column == 0) {
415 names[cell.row-1] = cell;
417 TableCell copy = new TableCell(cell);
418 int block = (copy.column-1) / fieldsPerRow;
419 copy.row = block*(h+1) + copy.row;
420 copy.column = 1 + (copy.column-1) % fieldsPerRow;
426 for(int j=0;j<blocks;j++) {
428 int rowBase = j*(h+1);
430 for(int i=0;i<h;i++) {
431 TableCell copy = new TableCell(names[i]);
432 copy.row = rowBase + copy.row;
436 TableCell legend = new TableCell(headers[0]);
437 legend.row = rowBase;
440 for(int i=1;i<columns;i++) {
442 int index = (j*fieldsPerRow) + i;
443 if(index >= w) continue;
445 TableCell header = new TableCell(headers[index]);
446 header.row = rowBase;
458 public static List<TableCell> modifyCells1(List<TableCell> cells, Function1<TableCell, TableCell> fn) {
459 ArrayList<TableCell> result = new ArrayList<TableCell>();
460 for(TableCell cell : cells)
461 result.add(fn.apply(cell));
465 public static List<TableCell> modifyCells(List<TableCell> cells, List<Function1<TableCell, TableCell>> fns) {
466 ArrayList<TableCell> result = new ArrayList<TableCell>();
467 for(TableCell cell : cells) {
468 for(Function1<TableCell,TableCell> fn : fns)
469 cell = fn.apply(cell);
475 public static TableCell applyFont(IFont font, Function1<TableCell,Boolean> filter, TableCell cell) {
476 if(!filter.apply(cell)) return cell;
477 TableCell result = new TableCell(cell);
482 public static TableCell applyAlign(int align, Function1<TableCell,Boolean> filter, TableCell cell) {
483 if(!filter.apply(cell)) return cell;
484 TableCell result = new TableCell(cell);
485 result.align = align;
489 public static TableCell applyForeground(IColor color, Function1<TableCell,Boolean> filter, TableCell cell) {
490 if(!filter.apply(cell)) return cell;
491 TableCell result = new TableCell(cell);
492 result.foreground = color;
496 public static TableCell applyBackground(IColor color,Function1<TableCell,Boolean> filter, TableCell cell) {
497 if(!filter.apply(cell)) return cell;
498 TableCell result = new TableCell(cell);
499 result.background = color;
503 public static IFont simpleFont(String family, String style, int height) {
504 return new SimpleFont(family, style, height);
507 public static IColor rgbColor(int r, int g, int b) {
508 return new RGBColor(r, g, b);
511 public static boolean selectRow(int row, TableCell cell) {
512 return cell.row == row;
515 public static boolean selectColumn(int column, TableCell cell) {
516 return cell.column == column;
519 public static void setSCLLine(WriteGraph graph, Resource spreadsheet, int row, String expression) throws DatabaseException {
521 Layer0 L0 = Layer0.getInstance(graph);
522 Resource lines = graph.syncRequest(new PossibleChild(spreadsheet, "Lines"));
523 BTree bt = new BTree(graph, lines);
524 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
526 Resource line = graph.newResource();
527 graph.claim(line, L0.InstanceOf, SR.Line);
528 graph.addLiteral(line, L0.HasName, L0.NameOf, "" + row, Bindings.STRING);
529 Layer0Utils.setExpression(graph, line, SR.Line_content, null, "[spreadsheetCell ]", L0.SCLValue);
530 bt.insertBTree(graph, Variant.ofInstance(row), line);
534 public static String getFormattedLabel(ClientModel model, int row, int column, int formatIndex, String formatString) {
535 if (formatString == null)
536 return getLabel(model, row, column);
538 String location = Spreadsheets.cellName(row, column);
539 Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT);
540 if(content != null) {
542 String contentString = SpreadsheetUtils.getContentString(content);
543 if(contentString.equals("~CIRCULAR~REF~"))
546 double value = Double.valueOf(contentString);
547 if (Double.isNaN(value))
548 return getLabel(model, row, column);
550 DataFormatter formatter = new DataFormatter();
551 return formatter.formatRawCellContents(value, formatIndex, formatString);
554 } catch (NumberFormatException e) {
555 return getLabel(model, row, column);
556 } catch (Throwable e) {