// Create a new workbook Workbook workbook = new Workbook(); IWorksheet sheet = workbook.getWorksheets().get(0); //Conditional formatting on merge cell sheet.getRange("B2:C4").merge(); sheet.getRange("B2:C4").setValue(123); IFormatCondition cf = (IFormatCondition) sheet.getRange("B2:C4").getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 0, 0); cf.getBorders().setThemeColor(ThemeColor.Accent1); cf.getBorders().setLineStyle(BorderLineStyle.Thin); //Set cell values int[] data = new int[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; sheet.getRange("B10:B19").setValue(data); sheet.getRange("C10:C19").setValue(data); sheet.getRange("D10:D19").setValue(data); //Set conditional formattings //Color scale IColorScale cf1 = sheet.getRange("B10:B19").getFormatConditions().addColorScale(ColorScaleType.ThreeColorScale); cf1.getColorScaleCriteria().get(0).setType(ConditionValueTypes.LowestValue); cf1.getColorScaleCriteria().get(0).getFormatColor().setColor(Color.FromArgb(248, 105, 107)); cf1.getColorScaleCriteria().get(1).setType(ConditionValueTypes.Percentile); cf1.getColorScaleCriteria().get(1).setValue(50); cf1.getColorScaleCriteria().get(1).getFormatColor().setColor(Color.FromArgb(255, 235, 132)); cf1.getColorScaleCriteria().get(2).setType(ConditionValueTypes.HighestValue); cf1.getColorScaleCriteria().get(2).getFormatColor().setColor(Color.FromArgb(99, 190, 123)); //Data bar sheet.getRange("C14").setValue(-5); sheet.getRange("C17").setValue(-8); IDataBar cf2 = sheet.getRange("C10:C19").getFormatConditions().addDatabar(); cf2.getMinPoint().setType(ConditionValueTypes.AutomaticMin); cf2.getMaxPoint().setType(ConditionValueTypes.AutomaticMax); cf2.setBarFillType(DataBarFillType.Gradient); cf2.getBarColor().setColor(Color.FromArgb(0, 138, 239)); cf2.getBarBorder().getColor().setColor(Color.FromArgb(0, 138, 239)); cf2.getNegativeBarFormat().getColor().setColor(Color.FromArgb(255, 0, 0)); cf2.getNegativeBarFormat().setBorderColorType(DataBarNegativeColorType.Color); cf2.getNegativeBarFormat().getBorderColor().setColor(Color.FromArgb(255, 0, 0)); cf2.getAxisColor().setColor(Color.GetBlack()); cf2.setAxisPosition(DataBarAxisPosition.Automatic); //Icon set IIconSetCondition cf3 = sheet.getRange("D10:D19").getFormatConditions().addIconSetCondition(); cf3.setIconSet(workbook.getIconSets().get(IconSetType.Icon3Symbols)); // Save to a pdf file workbook.save("SaveConditionalFormatting.pdf");
// Create a new workbook var workbook = Workbook() val sheet = workbook.worksheets.get(0) //Conditional formatting on merge cell sheet.getRange("B2:C4").merge() sheet.getRange("B2:C4").value = 123 val cf = sheet.getRange("B2:C4").formatConditions.add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 0, 0) as IFormatCondition cf.borders.themeColor = ThemeColor.Accent1 cf.borders.lineStyle = BorderLineStyle.Thin //Set cell values val data = intArrayOf(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) sheet.getRange("B10:B19").value = data sheet.getRange("C10:C19").value = data sheet.getRange("D10:D19").value = data //Set conditional formattings //Color scale val cf1 = sheet.getRange("B10:B19").formatConditions.addColorScale(ColorScaleType.ThreeColorScale) cf1.colorScaleCriteria.get(0).type = ConditionValueTypes.LowestValue cf1.colorScaleCriteria.get(0).formatColor.color = Color.FromArgb(248, 105, 107) cf1.colorScaleCriteria.get(1).type = ConditionValueTypes.Percentile cf1.colorScaleCriteria.get(1).value = 50 cf1.colorScaleCriteria.get(1).formatColor.color = Color.FromArgb(255, 235, 132) cf1.colorScaleCriteria.get(2).type = ConditionValueTypes.HighestValue cf1.colorScaleCriteria.get(2).formatColor.color = Color.FromArgb(99, 190, 123) //Data bar sheet.getRange("C14").value = -5 sheet.getRange("C17").value = -8 val cf2 = sheet.getRange("C10:C19").formatConditions.addDatabar() cf2.minPoint.type = ConditionValueTypes.AutomaticMin cf2.maxPoint.type = ConditionValueTypes.AutomaticMax cf2.barFillType = DataBarFillType.Gradient cf2.barColor.color = Color.FromArgb(0, 138, 239) cf2.barBorder.color.color = Color.FromArgb(0, 138, 239) cf2.negativeBarFormat.color.color = Color.FromArgb(255, 0, 0) cf2.negativeBarFormat.borderColorType = DataBarNegativeColorType.Color cf2.negativeBarFormat.borderColor.color = Color.FromArgb(255, 0, 0) cf2.axisColor.color = Color.GetBlack() cf2.axisPosition = DataBarAxisPosition.Automatic //Icon set val cf3 = sheet.getRange("D10:D19").formatConditions.addIconSetCondition() cf3.iconSet = workbook.iconSets.get(IconSetType.Icon3Symbols) // Save to a pdf file workbook.save("SaveConditionalFormatting.pdf")