// Create a new workbook Workbook workbook = new Workbook(); // Load template file Blood sugar tracker.xlsx from resource workbook.open(this.getResourceStream("xlsx/Blood sugar tracker.xlsx")); IWorksheet worksheet = workbook.getActiveSheet(); // Insert 19 rows worksheet.getRange("1:19").insert(); // Change the rows(2~5) RowHeight worksheet.getRows().get(1).setRowHeight(34.5); worksheet.getRows().get(2).setRowHeight(15.75); worksheet.getRows().get(3).setRowHeight(19.5); worksheet.getRows().get(4).setRowHeight(15.75); // Set values worksheet.getRange("B2").setValue("BLOOD SUGAR TRACKING"); worksheet.getRange("B4").setValue("CHARTED PROGRESS"); // Set Styles worksheet.getRange("B2").getFont().setThemeFont(ThemeFont.Major); worksheet.getRange("B2").getFont().setSize(26); worksheet.getRange("B2").getFont().setThemeColor(ThemeColor.Dark1); worksheet.getRange("B2").getFont().setTintAndShade(0.34998626667073579); worksheet.getRange("B2:D2").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Thick); worksheet.getRange("B2:D2").getBorders().get(BordersIndex.EdgeBottom).setThemeColor(ThemeColor.Accent1); worksheet.getRange("B4").getFont().setThemeFont(ThemeFont.Major); worksheet.getRange("B4").getFont().setBold(true); worksheet.getRange("B4").getFont().setSize(14); worksheet.getRange("B4").getFont().setThemeColor(ThemeColor.Dark1); worksheet.getRange("B4").getFont().setTintAndShade(0.34998626667073579); worksheet.getRange("B4:D4").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Thick); worksheet.getRange("B4:D4").getBorders().get(BordersIndex.EdgeBottom).setThemeColor(ThemeColor.Accent2); // Add chart IShape shape = worksheet.getShapes().addChart(ChartType.Line, 9.75, 100.5, 365, 203.25); shape.setName("BloodSugarProgress"); // Add Series. ISeries series1 = shape.getChart().getSeriesCollection().newSeries(); series1.setFormula("=SERIES('BLOOD SUGAR DATA'!$C$23,'BLOOD SUGAR DATA'!$B$24:$B$45,'BLOOD SUGAR DATA'!$C$24:$C$45,1)"); series1.getFormat().getLine().getColor().setObjectThemeColor(ThemeColor.Accent1); series1.getFormat().getLine().setWeight(2.5); ISeries series2 = shape.getChart().getSeriesCollection().newSeries(); series2.setFormula("=SERIES('BLOOD SUGAR DATA'!$D$23,'BLOOD SUGAR DATA'!$B$24:$B$45,'BLOOD SUGAR DATA'!$D$24:$D$45,2)"); series2.getFormat().getLine().getColor().setObjectThemeColor(ThemeColor.Accent2); series2.getFormat().getLine().setWeight(2.5); // Hidden the chart title shape.getChart().setHasTitle(false); // Hidden the category axis IAxis category_axis = shape.getChart().getAxes().item(AxisType.Category); category_axis.setVisible(false); // Set value axis units IAxis value_axis = shape.getChart().getAxes().item(AxisType.Value); value_axis.setMaximumScale(140); value_axis.setMinimumScale(50); value_axis.setMajorUnit(10); value_axis.setMinorUnit(2); // Add a rectangle shape IShape shape1 = worksheet.getShapes().addShape(AutoShapeType.Rectangle, 385, 84.75, 102, 218.25); shape1.getFill().solid(); shape1.getFill().getColor().setObjectThemeColor(ThemeColor.Accent1); shape1.getFill().getColor().setBrightness(0.6); // Set shape's border to no line shape1.getLine().getColor().setColorType(SolidColorType.None); // Set shape rich text ITextRange shape1_p1 = shape1.getTextFrame().getTextRange().getParagraphs().get(0); shape1_p1.setText("INFO:"); shape1_p1.getRuns().add(" Blood sugar levels will vary from person-to-person. There are many factors to keeping it within your normal range and isn't based on sugar alone. Consult a physician for additional information or follow-up."); ITextRange shape1_p2 = shape1.getTextFrame().getTextRange().getParagraphs().add(""); ITextRange shape1_p3 = shape1.getTextFrame().getTextRange().getParagraphs().add("More info can be found here:"); ITextRange shape1_p4 = shape1.getTextFrame().getTextRange().getParagraphs().add("https://diabetes.webmd.com/blood-glucose"); shape1.getTextFrame().getTextRange().getFont().setSize(10); shape1.getTextFrame().getTextRange().getFont().setThemeFont(ThemeFont.Minor); shape1.getTextFrame().getTextRange().getFont().getColor().setObjectThemeColor(ThemeColor.Dark1); shape1.getTextFrame().getTextRange().getFont().getColor().setBrightness(0.25); shape1_p1.getRuns().get(0).getFont().setBold(true); shape1_p3.getRuns().get(0).getFont().setBold(true); // Do table filter ITable table = worksheet.getTables().get(0); table.getRange().autoFilter(1, ">=102"); // Save to an excel file workbook.save("BloodSugarTracker.xlsx");
// Create a new workbook var workbook = Workbook() //Load template file Blood sugar tracker.xlsx from resource workbook.open(this.getResourceStream("xlsx/Blood sugar tracker.xlsx")!!) val worksheet = workbook.activeSheet //insert 19 rows worksheet.getRange("1:19").insert() //Change the rows(2~5) RowHeight worksheet.rows.get(1).rowHeight = 34.5 worksheet.rows.get(2).rowHeight = 15.75 worksheet.rows.get(3).rowHeight = 19.5 worksheet.rows.get(4).rowHeight = 15.75 //Set values worksheet.getRange("B2").value = "BLOOD SUGAR TRACKING" worksheet.getRange("B4").value = "CHARTED PROGRESS" //Set Styles worksheet.getRange("B2").font.themeFont = ThemeFont.Major worksheet.getRange("B2").font.size = 26.0 worksheet.getRange("B2").font.themeColor = ThemeColor.Dark1 worksheet.getRange("B2").font.tintAndShade = 0.34998626667073579 worksheet.getRange("B2:D2").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Thick worksheet.getRange("B2:D2").borders.get(BordersIndex.EdgeBottom).themeColor = ThemeColor.Accent1 worksheet.getRange("B4").font.themeFont = ThemeFont.Major worksheet.getRange("B4").font.bold = true worksheet.getRange("B4").font.size = 14.0 worksheet.getRange("B4").font.themeColor = ThemeColor.Dark1 worksheet.getRange("B4").font.tintAndShade = 0.34998626667073579 worksheet.getRange("B4:D4").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Thick worksheet.getRange("B4:D4").borders.get(BordersIndex.EdgeBottom).themeColor = ThemeColor.Accent2 //Add chart val shape = worksheet.shapes.addChart(ChartType.Line, 9.75, 100.5, 365.0, 203.25) shape.name = "BloodSugarProgress" //Add Series. val series1 = shape.chart.seriesCollection.newSeries() series1.formula = "=SERIES('BLOOD SUGAR DATA'!\$C$23,'BLOOD SUGAR DATA'!\$B$24:\$B$45,'BLOOD SUGAR DATA'!\$C$24:\$C$45,1)" series1.format.line.color.objectThemeColor = ThemeColor.Accent1 series1.format.line.weight = 2.5 val series2 = shape.chart.seriesCollection.newSeries() series2.formula = "=SERIES('BLOOD SUGAR DATA'!\$D$23,'BLOOD SUGAR DATA'!\$B$24:\$B$45,'BLOOD SUGAR DATA'!\$D$24:\$D$45,2)" series2.format.line.color.objectThemeColor = ThemeColor.Accent2 series2.format.line.weight = 2.5 //Hidden the chart title shape.chart.hasTitle = false //Hidden the category axis val category_axis = shape.chart.axes.item(AxisType.Category) category_axis.visible = false //Set value axis units val value_axis = shape.chart.axes.item(AxisType.Value) value_axis.maximumScale = 140.0 value_axis.minimumScale = 50.0 value_axis.majorUnit = 10.0 value_axis.minorUnit = 2.0 //Add a rectange shape val shape1 = worksheet.shapes.addShape(AutoShapeType.Rectangle, 385.0, 84.75, 102.0, 218.25) shape1.fill.solid() shape1.fill.color.objectThemeColor = ThemeColor.Accent1 shape1.fill.color.brightness = 0.6 //set shape's border to no line shape1.line.color.colorType = SolidColorType.None //set shape rich text val shape1_p1 = shape1.textFrame.textRange.paragraphs.get(0) shape1_p1.text = "INFO:" shape1_p1.runs.add(" Blood sugar levels will vary from person-to-person. There are many factors to keeping it within your normal range and isn't based on sugar alone. Consult a physician for additional information or follow-up.") val shape1_p2 = shape1.textFrame.textRange.paragraphs.add("") val shape1_p3 = shape1.textFrame.textRange.paragraphs.add("More info can be found here:") val shape1_p4 = shape1.textFrame.textRange.paragraphs.add("https://diabetes.webmd.com/blood-glucose") shape1.textFrame.textRange.font.size = 10.0 shape1.textFrame.textRange.font.themeFont = ThemeFont.Minor shape1.textFrame.textRange.font.color.objectThemeColor = ThemeColor.Dark1 shape1.textFrame.textRange.font.color.brightness = 0.25 shape1_p1.runs.get(0).font.bold = true shape1_p3.runs.get(0).font.bold = true //Do table filter val table = worksheet.tables.get(0) table.range.autoFilter(1, ">=102") // Save to an excel file workbook.save("BloodSugarTracker.xlsx")