Scenarios

xlsgen > overview > Scenarios

Scenarios in Excel

Scenarios is a feature in Excel for testing values in target cells in a spreadsheet where one or more input values are changed. In Excel, this feature can be accessed by going to the Tool / Scenarios menu option if you are using Excel 2003 or an older release of Excel, and can be accessed by going to the Data ribbon tab, What-if analysis and Scenarios menu option if you are using Excel 2007 or a more recent version of Excel.

Scenarios differ significantly over what-if tables in the sense that what-if tables are designed to test changes in formula values when one or more of its parameter are tested against other values expressed in a table, whereas scenarios input values are cells, and resulting values are not expressed in a table.

Scenarios in Excel

xlsgen supports the following scenarios capabilities :

creation
automatic conversion in XLS to XLSX scenarios

Creating scenarios

Each sheet can hold one or more scenarios, defined by their names, a number of input values, and optionally result cells.

Java code

worksheet.putNumber(2,3,  1);
worksheet.putNumber(3,3,  10);
worksheet.putNumber(4,3,  100);

worksheet.putFormula(8,3, "=SUM(C2:C4)");

// one scenario
XlsScenario s1 = worksheet.NewScenario();
s1.putScenarioName("scenario 1");
s1.putScenarioComment("my scenario!");
s1.putScenarioResultCellsRange("C8:E8");

// one input value to be tested
XlsScenarioValue sv1 = s1.ScenarioAddValue();
sv1.putScenarioValueRow(3);
sv1.putScenarioValueColumn(3);
sv1.putScenarioValueAsFloat(20); // replace 10 with 20 (in the scenario only)

VB code

worksheet.Number(2,3) = 1
worksheet.Number(3,3) = 10
worksheet.Number(4,3) = 100

worksheet.Formula(8,3) = "=SUM(C2:C4)"

' one scenario
Dim s1 As IXlsScenario 
Set s1 = worksheet.NewScenario
s1.ScenarioName = "scenario 1"
s1.ScenarioComment = "my scenario!"
s1.ScenarioResultCellsRange = "C8:E8"

' one input value to be tested
Dim sv1 As IXlsScenarioValue 
Set sv1 = s1.ScenarioAddValue()
sv1.ScenarioValueRow = 3
sv1.ScenarioValueColumn = 3
sv1.ScenarioValueAsFloat = 20 ' replace 10 with 20 (in the scenario only)

C# code

worksheet.set_Number(2,3,  1);
worksheet.set_Number(3,3,  10);
worksheet.set_Number(4,3,  100);

worksheet.set_Formula(8,3, "=SUM(C2:C4)");

// one scenario
IXlsScenario s1 = worksheet.NewScenario();
s1.ScenarioName = "scenario 1";
s1.ScenarioComment = "my scenario!";
s1.ScenarioResultCellsRange = "C8:E8";

// one input value to be tested
IXlsScenarioValue sv1 = s1.ScenarioAddValue();
sv1.ScenarioValueRow = 3;
sv1.ScenarioValueColumn = 3;
sv1.ScenarioValueAsFloat = 20; // replace 10 with 20 (in the scenario only)

C++ code

worksheet->Number[2][3] = 1;
worksheet->Number[3][3] = 10;
worksheet->Number[4][3] = 100;

worksheet->Formula[8][3] = L"=SUM(C2:C4)";

// one scenario
xlsgen::IXlsScenarioPtr s1 = worksheet->NewScenario();
s1->ScenarioName = L"scenario 1";
s1->ScenarioComment = L"my scenario!";
s1->ScenarioResultCellsRange = L"C8:E8";

// one input value to be tested
xlsgen::IXlsScenarioValuePtr sv1 = s1->ScenarioAddValue();
sv1->ScenarioValueRow = 3;
sv1->ScenarioValueColumn = 3;
sv1->ScenarioValueAsFloat = 20; // replace 10 with 20 (in the scenario only)