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.WriteRequest; 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.graph.SpreadsheetStyle.SpreadsheetStyleBuilder; import org.simantics.spreadsheet.graph.formula.SpreadsheetEvaluationEnvironment; import org.simantics.spreadsheet.graph.parser.SheetFormulaParser; import org.simantics.spreadsheet.graph.parser.ast.AstValue; import org.simantics.spreadsheet.resource.SpreadsheetResource; import org.simantics.spreadsheet.util.SpreadsheetUtils; import org.simantics.utils.DataContainer; public class ExcelImport { private static final double POINT_TO_PIXEL_RATIO = 1.33; public static void importBook(Resource container, File file) { try { DataContainer> btreeContainer = new DataContainer<>(); DataContainer bookContainer = new DataContainer<>(); Simantics.getSession().sync(new DelayedWriteRequest() { @Override public void perform(WriteGraph graph) throws DatabaseException { Layer0Utils.setDependenciesIndexingDisabled(graph, true); List 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 existingStyles = new HashMap<>(); Map existingStyles2 = new HashMap<>(); // Collection 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, SpreadsheetUtils.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 columnWidths = new ArrayList<>(); List 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 existingStyles, Map 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(); Object o = v.accept(new CellValueVisitor(SpreadsheetEvaluationEnvironment.getInstance(book), null)); System.err.println("o="+o); } } }