//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")