bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/ExcelImport.java

   1 package org.simantics.spreadsheet.graph;
   2
   3 import java.io.File;
   4 import java.io.FileInputStream;
   5 import java.io.StringReader;
   6 import java.util.ArrayList;
   7 import java.util.Collections;
   8 import java.util.HashMap;
   9 import java.util.List;
  10 import java.util.Map;
  11
  12 import org.apache.poi.ss.usermodel.Cell;
  13 import org.apache.poi.ss.usermodel.CellStyle;
  14 import org.apache.poi.ss.usermodel.Row;
  15 import org.apache.poi.ss.usermodel.Sheet;
  16 import org.apache.poi.ss.usermodel.Workbook;
  17 import org.apache.poi.ss.usermodel.WorkbookFactory;
  18 import org.apache.poi.ss.util.CellRangeAddress;
  19 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
  20 import org.apache.poi.xssf.usermodel.XSSFColor;
  21 import org.apache.poi.xssf.usermodel.XSSFFont;
  22 import org.simantics.Simantics;
  23 import org.simantics.databoard.Bindings;
  24 import org.simantics.databoard.binding.mutable.Variant;
  25 import org.simantics.datatypes.literal.Font;
  26 import org.simantics.datatypes.literal.RGB;
  27 import org.simantics.datatypes.utils.BTree;
  28 import org.simantics.db.Resource;
  29 import org.simantics.db.WriteGraph;
  30 import org.simantics.db.common.request.DelayedWriteRequest;
  31 import org.simantics.db.common.request.WriteResultRequest;
  32 import org.simantics.db.common.utils.NameUtils;
  33 import org.simantics.db.exception.BindingException;
  34 import org.simantics.db.exception.DatabaseException;
  35 import org.simantics.db.exception.ManyObjectsForFunctionalRelationException;
  36 import org.simantics.db.exception.ServiceException;
  37 import org.simantics.db.indexing.DatabaseIndexing;
  38 import org.simantics.db.layer0.util.Layer0Utils;
  39 import org.simantics.db.service.XSupport;
  40 import org.simantics.layer0.Layer0;
  41 import org.simantics.operation.Layer0X;
  42 import org.simantics.spreadsheet.Spreadsheets;
  43 import org.simantics.spreadsheet.resource.SpreadsheetResource;
  44 import org.simantics.spreadsheet.solver.SpreadsheetBook;
  45 import org.simantics.spreadsheet.solver.SpreadsheetStyle;
  46 import org.simantics.spreadsheet.solver.SpreadsheetStyle.SpreadsheetStyleBuilder;
  47 import org.simantics.spreadsheet.solver.formula.CellValueVisitor;
  48 import org.simantics.spreadsheet.solver.formula.SpreadsheetEvaluationEnvironment;
  49 import org.simantics.spreadsheet.solver.formula.parser.SheetFormulaParser;
  50 import org.simantics.spreadsheet.solver.formula.parser.ast.AstValue;
  51 import org.simantics.spreadsheet.synchronization.ExcelArrayFormula;
  52 import org.simantics.spreadsheet.synchronization.ExcelFormula;
  53 import org.simantics.utils.DataContainer;
  54 import org.slf4j.Logger;
  55 import org.slf4j.LoggerFactory;
  56
  57 public class ExcelImport {
  58
  59     private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImport.class);
  60
  61         private static final double POINT_TO_PIXEL_RATIO = 1.33;
  62
  63         public static void importBook(Resource container, File file) {
  64             importBookR(container, file);
  65         }
  66
  67     public static Resource importBookR(Resource container, File file) {
  68
  69         try {
  70
  71             DataContainer<List<BTree>> btreeContainer = new DataContainer<>();
  72             DataContainer<Resource> bookContainer = new DataContainer<>();
  73
  74             Simantics.getSession().sync(new DelayedWriteRequest() {
  75
  76                 @Override
  77                 public void perform(WriteGraph graph) throws DatabaseException {
  78                     Layer0Utils.setDependenciesIndexingDisabled(graph, true);
  79
  80                     List<BTree> result = new ArrayList<>();
  81                     try {
  82                         FileInputStream fis = new FileInputStream(file);
  83                         Workbook workBook = WorkbookFactory.create(fis);
  84                         fis.close();
  85
  86                         Layer0 L0 = Layer0.getInstance(graph);
  87                         SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
  88
  89                         String originalFileName = file.getName();
  90                         int lastDot = originalFileName.lastIndexOf('.');
  91                         String suggestion = originalFileName;
  92                         if(lastDot!=-1)
  93                                 suggestion = originalFileName.substring(0, lastDot);
  94
  95                         String uniqueName = NameUtils.findFreshEscapedName(graph, suggestion, container);
  96
  97                         Resource book = SpreadsheetGraphUtils.createBook(graph, container, uniqueName);
  98
  99                      // Initial empty style for every book
 100 //                        Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, SpreadsheetStyle.empty());
 101
 102                         Map<Integer, Resource> existingStyles = new HashMap<>();
 103                         Map<Integer, SpreadsheetStyle> existingStyles2 = new HashMap<>();
 104
 105 //                        Collection<Resource> styles = graph.syncRequest(new ObjectsWithType(book, L0.ConsistsOf, SR.Style));
 106 //                        for (Resource eStyle : styles) {
 107 //                            int styleId = graph.getRelatedValue2(eStyle, SR.Style_id, Bindings.INTEGER);
 108 //                            existingStyles.put(styleId, eStyle);
 109 //                        }
 110
 111                         for(int sheetNumber = 0; sheetNumber < workBook.getNumberOfSheets(); sheetNumber++) {
 112
 113                             Sheet sheet_ = workBook.getSheetAt(sheetNumber);
 114                             String sheetName = sheet_.getSheetName();
 115
 116                             Resource sheet = graph.newResource();
 117                             graph.claim(sheet, L0.InstanceOf, SR.Spreadsheet);
 118                             graph.claimLiteral(sheet, L0.HasName, L0.NameOf, L0.String, sheetName, Bindings.STRING);
 119                             graph.claim(book, L0.ConsistsOf, sheet);
 120
 121                             {
 122                                 Resource newCell = graph.newResource();
 123                                 graph.claim(newCell, L0.InstanceOf, null, SR.Dimensions);
 124                                 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);
 125                                 graph.addLiteral(newCell, SR.Dimensions_fitColumns, SR.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
 126                                 graph.addLiteral(newCell, SR.Dimensions_fitRows, SR.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
 127                                 graph.addLiteral(newCell, SR.Dimensions_columnCount, SR.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);
 128                                 graph.addLiteral(newCell, SR.Dimensions_rowCount, SR.Dimensions_rowCount_Inverse, L0.Integer, 4096, Bindings.INTEGER);
 129                                 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
 130                             }
 131
 132                             BTree bt = new BTree(graph, Spreadsheets.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);
 133                             result.add(bt);
 134                             Resource lines = bt.rootOfBTree();
 135
 136                             graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
 137                             graph.claim(sheet, L0.ConsistsOf, L0.PartOf, lines);
 138
 139                             List<Integer> columnWidths = new ArrayList<>();
 140                             List<Integer> rowHeights = new ArrayList<>();
 141
 142                             for(int rowN = 0;rowN<=sheet_.getLastRowNum();rowN++) {
 143
 144                                 Row row = sheet_.getRow(rowN);
 145                                 if(row == null) continue;
 146
 147                                 boolean hasSomething = false;
 148                                 int count = row.getLastCellNum();
 149                                 for(int i=0;i<count;i++) {
 150                                     Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
 151                                     if(val != null) {
 152                                         hasSomething = true;
 153                                         break;
 154                                     }
 155                                 }
 156
 157                                 if(!hasSomething) continue;
 158
 159                                 Resource line = graph.newResource();
 160                                 graph.claim(line, L0.InstanceOf, null, SR.Line);
 161                                 graph.claimLiteral(line, L0.HasName, L0.NameOf, L0.String, "Row" + rowN, Bindings.STRING);
 162                                 bt.insertBTree(graph, Variant.ofInstance(rowN), line);
 163
 164                                 if(rowHeights.size()<=rowN){
 165                                         for(int zz = rowHeights.size(); zz <= rowN; zz++)
 166                                                 rowHeights.add(null);
 167                                 }
 168                                 if(rowHeights.get(rowN)==null){
 169                                         Double rowHeightInPoints = row.getHeight()/20.0;
 170                                     Double rowHeightInPixels = rowHeightInPoints*POINT_TO_PIXEL_RATIO+1;
 171                                     int rH = rowHeightInPixels.intValue();
 172                                                         //System.out.println("rowHeightInPixels: " + rH);
 173                                         rowHeights.set(rowN, rH);
 174                                 }
 175
 176                                 for(int i=0;i<count;i++) {
 177                                     Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
 178                                     if(val != null) {
 179
 180                                         String cellName = Spreadsheets.cellName(rowN, i);
 181
 182                                         int ccIndx = val.getColumnIndex();
 183                                         if(columnWidths.size()<=ccIndx){
 184                                                 for(int zz = columnWidths.size(); zz <= ccIndx; zz++)
 185                                                         columnWidths.add(null);
 186                                         }
 187                                         if(columnWidths.get(ccIndx)==null){
 188                                                 Integer ccWidth = sheet_.getColumnWidth(ccIndx);
 189                                                 Double characterWidth = null;
 190                                                 Double pixelWidthD = null;
 191                                                 if(ccWidth<=438) {
 192                                                         characterWidth = (ccWidth/256.0)/(1.0+181.92/256.0);
 193                                                         characterWidth = Math.round(characterWidth*100)/100.0;
 194                                                         pixelWidthD = (characterWidth*11.986)+0.0078;
 195                                                 }
 196                                                 else {
 197                                                         characterWidth = (ccWidth-181.92)/256.0;
 198                                                         characterWidth = Math.round(characterWidth*100)/100.0;
 199                                                         pixelWidthD = (characterWidth*7.0)+5.0;
 200                                                 }
 201                                                 columnWidths.set(ccIndx, (int)Math.round(pixelWidthD));
 202                                         }
 203
 204                                         Resource cell = graph.newResource();
 205                                         graph.claim(cell, L0.InstanceOf, null, SR.TextCell);
 206                                         graph.claimLiteral(cell, L0.HasName, L0.NameOf, L0.String, cellName, Bindings.STRING);
 207                                         graph.claim(line, L0.ConsistsOf, L0.PartOf, cell);
 208
 209                                         if(Cell.CELL_TYPE_BOOLEAN == val.getCellType()){
 210                                             boolean bool = val.getBooleanCellValue();
 211                                             graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(bool), Bindings.VARIANT);
 212                                         }
 213                                         else if(Cell.CELL_TYPE_FORMULA == val.getCellType()) {
 214                                             Variant v = null;
 215                                             String formula = val.getCellFormula();
 216                                             if(val.isPartOfArrayFormulaGroup()) {
 217                                                 CellRangeAddress addr = val.getArrayFormulaRange();
 218                                                 v = new Variant(ExcelArrayFormula.BINDING, new ExcelArrayFormula(addr.formatAsString(), formula));
 219                                             } else {
 220                                                 v = new Variant(ExcelFormula.BINDING, new ExcelFormula(formula));
 221                                             }
 222                                             graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, v, Bindings.VARIANT);
 223
 224                                         } else if(Cell.CELL_TYPE_STRING == val.getCellType()) {
 225                                             graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
 226                                         } else if(Cell.CELL_TYPE_NUMERIC == val.getCellType()) {
 227                                             Double value = Double.parseDouble(val.toString());
 228                                             graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(value), Bindings.VARIANT);
 229                                         } else {
 230                                             graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
 231                                             System.err.println("Unprocessed cell type " + val.getCellType() + ", SheetName: " + sheetName + ", Row: " + rowN + ", Col:" + i);
 232                                         }
 233 //                                    System.err.println("Current cell " + sheetName + ":"+ cellName);
 234                                         Resource style = assignStyles(graph, SR, val, book, existingStyles, existingStyles2, "Style_" + existingStyles.size());
 235                                         if (style != null)
 236                                             graph.claim(cell, SR.Cell_HasStyle, style);
 237
 238                                     }
 239                                 }
 240                             }
 241                             {
 242                                 int[] cw = new int[(columnWidths.size())];
 243                                 int[] rw = new int[(rowHeights.size())];
 244                                 for(int i = 0; i<columnWidths.size();i++){
 245                                         Integer colWidth = columnWidths.get(i);
 246                                         if(colWidth==null) cw[i] = 0;
 247                                         else cw[i] = colWidth;
 248                                 }
 249                                 for(int i = 0; i < rowHeights.size();i++){
 250                                         Integer rowHeight = rowHeights.get(i);
 251                                         if(rowHeight==null) rw[i] = 0;
 252                                         else rw[i] = rowHeight;
 253                                 }
 254                                 Resource newCell = graph.newResource();
 255                                 graph.claim(newCell, L0.InstanceOf, null, SR.Headers);
 256                                 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);
 257                                 graph.addLiteral(newCell, SR.Headers_columnLabels, SR.Headers_columnLabels_Inverse, L0.StringArray, new String[0], Bindings.STRING_ARRAY);
 258                                 graph.addLiteral(newCell, SR.Headers_columnWidths, SR.Headers_columnWidths_Inverse, L0.IntegerArray, cw, Bindings.INT_ARRAY);
 259                                 graph.addLiteral(newCell, SR.Headers_rowHeights, SR.Headers_rowHeights_Inverse, L0.IntegerArray, rw, Bindings.INT_ARRAY);
 260                                 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
 261                             }
 262                         }
 263
 264                         Layer0X L0X = Layer0X.getInstance(graph);
 265                         DatabaseIndexing.deleteIndex(graph, L0X.DependenciesRelation, container);
 266
 267                         btreeContainer.set(result);
 268                         bookContainer.set(book);
 269                     } catch (Exception e) {
 270                         LOGGER.error("Could not import book " + file.getAbsolutePath(), e);
 271                         btreeContainer.add(Collections.emptyList());
 272                     }
 273                 }
 274             });
 275
 276             Simantics.getSession().sync(new DelayedWriteRequest() {
 277
 278                 @Override
 279                 public void perform(WriteGraph graph) throws DatabaseException {
 280                     for (BTree bt : btreeContainer.get())
 281                         bt.flushCachedBTree(graph);
 282                 }
 283             });
 284
 285             return Simantics.getSession().sync(new WriteResultRequest<Resource>() {
 286
 287                 @Override
 288                 public Resource perform(WriteGraph graph) throws DatabaseException {
 289                     Resource delayedBook = bookContainer.get();
 290                     XSupport support = graph.getService(XSupport.class);
 291                     Resource book = support.convertDelayedResourceToResource(delayedBook);
 292                     SpreadsheetGraphUtils.constructAndInitializeRunVariable(graph, book);
 293                     return book;
 294                 }
 295             });
 296         } catch (Exception e) {
 297             LOGGER.error("Could not import book " + file.getAbsolutePath(), e);
 298             return null;
 299         }
 300     }
 301
 302     private static Resource assignStyles(WriteGraph graph, SpreadsheetResource SR, Cell cell, Resource book, Map<Integer, Resource> existingStyles, Map<Integer, SpreadsheetStyle> existingStyles2, String styleName) throws DatabaseException {
 303         CellStyle cellStyle = cell.getCellStyle();
 304         if (cellStyle != null) {
 305
 306             SpreadsheetStyle sstyle = existingStyles2.get(cellStyle.hashCode());
 307             if (sstyle == null) {
 308                 SpreadsheetStyleBuilder builder = SpreadsheetStyle.newInstace();
 309                 foregroundColor(cellStyle, builder);
 310                 backgroundColor(cellStyle, builder);
 311                 alignment(cellStyle, builder);
 312                 borders(cellStyle, builder);
 313                 font(cellStyle, builder);
 314                 dataformat(cellStyle, builder);
 315
 316                 builder.name(styleName);
 317                 sstyle = builder.build();
 318                 existingStyles2.put(cellStyle.hashCode(), sstyle);
 319             }
 320
 321             int styleId = sstyle.getStyleId();
 322
 323             Resource style = existingStyles.get(styleId);
 324             if (style == null) {
 325                 style = SpreadsheetGraphUtils.createStyle(graph, book, sstyle);
 326
 327                 existingStyles.put(styleId, style);
 328             }
 329             return style;
 330         } else {
 331             SpreadsheetStyle empty = SpreadsheetStyle.empty();
 332             Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, empty);
 333             existingStyles.put(empty.getStyleId(), emptyStyle);
 334         }
 335         return null;
 336     }
 337
 338     private static void dataformat(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
 339         if (cellStyle instanceof XSSFCellStyle) {
 340             XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
 341             String formatString = xssfStyle.getDataFormatString();
 342             short formatIndex = xssfStyle.getDataFormat();
 343
 344             builder.formatString(formatString);
 345             builder.formatIndex(formatIndex);
 346         }
 347     }
 348
 349     private static void borders(CellStyle style, SpreadsheetStyleBuilder builder) {
 350 //        short borderBottom = style.getBorderBottom();
 351 //        System.out.println("BorderBottom : " + borderBottom);
 352 //
 353 //        short borderTop = style.getBorderTop();
 354 //        System.out.println("BorderTop : " + borderTop);
 355 //
 356 //        short borderLeft = style.getBorderLeft();
 357 //        System.out.println("BorderLeft : " + borderLeft);
 358 //
 359 //        short borderRight = style.getBorderRight();
 360 //        System.out.println("BorderRight : " + borderRight);
 361     }
 362
 363     private static void alignment(CellStyle style, SpreadsheetStyleBuilder builder) {
 364         short alignment = style.getAlignment();
 365         int horizontal;
 366         switch (alignment) {
 367         case CellStyle.ALIGN_CENTER:
 368             horizontal = 1;
 369             break;
 370         case CellStyle.ALIGN_LEFT:
 371             horizontal = 0;
 372             break;
 373         case CellStyle.ALIGN_RIGHT:
 374             horizontal = 2;
 375         default:
 376             horizontal = 0;
 377             break;
 378         }
 379
 380         short verticalAlignment = style.getVerticalAlignment();
 381         int vertical;
 382         switch (verticalAlignment) {
 383         case CellStyle.VERTICAL_BOTTOM:
 384             vertical = 2;
 385             break;
 386         case CellStyle.VERTICAL_TOP:
 387             vertical = 0;
 388             break;
 389         case CellStyle.VERTICAL_CENTER:
 390             vertical = 1;
 391         default:
 392             vertical = 2;
 393             break;
 394         }
 395         int align = 0;
 396         align = (align & 12) + horizontal;
 397         align = (align & 3) + (vertical << 2);
 398         builder.align(align);
 399     }
 400
 401     private static void foregroundColor(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
 402         if (cellStyle instanceof XSSFCellStyle) {
 403             XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
 404
 405             XSSFColor fillColor = xssfStyle.getFillForegroundXSSFColor();
 406             XSSFColor bColor = xssfStyle.getFillBackgroundXSSFColor();
 407             XSSFColor colorColor = xssfStyle.getFillForegroundColorColor();
 408             XSSFColor fillcColor = xssfStyle.getFillForegroundXSSFColor();
 409 //            byte[] fills = fillColor.getRgbWithTint();
 410
 411             XSSFFont xssfFont = xssfStyle.getFont();
 412             XSSFColor fontColor = xssfFont.getXSSFColor();
 413             if (fontColor != null) {
 414
 415                 byte[] rgb = fontColor.getRGBWithTint();
 416
 417                 String ix = fontColor.getARGBHex();
 418                 RGB.Integer s = hex2Rgb(ix);
 419                 builder.foreground(s);
 420 //                if (rgb != null) {
 421 //                    int red = rgb[0] & 0xff;
 422 //                    int green = rgb[1] & 0xff;
 423 //                    int blue = rgb[2] & 0xff;
 424 //                    RGB.Integer color = s;
 425 //                    builder.foreground(color);
 426 //                }
 427             }
 428         }
 429     }
 430
 431     private static void font(CellStyle style, SpreadsheetStyleBuilder builder) throws BindingException, ManyObjectsForFunctionalRelationException, ServiceException {
 432         if (style instanceof XSSFCellStyle) {
 433             XSSFCellStyle xssfStyle = (XSSFCellStyle) style;
 434             XSSFFont xssfFont = xssfStyle.getFont();
 435             String fontStyle = "Normal";
 436             if (xssfFont.getBold()) {
 437                 fontStyle = "Bold";
 438                 if (xssfFont.getItalic()) {
 439                     fontStyle += "Italic";
 440                 }
 441             } else if (xssfFont.getItalic()) {
 442                 fontStyle = "Italic";
 443             } else if (xssfFont.getUnderline() != 0) {
 444                 // not supported
 445             } else if (xssfFont.getStrikeout()) {
 446                 // not supported
 447             }
 448             Font font = new Font(xssfFont.getFontName(), xssfFont.getFontHeightInPoints(), fontStyle);
 449             builder.font(font);
 450         }
 451     }
 452
 453     private static void backgroundColor(CellStyle style, SpreadsheetStyleBuilder builder) {
 454         if (style instanceof XSSFCellStyle) {
 455             XSSFCellStyle cellStyle = (XSSFCellStyle) style;
 456
 457             XSSFColor xssfColor = cellStyle.getFillBackgroundColorColor();
 458             XSSFColor xssfC = cellStyle.getFillForegroundColorColor();
 459
 460             if (xssfC != null) {
 461                 String hex = xssfC.getARGBHex();
 462                 if (hex == null)
 463                     return;
 464                 RGB.Integer color = hex2Rgb(hex);
 465                 builder.background(color);
 466             } else if (xssfColor != null) {
 467                 String hex = xssfColor.getARGBHex();
 468                 if (hex == null)
 469                     return;
 470                 RGB.Integer color = hex2Rgb(hex);
 471                 builder.background(color);
 472             }
 473
 474 //            byte[] rgb = xssfColor.getRgbWithTint();
 475 //
 476 //            if (rgb != null) {
 477 //                int red = rgb[0] & 0xff;
 478 //                int green = rgb[1] & 0xff;
 479 //                int blue = rgb[2] & 0xff;
 480 //
 481 //                RGB.Integer color = new RGB.Integer(red, green, blue);
 482 //                builder.background(color);
 483 //            }
 484         }
 485     }
 486
 487     public static RGB.Integer hex2Rgb(String colorStr) {
 488
 489         String s1 = colorStr.substring( 2, 4 );
 490         String s2 = colorStr.substring( 4, 6 );
 491         String s3 = colorStr.substring( 6, 8 );
 492         return new RGB.Integer(
 493                 Integer.valueOf( s1, 16 ),
 494                 Integer.valueOf( s2, 16 ),
 495                 Integer.valueOf( s3, 16 ) );
 496     }
 497
 498
 499     public static void main(String[] args) throws Exception {
 500
 501         {
 502             SheetFormulaParser p = new SheetFormulaParser(new StringReader("E888"));
 503             AstValue v = p.relation();
 504             System.err.println("v="+v);
 505         }
 506         {
 507             SheetFormulaParser p = new SheetFormulaParser(new StringReader("E8:E22"));
 508             AstValue v = p.relation();
 509             System.err.println("v="+v);
 510         }
 511         {
 512             SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(E8:E22)"));
 513             AstValue v = p.relation();
 514             System.err.println("v="+v);
 515         }
 516         {
 517             SheetFormulaParser p = new SheetFormulaParser(new StringReader("kääk"));
 518             AstValue v = p.relation();
 519             System.err.println("v="+v);
 520         }
 521
 522         {
 523             SheetFormulaParser p = new SheetFormulaParser(new StringReader("WeekStart(SheetNumber(A1),Year)"));
 524             AstValue v = p.relation();
 525             System.err.println("v="+v);
 526         }
 527
 528         {
 529             SheetFormulaParser p = new SheetFormulaParser(new StringReader("0.001*C41"));
 530             AstValue v = p.relation();
 531             System.err.println("v="+v);
 532         }
 533
 534         {
 535             SheetFormulaParser p = new SheetFormulaParser(new StringReader("1+0.5"));
 536             AstValue v = p.arithmetic_expression();
 537             System.err.println("v="+v);
 538         }
 539
 540         {
 541             SheetFormulaParser p = new SheetFormulaParser(new StringReader("J675+0.5"));
 542             AstValue v = p.arithmetic_expression();
 543             System.err.println("v="+v);
 544         }
 545
 546         {
 547             SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(J675+0.5)"));
 548             AstValue v = p.relation();
 549             System.err.println("v="+v);
 550         }
 551
 552         {
 553             SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(J711=0,0,J675+0.5+0.05)"));
 554             AstValue v = p.relation();
 555             System.err.println("v="+v);
 556         }
 557
 558         {
 559             SheetFormulaParser p = new SheetFormulaParser(new StringReader("B:B"));
 560             AstValue v = p.relation();
 561             System.err.println("v="+v);
 562         }
 563
 564         {
 565             SheetFormulaParser p = new SheetFormulaParser(new StringReader("+SUMIF(B:B,E9,C:C)"));
 566             AstValue v = p.relation();
 567             System.err.println("v="+v);
 568         }
 569
 570         {
 571             SheetFormulaParser p = new SheetFormulaParser(new StringReader("A(B())"));
 572             AstValue v = p.relation();
 573             System.err.println("v="+v);
 574         }
 575
 576         {
 577             SheetFormulaParser p = new SheetFormulaParser(new StringReader("+VLOOKUP(ROUND(C154*100, 0),$M$3:$T$34,C$2,TRUE)"));
 578             AstValue v = p.relation();
 579             System.err.println("v="+v);
 580         }
 581
 582         {
 583             SheetFormulaParser p = new SheetFormulaParser(new StringReader("LINEST(N37:N42,M37:M42^{1,2},TRUE,1)"));
 584             AstValue v = p.relation();
 585             System.err.println("v="+v);
 586         }
 587
 588         {
 589             SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(K16>0,(SUM(K614))/K16,)"));
 590             AstValue v = p.relation();
 591             System.err.println("v="+v);
 592         }
 593
 594         {
 595             SheetFormulaParser p = new SheetFormulaParser(new StringReader("(-(0.2/2)+SQRT((0.2/2)^2-4*((0.2/2)^2-(3*0.17625)/(PI()*4.7))))"));
 596             AstValue v = p.relation();
 597             System.err.println("v="+v);
 598             SpreadsheetBook book = new SpreadsheetBook("http:/");
 599             Object o = v.accept(new CellValueVisitor(SpreadsheetEvaluationEnvironment.getInstance(book), null));
 600             System.err.println("o="+o);
 601         }
 602
 603     }
 604
 605 }