bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/ExcelImport.java

   1 package org.simantics.spreadsheet.graph;\r
   2 \r
   3 import java.io.File;\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
  11 \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
  49 \r
  50 public class ExcelImport {\r
  51         \r
  52         private static final double POINT_TO_PIXEL_RATIO = 1.33;\r
  53         \r
  54     public static void importBook(Resource container, File file) {\r
  55         \r
  56         try {\r
  57             \r
  58             DataContainer<List<BTree>> btreeContainer = new DataContainer<>();\r
  59             DataContainer<Resource> bookContainer = new DataContainer<>();\r
  60             \r
  61             Simantics.getSession().sync(new DelayedWriteRequest() {\r
  62                 \r
  63                 @Override\r
  64                 public void perform(WriteGraph graph) throws DatabaseException {\r
  65                     Layer0Utils.setDependenciesIndexingDisabled(graph, true);\r
  66 \r
  67                     List<BTree> result = new ArrayList<>();\r
  68                     try {\r
  69                         FileInputStream fis = new FileInputStream(file);\r
  70                         Workbook workBook = WorkbookFactory.create(fis);\r
  71                         fis.close();\r
  72                     \r
  73                         Layer0 L0 = Layer0.getInstance(graph);\r
  74                         SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);\r
  75 \r
  76                         String originalFileName = file.getName();\r
  77                         int lastDot = originalFileName.lastIndexOf('.');\r
  78                         String suggestion = originalFileName;\r
  79                         if(lastDot!=-1)\r
  80                                 suggestion = originalFileName.substring(0, lastDot);\r
  81                         \r
  82                         String uniqueName = NameUtils.findFreshEscapedName(graph, suggestion, container);\r
  83                         \r
  84                         Resource book = SpreadsheetGraphUtils.createBook(graph, container, uniqueName);\r
  85                         \r
  86                      // Initial empty style for every book\r
  87 //                        Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, SpreadsheetStyle.empty());\r
  88                         \r
  89                         Map<Integer, Resource> existingStyles = new HashMap<>();\r
  90                         Map<Integer, SpreadsheetStyle> existingStyles2 = new HashMap<>();\r
  91                         \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
  96 //                        }\r
  97                         \r
  98                         for(int sheetNumber = 0; sheetNumber < workBook.getNumberOfSheets(); sheetNumber++) {\r
  99 \r
 100                             Sheet sheet_ = workBook.getSheetAt(sheetNumber);\r
 101                             String sheetName = sheet_.getSheetName();\r
 102 \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
 107 \r
 108                             {\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
 117                             }\r
 118 \r
 119                             BTree bt = new BTree(graph, SpreadsheetUtils.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);\r
 120                             result.add(bt);\r
 121                             Resource lines = bt.rootOfBTree();\r
 122 \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
 125                             \r
 126                             List<Integer> columnWidths = new ArrayList<>();\r
 127                             List<Integer> rowHeights = new ArrayList<>();\r
 128                             \r
 129                             for(int rowN = 0;rowN<=sheet_.getLastRowNum();rowN++) {\r
 130                                 \r
 131                                 Row row = sheet_.getRow(rowN);\r
 132                                 if(row == null) continue;\r
 133 \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
 138                                     if(val != null) {\r
 139                                         hasSomething = true;\r
 140                                         break;\r
 141                                     }\r
 142                                 }\r
 143 \r
 144                                 if(!hasSomething) continue;\r
 145                                 \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
 150 \r
 151                                 if(rowHeights.size()<=rowN){\r
 152                                         for(int zz = rowHeights.size(); zz <= rowN; zz++)\r
 153                                                 rowHeights.add(null);\r
 154                                 }\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
 161                                 }\r
 162                                 \r
 163                                 for(int i=0;i<count;i++) {\r
 164                                     Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);\r
 165                                     if(val != null) {\r
 166                                         \r
 167                                         String cellName = SpreadsheetUtils.cellName(rowN, i);\r
 168                                         \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
 173                                         }\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
 178                                                 if(ccWidth<=438) {\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
 182                                                 }\r
 183                                                 else {\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
 187                                                 }\r
 188                                                 columnWidths.set(ccIndx, (int)Math.round(pixelWidthD));\r
 189                                         }\r
 190                                         \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
 195 \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
 199                                         }\r
 200                                         else if(Cell.CELL_TYPE_FORMULA == val.getCellType()) {\r
 201                                             Variant v = null;\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
 206                                             } else {\r
 207                                                 v = new Variant(ExcelFormula.BINDING, new ExcelFormula(formula));\r
 208                                             }\r
 209                                             graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, v, Bindings.VARIANT);\r
 210                                             \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
 216                                         } else {\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
 219                                         }\r
 220 //                                    System.err.println("Current cell " + sheetName + ":"+ cellName);\r
 221                                         Resource style = assignStyles(graph, SR, val, book, existingStyles, existingStyles2, "Style_" + existingStyles.size());\r
 222                                         if (style != null)\r
 223                                             graph.claim(cell, SR.Cell_HasStyle, style);\r
 224                                         \r
 225                                     }\r
 226                                 }\r
 227                             }\r
 228                             {\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
 235                                 }\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
 240                                 }\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
 248                             }\r
 249                         }\r
 250 \r
 251                         Layer0X L0X = Layer0X.getInstance(graph);\r
 252                         DatabaseIndexing.deleteIndex(graph, L0X.DependenciesRelation, container);\r
 253 \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
 259                     }\r
 260                 }\r
 261             });\r
 262             \r
 263             Simantics.getSession().sync(new DelayedWriteRequest() {\r
 264                 \r
 265                 @Override\r
 266                 public void perform(WriteGraph graph) throws DatabaseException {\r
 267                     for (BTree bt : btreeContainer.get())\r
 268                         bt.flushCachedBTree(graph);\r
 269                 }\r
 270             });\r
 271             \r
 272             Simantics.getSession().sync(new WriteRequest() {\r
 273                 \r
 274                 @Override\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
 280                 }\r
 281             });\r
 282         } catch (Exception e) {\r
 283             e.printStackTrace();\r
 284         }\r
 285         \r
 286     }\r
 287     \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
 291             \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
 301                 \r
 302                 builder.name(styleName);\r
 303                 sstyle = builder.build();\r
 304                 existingStyles2.put(cellStyle.hashCode(), sstyle);\r
 305             }\r
 306 \r
 307             int styleId = sstyle.getStyleId();\r
 308             \r
 309             Resource style = existingStyles.get(styleId);\r
 310             if (style == null) {\r
 311                 style = SpreadsheetGraphUtils.createStyle(graph, book, sstyle);\r
 312                 \r
 313                 existingStyles.put(styleId, style);\r
 314             }\r
 315             return style;\r
 316         } else {\r
 317             SpreadsheetStyle empty = SpreadsheetStyle.empty();\r
 318             Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, empty);\r
 319             existingStyles.put(empty.getStyleId(), emptyStyle);\r
 320         }\r
 321         return null;\r
 322     }\r
 323 \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
 329             \r
 330             builder.formatString(formatString);\r
 331             builder.formatIndex(formatIndex);\r
 332         }\r
 333     }\r
 334 \r
 335     private static void borders(CellStyle style, SpreadsheetStyleBuilder builder) {\r
 336 //        short borderBottom = style.getBorderBottom();\r
 337 //        System.out.println("BorderBottom : " + borderBottom);\r
 338 //        \r
 339 //        short borderTop = style.getBorderTop();\r
 340 //        System.out.println("BorderTop : " + borderTop);\r
 341 //        \r
 342 //        short borderLeft = style.getBorderLeft();\r
 343 //        System.out.println("BorderLeft : " + borderLeft);\r
 344 //        \r
 345 //        short borderRight = style.getBorderRight();\r
 346 //        System.out.println("BorderRight : " + borderRight);\r
 347     }\r
 348 \r
 349     private static void alignment(CellStyle style, SpreadsheetStyleBuilder builder) {\r
 350         short alignment = style.getAlignment();\r
 351         int horizontal;\r
 352         switch (alignment) {\r
 353         case CellStyle.ALIGN_CENTER:\r
 354             horizontal = 1;\r
 355             break;\r
 356         case CellStyle.ALIGN_LEFT:\r
 357             horizontal = 0;\r
 358             break;\r
 359         case CellStyle.ALIGN_RIGHT:\r
 360             horizontal = 2;\r
 361         default:\r
 362             horizontal = 0;\r
 363             break;\r
 364         }\r
 365         \r
 366         short verticalAlignment = style.getVerticalAlignment();\r
 367         int vertical;\r
 368         switch (verticalAlignment) {\r
 369         case CellStyle.VERTICAL_BOTTOM:\r
 370             vertical = 2;\r
 371             break;\r
 372         case CellStyle.VERTICAL_TOP:\r
 373             vertical = 0;\r
 374             break;\r
 375         case CellStyle.VERTICAL_CENTER:\r
 376             vertical = 1;\r
 377         default:\r
 378             vertical = 2;\r
 379             break;\r
 380         }\r
 381         int align = 0;\r
 382         align = (align & 12) + horizontal;\r
 383         align = (align & 3) + (vertical << 2);\r
 384         builder.align(align);\r
 385     }\r
 386 \r
 387     private static void foregroundColor(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {\r
 388         if (cellStyle instanceof XSSFCellStyle) {\r
 389             XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;\r
 390             \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
 396             \r
 397             XSSFFont xssfFont = xssfStyle.getFont();\r
 398             XSSFColor fontColor = xssfFont.getXSSFColor();\r
 399             if (fontColor != null) {\r
 400                 \r
 401                 byte[] rgb = fontColor.getRGBWithTint();\r
 402                 \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
 412 //                }\r
 413             }\r
 414         }\r
 415     }\r
 416 \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
 426                 }\r
 427             } else if (xssfFont.getItalic()) {\r
 428                 fontStyle = "Italic";\r
 429             } else if (xssfFont.getUnderline() != 0) {\r
 430                 // not supported\r
 431             } else if (xssfFont.getStrikeout()) {\r
 432                 // not supported\r
 433             }\r
 434             Font font = new Font(xssfFont.getFontName(), xssfFont.getFontHeightInPoints(), fontStyle);\r
 435             builder.font(font);\r
 436         }\r
 437     }\r
 438 \r
 439     private static void backgroundColor(CellStyle style, SpreadsheetStyleBuilder builder) {\r
 440         if (style instanceof XSSFCellStyle) {\r
 441             XSSFCellStyle cellStyle = (XSSFCellStyle) style;\r
 442             \r
 443             XSSFColor xssfColor = cellStyle.getFillBackgroundColorColor();\r
 444             XSSFColor xssfC = cellStyle.getFillForegroundColorColor();\r
 445             \r
 446             if (xssfC != null) {\r
 447                 String hex = xssfC.getARGBHex();\r
 448                 if (hex == null)\r
 449                     return;\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
 454                 if (hex == null)\r
 455                     return;\r
 456                 RGB.Integer color = hex2Rgb(hex);\r
 457                 builder.background(color);\r
 458             }\r
 459             \r
 460 //            byte[] rgb = xssfColor.getRgbWithTint();\r
 461 //            \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
 466 //                \r
 467 //                RGB.Integer color = new RGB.Integer(red, green, blue);\r
 468 //                builder.background(color);\r
 469 //            }\r
 470         }\r
 471     }\r
 472     \r
 473     public static RGB.Integer hex2Rgb(String colorStr) {\r
 474 \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
 482     }\r
 483 \r
 484 \r
 485     public static void main(String[] args) throws Exception {\r
 486 \r
 487         {\r
 488             SheetFormulaParser p = new SheetFormulaParser(new StringReader("E888"));\r
 489             AstValue v = p.relation();\r
 490             System.err.println("v="+v);\r
 491         }\r
 492         {\r
 493             SheetFormulaParser p = new SheetFormulaParser(new StringReader("E8:E22"));\r
 494             AstValue v = p.relation();\r
 495             System.err.println("v="+v);\r
 496         }\r
 497         {\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
 501         }\r
 502         {\r
 503             SheetFormulaParser p = new SheetFormulaParser(new StringReader("kääk"));\r
 504             AstValue v = p.relation();\r
 505             System.err.println("v="+v);\r
 506         }\r
 507         \r
 508         {\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
 512         }\r
 513         \r
 514         {\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
 518         }\r
 519 \r
 520         {\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
 524         }\r
 525         \r
 526         {\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
 530         }\r
 531 \r
 532         {\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
 536         }\r
 537         \r
 538         {\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
 542         }\r
 543 \r
 544         {\r
 545             SheetFormulaParser p = new SheetFormulaParser(new StringReader("B:B"));\r
 546             AstValue v = p.relation();\r
 547             System.err.println("v="+v);\r
 548         }\r
 549 \r
 550         {\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
 554         }\r
 555 \r
 556         {\r
 557             SheetFormulaParser p = new SheetFormulaParser(new StringReader("A(B())"));\r
 558             AstValue v = p.relation();\r
 559             System.err.println("v="+v);\r
 560         }\r
 561 \r
 562         {\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
 566         }\r
 567         \r
 568         {\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
 572         }\r
 573 \r
 574         {\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
 578         }\r
 579 \r
 580         {\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
 587         }\r
 588         \r
 589     }\r
 590     \r
 591 }\r