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