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.ui.editor;
15 import java.io.IOException;
17 import org.simantics.Simantics;
18 import org.simantics.databoard.Bindings;
19 import org.simantics.databoard.binding.mutable.Variant;
20 import org.simantics.db.ReadGraph;
21 import org.simantics.db.Resource;
22 import org.simantics.db.Session;
23 import org.simantics.db.WriteGraph;
24 import org.simantics.db.common.request.ResourceRead;
25 import org.simantics.db.common.request.WriteRequest;
26 import org.simantics.db.common.utils.Logger;
27 import org.simantics.db.exception.DatabaseException;
28 import org.simantics.db.layer0.util.Layer0Utils;
29 import org.simantics.excel.Excel;
30 import org.simantics.excel.ExcelException;
31 import org.simantics.layer0.Layer0;
32 import org.simantics.spreadsheet.ClientModel;
33 import org.simantics.spreadsheet.Range;
34 import org.simantics.spreadsheet.common.client.ClientModelListenerAdapter;
35 import org.simantics.spreadsheet.common.exception.CellParseException;
36 import org.simantics.spreadsheet.resource.SpreadsheetResource;
37 import org.simantics.spreadsheet.util.SpreadsheetUtils;
38 import org.simantics.utils.FileUtils;
39 import org.simantics.utils.datastructures.Pair;
40 import org.simantics.utils.ui.dialogs.ShowMessage;
42 public class ExcelLink extends ClientModelListenerAdapter {
44 final private Session session;
45 final private Resource container;
46 final private ClientModel model;
47 final private Resource book;
48 final private String bookName;
49 final private String sheetName;
50 final private String prefix;
51 final private Excel excel;
54 public ExcelLink(Session session, Resource container, ClientModel model, final Resource book, String bookName, String sheetName, String prefix) {
56 this.session = session;
57 this.container = container;
60 this.bookName = bookName + ".xlsx";
61 this.sheetName = sheetName;
65 this.excel = tryGetExcel(book, bookName);
67 model.addListener(this);
71 public static Excel tryGetExcel(final Resource book, final String bookName) {
75 Excel excel = Excel.getInstance(System.out);
76 String file = excel.getFile(bookName);
77 final File tester = new File(file);
79 if(!tester.exists()) {
80 // Restore file from graph
82 byte[] saved = Simantics.getSession().syncRequest(new ResourceRead<byte[]>(book) {
85 public byte[] perform(ReadGraph graph) throws DatabaseException {
86 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
87 return graph.getPossibleRelatedValue(book, SR.HasMicrosoftExcelDocumentData, Bindings.BYTE_ARRAY);
91 if(saved != null) FileUtils.writeFile(tester, saved);
92 } catch (DatabaseException e) {
93 Logger.defaultLogError(e);
94 } catch (IOException e) {
95 Logger.defaultLogError(e);
101 } catch (ExcelException e1) {
103 Logger.defaultLogError(e1);
111 public void dispose() {
113 model.removeListener(this);
118 if(handle != 0) excel.close_(handle);
119 String file = excel.getFile(bookName);
120 File tester = new File(file);
121 if(tester.exists()) {
122 final byte[] saved = FileUtils.readFile(tester);
124 Simantics.getSession().syncRequest(new WriteRequest() {
127 public void perform(WriteGraph graph) throws DatabaseException {
128 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
129 graph.claimLiteral(book, SR.HasMicrosoftExcelDocumentData, saved, Bindings.BYTE_ARRAY);
137 } catch (DatabaseException e) {
138 Logger.defaultLogError(e);
139 } catch (IOException e) {
140 Logger.defaultLogError(e);
146 public void propertyChange(String location, String property, Object value) {
148 if(ClientModel.CONTENT.equals(property) && value != null && value instanceof Variant) {
150 if(handle == 0) return;
151 Range range = SpreadsheetUtils.decodeCellAbsolute(location);
152 excel.setString_(handle, range.startRow, range.startColumn, ((Variant)value).getValue().toString());
153 final String modis = excel.getModifications_(handle);
155 session.asyncRequest(new WriteRequest() {
158 public void perform(WriteGraph graph) throws DatabaseException {
160 String[] parts = (modis+"0").split("#");
162 Range range = SpreadsheetUtils.decodeRange(parts[0]);
164 Layer0 L0 = Layer0.getInstance(graph);
165 SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph);
168 for(int i=0;i<range.height();i++) {
169 for(int j=0;j<range.width();j++) {
171 String addr = SpreadsheetUtils.cellName(range.startRow + i, range.startColumn + j);
172 String content = parts[index++];
174 Resource cell = Layer0Utils.getPossibleChild(graph, container, addr);
176 Variant newValue = Variant.ofInstance(content);
177 Variant existing = graph.getRelatedValue(cell, SHEET.Cell_content, Bindings.VARIANT);
178 if(!newValue.equals(existing)) {
179 graph.claimLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, newValue, Bindings.VARIANT);
182 cell = graph.newResource();
183 graph.claim(cell, L0.InstanceOf, null, SHEET.TextCell);
184 graph.addLiteral(cell, L0.HasName, L0.NameOf, L0.String, addr, Bindings.STRING);
185 graph.addLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, Variant.ofInstance(content), Bindings.VARIANT);
186 graph.claim(cell, L0.PartOf, container);
192 for(int i=1;i<parts.length;i++) {
193 // String addr = parts[i];
194 // String content = parts[i+1];
196 // Layer0 L0 = Layer0.getInstance(graph);
197 // SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph);
199 // Resource cell = Layer0Utils.getPossibleChild(graph, container, addr);
200 // if(cell != null) {
201 // Variant newValue = Variant.ofInstance(content);
202 // Variant existing = graph.getRelatedValue(cell, SHEET.Cell_content, Bindings.VARIANT);
203 // if(!newValue.equals(existing))
204 // graph.claimLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, newValue, Bindings.VARIANT);
206 // cell = graph.newResource();
207 // graph.claim(cell, L0.InstanceOf, null, SHEET.TextCell);
208 // graph.addLiteral(cell, L0.HasName, L0.NameOf, L0.String, addr, Bindings.STRING);
209 // graph.addLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, Variant.ofInstance(content), Bindings.VARIANT);
210 // graph.claim(cell, L0.PartOf, container);
221 // System.err.println("excel.setString " + value);
223 } else if("Excel".equals(location) && "Visible".equals(property) && value instanceof Boolean) {
225 Boolean visible = (Boolean)value;
231 Excel excel = Excel.getInstance(System.out);
232 String file = excel.getFile(bookName);
233 FileUtils.ensureParentDirectoryExists(file);
235 //String sheetName2 = sheetName;//(sheetName + UUID.randomUUID().toString()).substring(0,30);
236 String sheetName2 = (bookName + "_" + sheetName).substring(0,30).replace(".", "_");
238 String handleString = excel.open_(file, sheetName2);
240 handle = Integer.valueOf(handleString);
241 } catch (NumberFormatException e) {
242 ShowMessage.showError("Problems with Excel", handleString);
243 Logger.defaultLogError(new RuntimeException(handleString));
246 // System.err.println("excel.open " + (name + ".xlsx"));
248 for(Pair<String, Object> label : model.listAll(ClientModel.LABEL)) {
250 Range range = SpreadsheetUtils.decodeCellAbsolute(label.first);
251 excel.setString_(handle, range.startRow, range.startColumn, (String)label.second);
252 // System.err.println("excel.setString " + label.second);
253 } catch (CellParseException e) {
256 for(Pair<String, Object> label : model.listAll(ClientModel.CONTENT)) {
258 Range range = SpreadsheetUtils.decodeCellAbsolute(label.first);
259 //excel.setString_(handle, range.startRow, range.startColumn, (String)label.second);
261 String uri = (String)label.second;
262 if(uri.startsWith(prefix)) {
263 String rvi = uri.substring(prefix.length()+1).replace("#", "_").replace("(", "_").replace(")", "_").replace("+", "p").replace("-", "m");
264 excel.setName_(handle, range.startRow, range.startColumn, rvi);
265 // System.err.println("excel.setCellName '" + rvi + "'");
268 } catch (CellParseException e) {
273 } catch (Throwable t) {
274 Logger.defaultLogError(t);
282 Excel excel = Excel.getInstance(System.out);
283 handle = excel.close_(handle);
286 } catch (Throwable t) {
287 Logger.defaultLogError(t);
291 // excel.setVisible_(handle, (Boolean)value);
292 // System.err.println("excel.setVisible " + value);
297 // public void changed(int row, int column, Cell cell) {
299 // String value = cell != null ? cell.toString() : "";
300 // if(value == null) value = "...";
301 // excel.setString_(handle, row, column, value);