bundles/org.simantics.spreadsheet.ui/src/org/simantics/spreadsheet/ui/editor/ExcelLink.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.ui.editor;
  13
  14 import java.io.File;
  15 import java.io.IOException;
  16
  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.CellParseException;
  33 import org.simantics.spreadsheet.ClientModel;
  34 import org.simantics.spreadsheet.Range;
  35 import org.simantics.spreadsheet.Spreadsheets;
  36 import org.simantics.spreadsheet.common.client.ClientModelListenerAdapter;
  37 import org.simantics.spreadsheet.resource.SpreadsheetResource;
  38 import org.simantics.spreadsheet.util.SpreadsheetUtils;
  39 import org.simantics.utils.FileUtils;
  40 import org.simantics.utils.datastructures.Pair;
  41 import org.simantics.utils.ui.dialogs.ShowMessage;
  42
  43 public class ExcelLink extends ClientModelListenerAdapter {
  44
  45         final private Session session;
  46         final private Resource container;
  47     final private ClientModel model;
  48     final private Resource book;
  49     final private String bookName;
  50     final private String sheetName;
  51     final private String prefix;
  52     final private Excel excel;
  53     private int handle;
  54
  55     public ExcelLink(Session session, Resource container, ClientModel model, final Resource book, String bookName, String sheetName, String prefix) {
  56
  57         this.session = session;
  58         this.container = container;
  59         this.handle = 0;
  60         this.model = model;
  61         this.bookName = bookName + ".xlsx";
  62         this.sheetName = sheetName;
  63         this.prefix = prefix;
  64         this.book = book;
  65
  66         this.excel = tryGetExcel(book, bookName);
  67
  68                 model.addListener(this);
  69
  70     }
  71
  72     public static Excel tryGetExcel(final Resource book, final String bookName) {
  73
  74         try {
  75
  76                 Excel excel = Excel.getInstance(System.out);
  77                 String file = excel.getFile(bookName);
  78                 final File tester = new File(file);
  79
  80                 if(!tester.exists()) {
  81                         // Restore file from graph
  82                         try {
  83                                 byte[] saved = Simantics.getSession().syncRequest(new ResourceRead<byte[]>(book) {
  84
  85                                         @Override
  86                                         public byte[] perform(ReadGraph graph) throws DatabaseException {
  87                                                 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
  88                                                 return graph.getPossibleRelatedValue(book, SR.HasMicrosoftExcelDocumentData, Bindings.BYTE_ARRAY);
  89                                         }
  90
  91                                 });
  92                                 if(saved != null) FileUtils.writeFile(tester, saved);
  93                         } catch (DatabaseException e) {
  94                                 Logger.defaultLogError(e);
  95                         } catch (IOException e) {
  96                                 Logger.defaultLogError(e);
  97                         }
  98                 }
  99
 100                 return excel;
 101
 102         } catch (ExcelException e1) {
 103
 104                 Logger.defaultLogError(e1);
 105
 106         }
 107
 108         return null;
 109
 110     }
 111
 112     public void dispose() {
 113
 114         model.removeListener(this);
 115
 116                 try {
 117
 118                         if(excel != null) {
 119                         if(handle != 0) excel.close_(handle);
 120                                 String file = excel.getFile(bookName);
 121                                 File tester = new File(file);
 122                                 if(tester.exists()) {
 123                                         final byte[] saved = FileUtils.readFile(tester);
 124                                         if(saved != null) {
 125                                                 Simantics.getSession().syncRequest(new WriteRequest() {
 126
 127                                                         @Override
 128                                                         public void perform(WriteGraph graph) throws DatabaseException {
 129                                                                 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
 130                                                                 graph.claimLiteral(book, SR.HasMicrosoftExcelDocumentData, saved, Bindings.BYTE_ARRAY);
 131                                                         }
 132
 133                                                 });
 134                                         }
 135                                 }
 136                         }
 137
 138                 } catch (DatabaseException e) {
 139                         Logger.defaultLogError(e);
 140                 } catch (IOException e) {
 141                         Logger.defaultLogError(e);
 142                 }
 143
 144     }
 145
 146         @Override
 147         public void propertyChange(String location, String property, Object value) {
 148
 149                 if(ClientModel.CONTENT.equals(property) && value != null && value instanceof Variant) {
 150
 151                         if(handle == 0) return;
 152                         Range range = Spreadsheets.decodeCellAbsolute(location);
 153                         excel.setString_(handle, range.startRow, range.startColumn, ((Variant)value).getValue().toString());
 154                         final String modis = excel.getModifications_(handle);
 155
 156                         session.asyncRequest(new WriteRequest() {
 157
 158                                 @Override
 159                                 public void perform(WriteGraph graph) throws DatabaseException {
 160
 161                                         String[] parts = (modis+"0").split("#");
 162
 163                                         Range range = Spreadsheets.decodeRange(parts[0]);
 164
 165                                 Layer0 L0 = Layer0.getInstance(graph);
 166                                 SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph);
 167
 168                                         int index = 1;
 169                                         for(int i=0;i<range.height();i++) {
 170                                                 for(int j=0;j<range.width();j++) {
 171
 172                                                         String addr = Spreadsheets.cellName(range.startRow + i, range.startColumn + j);
 173                                                         String content = parts[index++];
 174
 175                                                         Resource cell = Layer0Utils.getPossibleChild(graph, container, addr);
 176                                                         if(cell != null) {
 177                                                                 Variant newValue = Variant.ofInstance(content);
 178                                                                 Variant existing = graph.getRelatedValue(cell, SHEET.Cell_content, Bindings.VARIANT);
 179                                                                 if(!newValue.equals(existing)) {
 180                                                                         graph.claimLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, newValue, Bindings.VARIANT);
 181                                                                 }
 182                                                         } else {
 183                                                                 cell = graph.newResource();
 184                                                                 graph.claim(cell, L0.InstanceOf, null, SHEET.TextCell);
 185                                                                 graph.addLiteral(cell, L0.HasName, L0.NameOf, L0.String, addr, Bindings.STRING);
 186                                                                 graph.addLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, Variant.ofInstance(content), Bindings.VARIANT);
 187                                                                 graph.claim(cell, L0.PartOf, container);
 188                                                         }
 189
 190                                                 }
 191                                         }
 192
 193                                         for(int i=1;i<parts.length;i++) {
 194 //                                              String addr = parts[i];
 195 //                                              String content = parts[i+1];
 196 //
 197 //                                      Layer0 L0 = Layer0.getInstance(graph);
 198 //                                      SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph);
 199 //
 200 //                                      Resource cell = Layer0Utils.getPossibleChild(graph, container, addr);
 201 //                                      if(cell != null) {
 202 //                                              Variant newValue = Variant.ofInstance(content);
 203 //                                              Variant existing = graph.getRelatedValue(cell, SHEET.Cell_content, Bindings.VARIANT);
 204 //                                              if(!newValue.equals(existing))
 205 //                                                      graph.claimLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, newValue, Bindings.VARIANT);
 206 //                                      } else {
 207 //                                              cell = graph.newResource();
 208 //                                              graph.claim(cell, L0.InstanceOf, null, SHEET.TextCell);
 209 //                                              graph.addLiteral(cell, L0.HasName, L0.NameOf, L0.String, addr, Bindings.STRING);
 210 //                                              graph.addLiteral(cell, SHEET.Cell_content, SHEET.Cell_content_Inverse, L0.Variant, Variant.ofInstance(content), Bindings.VARIANT);
 211 //                                              graph.claim(cell, L0.PartOf, container);
 212 //                                      }
 213
 214
 215                                         }
 216
 217
 218                                 }
 219
 220                         });
 221
 222 //                      System.err.println("excel.setString " + value);
 223
 224                 } else if("Excel".equals(location) && "Visible".equals(property) && value instanceof Boolean) {
 225
 226                         Boolean visible = (Boolean)value;
 227                         if(visible) {
 228
 229                         try {
 230                                 if(handle == 0) {
 231
 232                                         Excel excel = Excel.getInstance(System.out);
 233                                         String file = excel.getFile(bookName);
 234                                         FileUtils.ensureParentDirectoryExists(file);
 235
 236                                         //String sheetName2 = sheetName;//(sheetName + UUID.randomUUID().toString()).substring(0,30);
 237                                         String sheetName2 = (bookName + "_" + sheetName).substring(0,30).replace(".", "_");
 238
 239                                         String handleString = excel.open_(file, sheetName2);
 240                                         try {
 241                                                 handle = Integer.valueOf(handleString);
 242                                         } catch (NumberFormatException e) {
 243                                                 ShowMessage.showError("Problems with Excel", handleString);
 244                                                 Logger.defaultLogError(new RuntimeException(handleString));
 245                                                 return;
 246                                         }
 247 //                                      System.err.println("excel.open " + (name + ".xlsx"));
 248
 249                                                 for(Pair<String, Object> label : model.listAll(ClientModel.LABEL)) {
 250                                                         try {
 251                                                                 Range range = Spreadsheets.decodeCellAbsolute(label.first);
 252                                                                 excel.setString_(handle, range.startRow, range.startColumn, (String)label.second);
 253 //                                                              System.err.println("excel.setString " + label.second);
 254                                                         } catch (CellParseException e) {
 255                                                         }
 256                                                 }
 257                                                 for(Pair<String, Object> label : model.listAll(ClientModel.CONTENT)) {
 258                                                         try {
 259                                                                 Range range = Spreadsheets.decodeCellAbsolute(label.first);
 260                                                                 //excel.setString_(handle, range.startRow, range.startColumn, (String)label.second);
 261
 262                                                                 String uri = (String)label.second;
 263                                                                 if(uri.startsWith(prefix)) {
 264                                                                         String rvi = uri.substring(prefix.length()+1).replace("#", "_").replace("(", "_").replace(")", "_").replace("+", "p").replace("-", "m");
 265                                                                         excel.setName_(handle, range.startRow, range.startColumn, rvi);
 266 //                                                                      System.err.println("excel.setCellName '" + rvi + "'");
 267                                                                 }
 268
 269                                                         } catch (CellParseException e) {
 270                                                         }
 271                                                 }
 272
 273                                 }
 274                         } catch (Throwable t) {
 275                                 Logger.defaultLogError(t);
 276                         }
 277
 278                         } else {
 279
 280                         try {
 281 //                              doClose();
 282                                 if(handle != 0) {
 283                                         Excel excel = Excel.getInstance(System.out);
 284                                         handle = excel.close_(handle);
 285                                         handle = 0;
 286                                 }
 287                         } catch (Throwable t) {
 288                                 Logger.defaultLogError(t);
 289                         }
 290
 291                         }
 292 //                      excel.setVisible_(handle, (Boolean)value);
 293 //                      System.err.println("excel.setVisible " + value);
 294                 }
 295         }
 296
 297 //    @Override
 298 //    public void changed(int row, int column, Cell cell) {
 299 //
 300 //        String value = cell != null ? cell.toString() : "";
 301 //        if(value == null) value = "...";
 302 //        excel.setString_(handle, row, column, value);
 303 //
 304 //    }
 305
 306 }