bundles/org.simantics.spreadsheet.ui/src/org/simantics/spreadsheet/ui/editor/ExcelLink.java

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