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