// Create a new workbook Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("F7:G8").getInterior().setColor(Color.FromArgb(59, 92, 170)); worksheet.getRange("F7:G8").getFont().setColor(Color.GetWhite()); worksheet.getRange("F7").setValue("Input:"); worksheet.getRange("G7").setValue(300); worksheet.getRange("F8").setValue("Output:"); worksheet.getRange("G8").setFormula("=IF(G7<=250, \"A result\", \"B result\")"); worksheet.getRange("H8").setValue("=IF(G7<=250, \"A result\", \"B result\")"); worksheet.getRange("H8").getFont().setColor(Color.GetRed()); IShape shapeStart = worksheet.getShapes().addShape(AutoShapeType.Rectangle, worksheet.getRange("B3:D4")); shapeStart.getTextFrame().getTextRange().setText("Start"); shapeStart.getTextFrame().setVerticalAnchor(VerticalAnchor.AnchorMiddle); shapeStart.getTextFrame().setHorizontalAnchor(HorizontalAnchor.Center); // set shape formula to G8 IShape shapeResult = worksheet.getShapes().addShape(AutoShapeType.Rectangle, worksheet.getRange("B7:D8")); shapeResult.setFormula("=G8"); shapeResult.getTextFrame().setVerticalAnchor(VerticalAnchor.AnchorMiddle); shapeResult.getTextFrame().setHorizontalAnchor(HorizontalAnchor.Center); double startX = shapeStart.getLeftInPixel() + shapeStart.getWidthInPixel() / 2; double startY = shapeStart.getTopInPixel() + shapeStart.getHeightInPixel(); double endX = shapeResult.getLeftInPixel() + shapeResult.getWidthInPixel() / 2; double endY = shapeResult.getTopInPixel(); IShape connector = worksheet.getShapes().addConnectorInPixel(ConnectorType.Straight, (float)(startX), (float)(startY), (float)(endX), (float)(endY)); connector.getLine().setEndArrowheadStyle(ArrowheadStyle.Triangle); // Save to an excel file workbook.save("SetShapeFormula.xlsx");
// Create a new workbook var workbook = Workbook() val worksheet = workbook.worksheets[0] worksheet.getRange("F7:G8").interior.color = Color.FromArgb(59, 92, 170) worksheet.getRange("F7:G8").font.color = Color.GetWhite() worksheet.getRange("F7").value = "Input:" worksheet.getRange("G7").value = 300 worksheet.getRange("F8").value = "Output:" worksheet.getRange("G8").formula = "=IF(G7<=250, \"A result\", \"B result\")" worksheet.getRange("H8").value = "=IF(G7<=250, \"A result\", \"B result\")" worksheet.getRange("H8").font.color = Color.GetRed() val shapeStart = worksheet.shapes.addShape(AutoShapeType.Rectangle, worksheet.getRange("B3:D4")) shapeStart.textFrame.textRange.text = "Start" shapeStart.textFrame.verticalAnchor = VerticalAnchor.AnchorMiddle shapeStart.textFrame.horizontalAnchor = HorizontalAnchor.Center // set shape formula to G8 val shapeResult = worksheet.shapes.addShape(AutoShapeType.Rectangle, worksheet.getRange("B7:D8")) shapeResult.formula = "=G8" shapeResult.textFrame.verticalAnchor = VerticalAnchor.AnchorMiddle shapeResult.textFrame.horizontalAnchor = HorizontalAnchor.Center val startX = shapeStart.leftInPixel + shapeStart.widthInPixel / 2 val startY = shapeStart.topInPixel + shapeStart.heightInPixel val endX = shapeResult.leftInPixel + shapeResult.widthInPixel / 2 val endY = shapeResult.topInPixel val connector = worksheet.shapes.addConnectorInPixel(ConnectorType.Straight, startX.toFloat(), startY.toFloat(), endX.toFloat(), endY.toFloat()) connector.line.endArrowheadStyle = ArrowheadStyle.Triangle // Save to an excel file workbook.save("SetShapeFormula.xlsx")