Excel API Library for .NET - Sample Browser | Document Solutions | Create What-If scenarios

[フレーム]

Refer to the following example code to see how to add different scenarios in a worksheet. This demo adds multiple scenarios to the worksheet by modifying the discount rates and quantities sold.


 
 //create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open(this.GetResourceStream(@"xlsx\What-If-Analysis-Scenarios.xlsx"));
var worksheet = workbook.ActiveSheet;
// Add different scenarios which represent the different discount rates. 
// Create a scenario with less discount rates.
// The changing cells are D2:D6. The values of the changing cells are [0.05, 0.02, 0.03, 0.02, 0.05].
// The comment of the scenario is "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");
// Create a scenario with normal discount rates.
// The changing cells are D2:D6. The values of the changing cells are [0.1, 0.05, 0.05, 0.05, 0.1].
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. The values of the changing cells are [0, 0, 0, 0, 0].
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. The values of the changing cells are [1000, 1000, 1000, 1000, 1000].
var bulkQuantitySoldValues = new List<object> { 1000, 1000, 1000, 1000, 1000 };
var bulkQuantitySold = worksheet.Scenarios.Add("Bulk Quantity Sold", worksheet.Range["E2:E6"], bulkQuantitySoldValues);
// Modify the values of the changing cells of the "Less Discount Rates" scenario.
lessDiscountRatesValues[1] = 0.04;
lessDiscountRates.ChangeScenario(worksheet.Range["D2:D6"], lessDiscountRatesValues);
// After showing the "Less Discount Rates" scenario, the D4:D6 will be assigned the values [0.05, 0.04, 0.03, 0.02, 0.05].
// The fomulas(F2:K6, N6) associated with D2:D6 is recalculated and the charts are updated.
worksheet.Scenarios["Less Discount Rates"].Show();
 
// Save to an excel file
workbook.Save("CreateScenarios.xlsx");


 
 ' Create a new Workbook
Dim workbook As New Workbook
Dim fileStream As Stream = GetResourceStream("xlsx\What-If-Analysis-Scenarios.xlsx")
workbook.Open(fileStream)
Dim worksheet = workbook.ActiveSheet
' Add different scenarios which represent the different discount rates. 
' Create a scenario with less discount rates.
' The changing cells are D2:D6. The values of the changing cells are [0.05, 0.02, 0.03, 0.02, 0.05].
' The comment of the scenario is "Created by Document Solutions for Excel".
Dim lessDiscountRatesValues As New List(Of Object) From {0.05, 0.02, 0.03, 0.02, 0.05}
Dim 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. The values of the changing cells are [0.1, 0.05, 0.05, 0.05, 0.1].
Dim normalDiscountRatesValues As New List(Of Object) From {0.1, 0.05, 0.05, 0.05, 0.1}
Dim 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. The values of the changing cells are [0, 0, 0, 0, 0].
Dim sellingWithoutDiscountValues As New List(Of Object) From {0, 0, 0, 0, 0}
Dim 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. The values of the changing cells are [1000, 1000, 1000, 1000, 1000].
Dim bulkQuantitySoldValues As New List(Of Object) From {1000, 1000, 1000, 1000, 1000}
Dim bulkQuantitySold = worksheet.Scenarios.Add("Bulk Quantity Sold", worksheet.Range("E2:E6"), bulkQuantitySoldValues)
' Modify the values of the changing cells of the "Less Discount Rates" scenario.
lessDiscountRatesValues(1) = 0.04
lessDiscountRates.ChangeScenario(worksheet.Range("D2:D6"), lessDiscountRatesValues)
' After showing the "Less Discount Rates" scenario, the D4:D6 will be assigned the values [0.05, 0.04, 0.03, 0.02, 0.05].
' The formulas (F2:K6, N6) associated with D2:D6 are recalculated and the charts are updated.
worksheet.Scenarios("Less Discount Rates").Show()
 
' save to an excel file
workbook.Save("CreateScenarios.xlsx")