Scenarios | Document Solutions for Excel, Java Edition

Scenarios

In This Topic

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 getScenarios method 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.
  Note: DsExcel also provides the following properties:
  - setHidden and setLocked methods of IScenario interface that hides and locks the scenario respectively.
  - setAllowEditingScenarios method of IProtectionSettings interface that sets whether you can edit scenarios on a protected worksheet or not.
  These methods 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 setProtection method of IWorksheet interface and values of these two methods.
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.

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:

Java	Copy Code
// 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.getActiveSheet(); // Create and add different scenarios which represent the different discount rates. // Create a scenario with less discount rates. // The changing cells are D2:D6. List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05); var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("D2:D6"), lessDiscountRatesValues); // Create a scenario with normal discount rates. // The changing cells are D2:D6. List<Object> normalDiscountRatesValues = Arrays.asList(0.1, 0.05, 0.05, 0.05, 0.1); var normalDiscountRates = worksheet.getScenarios().add("Normal Discount Rates", worksheet.getRange("D2:D6"), normalDiscountRatesValues); // Create a scenario with selling without discount. // The changing cells are D2:D6. List<Object> sellingWithoutDiscountValues = Arrays.asList(0, 0, 0, 0, 0); var sellingWithoutDiscount = worksheet.getScenarios().add("Selling Without Discount", worksheet.getRange("D2:D6"), sellingWithoutDiscountValues); // Create a scenario with bulk quantity sold. // The changing cells are E2:E6. List<Object> bulkQuantitySoldValues = Arrays.asList(1000, 1000, 1000, 1000, 1000); var bulkQuantitySold = worksheet.getScenarios().add("Bulk Quantity Sold", worksheet.getRange("E2:E6"), bulkQuantitySoldValues); // Save the workbook. workbook.save("CreateScenarios.xlsx");

Java

Copy Code

// 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.getActiveSheet();

// Create and add different scenarios which represent the different discount rates. 
// Create a scenario with less discount rates.
// The changing cells are D2:D6.
List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05);
var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("D2:D6"), lessDiscountRatesValues);

// Create a scenario with normal discount rates.
// The changing cells are D2:D6.
List<Object> normalDiscountRatesValues = Arrays.asList(0.1, 0.05, 0.05, 0.05, 0.1);
var normalDiscountRates = worksheet.getScenarios().add("Normal Discount Rates", worksheet.getRange("D2:D6"), normalDiscountRatesValues);

// Create a scenario with selling without discount.
// The changing cells are D2:D6.
List<Object> sellingWithoutDiscountValues = Arrays.asList(0, 0, 0, 0, 0);
var sellingWithoutDiscount = worksheet.getScenarios().add("Selling Without Discount", worksheet.getRange("D2:D6"), sellingWithoutDiscountValues);

// Create a scenario with bulk quantity sold.
// The changing cells are E2:E6.
List<Object> bulkQuantitySoldValues = Arrays.asList(1000, 1000, 1000, 1000, 1000);
var bulkQuantitySold = worksheet.getScenarios().add("Bulk Quantity Sold", worksheet.getRange("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:

Java	Copy Code
// Modify the value of changing cell D3 of the "Less Discount Rates" scenario. lessDiscountRatesValues.set(1, 0.04); lessDiscountRates.changeScenario(worksheet.getRange("D2:D6"), lessDiscountRatesValues);

Java

Copy Code

// Modify the value of changing cell D3 of the "Less Discount Rates" scenario.
lessDiscountRatesValues.set(1, 0.04);
lessDiscountRates.changeScenario(worksheet.getRange("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 code to show "Less Discount Rates" scenario:

Java	Copy Code
// Show "Less Discount Rates" scenario. worksheet.getScenarios().get("Less Discount Rates").show();

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

Java	Copy Code
// Show "Normal Discount Rates" scenario. worksheet.getScenarios().get("Normal Discount Rates").show();

Delete Scenario

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

Java	Copy Code
// Delete "Less Discount Rates" scenario. worksheet.getScenarios().get("Less Discount Rates").delete();

Hide Scenario

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

Java	Copy Code
// Create a scenario with less discount rates and hide the scenario when the sheet is protected. List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05); var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("D2:D6"), lessDiscountRatesValues, null, false, true); // Or // Hide "Less Discount Rates" scenario when the sheet is protected. worksheet.getScenarios().get("Less Discount Rates").setHidden(true);

Java

Copy Code

// Create a scenario with less discount rates and hide the scenario when the sheet is protected.
List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05);
var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("D2:D6"), lessDiscountRatesValues, null, false, true);

// Or

// Hide "Less Discount Rates" scenario when the sheet is protected.
worksheet.getScenarios().get("Less Discount Rates").setHidden(true);

Protect Scenario

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

Java	Copy Code
// Create a scenario with less discount rates and protect the scenario from any changes when the sheet is protected. List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05); var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("D2:D6"), lessDiscountRatesValues, null, true); // Or // Protect "Less Discount Rates" scenario from any changes when the sheet is protected. worksheet.getScenarios().get("Less Discount Rates").setLocked(true);

Java

Copy Code

// Create a scenario with less discount rates and protect the scenario from any changes when the sheet is protected.
List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05);
var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("D2:D6"), lessDiscountRatesValues, null, true);

// Or

// Protect "Less Discount Rates" scenario from any changes when the sheet is protected.
worksheet.getScenarios().get("Less Discount Rates").setLocked(true);

Add Comment

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

Java	Copy Code
// Add comment to the scenario: "Created by Document Solutions for Excel". List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05); var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("D2:D6"), lessDiscountRatesValues, "Created by Document Solutions for Excel");

Java

Copy Code

// Add comment to the scenario: "Created by Document Solutions for Excel".
List<Object> lessDiscountRatesValues = Arrays.asList(0.05, 0.02, 0.03, 0.02, 0.05);
var lessDiscountRates = worksheet.getScenarios().add("Less Discount Rates", worksheet.getRange("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