Set Conditional Formats of Excel and ODS files
Introduction
Conditional formatting is an advanced feature that allows you to apply formats to a cell or range of cells and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 500. When the value of the cell meets the condition, the specified format is applied to the cell. If the value of the cell does not meet the format condition, the cell’s default formatting is used. In Microsoft Excel, select Format, then Conditional Formatting to open the Conditional Formatting dialog.
Aspose.Cells supports applying conditional formatting to cells at runtime. This article explains how. It also explains how to calculate the color used by Excel for color scale conditional formatting.
Applying Conditional Formatting
Aspose.Cells supports conditional formatting in several ways:
- Using designer spreadsheet
- Using the copy method.
- Creating conditional formatting at runtime.
Using Designer Spreadsheet
Developers can create a designer spreadsheet that contains conditional formatting in Microsoft Excel and then open that spreadsheet with Aspose.Cells. Aspose.Cells loads and saves the designer spreadsheet, keeping any conditional formatting setting.
Using the Copy Method
Aspose.Cells allows developers to copy conditional format settings from one cell to another in the worksheet by calling the Range.copy() method.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Extract Conditional Formatting Icon Image</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Worksheet, Cell } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
const downloadLink = document.getElementById('downloadLink');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Open workbook from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get the first worksheet in the workbook
const sheet = workbook.worksheets.get(0);
// Get the A1 cell
const cell = sheet.cells.get("A1");
// Get the conditional formatting result object
const cfr = cell.conditionalFormattingResult;
// Get the icon set
const icon = cfr.conditionalFormattingIcon;
// Get image data from the icon
const imageData = icon.imageData;
// Create a blob and provide download link for the image
const blob = new Blob([imageData], { type: "image/jpeg" });
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'imgIcon.out.jpg';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Image File';
resultDiv.innerHTML = '<p style="color: green;">Image extracted successfully! Click the download link to save the image.</p>';
});
</script>
</html>
Applying Conditional Formatting at Runtime
Aspose.Cells lets you both add and remove conditional formatting at runtime. The code samples below show how to set conditional formatting:
- Instantiate a workbook.
- Add an empty conditional format.
- Set the range that the formatting should apply to.
- Define the formatting conditions.
- Save the file.
After this example comes a number of other smaller examples that show how to apply font settings, borders settings, and patterns.
Microsoft Excel 2007 added more advanced conditional formatting that Aspose.Cells also supports. The examples here illustrate how to use simple formatting, while the Microsoft Excel 2007 examples show how to apply more advanced conditional formatting.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Conditional Formatting Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
// If a file is provided, open it; otherwise create a new workbook
let workbook;
if (fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Access the first worksheet
const sheet = workbook.worksheets.get(0);
// Adds an empty conditional formatting
const index = sheet.conditionalFormattings.count;
const fcs = sheet.conditionalFormattings.get(index);
// Sets the conditional format range.
let ca = AsposeCells.CellArea.createCellArea(0, 0, 0, 0);
fcs.addArea(ca);
ca = AsposeCells.CellArea.createCellArea(1, 1, 1, 1);
fcs.addArea(ca);
// Adds condition.
const conditionIndex = fcs.addCondition(AsposeCells.FormatConditionType.CellValue, AsposeCells.OperatorType.Between, "=A2", "100");
// Adds condition.
const conditionIndex2 = fcs.addCondition(AsposeCells.FormatConditionType.CellValue, AsposeCells.OperatorType.Between, "50", "100");
// Sets the background color.
const fc = fcs.get(conditionIndex);
fc.style.backgroundColor = AsposeCells.Color.Red;
// Saving the Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Conditional formatting applied. Click the download link to get the modified file.</p>';
});
</script>
</html>
Set Font
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Extract Conditional Formatting Icon Image</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Worksheet, Cell } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get the first worksheet in the workbook
const sheet = workbook.worksheets.get(0);
// Get the A1 cell
const cell = sheet.cells.get("A1");
// Get the conditional formatting result object
const cfr = cell.conditionalFormattingResult;
// Get the icon set
const icon = cfr.conditionalFormattingIcon;
// Get the image data from the icon
const imageData = icon.imageData;
// Create a Blob and provide a download link for the image
const blob = new Blob([imageData], { type: 'image/jpeg' });
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'imgIcon.out.jpg';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Image';
resultDiv.innerHTML = '<p style="color: green;">Image extracted successfully! Click the download link to save the image.</p>';
});
</script>
</html>
Set Border
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Conditional Formatting Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, CellArea, FormatConditionType, OperatorType, BorderType, CellBorderType, Color } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
// This example creates a new workbook, adds conditional formatting and offers the result for download.
const workbook = new Workbook();
const sheet = workbook.worksheets.get(0);
// Adds an empty conditional formatting
const index = sheet.conditionalFormattings.add();
const fcs = sheet.conditionalFormattings.get(index);
// Sets the conditional format range.
const ca = CellArea.createCellArea(0, 0, 5, 3);
fcs.addArea(ca);
// Adds condition.
const conditionIndex = fcs.addCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");
// Sets the borders' line style to dashed and colors
const fc = fcs.get(conditionIndex);
fc.style.borders.get(BorderType.LeftBorder).lineStyle = CellBorderType.Dashed;
fc.style.borders.get(BorderType.RightBorder).lineStyle = CellBorderType.Dashed;
fc.style.borders.get(BorderType.TopBorder).lineStyle = CellBorderType.Dashed;
fc.style.borders.get(BorderType.BottomBorder).lineStyle = CellBorderType.Dashed;
fc.style.borders.get(BorderType.LeftBorder).color = new Color(0, 255, 255);
fc.style.borders.get(BorderType.RightBorder).color = new Color(0, 255, 255);
fc.style.borders.get(BorderType.TopBorder).color = new Color(0, 255, 255);
fc.style.borders.get(BorderType.BottomBorder).color = new Color(255, 255, 0);
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Conditional formatting added successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Set Pattern
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
<meta charset="utf-8" />
</head>
<body>
<h1>Add Conditional Formatting Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const file = fileInput.files && fileInput.files.length ? fileInput.files[0] : null;
// Instantiate workbook from uploaded file or create a new one
let workbook;
if (file) {
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Access the first worksheet
const sheet = workbook.worksheets.get(0);
// Adds an empty conditional formatting
const index = sheet.conditionalFormattings.add();
const fcs = sheet.conditionalFormattings.get(index);
// Sets the conditional format range.
const ca = AsposeCells.CellArea.createCellArea(0, 0, 5, 3);
fcs.addArea(ca);
// Adds condition.
const conditionIndex = fcs.addCondition(AsposeCells.FormatConditionType.CellValue, AsposeCells.OperatorType.Between, "50", "100");
const fc = fcs.get(conditionIndex);
// Apply style using property assignments (getter/setter conversion)
fc.style.pattern = AsposeCells.BackgroundType.ReverseDiagonalStripe;
fc.style.foregroundColor = new AsposeCells.Color(255, 255, 0);
fc.style.backgroundColor = new AsposeCells.Color(0, 255, 255);
// Save workbook to browser downloadable file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Result Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Conditional formatting added. Click the download link to get the modified file.</p>';
});
</script>
</body>
</html>
Advance topics
- Adding 2-Color Scale and 3-Color Scale Conditional Formattings
- Apply Conditional Formatting in Worksheets
- Apply Shading to Alternate Rows and Columns with Conditional Formatting
- Generate Conditional Formatting DataBars Images
- Get Icon Sets, Data Bars or Color Scales Objects used in Conditional Formatting