-package org.simantics.spreadsheet.graph;\r
-\r
-import java.io.File;\r
-import java.io.FileInputStream;\r
-import java.io.StringReader;\r
-import java.util.ArrayList;\r
-import java.util.Collections;\r
-import java.util.HashMap;\r
-import java.util.List;\r
-import java.util.Map;\r
-\r
-import org.apache.poi.ss.usermodel.Cell;\r
-import org.apache.poi.ss.usermodel.CellStyle;\r
-import org.apache.poi.ss.usermodel.Row;\r
-import org.apache.poi.ss.usermodel.Sheet;\r
-import org.apache.poi.ss.usermodel.Workbook;\r
-import org.apache.poi.ss.usermodel.WorkbookFactory;\r
-import org.apache.poi.ss.util.CellRangeAddress;\r
-import org.apache.poi.xssf.usermodel.XSSFCellStyle;\r
-import org.apache.poi.xssf.usermodel.XSSFColor;\r
-import org.apache.poi.xssf.usermodel.XSSFFont;\r
-import org.simantics.Simantics;\r
-import org.simantics.databoard.Bindings;\r
-import org.simantics.databoard.binding.mutable.Variant;\r
-import org.simantics.datatypes.literal.Font;\r
-import org.simantics.datatypes.literal.RGB;\r
-import org.simantics.datatypes.utils.BTree;\r
-import org.simantics.db.Resource;\r
-import org.simantics.db.WriteGraph;\r
-import org.simantics.db.common.request.DelayedWriteRequest;\r
-import org.simantics.db.common.request.WriteRequest;\r
-import org.simantics.db.common.utils.NameUtils;\r
-import org.simantics.db.exception.BindingException;\r
-import org.simantics.db.exception.DatabaseException;\r
-import org.simantics.db.exception.ManyObjectsForFunctionalRelationException;\r
-import org.simantics.db.exception.ServiceException;\r
-import org.simantics.db.indexing.DatabaseIndexing;\r
-import org.simantics.db.layer0.util.Layer0Utils;\r
-import org.simantics.db.service.XSupport;\r
-import org.simantics.layer0.Layer0;\r
-import org.simantics.operation.Layer0X;\r
-import org.simantics.spreadsheet.graph.SpreadsheetStyle.SpreadsheetStyleBuilder;\r
-import org.simantics.spreadsheet.graph.formula.SpreadsheetEvaluationEnvironment;\r
-import org.simantics.spreadsheet.graph.parser.SheetFormulaParser;\r
-import org.simantics.spreadsheet.graph.parser.ast.AstValue;\r
-import org.simantics.spreadsheet.resource.SpreadsheetResource;\r
-import org.simantics.spreadsheet.util.SpreadsheetUtils;\r
-import org.simantics.utils.DataContainer;\r
-\r
-public class ExcelImport {\r
- \r
- private static final double POINT_TO_PIXEL_RATIO = 1.33;\r
- \r
- public static void importBook(Resource container, File file) {\r
- \r
- try {\r
- \r
- DataContainer<List<BTree>> btreeContainer = new DataContainer<>();\r
- DataContainer<Resource> bookContainer = new DataContainer<>();\r
- \r
- Simantics.getSession().sync(new DelayedWriteRequest() {\r
- \r
- @Override\r
- public void perform(WriteGraph graph) throws DatabaseException {\r
- Layer0Utils.setDependenciesIndexingDisabled(graph, true);\r
-\r
- List<BTree> result = new ArrayList<>();\r
- try {\r
- FileInputStream fis = new FileInputStream(file);\r
- Workbook workBook = WorkbookFactory.create(fis);\r
- fis.close();\r
- \r
- Layer0 L0 = Layer0.getInstance(graph);\r
- SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);\r
-\r
- String originalFileName = file.getName();\r
- int lastDot = originalFileName.lastIndexOf('.');\r
- String suggestion = originalFileName;\r
- if(lastDot!=-1)\r
- suggestion = originalFileName.substring(0, lastDot);\r
- \r
- String uniqueName = NameUtils.findFreshEscapedName(graph, suggestion, container);\r
- \r
- Resource book = SpreadsheetGraphUtils.createBook(graph, container, uniqueName);\r
- \r
- // Initial empty style for every book\r
-// Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, SpreadsheetStyle.empty());\r
- \r
- Map<Integer, Resource> existingStyles = new HashMap<>();\r
- Map<Integer, SpreadsheetStyle> existingStyles2 = new HashMap<>();\r
- \r
-// Collection<Resource> styles = graph.syncRequest(new ObjectsWithType(book, L0.ConsistsOf, SR.Style));\r
-// for (Resource eStyle : styles) {\r
-// int styleId = graph.getRelatedValue2(eStyle, SR.Style_id, Bindings.INTEGER);\r
-// existingStyles.put(styleId, eStyle);\r
-// }\r
- \r
- for(int sheetNumber = 0; sheetNumber < workBook.getNumberOfSheets(); sheetNumber++) {\r
-\r
- Sheet sheet_ = workBook.getSheetAt(sheetNumber);\r
- String sheetName = sheet_.getSheetName();\r
-\r
- Resource sheet = graph.newResource();\r
- graph.claim(sheet, L0.InstanceOf, SR.Spreadsheet);\r
- graph.claimLiteral(sheet, L0.HasName, L0.NameOf, L0.String, sheetName, Bindings.STRING);\r
- graph.claim(book, L0.ConsistsOf, sheet);\r
-\r
- {\r
- Resource newCell = graph.newResource();\r
- graph.claim(newCell, L0.InstanceOf, null, SR.Dimensions);\r
- graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);\r
- graph.addLiteral(newCell, SR.Dimensions_fitColumns, SR.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);\r
- graph.addLiteral(newCell, SR.Dimensions_fitRows, SR.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);\r
- graph.addLiteral(newCell, SR.Dimensions_columnCount, SR.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);\r
- graph.addLiteral(newCell, SR.Dimensions_rowCount, SR.Dimensions_rowCount_Inverse, L0.Integer, 4096, Bindings.INTEGER);\r
- graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);\r
- }\r
-\r
- BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);\r
- result.add(bt);\r
- Resource lines = bt.rootOfBTree();\r
-\r
- graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);\r
- graph.claim(sheet, L0.ConsistsOf, L0.PartOf, lines);\r
- \r
- List<Integer> columnWidths = new ArrayList<>();\r
- List<Integer> rowHeights = new ArrayList<>();\r
- \r
- for(int rowN = 0;rowN<=sheet_.getLastRowNum();rowN++) {\r
- \r
- Row row = sheet_.getRow(rowN);\r
- if(row == null) continue;\r
-\r
- boolean hasSomething = false;\r
- int count = row.getLastCellNum();\r
- for(int i=0;i<count;i++) {\r
- Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);\r
- if(val != null) {\r
- hasSomething = true;\r
- break;\r
- }\r
- }\r
-\r
- if(!hasSomething) continue;\r
- \r
- Resource line = graph.newResource();\r
- graph.claim(line, L0.InstanceOf, null, SR.Line);\r
- graph.claimLiteral(line, L0.HasName, L0.NameOf, L0.String, "Row" + rowN, Bindings.STRING);\r
- bt.insertBTree(graph, Variant.ofInstance(rowN), line);\r
-\r
- if(rowHeights.size()<=rowN){\r
- for(int zz = rowHeights.size(); zz <= rowN; zz++)\r
- rowHeights.add(null);\r
- }\r
- if(rowHeights.get(rowN)==null){\r
- Double rowHeightInPoints = row.getHeight()/20.0;\r
- Double rowHeightInPixels = rowHeightInPoints*POINT_TO_PIXEL_RATIO+1;\r
- int rH = rowHeightInPixels.intValue();\r
- //System.out.println("rowHeightInPixels: " + rH);\r
- rowHeights.set(rowN, rH);\r
- }\r
- \r
- for(int i=0;i<count;i++) {\r
- Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);\r
- if(val != null) {\r
- \r
- String cellName = SpreadsheetUtils.cellName(rowN, i);\r
- \r
- int ccIndx = val.getColumnIndex();\r
- if(columnWidths.size()<=ccIndx){\r
- for(int zz = columnWidths.size(); zz <= ccIndx; zz++)\r
- columnWidths.add(null);\r
- }\r
- if(columnWidths.get(ccIndx)==null){\r
- Integer ccWidth = sheet_.getColumnWidth(ccIndx);\r
- Double characterWidth = null;\r
- Double pixelWidthD = null;\r
- if(ccWidth<=438) {\r
- characterWidth = (ccWidth/256.0)/(1.0+181.92/256.0);\r
- characterWidth = Math.round(characterWidth*100)/100.0;\r
- pixelWidthD = (characterWidth*11.986)+0.0078;\r
- }\r
- else {\r
- characterWidth = (ccWidth-181.92)/256.0;\r
- characterWidth = Math.round(characterWidth*100)/100.0;\r
- pixelWidthD = (characterWidth*7.0)+5.0;\r
- }\r
- columnWidths.set(ccIndx, (int)Math.round(pixelWidthD));\r
- }\r
- \r
- Resource cell = graph.newResource();\r
- graph.claim(cell, L0.InstanceOf, null, SR.TextCell);\r
- graph.claimLiteral(cell, L0.HasName, L0.NameOf, L0.String, cellName, Bindings.STRING);\r
- graph.claim(line, L0.ConsistsOf, L0.PartOf, cell);\r
-\r
- if(Cell.CELL_TYPE_BOOLEAN == val.getCellType()){\r
- boolean bool = val.getBooleanCellValue();\r
- graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(bool), Bindings.VARIANT);\r
- }\r
- else if(Cell.CELL_TYPE_FORMULA == val.getCellType()) {\r
- Variant v = null;\r
- String formula = val.getCellFormula();\r
- if(val.isPartOfArrayFormulaGroup()) {\r
- CellRangeAddress addr = val.getArrayFormulaRange();\r
- v = new Variant(ExcelArrayFormula.BINDING, new ExcelArrayFormula(addr.formatAsString(), formula));\r
- } else {\r
- v = new Variant(ExcelFormula.BINDING, new ExcelFormula(formula));\r
- }\r
- graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, v, Bindings.VARIANT);\r
- \r
- } else if(Cell.CELL_TYPE_STRING == val.getCellType()) {\r
- graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);\r
- } else if(Cell.CELL_TYPE_NUMERIC == val.getCellType()) {\r
- Double value = Double.parseDouble(val.toString());\r
- graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(value), Bindings.VARIANT);\r
- } else {\r
- graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);\r
- System.err.println("Unprocessed cell type " + val.getCellType() + ", SheetName: " + sheetName + ", Row: " + rowN + ", Col:" + i);\r
- }\r
-// System.err.println("Current cell " + sheetName + ":"+ cellName);\r
- Resource style = assignStyles(graph, SR, val, book, existingStyles, existingStyles2, "Style_" + existingStyles.size());\r
- if (style != null)\r
- graph.claim(cell, SR.Cell_HasStyle, style);\r
- \r
- }\r
- }\r
- }\r
- {\r
- int[] cw = new int[(columnWidths.size())];\r
- int[] rw = new int[(rowHeights.size())];\r
- for(int i = 0; i<columnWidths.size();i++){\r
- Integer colWidth = columnWidths.get(i);\r
- if(colWidth==null) cw[i] = 0;\r
- else cw[i] = colWidth;\r
- }\r
- for(int i = 0; i < rowHeights.size();i++){\r
- Integer rowHeight = rowHeights.get(i);\r
- if(rowHeight==null) rw[i] = 0;\r
- else rw[i] = rowHeight;\r
- }\r
- Resource newCell = graph.newResource();\r
- graph.claim(newCell, L0.InstanceOf, null, SR.Headers);\r
- graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);\r
- graph.addLiteral(newCell, SR.Headers_columnLabels, SR.Headers_columnLabels_Inverse, L0.StringArray, new String[0], Bindings.STRING_ARRAY);\r
- graph.addLiteral(newCell, SR.Headers_columnWidths, SR.Headers_columnWidths_Inverse, L0.IntegerArray, cw, Bindings.INT_ARRAY);\r
- graph.addLiteral(newCell, SR.Headers_rowHeights, SR.Headers_rowHeights_Inverse, L0.IntegerArray, rw, Bindings.INT_ARRAY);\r
- graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);\r
- }\r
- }\r
-\r
- Layer0X L0X = Layer0X.getInstance(graph);\r
- DatabaseIndexing.deleteIndex(graph, L0X.DependenciesRelation, container);\r
-\r
- btreeContainer.set(result);\r
- bookContainer.set(book);\r
- } catch (Exception e) {\r
- e.printStackTrace();\r
- btreeContainer.add(Collections.emptyList());\r
- }\r
- }\r
- });\r
- \r
- Simantics.getSession().sync(new DelayedWriteRequest() {\r
- \r
- @Override\r
- public void perform(WriteGraph graph) throws DatabaseException {\r
- for (BTree bt : btreeContainer.get())\r
- bt.flushCachedBTree(graph);\r
- }\r
- });\r
- \r
- Simantics.getSession().sync(new WriteRequest() {\r
- \r
- @Override\r
- public void perform(WriteGraph graph) throws DatabaseException {\r
- Resource delayedBook = bookContainer.get();\r
- XSupport support = graph.getService(XSupport.class);\r
- Resource book = support.convertDelayedResourceToResource(delayedBook);\r
- SpreadsheetGraphUtils.constructAndInitializeRunVariable(graph, book);\r
- }\r
- });\r
- } catch (Exception e) {\r
- e.printStackTrace();\r
- }\r
- \r
- }\r
- \r
- 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
- CellStyle cellStyle = cell.getCellStyle();\r
- if (cellStyle != null) {\r
- \r
- SpreadsheetStyle sstyle = existingStyles2.get(cellStyle.hashCode());\r
- if (sstyle == null) {\r
- SpreadsheetStyleBuilder builder = SpreadsheetStyle.newInstace();\r
- foregroundColor(cellStyle, builder);\r
- backgroundColor(cellStyle, builder);\r
- alignment(cellStyle, builder);\r
- borders(cellStyle, builder);\r
- font(cellStyle, builder);\r
- dataformat(cellStyle, builder);\r
- \r
- builder.name(styleName);\r
- sstyle = builder.build();\r
- existingStyles2.put(cellStyle.hashCode(), sstyle);\r
- }\r
-\r
- int styleId = sstyle.getStyleId();\r
- \r
- Resource style = existingStyles.get(styleId);\r
- if (style == null) {\r
- style = SpreadsheetGraphUtils.createStyle(graph, book, sstyle);\r
- \r
- existingStyles.put(styleId, style);\r
- }\r
- return style;\r
- } else {\r
- SpreadsheetStyle empty = SpreadsheetStyle.empty();\r
- Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, empty);\r
- existingStyles.put(empty.getStyleId(), emptyStyle);\r
- }\r
- return null;\r
- }\r
-\r
- private static void dataformat(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {\r
- if (cellStyle instanceof XSSFCellStyle) {\r
- XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;\r
- String formatString = xssfStyle.getDataFormatString();\r
- short formatIndex = xssfStyle.getDataFormat();\r
- \r
- builder.formatString(formatString);\r
- builder.formatIndex(formatIndex);\r
- }\r
- }\r
-\r
- private static void borders(CellStyle style, SpreadsheetStyleBuilder builder) {\r
-// short borderBottom = style.getBorderBottom();\r
-// System.out.println("BorderBottom : " + borderBottom);\r
-// \r
-// short borderTop = style.getBorderTop();\r
-// System.out.println("BorderTop : " + borderTop);\r
-// \r
-// short borderLeft = style.getBorderLeft();\r
-// System.out.println("BorderLeft : " + borderLeft);\r
-// \r
-// short borderRight = style.getBorderRight();\r
-// System.out.println("BorderRight : " + borderRight);\r
- }\r
-\r
- private static void alignment(CellStyle style, SpreadsheetStyleBuilder builder) {\r
- short alignment = style.getAlignment();\r
- int horizontal;\r
- switch (alignment) {\r
- case CellStyle.ALIGN_CENTER:\r
- horizontal = 1;\r
- break;\r
- case CellStyle.ALIGN_LEFT:\r
- horizontal = 0;\r
- break;\r
- case CellStyle.ALIGN_RIGHT:\r
- horizontal = 2;\r
- default:\r
- horizontal = 0;\r
- break;\r
- }\r
- \r
- short verticalAlignment = style.getVerticalAlignment();\r
- int vertical;\r
- switch (verticalAlignment) {\r
- case CellStyle.VERTICAL_BOTTOM:\r
- vertical = 2;\r
- break;\r
- case CellStyle.VERTICAL_TOP:\r
- vertical = 0;\r
- break;\r
- case CellStyle.VERTICAL_CENTER:\r
- vertical = 1;\r
- default:\r
- vertical = 2;\r
- break;\r
- }\r
- int align = 0;\r
- align = (align & 12) + horizontal;\r
- align = (align & 3) + (vertical << 2);\r
- builder.align(align);\r
- }\r
-\r
- private static void foregroundColor(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {\r
- if (cellStyle instanceof XSSFCellStyle) {\r
- XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;\r
- \r
- XSSFColor fillColor = xssfStyle.getFillForegroundXSSFColor();\r
- XSSFColor bColor = xssfStyle.getFillBackgroundXSSFColor();\r
- XSSFColor colorColor = xssfStyle.getFillForegroundColorColor();\r
- XSSFColor fillcColor = xssfStyle.getFillForegroundXSSFColor();\r
-// byte[] fills = fillColor.getRgbWithTint();\r
- \r
- XSSFFont xssfFont = xssfStyle.getFont();\r
- XSSFColor fontColor = xssfFont.getXSSFColor();\r
- if (fontColor != null) {\r
- \r
- byte[] rgb = fontColor.getRgbWithTint();\r
- \r
- String ix = fontColor.getARGBHex();\r
- RGB.Integer s = hex2Rgb(ix);\r
- builder.foreground(s);\r
-// if (rgb != null) {\r
-// int red = rgb[0] & 0xff;\r
-// int green = rgb[1] & 0xff;\r
-// int blue = rgb[2] & 0xff;\r
-// RGB.Integer color = s;\r
-// builder.foreground(color);\r
-// }\r
- }\r
- }\r
- }\r
-\r
- private static void font(CellStyle style, SpreadsheetStyleBuilder builder) throws BindingException, ManyObjectsForFunctionalRelationException, ServiceException {\r
- if (style instanceof XSSFCellStyle) {\r
- XSSFCellStyle xssfStyle = (XSSFCellStyle) style;\r
- XSSFFont xssfFont = xssfStyle.getFont();\r
- String fontStyle = "Normal";\r
- if (xssfFont.getBold()) {\r
- fontStyle = "Bold";\r
- if (xssfFont.getItalic()) {\r
- fontStyle += "Italic";\r
- }\r
- } else if (xssfFont.getItalic()) {\r
- fontStyle = "Italic";\r
- } else if (xssfFont.getUnderline() != 0) {\r
- // not supported\r
- } else if (xssfFont.getStrikeout()) {\r
- // not supported\r
- }\r
- Font font = new Font(xssfFont.getFontName(), xssfFont.getFontHeightInPoints(), fontStyle);\r
- builder.font(font);\r
- }\r
- }\r
-\r
- private static void backgroundColor(CellStyle style, SpreadsheetStyleBuilder builder) {\r
- if (style instanceof XSSFCellStyle) {\r
- XSSFCellStyle cellStyle = (XSSFCellStyle) style;\r
- \r
- XSSFColor xssfColor = cellStyle.getFillBackgroundColorColor();\r
- XSSFColor xssfC = cellStyle.getFillForegroundColorColor();\r
- \r
- if (xssfC != null) {\r
- String hex = xssfC.getARGBHex();\r
- if (hex == null)\r
- return;\r
- RGB.Integer color = hex2Rgb(hex);\r
- builder.background(color);\r
- } else if (xssfColor != null) {\r
- String hex = xssfColor.getARGBHex();\r
- if (hex == null)\r
- return;\r
- RGB.Integer color = hex2Rgb(hex);\r
- builder.background(color);\r
- }\r
- \r
-// byte[] rgb = xssfColor.getRgbWithTint();\r
-// \r
-// if (rgb != null) {\r
-// int red = rgb[0] & 0xff;\r
-// int green = rgb[1] & 0xff;\r
-// int blue = rgb[2] & 0xff;\r
-// \r
-// RGB.Integer color = new RGB.Integer(red, green, blue);\r
-// builder.background(color);\r
-// }\r
- }\r
- }\r
- \r
- public static RGB.Integer hex2Rgb(String colorStr) {\r
-\r
- String s1 = colorStr.substring( 2, 4 );\r
- String s2 = colorStr.substring( 4, 6 );\r
- String s3 = colorStr.substring( 6, 8 );\r
- return new RGB.Integer(\r
- Integer.valueOf( s1, 16 ),\r
- Integer.valueOf( s2, 16 ),\r
- Integer.valueOf( s3, 16 ) );\r
- }\r
-\r
-\r
- public static void main(String[] args) throws Exception {\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("E888"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("E8:E22"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(E8:E22)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("kääk"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
- \r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("WeekStart(SheetNumber(A1),Year)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
- \r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("0.001*C41"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("1+0.5"));\r
- AstValue v = p.arithmetic_expression();\r
- System.err.println("v="+v);\r
- }\r
- \r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("J675+0.5"));\r
- AstValue v = p.arithmetic_expression();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(J675+0.5)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
- \r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(J711=0,0,J675+0.5+0.05)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("B:B"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("+SUMIF(B:B,E9,C:C)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("A(B())"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("+VLOOKUP(ROUND(C154*100, 0),$M$3:$T$34,C$2,TRUE)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
- \r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("LINEST(N37:N42,M37:M42^{1,2},TRUE,1)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(K16>0,(SUM(K614))/K16,)"));\r
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- }\r
-\r
- {\r
- 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
- AstValue v = p.relation();\r
- System.err.println("v="+v);\r
- SpreadsheetBook book = new SpreadsheetBook();\r
- Object o = v.accept(new CellValueVisitor(SpreadsheetEvaluationEnvironment.getInstance(book), null));\r
- System.err.println("o="+o);\r
- }\r
- \r
- }\r
- \r
-}\r
+package org.simantics.spreadsheet.graph;
+
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.StringReader;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.WorkbookFactory;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.xssf.usermodel.XSSFCellStyle;
+import org.apache.poi.xssf.usermodel.XSSFColor;
+import org.apache.poi.xssf.usermodel.XSSFFont;
+import org.simantics.Simantics;
+import org.simantics.databoard.Bindings;
+import org.simantics.databoard.binding.mutable.Variant;
+import org.simantics.datatypes.literal.Font;
+import org.simantics.datatypes.literal.RGB;
+import org.simantics.datatypes.utils.BTree;
+import org.simantics.db.Resource;
+import org.simantics.db.WriteGraph;
+import org.simantics.db.common.request.DelayedWriteRequest;
+import org.simantics.db.common.request.WriteResultRequest;
+import org.simantics.db.common.utils.NameUtils;
+import org.simantics.db.exception.BindingException;
+import org.simantics.db.exception.DatabaseException;
+import org.simantics.db.exception.ManyObjectsForFunctionalRelationException;
+import org.simantics.db.exception.ServiceException;
+import org.simantics.db.indexing.DatabaseIndexing;
+import org.simantics.db.layer0.util.Layer0Utils;
+import org.simantics.db.service.XSupport;
+import org.simantics.layer0.Layer0;
+import org.simantics.operation.Layer0X;
+import org.simantics.spreadsheet.Spreadsheets;
+import org.simantics.spreadsheet.resource.SpreadsheetResource;
+import org.simantics.spreadsheet.solver.SpreadsheetBook;
+import org.simantics.spreadsheet.solver.SpreadsheetStyle;
+import org.simantics.spreadsheet.solver.SpreadsheetStyle.SpreadsheetStyleBuilder;
+import org.simantics.spreadsheet.solver.formula.CellValueVisitor;
+import org.simantics.spreadsheet.solver.formula.SpreadsheetEvaluationEnvironment;
+import org.simantics.spreadsheet.solver.formula.parser.SheetFormulaParser;
+import org.simantics.spreadsheet.solver.formula.parser.ast.AstValue;
+import org.simantics.spreadsheet.synchronization.ExcelArrayFormula;
+import org.simantics.spreadsheet.synchronization.ExcelFormula;
+import org.simantics.utils.DataContainer;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+public class ExcelImport {
+
+ private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImport.class);
+
+ private static final double POINT_TO_PIXEL_RATIO = 1.33;
+
+ public static void importBook(Resource container, File file) {
+ importBookR(container, file);
+ }
+
+ public static Resource importBookR(Resource container, File file) {
+
+ try {
+
+ DataContainer<List<BTree>> btreeContainer = new DataContainer<>();
+ DataContainer<Resource> bookContainer = new DataContainer<>();
+
+ Simantics.getSession().sync(new DelayedWriteRequest() {
+
+ @Override
+ public void perform(WriteGraph graph) throws DatabaseException {
+ Layer0Utils.setDependenciesIndexingDisabled(graph, true);
+
+ List<BTree> result = new ArrayList<>();
+ try {
+ FileInputStream fis = new FileInputStream(file);
+ Workbook workBook = WorkbookFactory.create(fis);
+ fis.close();
+
+ Layer0 L0 = Layer0.getInstance(graph);
+ SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
+
+ String originalFileName = file.getName();
+ int lastDot = originalFileName.lastIndexOf('.');
+ String suggestion = originalFileName;
+ if(lastDot!=-1)
+ suggestion = originalFileName.substring(0, lastDot);
+
+ String uniqueName = NameUtils.findFreshEscapedName(graph, suggestion, container);
+
+ Resource book = SpreadsheetGraphUtils.createBook(graph, container, uniqueName);
+
+ // Initial empty style for every book
+// Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, SpreadsheetStyle.empty());
+
+ Map<Integer, Resource> existingStyles = new HashMap<>();
+ Map<Integer, SpreadsheetStyle> existingStyles2 = new HashMap<>();
+
+// Collection<Resource> styles = graph.syncRequest(new ObjectsWithType(book, L0.ConsistsOf, SR.Style));
+// for (Resource eStyle : styles) {
+// int styleId = graph.getRelatedValue2(eStyle, SR.Style_id, Bindings.INTEGER);
+// existingStyles.put(styleId, eStyle);
+// }
+
+ for(int sheetNumber = 0; sheetNumber < workBook.getNumberOfSheets(); sheetNumber++) {
+
+ Sheet sheet_ = workBook.getSheetAt(sheetNumber);
+ String sheetName = sheet_.getSheetName();
+
+ Resource sheet = graph.newResource();
+ graph.claim(sheet, L0.InstanceOf, SR.Spreadsheet);
+ graph.claimLiteral(sheet, L0.HasName, L0.NameOf, L0.String, sheetName, Bindings.STRING);
+ graph.claim(book, L0.ConsistsOf, sheet);
+
+ {
+ Resource newCell = graph.newResource();
+ graph.claim(newCell, L0.InstanceOf, null, SR.Dimensions);
+ graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);
+ graph.addLiteral(newCell, SR.Dimensions_fitColumns, SR.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
+ graph.addLiteral(newCell, SR.Dimensions_fitRows, SR.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
+ graph.addLiteral(newCell, SR.Dimensions_columnCount, SR.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);
+ graph.addLiteral(newCell, SR.Dimensions_rowCount, SR.Dimensions_rowCount_Inverse, L0.Integer, 4096, Bindings.INTEGER);
+ graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
+ }
+
+ BTree bt = new BTree(graph, Spreadsheets.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);
+ result.add(bt);
+ Resource lines = bt.rootOfBTree();
+
+ graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
+ graph.claim(sheet, L0.ConsistsOf, L0.PartOf, lines);
+
+ List<Integer> columnWidths = new ArrayList<>();
+ List<Integer> rowHeights = new ArrayList<>();
+
+ for(int rowN = 0;rowN<=sheet_.getLastRowNum();rowN++) {
+
+ Row row = sheet_.getRow(rowN);
+ if(row == null) continue;
+
+ boolean hasSomething = false;
+ int count = row.getLastCellNum();
+ for(int i=0;i<count;i++) {
+ Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
+ if(val != null) {
+ hasSomething = true;
+ break;
+ }
+ }
+
+ if(!hasSomething) continue;
+
+ Resource line = graph.newResource();
+ graph.claim(line, L0.InstanceOf, null, SR.Line);
+ graph.claimLiteral(line, L0.HasName, L0.NameOf, L0.String, "Row" + rowN, Bindings.STRING);
+ bt.insertBTree(graph, Variant.ofInstance(rowN), line);
+
+ if(rowHeights.size()<=rowN){
+ for(int zz = rowHeights.size(); zz <= rowN; zz++)
+ rowHeights.add(null);
+ }
+ if(rowHeights.get(rowN)==null){
+ Double rowHeightInPoints = row.getHeight()/20.0;
+ Double rowHeightInPixels = rowHeightInPoints*POINT_TO_PIXEL_RATIO+1;
+ int rH = rowHeightInPixels.intValue();
+ //System.out.println("rowHeightInPixels: " + rH);
+ rowHeights.set(rowN, rH);
+ }
+
+ for(int i=0;i<count;i++) {
+ Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
+ if(val != null) {
+
+ String cellName = Spreadsheets.cellName(rowN, i);
+
+ int ccIndx = val.getColumnIndex();
+ if(columnWidths.size()<=ccIndx){
+ for(int zz = columnWidths.size(); zz <= ccIndx; zz++)
+ columnWidths.add(null);
+ }
+ if(columnWidths.get(ccIndx)==null){
+ Integer ccWidth = sheet_.getColumnWidth(ccIndx);
+ Double characterWidth = null;
+ Double pixelWidthD = null;
+ if(ccWidth<=438) {
+ characterWidth = (ccWidth/256.0)/(1.0+181.92/256.0);
+ characterWidth = Math.round(characterWidth*100)/100.0;
+ pixelWidthD = (characterWidth*11.986)+0.0078;
+ }
+ else {
+ characterWidth = (ccWidth-181.92)/256.0;
+ characterWidth = Math.round(characterWidth*100)/100.0;
+ pixelWidthD = (characterWidth*7.0)+5.0;
+ }
+ columnWidths.set(ccIndx, (int)Math.round(pixelWidthD));
+ }
+
+ Resource cell = graph.newResource();
+ graph.claim(cell, L0.InstanceOf, null, SR.TextCell);
+ graph.claimLiteral(cell, L0.HasName, L0.NameOf, L0.String, cellName, Bindings.STRING);
+ graph.claim(line, L0.ConsistsOf, L0.PartOf, cell);
+
+ if(Cell.CELL_TYPE_BOOLEAN == val.getCellType()){
+ boolean bool = val.getBooleanCellValue();
+ graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(bool), Bindings.VARIANT);
+ }
+ else if(Cell.CELL_TYPE_FORMULA == val.getCellType()) {
+ Variant v = null;
+ String formula = val.getCellFormula();
+ if(val.isPartOfArrayFormulaGroup()) {
+ CellRangeAddress addr = val.getArrayFormulaRange();
+ v = new Variant(ExcelArrayFormula.BINDING, new ExcelArrayFormula(addr.formatAsString(), formula));
+ } else {
+ v = new Variant(ExcelFormula.BINDING, new ExcelFormula(formula));
+ }
+ graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, v, Bindings.VARIANT);
+
+ } else if(Cell.CELL_TYPE_STRING == val.getCellType()) {
+ graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
+ } else if(Cell.CELL_TYPE_NUMERIC == val.getCellType()) {
+ Double value = Double.parseDouble(val.toString());
+ graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(value), Bindings.VARIANT);
+ } else {
+ graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
+ System.err.println("Unprocessed cell type " + val.getCellType() + ", SheetName: " + sheetName + ", Row: " + rowN + ", Col:" + i);
+ }
+// System.err.println("Current cell " + sheetName + ":"+ cellName);
+ Resource style = assignStyles(graph, SR, val, book, existingStyles, existingStyles2, "Style_" + existingStyles.size());
+ if (style != null)
+ graph.claim(cell, SR.Cell_HasStyle, style);
+
+ }
+ }
+ }
+ {
+ int[] cw = new int[(columnWidths.size())];
+ int[] rw = new int[(rowHeights.size())];
+ for(int i = 0; i<columnWidths.size();i++){
+ Integer colWidth = columnWidths.get(i);
+ if(colWidth==null) cw[i] = 0;
+ else cw[i] = colWidth;
+ }
+ for(int i = 0; i < rowHeights.size();i++){
+ Integer rowHeight = rowHeights.get(i);
+ if(rowHeight==null) rw[i] = 0;
+ else rw[i] = rowHeight;
+ }
+ Resource newCell = graph.newResource();
+ graph.claim(newCell, L0.InstanceOf, null, SR.Headers);
+ graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);
+ graph.addLiteral(newCell, SR.Headers_columnLabels, SR.Headers_columnLabels_Inverse, L0.StringArray, new String[0], Bindings.STRING_ARRAY);
+ graph.addLiteral(newCell, SR.Headers_columnWidths, SR.Headers_columnWidths_Inverse, L0.IntegerArray, cw, Bindings.INT_ARRAY);
+ graph.addLiteral(newCell, SR.Headers_rowHeights, SR.Headers_rowHeights_Inverse, L0.IntegerArray, rw, Bindings.INT_ARRAY);
+ graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
+ }
+ }
+
+ Layer0X L0X = Layer0X.getInstance(graph);
+ DatabaseIndexing.deleteIndex(graph, L0X.DependenciesRelation, container);
+
+ btreeContainer.set(result);
+ bookContainer.set(book);
+ } catch (Exception e) {
+ LOGGER.error("Could not import book " + file.getAbsolutePath(), e);
+ btreeContainer.add(Collections.emptyList());
+ }
+ }
+ });
+
+ Simantics.getSession().sync(new DelayedWriteRequest() {
+
+ @Override
+ public void perform(WriteGraph graph) throws DatabaseException {
+ for (BTree bt : btreeContainer.get())
+ bt.flushCachedBTree(graph);
+ }
+ });
+
+ return Simantics.getSession().sync(new WriteResultRequest<Resource>() {
+
+ @Override
+ public Resource perform(WriteGraph graph) throws DatabaseException {
+ Resource delayedBook = bookContainer.get();
+ XSupport support = graph.getService(XSupport.class);
+ Resource book = support.convertDelayedResourceToResource(delayedBook);
+ SpreadsheetGraphUtils.constructAndInitializeRunVariable(graph, book);
+ return book;
+ }
+ });
+ } catch (Exception e) {
+ LOGGER.error("Could not import book " + file.getAbsolutePath(), e);
+ return null;
+ }
+ }
+
+ private static Resource assignStyles(WriteGraph graph, SpreadsheetResource SR, Cell cell, Resource book, Map<Integer, Resource> existingStyles, Map<Integer, SpreadsheetStyle> existingStyles2, String styleName) throws DatabaseException {
+ CellStyle cellStyle = cell.getCellStyle();
+ if (cellStyle != null) {
+
+ SpreadsheetStyle sstyle = existingStyles2.get(cellStyle.hashCode());
+ if (sstyle == null) {
+ SpreadsheetStyleBuilder builder = SpreadsheetStyle.newInstace();
+ foregroundColor(cellStyle, builder);
+ backgroundColor(cellStyle, builder);
+ alignment(cellStyle, builder);
+ borders(cellStyle, builder);
+ font(cellStyle, builder);
+ dataformat(cellStyle, builder);
+
+ builder.name(styleName);
+ sstyle = builder.build();
+ existingStyles2.put(cellStyle.hashCode(), sstyle);
+ }
+
+ int styleId = sstyle.getStyleId();
+
+ Resource style = existingStyles.get(styleId);
+ if (style == null) {
+ style = SpreadsheetGraphUtils.createStyle(graph, book, sstyle);
+
+ existingStyles.put(styleId, style);
+ }
+ return style;
+ } else {
+ SpreadsheetStyle empty = SpreadsheetStyle.empty();
+ Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, empty);
+ existingStyles.put(empty.getStyleId(), emptyStyle);
+ }
+ return null;
+ }
+
+ private static void dataformat(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
+ if (cellStyle instanceof XSSFCellStyle) {
+ XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
+ String formatString = xssfStyle.getDataFormatString();
+ short formatIndex = xssfStyle.getDataFormat();
+
+ builder.formatString(formatString);
+ builder.formatIndex(formatIndex);
+ }
+ }
+
+ private static void borders(CellStyle style, SpreadsheetStyleBuilder builder) {
+// short borderBottom = style.getBorderBottom();
+// System.out.println("BorderBottom : " + borderBottom);
+//
+// short borderTop = style.getBorderTop();
+// System.out.println("BorderTop : " + borderTop);
+//
+// short borderLeft = style.getBorderLeft();
+// System.out.println("BorderLeft : " + borderLeft);
+//
+// short borderRight = style.getBorderRight();
+// System.out.println("BorderRight : " + borderRight);
+ }
+
+ private static void alignment(CellStyle style, SpreadsheetStyleBuilder builder) {
+ short alignment = style.getAlignment();
+ int horizontal;
+ switch (alignment) {
+ case CellStyle.ALIGN_CENTER:
+ horizontal = 1;
+ break;
+ case CellStyle.ALIGN_LEFT:
+ horizontal = 0;
+ break;
+ case CellStyle.ALIGN_RIGHT:
+ horizontal = 2;
+ default:
+ horizontal = 0;
+ break;
+ }
+
+ short verticalAlignment = style.getVerticalAlignment();
+ int vertical;
+ switch (verticalAlignment) {
+ case CellStyle.VERTICAL_BOTTOM:
+ vertical = 2;
+ break;
+ case CellStyle.VERTICAL_TOP:
+ vertical = 0;
+ break;
+ case CellStyle.VERTICAL_CENTER:
+ vertical = 1;
+ default:
+ vertical = 2;
+ break;
+ }
+ int align = 0;
+ align = (align & 12) + horizontal;
+ align = (align & 3) + (vertical << 2);
+ builder.align(align);
+ }
+
+ private static void foregroundColor(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
+ if (cellStyle instanceof XSSFCellStyle) {
+ XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
+
+ XSSFColor fillColor = xssfStyle.getFillForegroundXSSFColor();
+ XSSFColor bColor = xssfStyle.getFillBackgroundXSSFColor();
+ XSSFColor colorColor = xssfStyle.getFillForegroundColorColor();
+ XSSFColor fillcColor = xssfStyle.getFillForegroundXSSFColor();
+// byte[] fills = fillColor.getRgbWithTint();
+
+ XSSFFont xssfFont = xssfStyle.getFont();
+ XSSFColor fontColor = xssfFont.getXSSFColor();
+ if (fontColor != null) {
+
+ byte[] rgb = fontColor.getRGBWithTint();
+
+ String ix = fontColor.getARGBHex();
+ RGB.Integer s = hex2Rgb(ix);
+ builder.foreground(s);
+// if (rgb != null) {
+// int red = rgb[0] & 0xff;
+// int green = rgb[1] & 0xff;
+// int blue = rgb[2] & 0xff;
+// RGB.Integer color = s;
+// builder.foreground(color);
+// }
+ }
+ }
+ }
+
+ private static void font(CellStyle style, SpreadsheetStyleBuilder builder) throws BindingException, ManyObjectsForFunctionalRelationException, ServiceException {
+ if (style instanceof XSSFCellStyle) {
+ XSSFCellStyle xssfStyle = (XSSFCellStyle) style;
+ XSSFFont xssfFont = xssfStyle.getFont();
+ String fontStyle = "Normal";
+ if (xssfFont.getBold()) {
+ fontStyle = "Bold";
+ if (xssfFont.getItalic()) {
+ fontStyle += "Italic";
+ }
+ } else if (xssfFont.getItalic()) {
+ fontStyle = "Italic";
+ } else if (xssfFont.getUnderline() != 0) {
+ // not supported
+ } else if (xssfFont.getStrikeout()) {
+ // not supported
+ }
+ Font font = new Font(xssfFont.getFontName(), xssfFont.getFontHeightInPoints(), fontStyle);
+ builder.font(font);
+ }
+ }
+
+ private static void backgroundColor(CellStyle style, SpreadsheetStyleBuilder builder) {
+ if (style instanceof XSSFCellStyle) {
+ XSSFCellStyle cellStyle = (XSSFCellStyle) style;
+
+ XSSFColor xssfColor = cellStyle.getFillBackgroundColorColor();
+ XSSFColor xssfC = cellStyle.getFillForegroundColorColor();
+
+ if (xssfC != null) {
+ String hex = xssfC.getARGBHex();
+ if (hex == null)
+ return;
+ RGB.Integer color = hex2Rgb(hex);
+ builder.background(color);
+ } else if (xssfColor != null) {
+ String hex = xssfColor.getARGBHex();
+ if (hex == null)
+ return;
+ RGB.Integer color = hex2Rgb(hex);
+ builder.background(color);
+ }
+
+// byte[] rgb = xssfColor.getRgbWithTint();
+//
+// if (rgb != null) {
+// int red = rgb[0] & 0xff;
+// int green = rgb[1] & 0xff;
+// int blue = rgb[2] & 0xff;
+//
+// RGB.Integer color = new RGB.Integer(red, green, blue);
+// builder.background(color);
+// }
+ }
+ }
+
+ public static RGB.Integer hex2Rgb(String colorStr) {
+
+ String s1 = colorStr.substring( 2, 4 );
+ String s2 = colorStr.substring( 4, 6 );
+ String s3 = colorStr.substring( 6, 8 );
+ return new RGB.Integer(
+ Integer.valueOf( s1, 16 ),
+ Integer.valueOf( s2, 16 ),
+ Integer.valueOf( s3, 16 ) );
+ }
+
+
+ public static void main(String[] args) throws Exception {
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("E888"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("E8:E22"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(E8:E22)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("kääk"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("WeekStart(SheetNumber(A1),Year)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("0.001*C41"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("1+0.5"));
+ AstValue v = p.arithmetic_expression();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("J675+0.5"));
+ AstValue v = p.arithmetic_expression();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(J675+0.5)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(J711=0,0,J675+0.5+0.05)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("B:B"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("+SUMIF(B:B,E9,C:C)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("A(B())"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("+VLOOKUP(ROUND(C154*100, 0),$M$3:$T$34,C$2,TRUE)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("LINEST(N37:N42,M37:M42^{1,2},TRUE,1)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(K16>0,(SUM(K614))/K16,)"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ }
+
+ {
+ 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))))"));
+ AstValue v = p.relation();
+ System.err.println("v="+v);
+ SpreadsheetBook book = new SpreadsheetBook("http:/");
+ Object o = v.accept(new CellValueVisitor(SpreadsheetEvaluationEnvironment.getInstance(book), null));
+ System.err.println("o="+o);
+ }
+
+ }
+
+}
Code Review / simantics / platform.git / blobdiff