1 package org.simantics.spreadsheet.graph;
\r
4 import java.io.FileInputStream;
\r
5 import java.io.StringReader;
\r
6 import java.util.ArrayList;
\r
7 import java.util.Collections;
\r
8 import java.util.HashMap;
\r
9 import java.util.List;
\r
10 import java.util.Map;
\r
12 import org.apache.poi.ss.usermodel.Cell;
\r
13 import org.apache.poi.ss.usermodel.CellStyle;
\r
14 import org.apache.poi.ss.usermodel.Row;
\r
15 import org.apache.poi.ss.usermodel.Sheet;
\r
16 import org.apache.poi.ss.usermodel.Workbook;
\r
17 import org.apache.poi.ss.usermodel.WorkbookFactory;
\r
18 import org.apache.poi.ss.util.CellRangeAddress;
\r
19 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
\r
20 import org.apache.poi.xssf.usermodel.XSSFColor;
\r
21 import org.apache.poi.xssf.usermodel.XSSFFont;
\r
22 import org.simantics.Simantics;
\r
23 import org.simantics.databoard.Bindings;
\r
24 import org.simantics.databoard.binding.mutable.Variant;
\r
25 import org.simantics.datatypes.literal.Font;
\r
26 import org.simantics.datatypes.literal.RGB;
\r
27 import org.simantics.datatypes.utils.BTree;
\r
28 import org.simantics.db.Resource;
\r
29 import org.simantics.db.WriteGraph;
\r
30 import org.simantics.db.common.request.DelayedWriteRequest;
\r
31 import org.simantics.db.common.request.WriteRequest;
\r
32 import org.simantics.db.common.utils.NameUtils;
\r
33 import org.simantics.db.exception.BindingException;
\r
34 import org.simantics.db.exception.DatabaseException;
\r
35 import org.simantics.db.exception.ManyObjectsForFunctionalRelationException;
\r
36 import org.simantics.db.exception.ServiceException;
\r
37 import org.simantics.db.indexing.DatabaseIndexing;
\r
38 import org.simantics.db.layer0.util.Layer0Utils;
\r
39 import org.simantics.db.service.XSupport;
\r
40 import org.simantics.layer0.Layer0;
\r
41 import org.simantics.operation.Layer0X;
\r
42 import org.simantics.spreadsheet.graph.SpreadsheetStyle.SpreadsheetStyleBuilder;
\r
43 import org.simantics.spreadsheet.graph.formula.SpreadsheetEvaluationEnvironment;
\r
44 import org.simantics.spreadsheet.graph.parser.SheetFormulaParser;
\r
45 import org.simantics.spreadsheet.graph.parser.ast.AstValue;
\r
46 import org.simantics.spreadsheet.resource.SpreadsheetResource;
\r
47 import org.simantics.spreadsheet.util.SpreadsheetUtils;
\r
48 import org.simantics.utils.DataContainer;
\r
50 public class ExcelImport {
\r
52 private static final double POINT_TO_PIXEL_RATIO = 1.33;
\r
54 public static void importBook(Resource container, File file) {
\r
58 DataContainer<List<BTree>> btreeContainer = new DataContainer<>();
\r
59 DataContainer<Resource> bookContainer = new DataContainer<>();
\r
61 Simantics.getSession().sync(new DelayedWriteRequest() {
\r
64 public void perform(WriteGraph graph) throws DatabaseException {
\r
65 Layer0Utils.setDependenciesIndexingDisabled(graph, true);
\r
67 List<BTree> result = new ArrayList<>();
\r
69 FileInputStream fis = new FileInputStream(file);
\r
70 Workbook workBook = WorkbookFactory.create(fis);
\r
73 Layer0 L0 = Layer0.getInstance(graph);
\r
74 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
\r
76 String originalFileName = file.getName();
\r
77 int lastDot = originalFileName.lastIndexOf('.');
\r
78 String suggestion = originalFileName;
\r
80 suggestion = originalFileName.substring(0, lastDot);
\r
82 String uniqueName = NameUtils.findFreshEscapedName(graph, suggestion, container);
\r
84 Resource book = SpreadsheetGraphUtils.createBook(graph, container, uniqueName);
\r
86 // Initial empty style for every book
\r
87 // Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, SpreadsheetStyle.empty());
\r
89 Map<Integer, Resource> existingStyles = new HashMap<>();
\r
90 Map<Integer, SpreadsheetStyle> existingStyles2 = new HashMap<>();
\r
92 // Collection<Resource> styles = graph.syncRequest(new ObjectsWithType(book, L0.ConsistsOf, SR.Style));
\r
93 // for (Resource eStyle : styles) {
\r
94 // int styleId = graph.getRelatedValue2(eStyle, SR.Style_id, Bindings.INTEGER);
\r
95 // existingStyles.put(styleId, eStyle);
\r
98 for(int sheetNumber = 0; sheetNumber < workBook.getNumberOfSheets(); sheetNumber++) {
\r
100 Sheet sheet_ = workBook.getSheetAt(sheetNumber);
\r
101 String sheetName = sheet_.getSheetName();
\r
103 Resource sheet = graph.newResource();
\r
104 graph.claim(sheet, L0.InstanceOf, SR.Spreadsheet);
\r
105 graph.claimLiteral(sheet, L0.HasName, L0.NameOf, L0.String, sheetName, Bindings.STRING);
\r
106 graph.claim(book, L0.ConsistsOf, sheet);
\r
109 Resource newCell = graph.newResource();
\r
110 graph.claim(newCell, L0.InstanceOf, null, SR.Dimensions);
\r
111 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);
\r
112 graph.addLiteral(newCell, SR.Dimensions_fitColumns, SR.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
\r
113 graph.addLiteral(newCell, SR.Dimensions_fitRows, SR.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
\r
114 graph.addLiteral(newCell, SR.Dimensions_columnCount, SR.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);
\r
115 graph.addLiteral(newCell, SR.Dimensions_rowCount, SR.Dimensions_rowCount_Inverse, L0.Integer, 4096, Bindings.INTEGER);
\r
116 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
\r
119 BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);
\r
121 Resource lines = bt.rootOfBTree();
\r
123 graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
\r
124 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, lines);
\r
126 List<Integer> columnWidths = new ArrayList<>();
\r
127 List<Integer> rowHeights = new ArrayList<>();
\r
129 for(int rowN = 0;rowN<=sheet_.getLastRowNum();rowN++) {
\r
131 Row row = sheet_.getRow(rowN);
\r
132 if(row == null) continue;
\r
134 boolean hasSomething = false;
\r
135 int count = row.getLastCellNum();
\r
136 for(int i=0;i<count;i++) {
\r
137 Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
\r
139 hasSomething = true;
\r
144 if(!hasSomething) continue;
\r
146 Resource line = graph.newResource();
\r
147 graph.claim(line, L0.InstanceOf, null, SR.Line);
\r
148 graph.claimLiteral(line, L0.HasName, L0.NameOf, L0.String, "Row" + rowN, Bindings.STRING);
\r
149 bt.insertBTree(graph, Variant.ofInstance(rowN), line);
\r
151 if(rowHeights.size()<=rowN){
\r
152 for(int zz = rowHeights.size(); zz <= rowN; zz++)
\r
153 rowHeights.add(null);
\r
155 if(rowHeights.get(rowN)==null){
\r
156 Double rowHeightInPoints = row.getHeight()/20.0;
\r
157 Double rowHeightInPixels = rowHeightInPoints*POINT_TO_PIXEL_RATIO+1;
\r
158 int rH = rowHeightInPixels.intValue();
\r
159 //System.out.println("rowHeightInPixels: " + rH);
\r
160 rowHeights.set(rowN, rH);
\r
163 for(int i=0;i<count;i++) {
\r
164 Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
\r
167 String cellName = SpreadsheetUtils.cellName(rowN, i);
\r
169 int ccIndx = val.getColumnIndex();
\r
170 if(columnWidths.size()<=ccIndx){
\r
171 for(int zz = columnWidths.size(); zz <= ccIndx; zz++)
\r
172 columnWidths.add(null);
\r
174 if(columnWidths.get(ccIndx)==null){
\r
175 Integer ccWidth = sheet_.getColumnWidth(ccIndx);
\r
176 Double characterWidth = null;
\r
177 Double pixelWidthD = null;
\r
179 characterWidth = (ccWidth/256.0)/(1.0+181.92/256.0);
\r
180 characterWidth = Math.round(characterWidth*100)/100.0;
\r
181 pixelWidthD = (characterWidth*11.986)+0.0078;
\r
184 characterWidth = (ccWidth-181.92)/256.0;
\r
185 characterWidth = Math.round(characterWidth*100)/100.0;
\r
186 pixelWidthD = (characterWidth*7.0)+5.0;
\r
188 columnWidths.set(ccIndx, (int)Math.round(pixelWidthD));
\r
191 Resource cell = graph.newResource();
\r
192 graph.claim(cell, L0.InstanceOf, null, SR.TextCell);
\r
193 graph.claimLiteral(cell, L0.HasName, L0.NameOf, L0.String, cellName, Bindings.STRING);
\r
194 graph.claim(line, L0.ConsistsOf, L0.PartOf, cell);
\r
196 if(Cell.CELL_TYPE_BOOLEAN == val.getCellType()){
\r
197 boolean bool = val.getBooleanCellValue();
\r
198 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(bool), Bindings.VARIANT);
\r
200 else if(Cell.CELL_TYPE_FORMULA == val.getCellType()) {
\r
202 String formula = val.getCellFormula();
\r
203 if(val.isPartOfArrayFormulaGroup()) {
\r
204 CellRangeAddress addr = val.getArrayFormulaRange();
\r
205 v = new Variant(ExcelArrayFormula.BINDING, new ExcelArrayFormula(addr.formatAsString(), formula));
\r
207 v = new Variant(ExcelFormula.BINDING, new ExcelFormula(formula));
\r
209 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, v, Bindings.VARIANT);
\r
211 } else if(Cell.CELL_TYPE_STRING == val.getCellType()) {
\r
212 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
\r
213 } else if(Cell.CELL_TYPE_NUMERIC == val.getCellType()) {
\r
214 Double value = Double.parseDouble(val.toString());
\r
215 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(value), Bindings.VARIANT);
\r
217 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
\r
218 System.err.println("Unprocessed cell type " + val.getCellType() + ", SheetName: " + sheetName + ", Row: " + rowN + ", Col:" + i);
\r
220 // System.err.println("Current cell " + sheetName + ":"+ cellName);
\r
221 Resource style = assignStyles(graph, SR, val, book, existingStyles, existingStyles2, "Style_" + existingStyles.size());
\r
223 graph.claim(cell, SR.Cell_HasStyle, style);
\r
229 int[] cw = new int[(columnWidths.size())];
\r
230 int[] rw = new int[(rowHeights.size())];
\r
231 for(int i = 0; i<columnWidths.size();i++){
\r
232 Integer colWidth = columnWidths.get(i);
\r
233 if(colWidth==null) cw[i] = 0;
\r
234 else cw[i] = colWidth;
\r
236 for(int i = 0; i < rowHeights.size();i++){
\r
237 Integer rowHeight = rowHeights.get(i);
\r
238 if(rowHeight==null) rw[i] = 0;
\r
239 else rw[i] = rowHeight;
\r
241 Resource newCell = graph.newResource();
\r
242 graph.claim(newCell, L0.InstanceOf, null, SR.Headers);
\r
243 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);
\r
244 graph.addLiteral(newCell, SR.Headers_columnLabels, SR.Headers_columnLabels_Inverse, L0.StringArray, new String[0], Bindings.STRING_ARRAY);
\r
245 graph.addLiteral(newCell, SR.Headers_columnWidths, SR.Headers_columnWidths_Inverse, L0.IntegerArray, cw, Bindings.INT_ARRAY);
\r
246 graph.addLiteral(newCell, SR.Headers_rowHeights, SR.Headers_rowHeights_Inverse, L0.IntegerArray, rw, Bindings.INT_ARRAY);
\r
247 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
\r
251 Layer0X L0X = Layer0X.getInstance(graph);
\r
252 DatabaseIndexing.deleteIndex(graph, L0X.DependenciesRelation, container);
\r
254 btreeContainer.set(result);
\r
255 bookContainer.set(book);
\r
256 } catch (Exception e) {
\r
257 e.printStackTrace();
\r
258 btreeContainer.add(Collections.emptyList());
\r
263 Simantics.getSession().sync(new DelayedWriteRequest() {
\r
266 public void perform(WriteGraph graph) throws DatabaseException {
\r
267 for (BTree bt : btreeContainer.get())
\r
268 bt.flushCachedBTree(graph);
\r
272 Simantics.getSession().sync(new WriteRequest() {
\r
275 public void perform(WriteGraph graph) throws DatabaseException {
\r
276 Resource delayedBook = bookContainer.get();
\r
277 XSupport support = graph.getService(XSupport.class);
\r
278 Resource book = support.convertDelayedResourceToResource(delayedBook);
\r
279 SpreadsheetGraphUtils.constructAndInitializeRunVariable(graph, book);
\r
282 } catch (Exception e) {
\r
283 e.printStackTrace();
\r
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 {
\r
289 CellStyle cellStyle = cell.getCellStyle();
\r
290 if (cellStyle != null) {
\r
292 SpreadsheetStyle sstyle = existingStyles2.get(cellStyle.hashCode());
\r
293 if (sstyle == null) {
\r
294 SpreadsheetStyleBuilder builder = SpreadsheetStyle.newInstace();
\r
295 foregroundColor(cellStyle, builder);
\r
296 backgroundColor(cellStyle, builder);
\r
297 alignment(cellStyle, builder);
\r
298 borders(cellStyle, builder);
\r
299 font(cellStyle, builder);
\r
300 dataformat(cellStyle, builder);
\r
302 builder.name(styleName);
\r
303 sstyle = builder.build();
\r
304 existingStyles2.put(cellStyle.hashCode(), sstyle);
\r
307 int styleId = sstyle.getStyleId();
\r
309 Resource style = existingStyles.get(styleId);
\r
310 if (style == null) {
\r
311 style = SpreadsheetGraphUtils.createStyle(graph, book, sstyle);
\r
313 existingStyles.put(styleId, style);
\r
317 SpreadsheetStyle empty = SpreadsheetStyle.empty();
\r
318 Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, empty);
\r
319 existingStyles.put(empty.getStyleId(), emptyStyle);
\r
324 private static void dataformat(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
\r
325 if (cellStyle instanceof XSSFCellStyle) {
\r
326 XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
\r
327 String formatString = xssfStyle.getDataFormatString();
\r
328 short formatIndex = xssfStyle.getDataFormat();
\r
330 builder.formatString(formatString);
\r
331 builder.formatIndex(formatIndex);
\r
335 private static void borders(CellStyle style, SpreadsheetStyleBuilder builder) {
\r
336 // short borderBottom = style.getBorderBottom();
\r
337 // System.out.println("BorderBottom : " + borderBottom);
\r
339 // short borderTop = style.getBorderTop();
\r
340 // System.out.println("BorderTop : " + borderTop);
\r
342 // short borderLeft = style.getBorderLeft();
\r
343 // System.out.println("BorderLeft : " + borderLeft);
\r
345 // short borderRight = style.getBorderRight();
\r
346 // System.out.println("BorderRight : " + borderRight);
\r
349 private static void alignment(CellStyle style, SpreadsheetStyleBuilder builder) {
\r
350 short alignment = style.getAlignment();
\r
352 switch (alignment) {
\r
353 case CellStyle.ALIGN_CENTER:
\r
356 case CellStyle.ALIGN_LEFT:
\r
359 case CellStyle.ALIGN_RIGHT:
\r
366 short verticalAlignment = style.getVerticalAlignment();
\r
368 switch (verticalAlignment) {
\r
369 case CellStyle.VERTICAL_BOTTOM:
\r
372 case CellStyle.VERTICAL_TOP:
\r
375 case CellStyle.VERTICAL_CENTER:
\r
382 align = (align & 12) + horizontal;
\r
383 align = (align & 3) + (vertical << 2);
\r
384 builder.align(align);
\r
387 private static void foregroundColor(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
\r
388 if (cellStyle instanceof XSSFCellStyle) {
\r
389 XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
\r
391 XSSFColor fillColor = xssfStyle.getFillForegroundXSSFColor();
\r
392 XSSFColor bColor = xssfStyle.getFillBackgroundXSSFColor();
\r
393 XSSFColor colorColor = xssfStyle.getFillForegroundColorColor();
\r
394 XSSFColor fillcColor = xssfStyle.getFillForegroundXSSFColor();
\r
395 // byte[] fills = fillColor.getRgbWithTint();
\r
397 XSSFFont xssfFont = xssfStyle.getFont();
\r
398 XSSFColor fontColor = xssfFont.getXSSFColor();
\r
399 if (fontColor != null) {
\r
401 byte[] rgb = fontColor.getRGBWithTint();
\r
403 String ix = fontColor.getARGBHex();
\r
404 RGB.Integer s = hex2Rgb(ix);
\r
405 builder.foreground(s);
\r
406 // if (rgb != null) {
\r
407 // int red = rgb[0] & 0xff;
\r
408 // int green = rgb[1] & 0xff;
\r
409 // int blue = rgb[2] & 0xff;
\r
410 // RGB.Integer color = s;
\r
411 // builder.foreground(color);
\r
417 private static void font(CellStyle style, SpreadsheetStyleBuilder builder) throws BindingException, ManyObjectsForFunctionalRelationException, ServiceException {
\r
418 if (style instanceof XSSFCellStyle) {
\r
419 XSSFCellStyle xssfStyle = (XSSFCellStyle) style;
\r
420 XSSFFont xssfFont = xssfStyle.getFont();
\r
421 String fontStyle = "Normal";
\r
422 if (xssfFont.getBold()) {
\r
423 fontStyle = "Bold";
\r
424 if (xssfFont.getItalic()) {
\r
425 fontStyle += "Italic";
\r
427 } else if (xssfFont.getItalic()) {
\r
428 fontStyle = "Italic";
\r
429 } else if (xssfFont.getUnderline() != 0) {
\r
431 } else if (xssfFont.getStrikeout()) {
\r
434 Font font = new Font(xssfFont.getFontName(), xssfFont.getFontHeightInPoints(), fontStyle);
\r
435 builder.font(font);
\r
439 private static void backgroundColor(CellStyle style, SpreadsheetStyleBuilder builder) {
\r
440 if (style instanceof XSSFCellStyle) {
\r
441 XSSFCellStyle cellStyle = (XSSFCellStyle) style;
\r
443 XSSFColor xssfColor = cellStyle.getFillBackgroundColorColor();
\r
444 XSSFColor xssfC = cellStyle.getFillForegroundColorColor();
\r
446 if (xssfC != null) {
\r
447 String hex = xssfC.getARGBHex();
\r
450 RGB.Integer color = hex2Rgb(hex);
\r
451 builder.background(color);
\r
452 } else if (xssfColor != null) {
\r
453 String hex = xssfColor.getARGBHex();
\r
456 RGB.Integer color = hex2Rgb(hex);
\r
457 builder.background(color);
\r
460 // byte[] rgb = xssfColor.getRgbWithTint();
\r
462 // if (rgb != null) {
\r
463 // int red = rgb[0] & 0xff;
\r
464 // int green = rgb[1] & 0xff;
\r
465 // int blue = rgb[2] & 0xff;
\r
467 // RGB.Integer color = new RGB.Integer(red, green, blue);
\r
468 // builder.background(color);
\r
473 public static RGB.Integer hex2Rgb(String colorStr) {
\r
475 String s1 = colorStr.substring( 2, 4 );
\r
476 String s2 = colorStr.substring( 4, 6 );
\r
477 String s3 = colorStr.substring( 6, 8 );
\r
478 return new RGB.Integer(
\r
479 Integer.valueOf( s1, 16 ),
\r
480 Integer.valueOf( s2, 16 ),
\r
481 Integer.valueOf( s3, 16 ) );
\r
485 public static void main(String[] args) throws Exception {
\r
488 SheetFormulaParser p = new SheetFormulaParser(new StringReader("E888"));
\r
489 AstValue v = p.relation();
\r
490 System.err.println("v="+v);
\r
493 SheetFormulaParser p = new SheetFormulaParser(new StringReader("E8:E22"));
\r
494 AstValue v = p.relation();
\r
495 System.err.println("v="+v);
\r
498 SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(E8:E22)"));
\r
499 AstValue v = p.relation();
\r
500 System.err.println("v="+v);
\r
503 SheetFormulaParser p = new SheetFormulaParser(new StringReader("kääk"));
\r
504 AstValue v = p.relation();
\r
505 System.err.println("v="+v);
\r
509 SheetFormulaParser p = new SheetFormulaParser(new StringReader("WeekStart(SheetNumber(A1),Year)"));
\r
510 AstValue v = p.relation();
\r
511 System.err.println("v="+v);
\r
515 SheetFormulaParser p = new SheetFormulaParser(new StringReader("0.001*C41"));
\r
516 AstValue v = p.relation();
\r
517 System.err.println("v="+v);
\r
521 SheetFormulaParser p = new SheetFormulaParser(new StringReader("1+0.5"));
\r
522 AstValue v = p.arithmetic_expression();
\r
523 System.err.println("v="+v);
\r
527 SheetFormulaParser p = new SheetFormulaParser(new StringReader("J675+0.5"));
\r
528 AstValue v = p.arithmetic_expression();
\r
529 System.err.println("v="+v);
\r
533 SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(J675+0.5)"));
\r
534 AstValue v = p.relation();
\r
535 System.err.println("v="+v);
\r
539 SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(J711=0,0,J675+0.5+0.05)"));
\r
540 AstValue v = p.relation();
\r
541 System.err.println("v="+v);
\r
545 SheetFormulaParser p = new SheetFormulaParser(new StringReader("B:B"));
\r
546 AstValue v = p.relation();
\r
547 System.err.println("v="+v);
\r
551 SheetFormulaParser p = new SheetFormulaParser(new StringReader("+SUMIF(B:B,E9,C:C)"));
\r
552 AstValue v = p.relation();
\r
553 System.err.println("v="+v);
\r
557 SheetFormulaParser p = new SheetFormulaParser(new StringReader("A(B())"));
\r
558 AstValue v = p.relation();
\r
559 System.err.println("v="+v);
\r
563 SheetFormulaParser p = new SheetFormulaParser(new StringReader("+VLOOKUP(ROUND(C154*100, 0),$M$3:$T$34,C$2,TRUE)"));
\r
564 AstValue v = p.relation();
\r
565 System.err.println("v="+v);
\r
569 SheetFormulaParser p = new SheetFormulaParser(new StringReader("LINEST(N37:N42,M37:M42^{1,2},TRUE,1)"));
\r
570 AstValue v = p.relation();
\r
571 System.err.println("v="+v);
\r
575 SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(K16>0,(SUM(K614))/K16,)"));
\r
576 AstValue v = p.relation();
\r
577 System.err.println("v="+v);
\r
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))))"));
\r
582 AstValue v = p.relation();
\r
583 System.err.println("v="+v);
\r
584 SpreadsheetBook book = new SpreadsheetBook();
\r
585 Object o = v.accept(new CellValueVisitor(SpreadsheetEvaluationEnvironment.getInstance(book), null));
\r
586 System.err.println("o="+o);
\r