Precedents and Dependents with JavaScript via C++
Introduction
- Precedent cells are cells that are referred to by a formula in another Cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.
- Dependent cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is dependent on cell B5.
To make the spreadsheet easy to read, you might want to clearly show which cells on a spreadsheet are used in a formula. Similarly, you may want to extract the dependent cells of other cells.
Aspose.Cells allows you to trace cells and find out which are linked.
Tracing Precedent and Dependent Cells: Microsoft Excel
Formulas may change based on modifications made by a client. For example, if cell C1 is dependent on C3 and C4 containing a formula, and C1 is changed (so the formula is overridden), C3 and C4, or other cells, need to change to balance the spreadsheet based on business rules.
Similarly, suppose C1 contains the formula “=(B122)/(M2N32)”. I want to find the cells that C1 depends on, that is the precedent cells B1, M2, and N32.
You might need to trace the dependency of a particular cell to other cells. If business rules are embedded in formulas, we would like to find out the dependency and execute some rules based on it. Similarly, if the value of a particular cell is modified, which cells in the worksheet are impacted by that change?
Microsoft Excel allows users to trace precedents and dependents.
- On the View Toolbar, select Formula Auditing. The Formula Auditing dialog will be displayed.
- Trace Precedents:
- Select the cell that contains the formula for which you want to find precedent cells.
- To display a tracer arrow to each cell that directly provides data to the active cell, click Trace Precedents on the Formula Auditing toolbar.
- Trace formulas that reference a particular cell (dependents)
- Select the cell for which you want to identify the dependent cells.
- To display a tracer arrow to each cell that is dependent on the active cell, click Trace Dependents on the Formula Auditing toolbar.
Tracing Precedent and Dependent Cells: Aspose.Cells
Tracing Precedents
Aspose.Cells makes it easy to get precedent cells. Not only can it retrieve cells that provide data to simple formula precedents but also find cells that provide data to complex formula precedents with named ranges.
In the example below, a template excel file, Book1.xls, is used. The spreadsheet has data and formulas on the first Worksheet.
Aspose.Cells provides the Cell class' Cell.precedents() method used to trace a cell’s precedents. It returns a collection of referred areas. As you can see above, in Book1.xls, cell B7 contains a formula “=SUM(A1:A3)”. So cells A1:A3 are the precedent cells to cell B7. The following example demonstrates the tracing precedents feature using the template file Book1.xls.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Get Cell Precedents 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 (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate workbook from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access first worksheet's cells
const cells = workbook.worksheets.get(0).cells;
// Get cell B4
const cell = cells.get("B4");
if (cell) {
// Get precedents (converted from getPrecedents)
const ret = cell.precedents;
if (!ret.isNull() && ret.count > 0) {
const area = ret.get(0);
const sheetName = area.sheetName;
const startAddress = AsposeCells.CellsHelper.cellIndexToName(area.startRow, area.startColumn);
const endAddress = AsposeCells.CellsHelper.cellIndexToName(area.endRow, area.endColumn);
console.log(sheetName);
console.log(startAddress);
console.log(endAddress);
document.getElementById('result').innerHTML = `<pre>Sheet: ${sheetName}\nStart: ${startAddress}\nEnd: ${endAddress}</pre>`;
} else {
document.getElementById('result').innerHTML = '<p style="color: red;">No precedents found for the cell.</p>';
}
} else {
document.getElementById('result').innerHTML = '<p style="color: red;">Cell B4 is null.</p>';
}
});
</script>
</html>
Tracing Dependents
Aspose.Cells lets you get dependent cells in spreadsheets. Aspose.Cells not only can retrieve cells that provide data regarding a simple formula but also find cells that provide data to complex formula dependents with named ranges.
Aspose.Cells provides the Cell class' Cell.dependents(boolean) method used to trace a cell’s dependents. For example, in Book1.xlsx there are formulas: “=A1+20” and “=A1+30” in the B2 and C2 cells respectively. The following example demonstrates how to trace the dependents for the A1 cell using the template file Book1.xlsx.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells - Get Cell Dependents 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, 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();
// Loads the workbook which contains hidden external links
const workbook = new Workbook(new Uint8Array(arrayBuffer));
const cells = workbook.worksheets.get(0).cells;
const cell = cells.get("B2");
// Ensure dependents is accessed as a property (converted from getDependents)
const dependents = cell.dependents;
if (Array.isArray(dependents)) {
let out = '<p>Dependents found:</p><ul>';
for (const c of dependents) {
console.log(c.name);
out += `<li>${c.name}</li>`;
}
out += '</ul>';
resultDiv.innerHTML = out;
} else {
console.error("Dependents is not an array");
resultDiv.innerHTML = '<p style="color: red;">Dependents is not an array</p>';
}
});
</script>
</html>
Tracing Precedent and Dependent cells according to calculation chain
The above APIs of tracing precedents and dependents are according to the formula expression itself. They simply provide a convenient way for users to trace interdependencies for a few formulas. If there are a large number of formulas in the workbook and the user needs to trace precedents and dependents for every cell, they will give poor performance. For such a situation, the user should consider using Cell.precedentsInCalculation() and Cell.dependentsInCalculation(boolean) methods. These two methods trace dependencies according to the calculation chain. So, to use them, firstly you need to enable the calculation chain by FormulaSettings.enableCalculationChain(). Then you should perform full calculation for the Workbook by Workbook.calculateFormula(). After that, you can trace precedents or dependents for all those cells you need.
For some formulas, the resultant precedents may be different for precedents and precedentsInCalculation, and the resultant dependents may be different for dependents and dependentsInCalculation. For example, if cell A1’s formula is “=IF(TRUE,B2,C3)”, precedents will provide B2 and C3 as A1’s precedent. Accordingly, B2 and C3 both have the dependent A1 when checking by dependents. However, for the calculation of this formula, it is obvious that only B2 can affect the calculated result. So precedentsInCalculation will not provide C3 for A1, and dependentsInCalculation will not provide A1 for C3. Sometimes users may just have the requirement of tracing those interdependencies that actually affect the calculated result of formulas based on the current data of the Workbook, then they also need to use dependentsInCalculation/precedentsInCalculation instead of dependents/precedents.
The following example demonstrates how to trace the precedents and dependents according to the calculation chain for cells:
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells 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');
const resultDiv = document.getElementById('result');
resultDiv.innerHTML = '';
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 by opening the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet and its cells
const cells = workbook.worksheets.get(0).cells;
// Setting formulas
cells.get("A1").formula = "=B1+SUM(B1:B10)+[Book1.xls]Sheet1!B2";
cells.get("A2").formula = "=IF(TRUE,B2,B1)";
// Enable calculation chain
workbook.settings.formulaSettings.enableCalculationChain = true;
// Calculate formulas
workbook.calculateFormula();
// Collect output
let output = '';
let en = cells.get("B1").dependentsInCalculation;
output += "B1's calculation dependents:\n";
if (en && en.length !== undefined) {
for (var cell of en) {
output += (cell.name || '') + "\n";
}
} else if (en) {
// If it's an iterable but doesn't have length
for (var cell of en) {
output += (cell.name || '') + "\n";
}
}
output += "\n";
en = cells.get("B2").dependentsInCalculation;
output += "B2's calculation dependents:\n";
if (en && en.length !== undefined) {
for (var cell of en) {
output += (cell.name || '') + "\n";
}
} else if (en) {
for (var cell of en) {
output += (cell.name || '') + "\n";
}
}
output += "\n";
en = cells.get("A1").precedentsInCalculation;
output += "A1's calculation precedents:\n";
if (en && en.length !== undefined) {
for (var area of en) {
output += area.toString() + "\n";
}
} else if (en) {
for (var area of en) {
output += area.toString() + "\n";
}
}
output += "\n";
en = cells.get("A2").precedentsInCalculation;
output += "A2's calculation precedents:\n";
if (en && en.length !== undefined) {
for (var area of en) {
output += area.toString() + "\n";
}
} else if (en) {
for (var area of en) {
output += area.toString() + "\n";
}
}
resultDiv.innerHTML = '<pre>' + output.replace(/</g, '<') + '</pre>';
// Save the modified workbook and provide download link
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 Modified Excel File';
});
</script>
</html>