Adding 2-Color Scale and 3-Color Scale Conditional Formattings

2-Color Scale and 3-Color Scale Conditional Formattings are added in the same way except they are differed by ColorScale.setIs3ColorScale(boolean) method. This method is false for 2-Color Scale and true for 3-Color Scale Conditional Formattings.

The following sample code adds 2-Color and 3-Color Scale Conditional Formattings. It generates the output excel file.

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");
const filePath = path.join(dataDir, "sample.xlsx");

// Create workbook
const workbook = new AsposeCells.Workbook();

// Access first worksheet
const worksheet = workbook.getWorksheets().get(0);

// Add some data in cells
worksheet.getCells().get("A1").putValue("2-Color Scale");
worksheet.getCells().get("D1").putValue("3-Color Scale");

for (let i = 2; i <= 15; i++) {
    worksheet.getCells().get("A" + i).putValue(i);
    worksheet.getCells().get("D" + i).putValue(i);
}

// Adding 2-Color Scale Conditional Formatting
let ca = AsposeCells.CellArea.createCellArea("A2", "A15");

let idx = worksheet.getConditionalFormattings().add();
let fcc = worksheet.getConditionalFormattings().get(idx);
fcc.addCondition(AsposeCells.FormatConditionType.ColorScale);
fcc.addArea(ca);

let fc = worksheet.getConditionalFormattings().get(idx).get(0);
fc.getColorScale().setIs3ColorScale(false);
fc.getColorScale().setMaxColor(AsposeCells.Color.LightBlue);
fc.getColorScale().setMinColor(AsposeCells.Color.LightGreen);

// Adding 3-Color Scale Conditional Formatting
ca = AsposeCells.CellArea.createCellArea("D2", "D15");

idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
fcc.addCondition(AsposeCells.FormatConditionType.ColorScale);
fcc.addArea(ca);

fc = worksheet.getConditionalFormattings().get(idx).get(0);
fc.getColorScale().setIs3ColorScale(true);
fc.getColorScale().setMaxColor(AsposeCells.Color.LightBlue);
fc.getColorScale().setMidColor(AsposeCells.Color.Yellow);
fc.getColorScale().setMinColor(AsposeCells.Color.LightGreen);

// Save the workbook
workbook.save(path.join(dataDir, "output_out.xlsx"));