X-Git-Url: https://gerrit.simantics.org/r/gitweb?p=simantics%2Fplatform.git;a=blobdiff_plain;f=bundles%2Forg.simantics.spreadsheet.graph%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Fgraph%2FExcelImport.java;h=24a0a85d0ce15c204e14da3eb7b9ddef85daba48;hp=444054c8b23e088da2a1840a057aa4e2b29bc118;hb=5c67a96d34fe904b8c4b0375cd08ff1d543bf369;hpb=d70b5f7a4cdeea05c7526560303feffeb03596b0 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 index 444054c8b..24a0a85d0 100644 --- 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 @@ -1,591 +1,605 @@ -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); - } - - } - -} +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> 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, 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 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() { + + @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 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("http:/"); + Object o = v.accept(new CellValueVisitor(SpreadsheetEvaluationEnvironment.getInstance(book), null)); + System.err.println("o="+o); + } + + } + +}