X-Git-Url: https://gerrit.simantics.org/r/gitweb?a=blobdiff_plain;f=bundles%2Forg.simantics.spreadsheet.graph%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Fgraph%2FExcelImport.java;fp=bundles%2Forg.simantics.spreadsheet.graph%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Fgraph%2FExcelImport.java;h=81aa67eaae38d20a9b7421d713414a874c416872;hb=969bd23cab98a79ca9101af33334000879fb60c5;hp=0000000000000000000000000000000000000000;hpb=866dba5cd5a3929bbeae85991796acb212338a08;p=simantics%2Fplatform.git diff --git a/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/ExcelImport.java b/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/ExcelImport.java new file mode 100644 index 000000000..81aa67eaa --- /dev/null +++ b/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/ExcelImport.java @@ -0,0 +1,591 @@ +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); + } + + } + +}