246f38edf8654dc7145ffd63c33bbe56e4dd99b3
[simantics/platform.git] / 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 }