Scenarios

Scenarios are sets of values that can substitute automatically on your worksheet and determine possible results of the formulas. You can switch between these scenarios to view the different results. DsExcel enables you to create and save different scenarios using Scenarios property of IWorksheet interface.

DsExcel provides the following methods to add and work with the scenarios:

Add method of IScenarios interface creates a new scenario and adds it to the list of scenarios available for the current worksheet. Add method consists of the following parameters that allow you to define the scenario:
- name: Name of the scenario.
- changingCells: A Range object that refers to the changing cells for the scenario. The values correspond to changingCells one by one. If the changingCells are "B2:C3," the corresponding values should be given in the order of B2, C2, B3, and C3.
- values: A list of values that contains the scenario values for the cells in ChangingCells. If this parameter is null, the scenario values will be the current values in the cells in ChangingCells.
- comment: A string that specifies comment text for the scenario. If this parameter is null, DsExcel will add the author's name (as Document Solutions for Excel) and date automatically.
- locked: Locks the scenario to prevent changes. The default value is True.
- hidden: Hides the scenario. The default value is False.
!type=note
Note: DsExcel also provides the following properties:
- Hidden and Locked properties of IScenario interface that hides and locks the scenario respectively.
- AllowEditingScenarios property of IProtectionSettings interface that sets whether you can edit scenarios on a protected worksheet or not.
These properties work in Microsoft Excel when the worksheet is protected, but they will work in DsExcel without any restrictions. You can customize the operation depending on Protection property of IWorksheet interface and values of these two properties.
Show method of IScenario interface displays the scenario. This method replaces the values in the worksheet with the values of the changing cells, and the formulas that reference the changing cells will be recalculated. If the calculation engine is off, the formulas will not be recalculated.
Delete method of IScenario interface deletes the scenario.
ChangeScenario method of IScenario interface changes the scenario to have a new set of changing cells and scenario values.

!type=note
Note: Microsoft Excel supports up to 32 changing cells, but DsExcel has no limit. Hence, it is not possible to open the Changing cells dialog box when there are more than 32 changing cells using Microsoft Excel.

Create Scenario

Refer to the following example code to create and add scenarios to the worksheet:

// Create a new workbook.
var workbook = new Workbook();

// Open an Excel file.
workbook.Open("What-If-Analysis-Scenarios.xlsx");

// Get the active sheet.
var worksheet = workbook.ActiveSheet;

// Create and add different scenarios which represent the different discount rates. 
// Create a scenario with less discount rates.
// The changing cells are D2:D6.
var lessDiscountRatesValues = new List<object> { 0.05, 0.02, 0.03, 0.02, 0.05 };
var lessDiscountRates = worksheet.Scenarios.Add("Less Discount Rates", worksheet.Range["D2:D6"], lessDiscountRatesValues);

// Create a scenario with normal discount rates.
// The changing cells are D2:D6.
var normalDiscountRatesValues = new List<object> { 0.1, 0.05, 0.05, 0.05, 0.1 };
var normalDiscountRates = worksheet.Scenarios.Add("Normal Discount Rates", worksheet.Range["D2:D6"], normalDiscountRatesValues);

// Create a scenario with selling without discount.
// The changing cells are D2:D6.
var sellingWithoutDiscountValues = new List<object> { 0, 0, 0, 0, 0 };
var sellingWithoutDiscount = worksheet.Scenarios.Add("Selling Without Discount", worksheet.Range["D2:D6"], sellingWithoutDiscountValues);

// Create a scenario with bulk quantity sold.
// The changing cells are E2:E6.
var bulkQuantitySoldValues = new List<object> { 1000, 1000, 1000, 1000, 1000 };
var bulkQuantitySold = worksheet.Scenarios.Add("Bulk Quantity Sold", worksheet.Range["E2:E6"], bulkQuantitySoldValues);

// Save the workbook.
workbook.Save("CreateScenarios.xlsx");

Modify Scenario

Refer to the following example code to modify the value of a changing cell in the "Less Discount Rates" scenario:

// Modify the value of changing cell D3 of the "Less Discount Rates" scenario.
lessDiscountRatesValues[1] = 0.04;
lessDiscountRates.ChangeScenario(worksheet.Range["D2:D6"], lessDiscountRatesValues);

The value of changing cell "D3" is updated from "0.02" to "0.04".

Before Modification	After Modification

Show Scenario

Refer to the following example codes to show "Less Discount Rates" scenario:

// Show "Less Discount Rates" scenario.
worksheet.Scenarios["Less Discount Rates"].Show();

Refer to the following example codes to show "Normal Discount Rates" scenario:

// Show "Normal Discount Rates" scenario.
worksheet.Scenarios["Normal Discount Rates"].Show();

Delete Scenario

Refer to the following example code to delete "Less Discount Rates" scenario:

// Delete "Less Discount Rates" scenario.
worksheet.Scenarios["Less Discount Rates"].Delete();

Hide Scenario

Refer to the following example code to hide "Less Discount Rates" scenario:

// Create a scenario with less discount rates and hide the scenario when the sheet is protected.
var lessDiscountRatesValues = new List<object> { 0.05, 0.02, 0.03, 0.02, 0.05 };
var lessDiscountRates = worksheet.Scenarios.Add("Less Discount Rates", worksheet.Range["D2:D6"], lessDiscountRatesValues, hidden:true);

// Or

// Hide "Less Discount Rates" scenario when the sheet is protected.
worksheet.Scenarios["Less Discount Rates"].Hidden =true;

Protect Scenario

Refer to the following example code to protect "Less Discount Rates" scenario from any changes:

// Create a scenario with less discount rates and protect the scenario from any changes when the sheet is protected.
var lessDiscountRatesValues = new List<object> { 0.05, 0.02, 0.03, 0.02, 0.05 };
var lessDiscountRates = worksheet.Scenarios.Add("Less Discount Rates", worksheet.Range["D2:D6"], lessDiscountRatesValues, locked: true);

// Or

// Protect "Less Discount Rates" scenario from any changes when the sheet is protected.
worksheet.Scenarios["Less Discount Rates"].Locked = true;

Add Comment

Refer to the following example code to add a comment to the "Less Discount Rates" scenario:

// Add comment to the scenario: "Created by Document Solutions for Excel".
var lessDiscountRatesValues = new List<object> { 0.05, 0.02, 0.03, 0.02, 0.05 };
var lessDiscountRates = worksheet.Scenarios.Add("Less Discount Rates", worksheet.Range["D2:D6"], lessDiscountRatesValues, "Created by Document Solutions for Excel");

Limitations

DsExcel does not support the following:

Scenario Summary
Merge Scenarios
Scenarios in SJS and SSJSON files, as SpreadJS does not support Scenarios

Create Scenario
Modify Scenario
Show Scenario
Delete Scenario
Hide Scenario
Protect Scenario
Add Comment
Limitations