1 package org.simantics.spreadsheet.graph;
4 import java.io.FileInputStream;
5 import java.io.StringReader;
6 import java.util.ArrayList;
7 import java.util.Collections;
8 import java.util.HashMap;
12 import org.apache.poi.ss.usermodel.Cell;
13 import org.apache.poi.ss.usermodel.CellStyle;
14 import org.apache.poi.ss.usermodel.Row;
15 import org.apache.poi.ss.usermodel.Sheet;
16 import org.apache.poi.ss.usermodel.Workbook;
17 import org.apache.poi.ss.usermodel.WorkbookFactory;
18 import org.apache.poi.ss.util.CellRangeAddress;
19 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
20 import org.apache.poi.xssf.usermodel.XSSFColor;
21 import org.apache.poi.xssf.usermodel.XSSFFont;
22 import org.simantics.Simantics;
23 import org.simantics.databoard.Bindings;
24 import org.simantics.databoard.binding.mutable.Variant;
25 import org.simantics.datatypes.literal.Font;
26 import org.simantics.datatypes.literal.RGB;
27 import org.simantics.datatypes.utils.BTree;
28 import org.simantics.db.Resource;
29 import org.simantics.db.WriteGraph;
30 import org.simantics.db.common.request.DelayedWriteRequest;
31 import org.simantics.db.common.request.WriteResultRequest;
32 import org.simantics.db.common.utils.NameUtils;
33 import org.simantics.db.exception.BindingException;
34 import org.simantics.db.exception.DatabaseException;
35 import org.simantics.db.exception.ManyObjectsForFunctionalRelationException;
36 import org.simantics.db.exception.ServiceException;
37 import org.simantics.db.indexing.DatabaseIndexing;
38 import org.simantics.db.layer0.util.Layer0Utils;
39 import org.simantics.db.service.XSupport;
40 import org.simantics.layer0.Layer0;
41 import org.simantics.operation.Layer0X;
42 import org.simantics.spreadsheet.Spreadsheets;
43 import org.simantics.spreadsheet.resource.SpreadsheetResource;
44 import org.simantics.spreadsheet.solver.SpreadsheetBook;
45 import org.simantics.spreadsheet.solver.SpreadsheetStyle;
46 import org.simantics.spreadsheet.solver.SpreadsheetStyle.SpreadsheetStyleBuilder;
47 import org.simantics.spreadsheet.solver.formula.CellValueVisitor;
48 import org.simantics.spreadsheet.solver.formula.SpreadsheetEvaluationEnvironment;
49 import org.simantics.spreadsheet.solver.formula.parser.SheetFormulaParser;
50 import org.simantics.spreadsheet.solver.formula.parser.ast.AstValue;
51 import org.simantics.spreadsheet.synchronization.ExcelArrayFormula;
52 import org.simantics.spreadsheet.synchronization.ExcelFormula;
53 import org.simantics.utils.DataContainer;
54 import org.slf4j.Logger;
55 import org.slf4j.LoggerFactory;
57 public class ExcelImport {
59 private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImport.class);
61 private static final double POINT_TO_PIXEL_RATIO = 1.33;
63 public static void importBook(Resource container, File file) {
64 importBookR(container, file);
67 public static Resource importBookR(Resource container, File file) {
71 DataContainer<List<BTree>> btreeContainer = new DataContainer<>();
72 DataContainer<Resource> bookContainer = new DataContainer<>();
74 Simantics.getSession().sync(new DelayedWriteRequest() {
77 public void perform(WriteGraph graph) throws DatabaseException {
78 Layer0Utils.setDependenciesIndexingDisabled(graph, true);
80 List<BTree> result = new ArrayList<>();
82 FileInputStream fis = new FileInputStream(file);
83 Workbook workBook = WorkbookFactory.create(fis);
86 Layer0 L0 = Layer0.getInstance(graph);
87 SpreadsheetResource SR = SpreadsheetResource.getInstance(graph);
89 String originalFileName = file.getName();
90 int lastDot = originalFileName.lastIndexOf('.');
91 String suggestion = originalFileName;
93 suggestion = originalFileName.substring(0, lastDot);
95 String uniqueName = NameUtils.findFreshEscapedName(graph, suggestion, container);
97 Resource book = SpreadsheetGraphUtils.createBook(graph, container, uniqueName);
99 // Initial empty style for every book
100 // Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, SpreadsheetStyle.empty());
102 Map<Integer, Resource> existingStyles = new HashMap<>();
103 Map<Integer, SpreadsheetStyle> existingStyles2 = new HashMap<>();
105 // Collection<Resource> styles = graph.syncRequest(new ObjectsWithType(book, L0.ConsistsOf, SR.Style));
106 // for (Resource eStyle : styles) {
107 // int styleId = graph.getRelatedValue2(eStyle, SR.Style_id, Bindings.INTEGER);
108 // existingStyles.put(styleId, eStyle);
111 for(int sheetNumber = 0; sheetNumber < workBook.getNumberOfSheets(); sheetNumber++) {
113 Sheet sheet_ = workBook.getSheetAt(sheetNumber);
114 String sheetName = sheet_.getSheetName();
116 Resource sheet = graph.newResource();
117 graph.claim(sheet, L0.InstanceOf, SR.Spreadsheet);
118 graph.claimLiteral(sheet, L0.HasName, L0.NameOf, L0.String, sheetName, Bindings.STRING);
119 graph.claim(book, L0.ConsistsOf, sheet);
122 Resource newCell = graph.newResource();
123 graph.claim(newCell, L0.InstanceOf, null, SR.Dimensions);
124 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Dimensions", Bindings.STRING);
125 graph.addLiteral(newCell, SR.Dimensions_fitColumns, SR.Dimensions_fitColumns_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
126 graph.addLiteral(newCell, SR.Dimensions_fitRows, SR.Dimensions_fitRows_Inverse, L0.Boolean, false, Bindings.BOOLEAN);
127 graph.addLiteral(newCell, SR.Dimensions_columnCount, SR.Dimensions_columnCount_Inverse, L0.Integer, 128, Bindings.INTEGER);
128 graph.addLiteral(newCell, SR.Dimensions_rowCount, SR.Dimensions_rowCount_Inverse, L0.Integer, 4096, Bindings.INTEGER);
129 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
132 BTree bt = new BTree(graph, Spreadsheets.SPREADSHEET_BTREE_SIZE, SR.Lines, SR.LineNode, L0.PartOf, true);
134 Resource lines = bt.rootOfBTree();
136 graph.claimLiteral(lines, L0.HasName, L0.NameOf, L0.String, "Lines", Bindings.STRING);
137 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, lines);
139 List<Integer> columnWidths = new ArrayList<>();
140 List<Integer> rowHeights = new ArrayList<>();
142 for(int rowN = 0;rowN<=sheet_.getLastRowNum();rowN++) {
144 Row row = sheet_.getRow(rowN);
145 if(row == null) continue;
147 boolean hasSomething = false;
148 int count = row.getLastCellNum();
149 for(int i=0;i<count;i++) {
150 Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
157 if(!hasSomething) continue;
159 Resource line = graph.newResource();
160 graph.claim(line, L0.InstanceOf, null, SR.Line);
161 graph.claimLiteral(line, L0.HasName, L0.NameOf, L0.String, "Row" + rowN, Bindings.STRING);
162 bt.insertBTree(graph, Variant.ofInstance(rowN), line);
164 if(rowHeights.size()<=rowN){
165 for(int zz = rowHeights.size(); zz <= rowN; zz++)
166 rowHeights.add(null);
168 if(rowHeights.get(rowN)==null){
169 Double rowHeightInPoints = row.getHeight()/20.0;
170 Double rowHeightInPixels = rowHeightInPoints*POINT_TO_PIXEL_RATIO+1;
171 int rH = rowHeightInPixels.intValue();
172 //System.out.println("rowHeightInPixels: " + rH);
173 rowHeights.set(rowN, rH);
176 for(int i=0;i<count;i++) {
177 Cell val = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
180 String cellName = Spreadsheets.cellName(rowN, i);
182 int ccIndx = val.getColumnIndex();
183 if(columnWidths.size()<=ccIndx){
184 for(int zz = columnWidths.size(); zz <= ccIndx; zz++)
185 columnWidths.add(null);
187 if(columnWidths.get(ccIndx)==null){
188 Integer ccWidth = sheet_.getColumnWidth(ccIndx);
189 Double characterWidth = null;
190 Double pixelWidthD = null;
192 characterWidth = (ccWidth/256.0)/(1.0+181.92/256.0);
193 characterWidth = Math.round(characterWidth*100)/100.0;
194 pixelWidthD = (characterWidth*11.986)+0.0078;
197 characterWidth = (ccWidth-181.92)/256.0;
198 characterWidth = Math.round(characterWidth*100)/100.0;
199 pixelWidthD = (characterWidth*7.0)+5.0;
201 columnWidths.set(ccIndx, (int)Math.round(pixelWidthD));
204 Resource cell = graph.newResource();
205 graph.claim(cell, L0.InstanceOf, null, SR.TextCell);
206 graph.claimLiteral(cell, L0.HasName, L0.NameOf, L0.String, cellName, Bindings.STRING);
207 graph.claim(line, L0.ConsistsOf, L0.PartOf, cell);
209 if(Cell.CELL_TYPE_BOOLEAN == val.getCellType()){
210 boolean bool = val.getBooleanCellValue();
211 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(bool), Bindings.VARIANT);
213 else if(Cell.CELL_TYPE_FORMULA == val.getCellType()) {
215 String formula = val.getCellFormula();
216 if(val.isPartOfArrayFormulaGroup()) {
217 CellRangeAddress addr = val.getArrayFormulaRange();
218 v = new Variant(ExcelArrayFormula.BINDING, new ExcelArrayFormula(addr.formatAsString(), formula));
220 v = new Variant(ExcelFormula.BINDING, new ExcelFormula(formula));
222 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, v, Bindings.VARIANT);
224 } else if(Cell.CELL_TYPE_STRING == val.getCellType()) {
225 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
226 } else if(Cell.CELL_TYPE_NUMERIC == val.getCellType()) {
228 Double value = Double.parseDouble(val.toString());
229 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(value), Bindings.VARIANT);
230 } catch (Exception e) {
231 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
234 graph.claimLiteral(cell, SR.Cell_content, SR.Cell_content_Inverse, L0.Variant, Variant.ofInstance(val.toString()), Bindings.VARIANT);
235 System.err.println("Unprocessed cell type " + val.getCellType() + ", SheetName: " + sheetName + ", Row: " + rowN + ", Col:" + i);
237 // System.err.println("Current cell " + sheetName + ":"+ cellName);
238 Resource style = assignStyles(graph, SR, val, book, existingStyles, existingStyles2, "Style_" + existingStyles.size());
240 graph.claim(cell, SR.Cell_HasStyle, style);
246 int[] cw = new int[(columnWidths.size())];
247 int[] rw = new int[(rowHeights.size())];
248 for(int i = 0; i<columnWidths.size();i++){
249 Integer colWidth = columnWidths.get(i);
250 if(colWidth==null) cw[i] = 0;
251 else cw[i] = colWidth;
253 for(int i = 0; i < rowHeights.size();i++){
254 Integer rowHeight = rowHeights.get(i);
255 if(rowHeight==null) rw[i] = 0;
256 else rw[i] = rowHeight;
258 Resource newCell = graph.newResource();
259 graph.claim(newCell, L0.InstanceOf, null, SR.Headers);
260 graph.claimLiteral(newCell, L0.HasName, L0.NameOf, L0.String, "Headers", Bindings.STRING);
261 graph.addLiteral(newCell, SR.Headers_columnLabels, SR.Headers_columnLabels_Inverse, L0.StringArray, new String[0], Bindings.STRING_ARRAY);
262 graph.addLiteral(newCell, SR.Headers_columnWidths, SR.Headers_columnWidths_Inverse, L0.IntegerArray, cw, Bindings.INT_ARRAY);
263 graph.addLiteral(newCell, SR.Headers_rowHeights, SR.Headers_rowHeights_Inverse, L0.IntegerArray, rw, Bindings.INT_ARRAY);
264 graph.claim(sheet, L0.ConsistsOf, L0.PartOf, newCell);
268 Layer0X L0X = Layer0X.getInstance(graph);
269 DatabaseIndexing.deleteIndex(graph, L0X.DependenciesRelation, container);
271 btreeContainer.set(result);
272 bookContainer.set(book);
273 } catch (Exception e) {
274 LOGGER.error("Could not import book " + file.getAbsolutePath(), e);
275 btreeContainer.add(Collections.emptyList());
280 Simantics.getSession().sync(new DelayedWriteRequest() {
283 public void perform(WriteGraph graph) throws DatabaseException {
284 for (BTree bt : btreeContainer.get())
285 bt.flushCachedBTree(graph);
289 return Simantics.getSession().sync(new WriteResultRequest<Resource>() {
292 public Resource perform(WriteGraph graph) throws DatabaseException {
293 Resource delayedBook = bookContainer.get();
294 XSupport support = graph.getService(XSupport.class);
295 Resource book = support.convertDelayedResourceToResource(delayedBook);
296 SpreadsheetGraphUtils.constructAndInitializeRunVariable(graph, book);
300 } catch (Exception e) {
301 LOGGER.error("Could not import book " + file.getAbsolutePath(), e);
306 private static Resource assignStyles(WriteGraph graph, SpreadsheetResource SR, Cell cell, Resource book, Map<Integer, Resource> existingStyles, Map<Integer, SpreadsheetStyle> existingStyles2, String styleName) throws DatabaseException {
307 CellStyle cellStyle = cell.getCellStyle();
308 if (cellStyle != null) {
310 SpreadsheetStyle sstyle = existingStyles2.get(cellStyle.hashCode());
311 if (sstyle == null) {
312 SpreadsheetStyleBuilder builder = SpreadsheetStyle.newInstace();
313 foregroundColor(cellStyle, builder);
314 backgroundColor(cellStyle, builder);
315 alignment(cellStyle, builder);
316 borders(cellStyle, builder);
317 font(cellStyle, builder);
318 dataformat(cellStyle, builder);
320 builder.name(styleName);
321 sstyle = builder.build();
322 existingStyles2.put(cellStyle.hashCode(), sstyle);
325 int styleId = sstyle.getStyleId();
327 Resource style = existingStyles.get(styleId);
329 style = SpreadsheetGraphUtils.createStyle(graph, book, sstyle);
331 existingStyles.put(styleId, style);
335 SpreadsheetStyle empty = SpreadsheetStyle.empty();
336 Resource emptyStyle = SpreadsheetGraphUtils.createStyle(graph, book, empty);
337 existingStyles.put(empty.getStyleId(), emptyStyle);
342 private static void dataformat(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
343 if (cellStyle instanceof XSSFCellStyle) {
344 XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
345 String formatString = xssfStyle.getDataFormatString();
346 short formatIndex = xssfStyle.getDataFormat();
348 builder.formatString(formatString);
349 builder.formatIndex(formatIndex);
353 private static void borders(CellStyle style, SpreadsheetStyleBuilder builder) {
354 // short borderBottom = style.getBorderBottom();
355 // System.out.println("BorderBottom : " + borderBottom);
357 // short borderTop = style.getBorderTop();
358 // System.out.println("BorderTop : " + borderTop);
360 // short borderLeft = style.getBorderLeft();
361 // System.out.println("BorderLeft : " + borderLeft);
363 // short borderRight = style.getBorderRight();
364 // System.out.println("BorderRight : " + borderRight);
367 private static void alignment(CellStyle style, SpreadsheetStyleBuilder builder) {
368 short alignment = style.getAlignment();
371 case CellStyle.ALIGN_CENTER:
374 case CellStyle.ALIGN_LEFT:
377 case CellStyle.ALIGN_RIGHT:
384 short verticalAlignment = style.getVerticalAlignment();
386 switch (verticalAlignment) {
387 case CellStyle.VERTICAL_BOTTOM:
390 case CellStyle.VERTICAL_TOP:
393 case CellStyle.VERTICAL_CENTER:
400 align = (align & 12) + horizontal;
401 align = (align & 3) + (vertical << 2);
402 builder.align(align);
405 private static void foregroundColor(CellStyle cellStyle, SpreadsheetStyleBuilder builder) {
406 if (cellStyle instanceof XSSFCellStyle) {
407 XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
409 XSSFColor fillColor = xssfStyle.getFillForegroundXSSFColor();
410 XSSFColor bColor = xssfStyle.getFillBackgroundXSSFColor();
411 XSSFColor colorColor = xssfStyle.getFillForegroundColorColor();
412 XSSFColor fillcColor = xssfStyle.getFillForegroundXSSFColor();
413 // byte[] fills = fillColor.getRgbWithTint();
415 XSSFFont xssfFont = xssfStyle.getFont();
416 XSSFColor fontColor = xssfFont.getXSSFColor();
417 if (fontColor != null) {
419 byte[] rgb = fontColor.getRGBWithTint();
421 String ix = fontColor.getARGBHex();
422 RGB.Integer s = hex2Rgb(ix);
423 builder.foreground(s);
424 // if (rgb != null) {
425 // int red = rgb[0] & 0xff;
426 // int green = rgb[1] & 0xff;
427 // int blue = rgb[2] & 0xff;
428 // RGB.Integer color = s;
429 // builder.foreground(color);
435 private static void font(CellStyle style, SpreadsheetStyleBuilder builder) throws BindingException, ManyObjectsForFunctionalRelationException, ServiceException {
436 if (style instanceof XSSFCellStyle) {
437 XSSFCellStyle xssfStyle = (XSSFCellStyle) style;
438 XSSFFont xssfFont = xssfStyle.getFont();
439 String fontStyle = "Normal";
440 if (xssfFont.getBold()) {
442 if (xssfFont.getItalic()) {
443 fontStyle += "Italic";
445 } else if (xssfFont.getItalic()) {
446 fontStyle = "Italic";
447 } else if (xssfFont.getUnderline() != 0) {
449 } else if (xssfFont.getStrikeout()) {
452 Font font = new Font(xssfFont.getFontName(), xssfFont.getFontHeightInPoints(), fontStyle);
457 private static void backgroundColor(CellStyle style, SpreadsheetStyleBuilder builder) {
458 if (style instanceof XSSFCellStyle) {
459 XSSFCellStyle cellStyle = (XSSFCellStyle) style;
461 XSSFColor xssfColor = cellStyle.getFillBackgroundColorColor();
462 XSSFColor xssfC = cellStyle.getFillForegroundColorColor();
465 String hex = xssfC.getARGBHex();
468 RGB.Integer color = hex2Rgb(hex);
469 builder.background(color);
470 } else if (xssfColor != null) {
471 String hex = xssfColor.getARGBHex();
474 RGB.Integer color = hex2Rgb(hex);
475 builder.background(color);
478 // byte[] rgb = xssfColor.getRgbWithTint();
480 // if (rgb != null) {
481 // int red = rgb[0] & 0xff;
482 // int green = rgb[1] & 0xff;
483 // int blue = rgb[2] & 0xff;
485 // RGB.Integer color = new RGB.Integer(red, green, blue);
486 // builder.background(color);
491 public static RGB.Integer hex2Rgb(String colorStr) {
493 String s1 = colorStr.substring( 2, 4 );
494 String s2 = colorStr.substring( 4, 6 );
495 String s3 = colorStr.substring( 6, 8 );
496 return new RGB.Integer(
497 Integer.valueOf( s1, 16 ),
498 Integer.valueOf( s2, 16 ),
499 Integer.valueOf( s3, 16 ) );
503 public static void main(String[] args) throws Exception {
506 SheetFormulaParser p = new SheetFormulaParser(new StringReader("E888"));
507 AstValue v = p.relation();
508 System.err.println("v="+v);
511 SheetFormulaParser p = new SheetFormulaParser(new StringReader("E8:E22"));
512 AstValue v = p.relation();
513 System.err.println("v="+v);
516 SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(E8:E22)"));
517 AstValue v = p.relation();
518 System.err.println("v="+v);
521 SheetFormulaParser p = new SheetFormulaParser(new StringReader("kääk"));
522 AstValue v = p.relation();
523 System.err.println("v="+v);
527 SheetFormulaParser p = new SheetFormulaParser(new StringReader("WeekStart(SheetNumber(A1),Year)"));
528 AstValue v = p.relation();
529 System.err.println("v="+v);
533 SheetFormulaParser p = new SheetFormulaParser(new StringReader("0.001*C41"));
534 AstValue v = p.relation();
535 System.err.println("v="+v);
539 SheetFormulaParser p = new SheetFormulaParser(new StringReader("1+0.5"));
540 AstValue v = p.arithmetic_expression();
541 System.err.println("v="+v);
545 SheetFormulaParser p = new SheetFormulaParser(new StringReader("J675+0.5"));
546 AstValue v = p.arithmetic_expression();
547 System.err.println("v="+v);
551 SheetFormulaParser p = new SheetFormulaParser(new StringReader("SUM(J675+0.5)"));
552 AstValue v = p.relation();
553 System.err.println("v="+v);
557 SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(J711=0,0,J675+0.5+0.05)"));
558 AstValue v = p.relation();
559 System.err.println("v="+v);
563 SheetFormulaParser p = new SheetFormulaParser(new StringReader("B:B"));
564 AstValue v = p.relation();
565 System.err.println("v="+v);
569 SheetFormulaParser p = new SheetFormulaParser(new StringReader("+SUMIF(B:B,E9,C:C)"));
570 AstValue v = p.relation();
571 System.err.println("v="+v);
575 SheetFormulaParser p = new SheetFormulaParser(new StringReader("A(B())"));
576 AstValue v = p.relation();
577 System.err.println("v="+v);
581 SheetFormulaParser p = new SheetFormulaParser(new StringReader("+VLOOKUP(ROUND(C154*100, 0),$M$3:$T$34,C$2,TRUE)"));
582 AstValue v = p.relation();
583 System.err.println("v="+v);
587 SheetFormulaParser p = new SheetFormulaParser(new StringReader("LINEST(N37:N42,M37:M42^{1,2},TRUE,1)"));
588 AstValue v = p.relation();
589 System.err.println("v="+v);
593 SheetFormulaParser p = new SheetFormulaParser(new StringReader("IF(K16>0,(SUM(K614))/K16,)"));
594 AstValue v = p.relation();
595 System.err.println("v="+v);
599 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))))"));
600 AstValue v = p.relation();
601 System.err.println("v="+v);
602 SpreadsheetBook book = new SpreadsheetBook("http:/");
603 Object o = v.accept(new CellValueVisitor(SpreadsheetEvaluationEnvironment.getInstance(book), null));
604 System.err.println("o="+o);