X-Git-Url: https://gerrit.simantics.org/r/gitweb?p=simantics%2Fplatform.git;a=blobdiff_plain;f=bundles%2Forg.simantics.spreadsheet.common%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Futil%2FSpreadsheetUtils.java;h=a784ccf9efaf340dd4834107a5dfd51e5e0cf5c9;hp=c70a9aa8d089fe7290fee4e387a6830c49bf6344;hb=5c67a96d34fe904b8c4b0375cd08ff1d543bf369;hpb=969bd23cab98a79ca9101af33334000879fb60c5 diff --git a/bundles/org.simantics.spreadsheet.common/src/org/simantics/spreadsheet/util/SpreadsheetUtils.java b/bundles/org.simantics.spreadsheet.common/src/org/simantics/spreadsheet/util/SpreadsheetUtils.java index c70a9aa8d..a784ccf9e 100644 --- a/bundles/org.simantics.spreadsheet.common/src/org/simantics/spreadsheet/util/SpreadsheetUtils.java +++ b/bundles/org.simantics.spreadsheet.common/src/org/simantics/spreadsheet/util/SpreadsheetUtils.java @@ -1,834 +1,633 @@ -/******************************************************************************* - * Copyright (c) 2007, 2010 Association for Decentralized Information Management - * in Industry THTH ry. - * All rights reserved. This program and the accompanying materials - * are made available under the terms of the Eclipse Public License v1.0 - * which accompanies this distribution, and is available at - * http://www.eclipse.org/legal/epl-v10.html - * - * Contributors: - * VTT Technical Research Centre of Finland - initial API and implementation - *******************************************************************************/ -package org.simantics.spreadsheet.util; - -import java.util.ArrayList; -import java.util.Collection; -import java.util.List; - -import org.apache.poi.ss.usermodel.DataFormatter; -import org.simantics.Simantics; -import org.simantics.databoard.Bindings; -import org.simantics.databoard.binding.error.BindingException; -import org.simantics.databoard.binding.mutable.MutableVariant; -import org.simantics.databoard.binding.mutable.Variant; -import org.simantics.datatypes.utils.BTree; -import org.simantics.datatypes.utils.BTreeUtils; -import org.simantics.db.ReadGraph; -import org.simantics.db.Resource; -import org.simantics.db.WriteGraph; -import org.simantics.db.common.request.PossibleChild; -import org.simantics.db.common.request.WriteRequest; -import org.simantics.db.common.utils.Logger; -import org.simantics.db.common.utils.NameUtils; -import org.simantics.db.exception.DatabaseException; -import org.simantics.db.layer0.exception.VariableException; -import org.simantics.db.layer0.util.Layer0Utils; -import org.simantics.db.layer0.variable.ProxyVariables; -import org.simantics.db.layer0.variable.Variable; -import org.simantics.db.layer0.variable.VariableSpaceManipulator.PropertyCreationData; -import org.simantics.db.layer0.variable.Variables; -import org.simantics.db.request.Write; -import org.simantics.db.service.SerialisationSupport; -import org.simantics.document.server.io.IColor; -import org.simantics.document.server.io.IFont; -import org.simantics.document.server.io.RGBColor; -import org.simantics.document.server.io.SimpleFont; -import org.simantics.layer0.Layer0; -import org.simantics.scl.runtime.function.Function1; -import org.simantics.scl.runtime.tuple.Tuple; -import org.simantics.spreadsheet.CellEditor; -import org.simantics.spreadsheet.CellEditor.Transaction; -import org.simantics.spreadsheet.ClientModel.OperationMode; -import org.simantics.spreadsheet.ClientModel; -import org.simantics.spreadsheet.Range; -import org.simantics.spreadsheet.common.TableCell; -import org.simantics.spreadsheet.common.cell.StringCellParser; -import org.simantics.spreadsheet.common.exception.CellParseException; -import org.simantics.spreadsheet.resource.SpreadsheetResource; -import org.simantics.utils.datastructures.Pair; - -public class SpreadsheetUtils { - - public static final int SPREADSHEET_BTREE_SIZE = 100; - - public static String offset(String location, int rowOffset, int columnOffset) { - - Range range = decodeCellAbsolute(location); - String result = cellName(range.startRow + rowOffset, range.startColumn + columnOffset); - // System.err.println("offset " + location + "(" + rowOffset + " " + columnOffset + ") = >" + result); - return result; - - } - - public static Object extract(Object object, int row, int column) { - if(object instanceof List) { - List list = (List)object; - if(list.size() <= row) return null; - Object item = list.get(row); - if(item instanceof Tuple) { - Tuple tuple = (Tuple)item; - if(tuple.length() <= column) return null; - return tuple.get(column); - } - } - return null; - } - - // 1 kirjain, 'A' + column - // 2 kirjainta, 'A' + column % 26 , 'A' + int((column-26)/26) - // 3 kirjainta 'A' + column % 26 , 'A' + int(column-(26*(26+1)) / 26) % 26 - - // 0 26 - // 26 26 + 26*26 - // 26 + 26*26 26 + 26*26 + 26*26*26 - - public static String columnName(int column, int current, int limit, int chars) { - - if(column < limit) { - - char[] buf = new char[chars]; - column -= current; - for(int i=chars-1;i>=0;i--) { - char rem = (char)(column % 26); - column = (column / 26); - buf[i] = (char)('A' + rem); - } - return new String(buf); - - } else return columnName(column, limit, 26*(limit+1), chars+1); - - } - - public static String columnName(int column) { - return columnName(column, 0, 26, 1); - } - - public static String cellName(int row, int column) { - - String result = columnName(column); - result += (row+1); - return result; - - } - - public static Range decodeCellAbsolute(String identifier) { - long l = decodeCellCoded(identifier); - int row = (int)(l & 0xffffffff) - 1; - int column = (int)((l>>32) & 0xffffffff); - return new Range(row, row, column, column); - } - - public static Range decodePossibleCellAbsolute(String identifier) { - try { - return decodeCellAbsolute(identifier); - } catch (CellParseException e) { - return null; - } - } - - public static long decodeCellCoded(String identifier) { - - // System.out.println("decodecellabsolute " + identifier); - - int row = 0; - int column = 0; - -// identifier. - - int position = 0; - - // We skip $ here - if(identifier.charAt(position) == '$') position++; - - int length = identifier.length(); - - while(position < length) { - char b = identifier.charAt(position); - if(b >= 'A' && b <= 'Z') column = column * 26 + (b-'A' + 1); - else break; - position++; - } - - // We skip $ here - if(position < length) - if(identifier.charAt(position) == '$') - position++; - - while(position < length) { - char b = identifier.charAt(position); - if(b >= '0' && b <= '9'){ - row = row * 10 + b-'0'; - } - else if(b=='-' && position < (length-1)){//identify use of full row range here. - position++; - char b2 = identifier.charAt(position); - if(b2=='1'){ - row = 0; - position++; - break; - } - } - else { - break; - } - position++; - } - - if(position == length) { - - // We need to be able to express -1 in row => report row + 1 here - column--; - // System.err.println("ra " + identifier + " => " + row + " " + column); - return row + (((long)column)<<32); - - } else { - - throw new CellParseException("Cell identifier '" + identifier + "' is not a valid cell reference."); - - } - - } - - public static Range decodeCellRelative(String identifier, int row, int column) { - - int offset = Integer.valueOf(identifier.substring(1).trim()); - // System.out.println("offset=" + offset); - - if(identifier.startsWith("L") || identifier.startsWith("l")) { - return new Range(row, row, column-offset, column-offset); - } else if(identifier.startsWith("R") || identifier.startsWith("r")) { - return new Range(row, row, column+offset, column+offset); - } else if(identifier.startsWith("U") || identifier.startsWith("u")) { - return new Range(row-offset, row-offset, column, column); - } else if(identifier.startsWith("D") || identifier.startsWith("d")) { - return new Range(row+offset, row+offset, column, column); - } else { - throw new CellParseException("Relative cell syntax must begin with L|R|U|D."); - } - - } - - public static Range decodeCell(String identifier, int row, int column) { - - if(identifier.startsWith("_")) { - return decodeCellRelative(identifier.substring(1), row, column); - } else { - return decodeCellAbsolute(identifier); - } - - } - - public static Range decodeReference(String identifier, int row, int column) { - if(!identifier.startsWith("&")) throw new CellParseException("A reference cell was expected."); - return decodeRange(identifier.substring(1), row, column); - } - - public static List decodeRanges(String ranges){ - String[] splitted = ranges.split(","); - List result = new ArrayList<>(); - for(String split : splitted){ - result.add(decodeRange(split)); - } - return result; - } - - public static int startRow(List ranges){ - int s = -1; - for(Range r : ranges){ - if(r.startRow ranges){ - int s = -1; - for(Range r : ranges){ - if(r.startColumnendRow){ - endRow = r.endRow; - } - return endRow - startRow +1; - } - - public static int amountOfColumns(Range r){ - int endColumn = -2; - int startColumn = -2; - if(r.isFullColumns()){ - return Range.MAXCOLUMNSPEC; - } - if(endColumn == -2 && startColumn == -2){ - endColumn = r.endColumn; - startColumn = r.startColumn; - } - if(r.startColumnendColumn){ - endColumn = r.endColumn; - } - return endColumn - startColumn +1; - } - - public static Range decodeRange(String rangeOrCell) { - if(rangeOrCell.isEmpty()) return fullRange(); - return decodeRange(rangeOrCell, 0, 0); - } - - public static Range fullRange() { - return new Range(0, -1, 0, -1); - } - - public static Range decodeRange(String rangeOrCell, int row, int column) { - - String[] parts = rangeOrCell.split(":"); - if(parts.length == 1) { - - return decodeCell(rangeOrCell, row, column); - - } else if (parts.length == 2) { - - Range from = decodeCell(parts[0].trim(), row, column); - // System.out.println("decodefrom=" + from); - Range to = decodeCell(parts[1].trim(), row, column); - // System.out.println("decodeto=" + to); - return Range.combine(from, to); - - } else { - - throw new CellParseException("The reference cell syntax was invalid. At most 1 occurrence of ':' is expected."); - - } - - } - - public static Pair> parse(String text, StringCellParser[] parsers) { - - try { - - for(StringCellParser parser : parsers) { - Collection parsed = parser.parse(text); - if(parsed != null) return Pair.make(parser.getType(), parsed); - } - - } catch (Throwable t) { - t.printStackTrace(); - } - - return null; - - } - - public static boolean isImmutable(Object object) { - return !(object instanceof Resource) && !(object instanceof Variable); - } - - public static String getLabel(ReadGraph graph, Object object) throws DatabaseException { - - if(object == null) { - return "no data"; - } - - if(object instanceof Resource) { - return NameUtils.getSafeName(graph, (Resource)object); - } else if (object instanceof Variable) { - try { - Object value = ((Variable)object).getValue(graph); - return value.toString(); - //return toString(value); - } catch (VariableException e) { - Object value = ((Variable)object).getPropertyValue(graph, "Label"); - return value.toString(); - } - } else if (object instanceof double[]) { - return object.toString(); - // return toString(object); - } else { - return object.toString(); - } - - } - - private static String toString(Object object) { - if(object instanceof double[]) { - try { - return Bindings.DOUBLE_ARRAY.toString(object); - } catch (BindingException e) { - return object.toString(); - } - } else { - return object.toString(); - } - } - - public static String getContent(ReadGraph graph, Object object) throws DatabaseException { - - if(object == null) { - return null; - } - - if(object instanceof Resource) { - SerialisationSupport support = graph.getService(SerialisationSupport.class); - return support.getResourceSerializer().createRandomAccessId((Resource)object); - } else if (object instanceof Variable) { - return ((Variable)object).getURI(graph); - } else { - return ""; - } - - } - - public static void main(String[] args) { - for(int i=0;i<16384;i++) { - String name = columnName(i); - Range r = decodeCellAbsolute(name + "1"); - System.err.println(i + " " + name + " " + r); - } - } - - public static String getLabel(ClientModel model, int row, int column) { - try { - String location = SpreadsheetUtils.cellName(row, column); - String label = model.getPropertyAt(location, ClientModel.LABEL); - if(label != null) return label; - Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT); - if(content != null) return SpreadsheetUtils.getContentString(content); - else return null; - } catch (Throwable e) { - e.printStackTrace(); - return null; - } - } - - public static String getContentString(Variant content) { - return content.getValue().toString(); - } - - public static boolean isInBounds(String base, String location, int wBounds, int hBounds) { - Range baseRange = decodeCellAbsolute(base); - Range locationRange = decodeCellAbsolute(location); - if(locationRange.startColumn < baseRange.startColumn) return false; - if(locationRange.startRow < baseRange.startRow) return false; - int wb = wBounds == -1 ? (Integer.MAX_VALUE / 3) : wBounds; - int hb = hBounds == -1 ? (Integer.MAX_VALUE / 3) : hBounds; - if(locationRange.startColumn > (baseRange.startColumn+wb-1)) return false; - if(locationRange.startRow > (baseRange.startRow+hb-1)) return false; - return true; - } - - public static void schedule(CellEditor.Transaction transaction, Write write) { - - if(transaction == null) { - - TransactionImpl impl = (TransactionImpl)startTransaction(OperationMode.OPERATION); - impl.add(write); - impl.commit(); - - } else { - - TransactionImpl impl = (TransactionImpl)transaction; - impl.add(write); - - } - - } - - public static Transaction startTransaction() { - return startTransaction(OperationMode.EDIT_MODE); - } - - public static Transaction startTransaction(OperationMode mode) { - return new TransactionImpl(mode); - } - - static class TransactionImpl implements CellEditor.Transaction { - - private ArrayList writes = new ArrayList<>(); - private final OperationMode mode; - private Object context; - private List needSync; - - public TransactionImpl(OperationMode mode) { - this.mode = mode; - } - - public void commit() { - - Simantics.async(new WriteRequest() { - - @Override - public void perform(WriteGraph graph) throws DatabaseException { - graph.markUndoPoint(); - for(int i=0;i(); - needSync.add(object); - } - - @Override - public List needSynchronization() { - return needSync; - } - } - - public static MutableVariant createVariant() { - return new MutableVariant(); - } - - public static Variant getSafeClientVariant(ClientModel clientModel, String location, String property) { - try { - return clientModel.getPossiblePropertyAt(location, property); - } catch (Throwable t) { - Logger.defaultLogError(t); - return Variant.ofInstance(t.getMessage()); - } - } - - public static Resource createSheet(WriteGraph graph, Resource book, String name) throws DatabaseException { - - return createSheet(graph, book, name, new String[] {}, new int[] {}); - - } - - public static Resource createSheet(WriteGraph graph, Resource book, String name, String[] colNames, int[] colWidths) throws DatabaseException { - - Layer0 L0 = Layer0.getInstance(graph); - SpreadsheetResource sr = SpreadsheetResource.getInstance(graph); - - Resource result = graph.newResource(); - graph.claim(result, L0.InstanceOf, null, sr.Spreadsheet); - - if(name == null) { - name = NameUtils.findFreshEscapedName(graph, "Sheet", book, L0.ConsistsOf); - } - graph.claimLiteral(result, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING); - graph.claim(book, L0.ConsistsOf, L0.PartOf, result); - -// Resource newCell = graph.newResource(); -// graph.claim(newCell, L0.InstanceOf, null, sr.Lines); -// graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING); -// graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell); -// BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true); - - BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, sr.Lines, sr.LineNode, L0.PartOf, false); -// BTree bt = BTreeUtils.create(graph, sr.Lines, sr.LineNode, L0.PartOf, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, false); - Resource lines = bt.rootOfBTree(); - - graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING); - graph.claim(result, L0.ConsistsOf, L0.PartOf, lines); - - { - Resource newCell = graph.newResource(); - graph.claim(newCell, L0.InstanceOf, null, sr.Dimensions); - graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING); - graph.addLiteral(newCell, sr.Dimensions_fitColumns, sr.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN); - graph.addLiteral(newCell, sr.Dimensions_fitRows, sr.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN); - graph.addLiteral(newCell, sr.Dimensions_columnCount, sr.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER); - graph.addLiteral(newCell, sr.Dimensions_rowCount, sr.Dimensions_rowCount_Inverse, L0.Integer, 256, Bindings.INTEGER); - graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell); - } - - { - Resource newCell = graph.newResource(); - graph.claim(newCell, L0.InstanceOf, null, sr.Headers); - graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING); - graph.addLiteral(newCell, sr.Headers_columnLabels, sr.Headers_columnLabels_Inverse, L0.StringArray, colNames, Bindings.STRING_ARRAY); - graph.addLiteral(newCell, sr.Headers_columnWidths, sr.Headers_columnWidths_Inverse, L0.IntegerArray, colWidths, Bindings.INT_ARRAY); - graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell); - } - - return result; - - } - - - public static Variable getBookVariable(ReadGraph graph, Resource book) throws DatabaseException { - Variable variable = Variables.getVariable(graph, book); - return ProxyVariables.makeProxyVariable(graph, variable, variable); - } - - public static Variable sheetRun(ReadGraph graph, Resource book, Variable context) throws DatabaseException { - Variable root = Variables.getVariable(graph, book); - return ProxyVariables.makeProxyVariable(graph, root, context); - } - - private static TableCell constructCell(int row, int column, Object data) { - TableCell cell = new TableCell(); - cell.row = row; - cell.column = column; - cell.text = data.toString(); - return cell; - } - - public static List queryCells(Object data) { - ArrayList result = new ArrayList(); - if(data instanceof List) { - List list = (List)data; - int row = 0; - for(Object o : list) { - if(o instanceof Tuple) { - Tuple t = (Tuple)o; - for(int i=0;i rowList = (List)o; - int index = 0; - for(Object obj : rowList) { - result.add(constructCell(row, index++, obj)); - } - } else { - result.add(constructCell(row, 0, o)); - } - row++; - } - } - return result; - } - - public static List organizeCells(int columns, List headers_, List cells) throws DatabaseException { - - ArrayList result = new ArrayList(); - - int w = 0; // name + fields - int h = 0; // number or rows excluding headers - - if(columns < 2) throw new DatabaseException("organizeCells: number of columns needs to be greater than 1"); - - for(TableCell cell : cells) { - if((cell.column+1)>w) w = cell.column+1; - if((cell.row)>h) h = cell.row; - } - - int fields = w - 1; - - 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"); - - int fieldsPerRow = columns - 1; - - int blocks = fields / fieldsPerRow; - if(fields%fieldsPerRow > 0) blocks++; - - TableCell[] names = new TableCell[h]; - TableCell[] headers = new TableCell[w]; - - for(TableCell cell : cells) { - - if(cell.row == 0) { - headers[cell.column] = cell; - } else if(cell.column == 0) { - names[cell.row-1] = cell; - } else { - TableCell copy = new TableCell(cell); - int block = (copy.column-1) / fieldsPerRow; - copy.row = block*(h+1) + copy.row; - copy.column = 1 + (copy.column-1) % fieldsPerRow; - result.add(copy); - } - - } - - for(int j=0;j= w) continue; - - TableCell header = new TableCell(headers[index]); - header.row = rowBase; - header.column = i; - result.add(header); - - } - - } - - return result; - - } - - public static List modifyCells1(List cells, Function1 fn) { - ArrayList result = new ArrayList(); - for(TableCell cell : cells) - result.add(fn.apply(cell)); - return result; - } - - public static List modifyCells(List cells, List> fns) { - ArrayList result = new ArrayList(); - for(TableCell cell : cells) { - for(Function1 fn : fns) - cell = fn.apply(cell); - result.add(cell); - } - return result; - } - - public static TableCell applyFont(IFont font, Function1 filter, TableCell cell) { - if(!filter.apply(cell)) return cell; - TableCell result = new TableCell(cell); - result.font = font; - return result; - } - - public static TableCell applyAlign(int align, Function1 filter, TableCell cell) { - if(!filter.apply(cell)) return cell; - TableCell result = new TableCell(cell); - result.align = align; - return result; - } - - public static TableCell applyForeground(IColor color, Function1 filter, TableCell cell) { - if(!filter.apply(cell)) return cell; - TableCell result = new TableCell(cell); - result.foreground = color; - return result; - } - - public static TableCell applyBackground(IColor color,Function1 filter, TableCell cell) { - if(!filter.apply(cell)) return cell; - TableCell result = new TableCell(cell); - result.background = color; - return result; - } - - public static IFont simpleFont(String family, String style, int height) { - return new SimpleFont(family, style, height); - } - - public static IColor rgbColor(int r, int g, int b) { - return new RGBColor(r, g, b); - } - - public static boolean selectRow(int row, TableCell cell) { - return cell.row == row; - } - - public static boolean selectColumn(int column, TableCell cell) { - return cell.column == column; - } - - public static void setSCLLine(WriteGraph graph, Resource spreadsheet, int row, String expression) throws DatabaseException { - - Layer0 L0 = Layer0.getInstance(graph); - Resource lines = graph.syncRequest(new PossibleChild(spreadsheet, "Lines")); - BTree bt = new BTree(graph, lines); - SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); - - Resource line = graph.newResource(); - graph.claim(line, L0.InstanceOf, SR.Line); - graph.addLiteral(line, L0.HasName, L0.NameOf, "" + row, Bindings.STRING); - Layer0Utils.setExpression(graph, line, SR.Line_content, null, "[spreadsheetCell ]", L0.SCLValue); - bt.insertBTree(graph, Variant.ofInstance(row), line); - - } - - public static String getFormattedLabel(ClientModel model, int row, int column, int formatIndex, String formatString) { - if (formatString == null) - return getLabel(model, row, column); - try { - String location = SpreadsheetUtils.cellName(row, column); - Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT); - if(content != null) { - - String contentString = SpreadsheetUtils.getContentString(content); - if(contentString.equals("~CIRCULAR~REF~")) - return "0"; - - double value = Double.valueOf(contentString); - if (Double.isNaN(value)) - return getLabel(model, row, column); - - DataFormatter formatter = new DataFormatter(); - return formatter.formatRawCellContents(value, formatIndex, formatString); - } - return null; - } catch (NumberFormatException e) { - return getLabel(model, row, column); - } catch (Throwable e) { - e.printStackTrace(); - return null; - } - } - -} +/******************************************************************************* + * Copyright (c) 2007, 2010 Association for Decentralized Information Management + * in Industry THTH ry. + * All rights reserved. This program and the accompanying materials + * are made available under the terms of the Eclipse Public License v1.0 + * which accompanies this distribution, and is available at + * http://www.eclipse.org/legal/epl-v10.html + * + * Contributors: + * VTT Technical Research Centre of Finland - initial API and implementation + *******************************************************************************/ +package org.simantics.spreadsheet.util; + +import java.util.ArrayList; +import java.util.Collection; +import java.util.List; + +import org.apache.poi.ss.usermodel.DataFormatter; +import org.simantics.Simantics; +import org.simantics.databoard.Bindings; +import org.simantics.databoard.binding.error.BindingException; +import org.simantics.databoard.binding.mutable.MutableVariant; +import org.simantics.databoard.binding.mutable.Variant; +import org.simantics.datatypes.utils.BTree; +import org.simantics.db.ReadGraph; +import org.simantics.db.Resource; +import org.simantics.db.WriteGraph; +import org.simantics.db.common.request.PossibleChild; +import org.simantics.db.common.request.WriteRequest; +import org.simantics.db.common.utils.Logger; +import org.simantics.db.common.utils.NameUtils; +import org.simantics.db.exception.AssumptionException; +import org.simantics.db.exception.DatabaseException; +import org.simantics.db.exception.VariableException; +import org.simantics.db.layer0.util.Layer0Utils; +import org.simantics.db.layer0.variable.ProxyVariables; +import org.simantics.db.layer0.variable.Variable; +import org.simantics.db.layer0.variable.VariableSpaceManipulator.PropertyCreationData; +import org.simantics.db.layer0.variable.Variables; +import org.simantics.db.request.Write; +import org.simantics.db.service.SerialisationSupport; +import org.simantics.document.server.io.IColor; +import org.simantics.document.server.io.IFont; +import org.simantics.document.server.io.RGBColor; +import org.simantics.document.server.io.SimpleFont; +import org.simantics.layer0.Layer0; +import org.simantics.scl.runtime.function.Function1; +import org.simantics.scl.runtime.tuple.Tuple; +import org.simantics.spreadsheet.ClientModel; +import org.simantics.spreadsheet.OperationMode; +import org.simantics.spreadsheet.Range; +import org.simantics.spreadsheet.Spreadsheets; +import org.simantics.spreadsheet.Transaction; +import org.simantics.spreadsheet.common.TableCell; +import org.simantics.spreadsheet.common.cell.StringCellParser; +import org.simantics.spreadsheet.resource.SpreadsheetResource; +import org.simantics.utils.datastructures.Pair; + +public class SpreadsheetUtils { + + public static Pair> parse(String text, StringCellParser[] parsers) { + + try { + + for(StringCellParser parser : parsers) { + Collection parsed = parser.parse(text); + if(parsed != null) return Pair.make(parser.getType(), parsed); + } + + } catch (Throwable t) { + t.printStackTrace(); + } + + return null; + + } + + public static boolean isImmutable(Object object) { + return !(object instanceof Resource) && !(object instanceof Variable); + } + + public static String getLabel(ReadGraph graph, Object object) throws DatabaseException { + + if(object == null) { + return "no data"; + } + + if(object instanceof Resource) { + return NameUtils.getSafeName(graph, (Resource)object); + } else if (object instanceof Variable) { + try { + Object value = ((Variable)object).getValue(graph); + return value.toString(); + //return toString(value); + } catch (VariableException e) { + Object value = ((Variable)object).getPropertyValue(graph, "Label"); + return value.toString(); + } + } else if (object instanceof double[]) { + return object.toString(); + // return toString(object); + } else { + return object.toString(); + } + + } + + private static String toString(Object object) { + if(object instanceof double[]) { + try { + return Bindings.DOUBLE_ARRAY.toString(object); + } catch (BindingException e) { + return object.toString(); + } + } else { + return object.toString(); + } + } + + public static String getContent(ReadGraph graph, Object object) throws DatabaseException { + + if(object == null) { + return null; + } + + if(object instanceof Resource) { + SerialisationSupport support = graph.getService(SerialisationSupport.class); + return support.getResourceSerializer().createRandomAccessId((Resource)object); + } else if (object instanceof Variable) { + return ((Variable)object).getURI(graph); + } else { + return ""; + } + + } + + public static void main(String[] args) { + for(int i=0;i<16384;i++) { + String name = Spreadsheets.columnName(i); + Range r = Spreadsheets.decodeCellAbsolute(name + "1"); + System.err.println(i + " " + name + " " + r); + } + } + + public static String getLabel(ClientModel model, int row, int column) { + try { + String location = Spreadsheets.cellName(row, column); + String label = model.getPropertyAt(location, ClientModel.LABEL); + if(label != null) return label; + Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT); + if(content != null) return SpreadsheetUtils.getContentString(content); + else return null; + } catch (Throwable e) { + e.printStackTrace(); + return null; + } + } + + public static String getContentString(Variant content) { + return content.getValue().toString(); + } + + public static boolean isInBounds(String base, String location, int wBounds, int hBounds) { + Range baseRange = Spreadsheets.decodeCellAbsolute(base); + Range locationRange = Spreadsheets.decodeCellAbsolute(location); + if(locationRange.startColumn < baseRange.startColumn) return false; + if(locationRange.startRow < baseRange.startRow) return false; + int wb = wBounds == -1 ? (Integer.MAX_VALUE / 3) : wBounds; + int hb = hBounds == -1 ? (Integer.MAX_VALUE / 3) : hBounds; + if(locationRange.startColumn > (baseRange.startColumn+wb-1)) return false; + if(locationRange.startRow > (baseRange.startRow+hb-1)) return false; + return true; + } + + public static void schedule(Transaction transaction, Write write) { + + if(transaction == null) { + + TransactionImpl impl = (TransactionImpl)startTransaction(OperationMode.OPERATION); + impl.add(write); + impl.commit(); + + } else { + + TransactionImpl impl = (TransactionImpl)transaction; + impl.add(write); + + } + + } + + public static Transaction startTransaction() { + return startTransaction(OperationMode.EDIT_MODE); + } + + public static Transaction startTransaction(OperationMode mode) { + return new TransactionImpl(mode); + } + + static class TransactionImpl implements Transaction { + + private ArrayList writes = new ArrayList<>(); + private final OperationMode mode; + private Object context; + private List needSync; + + public TransactionImpl(OperationMode mode) { + this.mode = mode; + } + + public void commit() { + + Simantics.async(new WriteRequest() { + + @Override + public void perform(WriteGraph graph) throws DatabaseException { + graph.markUndoPoint(); + for(int i=0;i(); + needSync.add(object); + } + + @Override + public List needSynchronization() { + return needSync; + } + } + + public static MutableVariant createVariant() { + return new MutableVariant(); + } + + public static Variant getSafeClientVariant(ClientModel clientModel, String location, String property) { + try { + return clientModel.getPossiblePropertyAt(location, property); + } catch (Throwable t) { + Logger.defaultLogError(t); + return Variant.ofInstance(t.getMessage()); + } + } + + public static Resource createSheet(WriteGraph graph, Resource book, String name) throws DatabaseException { + + return createSheet(graph, book, name, new String[] {}, new int[] {}); + + } + + public static Resource createSheet(WriteGraph graph, Resource book, String name, String[] colNames, int[] colWidths) throws DatabaseException { + + Layer0 L0 = Layer0.getInstance(graph); + SpreadsheetResource sr = SpreadsheetResource.getInstance(graph); + + Resource result = graph.newResource(); + graph.claim(result, L0.InstanceOf, null, sr.Spreadsheet); + + if(name == null) { + name = NameUtils.findFreshEscapedName(graph, "Sheet", book, L0.ConsistsOf); + } + graph.claimLiteral(result, L0.HasName, L0.NameOf, L0.String, name, Bindings.STRING); + graph.claim(book, L0.ConsistsOf, L0.PartOf, result); + +// Resource newCell = graph.newResource(); +// graph.claim(newCell, L0.InstanceOf, null, sr.Lines); +// graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING); +// graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell); +// BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true); + + BTree bt = new BTree(graph, Spreadsheets.SPREADSHEET_BTREE_SIZE, sr.Lines, sr.LineNode, L0.PartOf, false); +// BTree bt = BTreeUtils.create(graph, sr.Lines, sr.LineNode, L0.PartOf, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, false); + Resource lines = bt.rootOfBTree(); + + graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING); + graph.claim(result, L0.ConsistsOf, L0.PartOf, lines); + + { + Resource newCell = graph.newResource(); + graph.claim(newCell, L0.InstanceOf, null, sr.Dimensions); + graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING); + graph.addLiteral(newCell, sr.Dimensions_fitColumns, sr.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN); + graph.addLiteral(newCell, sr.Dimensions_fitRows, sr.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN); + graph.addLiteral(newCell, sr.Dimensions_columnCount, sr.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER); + graph.addLiteral(newCell, sr.Dimensions_rowCount, sr.Dimensions_rowCount_Inverse, L0.Integer, 256, Bindings.INTEGER); + graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell); + } + + { + Resource newCell = graph.newResource(); + graph.claim(newCell, L0.InstanceOf, null, sr.Headers); + graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING); + graph.addLiteral(newCell, sr.Headers_columnLabels, sr.Headers_columnLabels_Inverse, L0.StringArray, colNames, Bindings.STRING_ARRAY); + graph.addLiteral(newCell, sr.Headers_columnWidths, sr.Headers_columnWidths_Inverse, L0.IntegerArray, colWidths, Bindings.INT_ARRAY); + graph.claim(result, L0.ConsistsOf, L0.PartOf, newCell); + } + + return result; + + } + + + public static Variable getBookVariable(ReadGraph graph, Resource book) throws DatabaseException { + Variable variable = Variables.getVariable(graph, book); + return ProxyVariables.makeProxyVariable(graph, variable, variable); + } + + public static Variable sheetRun(ReadGraph graph, Resource book, Variable context) throws DatabaseException { + Variable root = Variables.getVariable(graph, book); + return ProxyVariables.makeProxyVariable(graph, root, context); + } + + private static TableCell constructCell(int row, int column, Object data) { + TableCell cell = new TableCell(); + cell.row = row; + cell.column = column; + cell.text = data.toString(); + return cell; + } + + public static List queryCells(Object data) { + ArrayList result = new ArrayList(); + if(data instanceof List) { + List list = (List)data; + int row = 0; + for(Object o : list) { + if(o instanceof Tuple) { + Tuple t = (Tuple)o; + for(int i=0;i rowList = (List)o; + int index = 0; + for(Object obj : rowList) { + result.add(constructCell(row, index++, obj)); + } + } else { + result.add(constructCell(row, 0, o)); + } + row++; + } + } + return result; + } + + public static List organizeCells(int columns, List headers_, List cells) throws DatabaseException { + + ArrayList result = new ArrayList(); + + int w = 0; // name + fields + int h = 0; // number or rows excluding headers + + if(columns < 2) throw new AssumptionException("organizeCells: number of columns needs to be greater than 1"); + + for(TableCell cell : cells) { + if((cell.column+1)>w) w = cell.column+1; + if((cell.row)>h) h = cell.row; + } + + int fields = w - 1; + + 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"); + + int fieldsPerRow = columns - 1; + + int blocks = fields / fieldsPerRow; + if(fields%fieldsPerRow > 0) blocks++; + + TableCell[] names = new TableCell[h]; + TableCell[] headers = new TableCell[w]; + + for(TableCell cell : cells) { + + if(cell.row == 0) { + headers[cell.column] = cell; + } else if(cell.column == 0) { + names[cell.row-1] = cell; + } else { + TableCell copy = new TableCell(cell); + int block = (copy.column-1) / fieldsPerRow; + copy.row = block*(h+1) + copy.row; + copy.column = 1 + (copy.column-1) % fieldsPerRow; + result.add(copy); + } + + } + + for(int j=0;j= w) continue; + + TableCell header = new TableCell(headers[index]); + header.row = rowBase; + header.column = i; + result.add(header); + + } + + } + + return result; + + } + + public static List modifyCells1(List cells, Function1 fn) { + ArrayList result = new ArrayList(); + for(TableCell cell : cells) + result.add(fn.apply(cell)); + return result; + } + + public static List modifyCells(List cells, List> fns) { + ArrayList result = new ArrayList(); + for(TableCell cell : cells) { + for(Function1 fn : fns) + cell = fn.apply(cell); + result.add(cell); + } + return result; + } + + public static TableCell applyFont(IFont font, Function1 filter, TableCell cell) { + if(!filter.apply(cell)) return cell; + TableCell result = new TableCell(cell); + result.font = font; + return result; + } + + public static TableCell applyAlign(int align, Function1 filter, TableCell cell) { + if(!filter.apply(cell)) return cell; + TableCell result = new TableCell(cell); + result.align = align; + return result; + } + + public static TableCell applyForeground(IColor color, Function1 filter, TableCell cell) { + if(!filter.apply(cell)) return cell; + TableCell result = new TableCell(cell); + result.foreground = color; + return result; + } + + public static TableCell applyBackground(IColor color,Function1 filter, TableCell cell) { + if(!filter.apply(cell)) return cell; + TableCell result = new TableCell(cell); + result.background = color; + return result; + } + + public static IFont simpleFont(String family, String style, int height) { + return new SimpleFont(family, style, height); + } + + public static IColor rgbColor(int r, int g, int b) { + return new RGBColor(r, g, b); + } + + public static boolean selectRow(int row, TableCell cell) { + return cell.row == row; + } + + public static boolean selectColumn(int column, TableCell cell) { + return cell.column == column; + } + + public static void setSCLLine(WriteGraph graph, Resource spreadsheet, int row, String expression) throws DatabaseException { + + Layer0 L0 = Layer0.getInstance(graph); + Resource lines = graph.syncRequest(new PossibleChild(spreadsheet, "Lines")); + BTree bt = new BTree(graph, lines); + SpreadsheetResource SR = SpreadsheetResource.getInstance(graph); + + Resource line = graph.newResource(); + graph.claim(line, L0.InstanceOf, SR.Line); + graph.addLiteral(line, L0.HasName, L0.NameOf, "" + row, Bindings.STRING); + Layer0Utils.setExpression(graph, line, SR.Line_content, null, "[spreadsheetCell ]", L0.SCLValue); + bt.insertBTree(graph, Variant.ofInstance(row), line); + + } + + public static String getFormattedLabel(ClientModel model, int row, int column, int formatIndex, String formatString) { + if (formatString == null) + return getLabel(model, row, column); + try { + String location = Spreadsheets.cellName(row, column); + Variant content = SpreadsheetUtils.getSafeClientVariant(model, location, ClientModel.CONTENT); + if(content != null) { + + String contentString = SpreadsheetUtils.getContentString(content); + if(contentString.equals("~CIRCULAR~REF~")) + return "0"; + + double value = Double.valueOf(contentString); + if (Double.isNaN(value)) + return getLabel(model, row, column); + + DataFormatter formatter = new DataFormatter(); + return formatter.formatRawCellContents(value, formatIndex, formatString); + } + return null; + } catch (NumberFormatException e) { + return getLabel(model, row, column); + } catch (Throwable e) { + e.printStackTrace(); + return null; + } + } + + /* + * Please use Spreadsheets.cellName instead + */ + @Deprecated + public static String cellName(int row, int column) { + return Spreadsheets.cellName(row, column); + } + + /* + * Please use Spreadsheets.columnName instead + */ + @Deprecated + public static String columnName(int column) { + return Spreadsheets.columnName(column); + } + + /* + * Please use Spreadsheets.decodeCellAbsolute instead + */ + @Deprecated + public static Range decodeCellAbsolute(String identifier) { + return Spreadsheets.decodeCellAbsolute(identifier); + } + + /* + * Please use Spreadsheets.decodePossibleCellAbsolute instead + */ + @Deprecated + public static Range decodePossibleCellAbsolute(String identifier) { + return Spreadsheets.decodePossibleCellAbsolute(identifier); + } + + /* + * Please use Spreadsheets.decodeRange instead + */ + @Deprecated + public static Range decodeRange(String rangeOrCell) { + return Spreadsheets.decodeRange(rangeOrCell); + } + + /* + * Please use Spreadsheets.decodeRanges instead + */ + @Deprecated + public static List decodeRanges(String ranges) { + return Spreadsheets.decodeRanges(ranges); + } + + /* + * Please use Spreadsheets.startColumn instead + */ + @Deprecated + public static int startColumn(List ranges) { + return Spreadsheets.startColumn(ranges); + } + + /* + * Please use Spreadsheets.startRow instead + */ + @Deprecated + public static int startRow(List ranges) { + return Spreadsheets.startRow(ranges); + } + + /* + * Please use Spreadsheets.offset instead + */ + @Deprecated + public static String offset(String location, int rowOffset, int columnOffset) { + return Spreadsheets.offset(location, rowOffset, columnOffset); + } + + +}