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.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;
  41
  42 public class ExcelLink extends ClientModelListenerAdapter {
  43
  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;
  52     private int handle;
  53
  54     public ExcelLink(Session session, Resource container, ClientModel model, final Resource book, String bookName, String sheetName, String prefix) {
  55
  56         this.session = session;
  57         this.container = container;
  58         this.handle = 0;
  59         this.model = model;
  60         this.bookName = bookName + ".xlsx";
  61         this.sheetName = sheetName;
  62         this.prefix = prefix;
  63         this.book = book;
  64
  65         this.excel = tryGetExcel(book, bookName);
  66
  67                 model.addListener(this);
  68
  69     }
  70
  71     public static Excel tryGetExcel(final Resource book, final String bookName) {
  72
  73         try {
  74
  75                 Excel excel = Excel.getInstance(System.out);
  76                 String file = excel.getFile(bookName);
  77                 final File tester = new File(file);
  78
  79                 if(!tester.exists()) {
  80                         // Restore file from graph
  81                         try {
  82                                 byte[] saved = Simantics.getSession().syncRequest(new ResourceRead<byte[]>(book) {
  83
  84                                         @Override
  85                                         public byte[] perform(ReadGraph graph) throws DatabaseException {
  86                                                 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
  87                                                 return graph.getPossibleRelatedValue(book, SR.HasMicrosoftExcelDocumentData, Bindings.BYTE_ARRAY);
  88                                         }
  89
  90                                 });
  91                                 if(saved != null) FileUtils.writeFile(tester, saved);
  92                         } catch (DatabaseException e) {
  93                                 Logger.defaultLogError(e);
  94                         } catch (IOException e) {
  95                                 Logger.defaultLogError(e);
  96                         }
  97                 }
  98
  99                 return excel;
 100
 101         } catch (ExcelException e1) {
 102
 103                 Logger.defaultLogError(e1);
 104
 105         }
 106
 107         return null;
 108
 109     }
 110
 111     public void dispose() {
 112
 113         model.removeListener(this);
 114
 115                 try {
 116
 117                         if(excel != null) {
 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);
 123                                         if(saved != null) {
 124                                                 Simantics.getSession().syncRequest(new WriteRequest() {
 125
 126                                                         @Override
 127                                                         public void perform(WriteGraph graph) throws DatabaseException {
 128                                                                 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
 129                                                                 graph.claimLiteral(book, SR.HasMicrosoftExcelDocumentData, saved, Bindings.BYTE_ARRAY);
 130                                                         }
 131
 132                                                 });
 133                                         }
 134                                 }
 135                         }
 136
 137                 } catch (DatabaseException e) {
 138                         Logger.defaultLogError(e);
 139                 } catch (IOException e) {
 140                         Logger.defaultLogError(e);
 141                 }
 142
 143     }
 144
 145         @Override
 146         public void propertyChange(String location, String property, Object value) {
 147
 148                 if(ClientModel.CONTENT.equals(property) && value != null && value instanceof Variant) {
 149
 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);
 154
 155                         session.asyncRequest(new WriteRequest() {
 156
 157                                 @Override
 158                                 public void perform(WriteGraph graph) throws DatabaseException {
 159
 160                                         String[] parts = (modis+"0").split("#");
 161
 162                                         Range range = SpreadsheetUtils.decodeRange(parts[0]);
 163
 164                                 Layer0 L0 = Layer0.getInstance(graph);
 165                                 SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph);
 166
 167                                         int index = 1;
 168                                         for(int i=0;i<range.height();i++) {
 169                                                 for(int j=0;j<range.width();j++) {
 170
 171                                                         String addr = SpreadsheetUtils.cellName(range.startRow + i, range.startColumn + j);
 172                                                         String content = parts[index++];
 173
 174                                                         Resource cell = Layer0Utils.getPossibleChild(graph, container, addr);
 175                                                         if(cell != null) {
 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);
 180                                                                 }
 181                                                         } else {
 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);
 187                                                         }
 188
 189                                                 }
 190                                         }
 191
 192                                         for(int i=1;i<parts.length;i++) {
 193 //                                              String addr = parts[i];
 194 //                                              String content = parts[i+1];
 195 //
 196 //                                      Layer0 L0 = Layer0.getInstance(graph);
 197 //                                      SpreadsheetResource SHEET = SpreadsheetResource.getInstance(graph);
 198 //
 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);
 205 //                                      } else {
 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);
 211 //                                      }
 212
 213
 214                                         }
 215
 216
 217                                 }
 218
 219                         });
 220
 221 //                      System.err.println("excel.setString " + value);
 222
 223                 } else if("Excel".equals(location) && "Visible".equals(property) && value instanceof Boolean) {
 224
 225                         Boolean visible = (Boolean)value;
 226                         if(visible) {
 227
 228                         try {
 229                                 if(handle == 0) {
 230
 231                                         Excel excel = Excel.getInstance(System.out);
 232                                         String file = excel.getFile(bookName);
 233                                         FileUtils.ensureParentDirectoryExists(file);
 234
 235                                         //String sheetName2 = sheetName;//(sheetName + UUID.randomUUID().toString()).substring(0,30);
 236                                         String sheetName2 = (bookName + "_" + sheetName).substring(0,30).replace(".", "_");
 237
 238                                         String handleString = excel.open_(file, sheetName2);
 239                                         try {
 240                                                 handle = Integer.valueOf(handleString);
 241                                         } catch (NumberFormatException e) {
 242                                                 ShowMessage.showError("Problems with Excel", handleString);
 243                                                 Logger.defaultLogError(new RuntimeException(handleString));
 244                                                 return;
 245                                         }
 246 //                                      System.err.println("excel.open " + (name + ".xlsx"));
 247
 248                                                 for(Pair<String, Object> label : model.listAll(ClientModel.LABEL)) {
 249                                                         try {
 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) {
 254                                                         }
 255                                                 }
 256                                                 for(Pair<String, Object> label : model.listAll(ClientModel.CONTENT)) {
 257                                                         try {
 258                                                                 Range range = SpreadsheetUtils.decodeCellAbsolute(label.first);
 259                                                                 //excel.setString_(handle, range.startRow, range.startColumn, (String)label.second);
 260
 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 + "'");
 266                                                                 }
 267
 268                                                         } catch (CellParseException e) {
 269                                                         }
 270                                                 }
 271
 272                                 }
 273                         } catch (Throwable t) {
 274                                 Logger.defaultLogError(t);
 275                         }
 276
 277                         } else {
 278
 279                         try {
 280 //                              doClose();
 281                                 if(handle != 0) {
 282                                         Excel excel = Excel.getInstance(System.out);
 283                                         handle = excel.close_(handle);
 284                                         handle = 0;
 285                                 }
 286                         } catch (Throwable t) {
 287                                 Logger.defaultLogError(t);
 288                         }
 289
 290                         }
 291 //                      excel.setVisible_(handle, (Boolean)value);
 292 //                      System.err.println("excel.setVisible " + value);
 293                 }
 294         }
 295
 296 //    @Override
 297 //    public void changed(int row, int column, Cell cell) {
 298 //
 299 //        String value = cell != null ? cell.toString() : "";
 300 //        if(value == null) value = "...";
 301 //        excel.setString_(handle, row, column, value);
 302 //
 303 //    }
 304
 305 }